时间戳

首页 > 解决方案 > 如何正确地从嵌套表中获取唯一行？

sql - 如何正确地从嵌套表中获取唯一行？

问题描述

我有一个名为 的表，A如下所示：

|       mytimestamp       | col2 | col3 |
|=========================|======|======|
| 2019-11-27 14:30:00 UTC | xxxx | yyyy |
| ....................... | .... | .... |
| 2019-11-27 14:42:28 UTC | tttt | zzzz |

col3是REPEATED RECORD被叫的一部分cols

DATE(mytimestamp)我想用标准 SQL 展平数据，然后只抓取, col2,的唯一组合 col3。

以下是我运行的 4 个不同版本的查询以及返回的不同结果的数量：

版本 1（未嵌套）

SELECT
    DATE(mytimestamp) AS my_date
    ,col2
    ,c.col3 AS as un_col3
FROM
    my_dataset.A
    ,UNNEST(cols) AS c
-- Adding this will return different results.
-- GROUP BY
--     my_date
--     ,col2
--     ,un_col3

GROUP BY返回4236104结果的版本。没有GROUP BY返回1107740805结果的版本。

版本 2（unnest + distinct）

SELECT DISTINCT
    DATE(mytimestamp) AS my_date
    ,col2
    ,c.col3 AS as un_col3
FROM
    my_dataset.A
    ,UNNEST(cols) AS c
-- Adding this will return the same results.
-- GROUP BY
--     my_date
--     ,col2
--     ,un_col3

有或没有GROUP BY它都会返回875301结果。

版本 3（无 unnest + distinct）

SELECT DISTINCT
    DATE(mytimestamp) AS my_date
    ,col2
    ,c.col3 AS as un_col3
FROM
    my_dataset.A
    ,A.cols AS c


-- Adding this will return the same results.
--GROUP BY
--    my_date
--    ,col2
--    ,un_col3

有或没有GROUP BY它都会返回875301结果。

版本 4（没有 unnest + no distinct）

SELECT
    DATE(mytimestamp) AS my_date
    ,col2
    ,c.col3 AS as un_col3
FROM
    my_dataset.A
    ,A.cols AS c

-- Adding this will return different results.
--GROUP BY
--    my_date
--    ,col2
--    ,un_col3

GROUP BY返回4236104结果的版本。没有GROUP BY返回1107740805结果的版本。

从结果和我所知道的，因为DISTINCT适用于所有列，无论有没有GROUP BY.

但是为什么第 1 版with GROUP BY给我的结果与第 2 版或第 3 版不同。

哪个版本是我想做的正确/最佳版本？我猜它是 2 或 3，因为返回的行数较少，但为什么呢？

谢谢

标签： sqlgoogle-bigquery

解决方案

以下是 BigQuery 标准 SQL 并根据所需逻辑产生正确的结果 

#standardSQL
SELECT DISTINCT DATE(mytimestamp) mydate, col2, col3
FROM `project.dataset.table`
LEFT JOIN UNNEST(cols)

作为测试 - 如果适用于以下过度简化的虚拟数据 

WITH `project.dataset.table` AS (
  SELECT TIMESTAMP '2019-11-27 14:30:00 UTC' mytimestamp, 'xxxx' col2, [STRUCT('yyyy' AS col3), STRUCT('zzzz')] cols UNION ALL
  SELECT '2019-11-27 14:30:00 UTC', 'xxxx', [STRUCT('yyyy')] UNION ALL
  SELECT '2019-11-27 14:42:28 UTC', 'tttt', [STRUCT('zzzz'), STRUCT('zzzz')] 
)

结果将是 

Row mydate      col2    col3     
1   2019-11-27  xxxx    yyyy     
2   2019-11-27  xxxx    zzzz     
3   2019-11-27  tttt    zzzz

推荐阅读