bigquery十六进制字符串到字节/ int

Question

是否有可能将由两个字节组成的十六进制字符串转换为字节/ int，而第一个字节是lsb，第二个是msb。

后处理该：

#standardSQL 
SELECT 
    timestamp, CAN_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 timestamp, CAN_Frame, TRIM(SPLIT(CAN_Frame)[OFFSET(4)]) AS bytes, 
SUBSTR(TRIM(SPLIT(CAN_Frame)[OFFSET(4)]), 1, 2) AS f 
    FROM `data.source` 
    WHERE SUBSTR(TRIM(SPLIT(CAN_Frame)[OFFSET(4)]), 1, 2) IN ('83', '84') 
), UNNEST(SPLIT(bytes, ' ')) AS b WITH OFFSET AS p 
GROUP BY timestamp,CAN_Frame, bytes 
ORDER BY timestamp desc 

我已经与在十六进制串中的每个由两个字节组成了4列（aiout，biout，AVOUT，bvout）（由于米哈伊尔Berlyant）看起来像这样：

 Aiout Biout Avout Bvout 
       00 25 00 21 00 
     03 00  0D     
        00 27 00 08 00 
       00 1C 00 15 00 
       00 25 00 21 00 
     03 00  03     
        00 2A 00 26 00 
     03 00  0D     
     00 00  07     
     04 00  04     

与像cast，格式，整数，...我总是有一个内部错误和请求无法完成。

问候

Answer 1

下面是建立在Mikhail's answer to your previous question一个例子。它占用空间分隔字节，并把它们转化成前缀为0x没有空间，可以转换为Int64字符串：

#standardSQL 
CREATE TEMP FUNCTION HexToInt(hex_string STRING) AS (
    IFNULL(SAFE_CAST(CONCAT('0x', REPLACE(hex_string, ' ', '')) AS INT64), 0) 
); 

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 *, 
    HexToInt(Aiout) AS Aiout_int64, 
    HexToInt(Biout) AS Biout_int64, 
    HexToInt(Avout) AS Avout_int64, 
    HexToInt(Bvout) AS Bvout_int64 
FROM (
    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 
); 

输出看起来是这样的：

+------------------------------------+-------------------------+-------------+-----------+-------------+-------------+-------------+-------------+-------------+-------------+ 
|    frame    |   bytes   | Aiout | Biout | Avout | Bvout | Aiout_int64 | Biout_int64 | Avout_int64 | Bvout_int64 | 
+------------------------------------+-------------------------+-------------+-----------+-------------+-------------+-------------+-------------+-------------+-------------+ 
| S,0,440,8, 83 40 4E A5 00 47 00 64 | 83 40 4E A5 00 47 00 64 |  47 00 |  64 |    |    |  18176 |   100 |   0 |   0 | 
| 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 |   0 |   1 |  4609 |  6401 | 
| 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 |   0 |   78 |  42240 |  17152 | 
+------------------------------------+-------------------------+-------------+-----------+-------------+-------------+-------------+-------------+-------------+-------------+ 

编辑：如果你想改变字节的解释顺序，例如：

#standardSQL 
CREATE TEMP FUNCTION HexReverse(hex_string STRING) AS (
    (SELECT STRING_AGG(s, ' ' ORDER BY off DESC) 
    FROM UNNEST(SPLIT(hex_string, ' ')) AS s WITH OFFSET off) 
); 

CREATE TEMP FUNCTION HexToInt(hex_string STRING) AS (
    IFNULL(SAFE_CAST(CONCAT('0x', REPLACE(HexReverse(hex_string), ' ', '')) AS INT64), 0) 
); 

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 *, 
    HexToInt(Aiout) AS Aiout_int64, 
    HexToInt(Biout) AS Biout_int64, 
    HexToInt(Avout) AS Avout_int64, 
    HexToInt(Bvout) AS Bvout_int64 
FROM (
    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 
);