BigQuery多条件过滤SQL实现：按营收正负数量筛选产品月度数据

实现思路 #

• 按product和month字段分组，统计每个分组下item_revenue的正值数量、负值数量
• 筛选出满足「正值数量≥4 或 负值数量≥4」的产品+月份分组
• 将符合条件的分组和原表关联，取出对应分组的全量行数据

代码实现 #

通用CTE写法（支持MySQL 8.0+、PostgreSQL、SQL Server等主流数据库） #

WITH qualified_groups AS (
    SELECT 
        product,
        month,
        SUM(CASE WHEN item_revenue > 0 THEN 1 ELSE 0 END) AS positive_cnt,
        SUM(CASE WHEN item_revenue < 0 THEN 1 ELSE 0 END) AS negative_cnt
    FROM your_table_name -- 替换为实际表名
    GROUP BY product, month
    HAVING positive_cnt >= 4 OR negative_cnt >=4
)
SELECT t.*
FROM your_table_name t
INNER JOIN qualified_groups q
    ON t.product = q.product
    AND t.month = q.month
ORDER BY t.product, t.month;

子查询写法（兼容低版本不支持CTE的数据库） #

SELECT t.*
FROM your_table_name t
INNER JOIN (
    SELECT product, month
    FROM your_table_name
    GROUP BY product, month
    HAVING SUM(CASE WHEN item_revenue > 0 THEN 1 ELSE 0 END) >=4
        OR SUM(CASE WHEN item_revenue < 0 THEN 1 ELSE 0 END) >=4
) qualified_q
ON t.product = qualified_q.product
AND t.month = qualified_q.month
ORDER BY t.product, t.month;

注意：item_revenue为0或空值时不会被计入正负值统计，符合常规业务逻辑，如有特殊统计规则可自行调整CASE语句的判断条件。

内容的提问来源于stack exchange，提问作者Dr. Gai Guerstein