基于组内多行比较筛选分组：BigQuery实现方案

我是SQL新手，我的表包含4列：Date、A、B和Count。按A、B分组后，每组对应5个日期及相应的Count值，且所有组的这5个日期完全相同。我需要筛选出最后日期的Count与其他所有日期Count的比值均小于0.1的分组。

样例数据 #

预期查询结果 #

我的尝试代码 #

WITH joined AS
(SELECT * 
FROM table a OUTER JOIN (select * from table b where b.date = '2023-07-14')
ON a.country = b.country
AND a.city = b.city)

SELECT country, city
FROM joined
WHERE b.count / a.count < 0.1

问题说明 #

这段代码会筛选出至少存在一个日期满足最后日期Count与该日期Count比值小于0.1的分组，不符合“所有日期都满足”的要求；同时处理超大型表时，外连接会产生大量中间数据，查询效率极低。

高效解决方案 #

最优方案：利用分组聚合简化逻辑 #

因为只要最后日期的Count除以其他日期中最小的Count的比值小于0.1，那么除以其他更大的Count的比值必然也小于0.1，因此可以通过一次分组聚合完成筛选，性能最优：

WITH group_metrics AS (
    SELECT 
        A,
        B,
        -- 获取分组内最后日期的Count值
        MAX(CASE WHEN Date = (SELECT MAX(Date) FROM your_table) THEN Count END) AS last_count,
        -- 获取分组内除最后日期外的最小Count值
        MIN(CASE WHEN Date != (SELECT MAX(Date) FROM your_table) THEN Count END) AS min_other_count
    FROM your_table
    GROUP BY A, B
)
SELECT A, B
FROM group_metrics
-- 最大比值小于0.1则所有比值都满足条件
WHERE last_count / min_other_count < 0.1;

备选方案：窗口函数+分组验证 #

如果需要明确验证每个日期的比值，可使用窗口函数，适合需要保留中间验证逻辑的场景：

WITH group_stats AS (
    SELECT 
        A,
        B,
        Count,
        -- 为每个分组绑定最后日期的Count值
        MAX(CASE WHEN Date = (SELECT MAX(Date) FROM your_table) THEN Count END) OVER (PARTITION BY A, B) AS last_date_count
    FROM your_table
    -- 排除最后日期本身，只处理需要对比的日期
    WHERE Date != (SELECT MAX(Date) FROM your_table)
)
SELECT DISTINCT A, B
FROM group_stats
WHERE last_date_count / Count < 0.1
-- 确保分组内所有其他日期都满足条件（记录数等于总日期数-1）
GROUP BY A, B
HAVING COUNT(*) = (SELECT COUNT(DISTINCT Date) FROM your_table) - 1;

效率说明 #

• 最优方案仅需一次全表扫描+分组聚合，中间数据量极小，适合超大型表；
• 若表上创建(A,B,Date,Count)的复合索引，可进一步提升聚合和过滤的速度；
• 避免了自连接操作，原方案的外连接会产生与原表行数相当的中间数据，在大表上性能极差。

内容的提问来源于stack exchange，提问作者John Doyle