基于BigQuery优化date_2对应date_1满足date_1>date_2的计数实现问询

我有一张包含date_1和date_2字段的表，样例数据如下：

我的需求是统计每个不同的date_2值对应的满足date_1 > date_2的date_1记录数量，期望输出如下：

我自己写了一段BigQuery代码，但不确定是不是最优实现，想请教有没有更高效的写法？

当前代码：

WITH sample_table AS (
 SELECT DATE('2018-12-08') AS date_1, DATE('2018-12-07') AS date_2, 'AAA' as uid
 UNION ALL
 SELECT DATE('2018-12-09') AS date_1, DATE('2018-12-07') AS date_2, 'AAA' as uid
 UNION ALL
 SELECT DATE('2018-12-13') AS date_1, DATE('2018-12-07') AS date_2, 'AAA' as uid
 UNION ALL
 SELECT DATE('2018-12-16') AS date_1, DATE('2018-12-07') AS date_2, 'AAA' as uid
 UNION ALL
 SELECT DATE('2018-12-14') AS date_1, DATE('2018-12-09') AS date_2, 'AAA' as uid
), distinct_date_2 AS (
 SELECT DISTINCT(date_2) AS distinct_date, uid FROM sample_table
)
SELECT distinct_date, COUNTIF(date_1 > distinct_date)
FROM sample_table
LEFT JOIN distinct_date_2 USING (uid)
GROUP BY distinct_date
ORDER BY distinct_date

你的当前写法依赖uid做关联，当uid取值重复较多时，会产生不必要的笛卡尔积，浪费计算资源。这里提供两种更高效简洁的写法，适配不同数据量场景：

方法1：交叉连接+聚合（轻量场景首选） #

这种写法直接提取所有唯一的date_2，再和全表交叉连接后统计满足条件的记录数，逻辑清晰且性能不错：

WITH sample_table AS (
 SELECT DATE('2018-12-08') AS date_1, DATE('2018-12-07') AS date_2, 'AAA' as uid
 UNION ALL
 SELECT DATE('2018-12-09') AS date_1, DATE('2018-12-07') AS date_2, 'AAA' as uid
 UNION ALL
 SELECT DATE('2018-12-13') AS date_1, DATE('2018-12-07') AS date_2, 'AAA' as uid
 UNION ALL
 SELECT DATE('2018-12-16') AS date_1, DATE('2018-12-07') AS date_2, 'AAA' as uid
 UNION ALL
 SELECT DATE('2018-12-14') AS date_1, DATE('2018-12-09') AS date_2, 'AAA' as uid
)
SELECT 
  d2.date_2,
  COUNTIF(st.date_1 > d2.date_2) AS count_of_date_1_after_date_2
FROM (SELECT DISTINCT date_2 FROM sample_table) d2
CROSS JOIN sample_table st
GROUP BY d2.date_2
ORDER BY d2.date_2

方法2：窗口函数+数组（大数据量场景） #

如果你的表数据量很大，交叉连接可能会占用过多资源，可以用窗口函数把所有date_1收集到数组中，再通过子查询统计符合条件的数量，减少数据扫描次数：

WITH sample_table AS (
 SELECT DATE('2018-12-08') AS date_1, DATE('2018-12-07') AS date_2, 'AAA' as uid
 UNION ALL
 SELECT DATE('2018-12-09') AS date_1, DATE('2018-12-07') AS date_2, 'AAA' as uid
 UNION ALL
 SELECT DATE('2018-12-13') AS date_1, DATE('2018-12-07') AS date_2, 'AAA' as uid
 UNION ALL
 SELECT DATE('2018-12-16') AS date_1, DATE('2018-12-07') AS date_2, 'AAA' as uid
 UNION ALL
 SELECT DATE('2018-12-14') AS date_1, DATE('2018-12-09') AS date_2, 'AAA' as uid
), all_dates AS (
 SELECT 
   date_2,
   ARRAY_AGG(date_1) OVER() AS all_date_1s
 FROM sample_table
)
SELECT DISTINCT
  date_2,
  (SELECT COUNT(*) FROM UNNEST(all_date_1s) d WHERE d > date_2) AS count_of_date_1_after_date_2
FROM all_dates
ORDER BY date_2

优化点说明 #

• 去掉了冗余的uid关联：原代码通过uid关联会导致同一uid下的所有date_2和全表记录重复关联，优化后的写法直接聚焦日期字段，避免了数据膨胀。
• 逻辑更清晰：两种写法都简化了CTE的使用，聚合逻辑一目了然，后续维护成本更低。
• 性能更优：方法2利用数组和窗口函数，只需要扫描全表一次，适合百万级以上数据量的场景。

内容的提问来源于stack exchange，提问作者akilesh raj