不同字节的SQL拆分六进制字符串 [英] SQL split hexastring in different bytes

查看：146
本文介绍了不同字节的SQL拆分六进制字符串的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

  #standardSQL 
 SELECT 
时间戳，
 CAN_Frame，
 TRIM（SPLIT（CAN_Frame）[OFFSET（4）]）AS字节
 FROM 
`data.source` 
 WHERE 
 LENGTH （CAN_Frame）> 1和
 SUBSTR（TRIM（SPLIT（CAN_Frame）[OFFSET（4）]），1,2）IN（'83'，'84'）
 ORDER BY 
 timestamp DESC 
 LIMIT 
 8000

或者像这样 

  #standardSQL 
 SELECT 
 * 
 FROM（
 SELECT 
 timestamp，
 CAN_Frame，
 REGEXP_EXTRACT（CAN_Frame，r'，（[^，] +）$'）AS bytes_string，
 FROM_HEX（REPLACE（REGEXP_EXTRACT（CAN_Frame，r'，（[^，] +）$'），'' '，''））AS 
字节
 
从`data.source` 
）
 WHERE SUBSTR（bytes，1,1）IN（b'\ x83'，b'\x84'）
 
 ORDER BY timestamp DESC 
 LIMIT 8000

与结果表：

行时间戳CAN_Frame字节

1 2017-09-29 14:31:02 UTC S，48778,410,8，84 10 00 25 00 21 00 4F 84 10 00
25 00 21 00 4F

2 2017-09-29 14:30:42 UTC S，35847 ，480,8，83 80 00 01 00 03 00 0D 83 80 00
01 00 03 00 0D

3 2017-09-29 14:30:40 UTC S，34612,4B2,8，84 B2 00 27 00 08 00 03 84 B2 00
27 00 08 00 03



或

行时间戳CAN_Frame bytes_string字节

1 2017-09-29 14:31:02 UTC S ，48778,410,8，84 10 00 25 00 21 00 4F 84 10 00 25 00 21 00 4F hBAAJQAhAE8 =

2 2017-09-29 14:30:42 UTC S，35847,480， 8，83 80 00 01 00 03 00 0D 83 80 00 01 00 03 00 0D g4AAAQADAA0 =

3 2017-09-29 14:30:40 UTC S，34612,4B2,8，84 B2 00 27 00 08 00 03 84 B2 00 27 00 08 00 03 hLIAJwAIAAM =

4 2017-09-29 14:30:39 UTC S，34314,4C0,8，84 C0 00 1C 00 15 00 07 84 C0 00 1C 00 15 00 07 hMAAHAAVAAc =



我的问题是现在如何分割8字节的hexa字符串，让我有第6和第7以83开始的字节的字节，从83开始的第8个字节和从84开始的第3个字节，以及以84开始的字符串的第4和第5字节。这些数据对是具有无符号整数的lsb msb的值，需要阅读。



我希望有人能够帮助我，或者至少能够理解我的问题。



最好的问候

解决方案


$ b 

  #standardSQL 
 WITH'data.source` AS（
 SELECT'S，0,2B3,8，C2 B3 00 00 00 00 03 DE'as frame UNION ALL 
 SELECT'S，0,3FA ，6，00 E0 04 A5 00 0B'UNION ALL 
 SELECT'S，0,440,8，83 40 4E A5 00 47 00 64'UNION ALL 
 SELECT'S，0,450,8，84 50 01 12 01 19 01 B3'UNION ALL 
 SELECT'S，0,4B0,8，84 B0 4E A5 00 43 00 64'
）
 SELECT 
 frame，bytes，STRING_AGG （b，'ORDER BY p）as selected_bytes 
 FROM（
 SELECT frame，TRIM（SPLIT（frame）[OFFSET（4）]）AS字节，SUBSTR（TRIM（SPLIT（frame）[OFFSET （4）]），1,2）AS f 
 FROM`data.source` 
 WHERE SUBSTR（TRIM（SPLIT（frame）[OFFSET（4）]），1,2）IN（' UNIEST（SPLIT（bytes，''））AS b WITH OFFSET as p 
 WHERE CASE f WH（83'，'84'）
），UNNEST EN'83'THEN p IN（5，6，7）'84'时，那么p IN（2,3,4）END 
 GROUP BY帧，字节
  -  ORDER BY frame

结果是： 

 帧字节selected_bytes 
 S，0,440,8,83 40 4E A5 00 47 00 64 83 40 4E A5 00 47 00 64 47 00 64 
 S，0,450， 8，84 50 01 12 01 19 01 B3 84 50 01 12 01 19 01 B3 01 12 01 
 S，0,4B0,8，84 B0 4E A5 00 43 00 64 84 B0 4E A5 00 43 00 64 4E A5 00

更新：


字节6和7从83开始的字符串被称为Aiout

一列包含从83开始的字符串中的字节8和从84开始的字符串中的字节3 3称为Biout

一列包含以84开头的字符串中的字节4和5，称为Avout

Bvout包含以84开头的字符串中的字节6和7 

  #standardSQL 
 WITH`data.source` AS（
 SELECT 'S，0,2B3,8，C2 B3 00 00 00 00 03 DE'AS框架UNION ALL 
 SELECT'S，0,3FA，6，00 E0 04 A5 00 0B'UNION ALL 
 SELECT 'S，0,440,8，83 40 4E A5 00 47 00 64'UNION ALL 
 SELECT'S，0,450,8，84 50 01 12 01 19 01 B3'UNION ALL 
 SELECT'S，0 ，4B0,8，84 B0 4E A5 00 43 00 64'
）
 SELECT 
 frame，bytes，
 STRING_AGG（CASE when f = '83'AND p IN（5 （f = '83'和p = 7）或者（f = '84'和p = 2），则b）那么b另一个''结束''命令由p）作为Biout，
 STRING_AGG（案件当f = '84'和pin（3,4）然后b否则''结束，''命令由p） AS Avout，
 STRING_AGG（CASE当f = '84'和p IN（5,6）然后b ELSE''END，'ORDER BY p）Bvout 
 FROM（
 SELECT帧，TRIM（SPLIT（帧）[OFFSET（4）]） ES，SUBSTR（TRIM（SPLIT（frame）[OFFSET（4）]），1，2）AS f 
 FROM`data.source` 
 WHERE SUBSTR（TRIM（SPLIT（frame）[OFFSET 4）]），1，2）IN（'83'，'84'）
），UNNEST（SPLIT（bytes，''））AS b WITH OFFSET as p 
 GROUP BY frame，bytes 
 ORDER BY frame

输出为 

  frame bytes Aiout Biout Avout Bvout 
 S，0,440,8，83 40 4E A5 00 47 00 64 83 40 4E A5 00 47 00 64 47 00 64 
 S，0,450,8，84 50 01 12 01 19 01 B3 84 50 01 12 01 19 01 B3 01 12 01 19 01 
 S，0,4B0,8，84 B0 4E A5 00 43 00 64 84 B0 4E A5 00 43 00 64 4E A5 00 43 00


I've got a google bigquery looking like this: 

    #standardSQL
SELECT
  timestamp,
  CAN_Frame, 
    TRIM(SPLIT(CAN_Frame)[OFFSET(4)]) AS bytes
FROM
  `data.source`  
WHERE
  LENGTH(CAN_Frame) > 1 and
  SUBSTR(TRIM(SPLIT(CAN_Frame)[OFFSET(4)]),1,2) IN ('83', '84')
 ORDER BY
  timestamp DESC
LIMIT
  8000

or like this

#standardSQL
SELECT
*  
FROM (
 SELECT
     timestamp,
    CAN_Frame,
    REGEXP_EXTRACT(CAN_Frame, r', ([^,]+)$') AS bytes_string,
    FROM_HEX(REPLACE(REGEXP_EXTRACT(CAN_Frame, r', ([^,]+)$'), ' ', '')) AS 
bytes

  FROM `data.source`
)
WHERE SUBSTR(bytes, 1, 1) IN (b'\x83', b'\x84')

ORDER BY timestamp DESC
LIMIT 8000

with the resulting tables:

Row timestamp CAN_Frame bytes
1 2017-09-29 14:31:02 UTC S,48778,410,8, 84 10 00 25 00 21 00 4F 84 10 00 25 00 21 00 4F
2 2017-09-29 14:30:42 UTC S,35847,480,8, 83 80 00 01 00 03 00 0D 83 80 00 01 00 03 00 0D
3 2017-09-29 14:30:40 UTC S,34612,4B2,8, 84 B2 00 27 00 08 00 03 84 B2 00 27 00 08 00 03

or

Row timestamp CAN_Frame bytes_string bytes
1 2017-09-29 14:31:02 UTC S,48778,410,8, 84 10 00 25 00 21 00 4F 84 10 00 25 00 21 00 4F hBAAJQAhAE8=
2 2017-09-29 14:30:42 UTC S,35847,480,8, 83 80 00 01 00 03 00 0D 83 80 00 01 00 03 00 0D g4AAAQADAA0=
3 2017-09-29 14:30:40 UTC S,34612,4B2,8, 84 B2 00 27 00 08 00 03 84 B2 00 27 00 08 00 03 hLIAJwAIAAM=
4 2017-09-29 14:30:39 UTC S,34314,4C0,8, 84 C0 00 1C 00 15 00 07 84 C0 00 1C 00 15 00 07 hMAAHAAVAAc=

My problem and question is now how to split the 8 byte hexa string in a way that let me have the 6th and 7th byte of string beginning with 83, the 8th byte from 83 and the 3rd byte from 84 and the 4th and 5th byte of the string beginning with 84. these datapairs are values with lsb msb in unsigned int that i need to read.

i hope somebody can help me or at least understand my problem.

best regards

解决方案 

#standardSQL
WITH `data.source` AS (
  SELECT 'S,0,2B3,8, C2 B3 00 00 00 00 03 DE' AS frame UNION ALL
  SELECT 'S,0,3FA,6, 00 E0 04 A5 00 0B' UNION ALL
  SELECT 'S,0,440,8, 83 40 4E A5 00 47 00 64' UNION ALL
  SELECT 'S,0,450,8, 84 50 01 12 01 19 01 B3' UNION ALL
  SELECT 'S,0,4B0,8, 84 B0 4E A5 00 43 00 64' 
)
SELECT
  frame, bytes, STRING_AGG(b, ' ' ORDER BY p) AS selected_bytes
FROM (
  SELECT frame, TRIM(SPLIT(frame)[OFFSET(4)]) AS bytes, SUBSTR(TRIM(SPLIT(frame)[OFFSET(4)]), 1, 2) AS f
  FROM `data.source`
  WHERE SUBSTR(TRIM(SPLIT(frame)[OFFSET(4)]), 1, 2) IN ('83', '84')
), UNNEST(SPLIT(bytes, ' ')) AS b WITH OFFSET AS p
WHERE CASE f WHEN '83' THEN p IN (5, 6, 7) WHEN '84' THEN p IN (2, 3, 4) END
GROUP BY frame, bytes
-- ORDER BY frame

result is: 

frame                               bytes                       selected_bytes
S,0,440,8, 83 40 4E A5 00 47 00 64  83 40 4E A5 00 47 00 64     47 00 64
S,0,450,8, 84 50 01 12 01 19 01 B3  84 50 01 12 01 19 01 B3     01 12 01
S,0,4B0,8, 84 B0 4E A5 00 43 00 64  84 B0 4E A5 00 43 00 64     4E A5 00

Update for:

byte 6 and 7 from the string beginning with 83 called Aiout
one column contains byte 8 from the string beginning with 83 and byte 3 from the string beginning with 84 called Biout
one column contains byte 4 and 5 from the string beginning with 84 called Avout
Bvout containing byte 6 and 7 from the string beginning with 84 

#standardSQL
WITH `data.source` AS (
  SELECT 'S,0,2B3,8, C2 B3 00 00 00 00 03 DE' AS frame UNION ALL
  SELECT 'S,0,3FA,6, 00 E0 04 A5 00 0B' UNION ALL
  SELECT 'S,0,440,8, 83 40 4E A5 00 47 00 64' UNION ALL
  SELECT 'S,0,450,8, 84 50 01 12 01 19 01 B3' UNION ALL
  SELECT 'S,0,4B0,8, 84 B0 4E A5 00 43 00 64' 
)
SELECT
  frame, bytes, 
  STRING_AGG(CASE WHEN f='83' AND p IN (5, 6) THEN b ELSE '' END, ' ' ORDER BY p) AS Aiout,
  STRING_AGG(CASE WHEN (f='83' AND p=7) OR (f='84' AND p=2)  THEN b ELSE '' END, ' ' ORDER BY p) AS Biout,
  STRING_AGG(CASE WHEN f='84' AND p IN (3, 4) THEN b ELSE '' END, ' ' ORDER BY p) AS Avout,
  STRING_AGG(CASE WHEN f='84' AND p IN (5, 6) THEN b ELSE '' END, ' ' ORDER BY p) AS Bvout
FROM (
  SELECT frame, TRIM(SPLIT(frame)[OFFSET(4)]) AS bytes, SUBSTR(TRIM(SPLIT(frame)[OFFSET(4)]), 1, 2) AS f
  FROM `data.source`
  WHERE SUBSTR(TRIM(SPLIT(frame)[OFFSET(4)]), 1, 2) IN ('83', '84')
), UNNEST(SPLIT(bytes, ' ')) AS b WITH OFFSET AS p
GROUP BY frame, bytes
ORDER BY frame

with output as 

frame                               bytes                   Aiout   Biout   Avout   Bvout
S,0,440,8, 83 40 4E A5 00 47 00 64  83 40 4E A5 00 47 00 64 47 00   64
S,0,450,8, 84 50 01 12 01 19 01 B3  84 50 01 12 01 19 01 B3         01      12 01   19 01
S,0,4B0,8, 84 B0 4E A5 00 43 00 64  84 B0 4E A5 00 43 00 64         4E      A5 00   43 00

这篇关于不同字节的SQL拆分六进制字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆