BigQuery中如何实现count(distinct...) over(partition by...order by)语法

解决方案 #

你可以用ARRAY_AGG+ARRAY_LENGTH的组合绕过BigQuery的窗口函数限制，修改后的查询如下：

SELECT
    SE.MARKET_ID,
    SE.LOCAL_POS_ID,
    SE.BC_ID,
    LEFT(SE.SALE_CREATION_DATE,6) AS DATE_ID_MONTH,
    -- 年初到当月累计去重活跃客户，逻辑和你原有预期完全一致
    ARRAY_LENGTH(
        ARRAY_AGG(DISTINCT CASE WHEN FLAG THEN SE.CUST_ID END IGNORE NULLS)
        OVER (
            PARTITION BY SE.MARKET_ID, SE.LOCAL_POS_ID, SE.BC_ID, LEFT(SE.SALE_CREATION_DATE,4)
            ORDER BY LEFT(SE.SALE_CREATION_DATE,6)
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        )
    ) AS NB_ACTIVE_CUSTOMERS
    -- 其余50个基于不同flag的指标可直接按照相同格式新增，无需额外WITH子句，示例：
    -- ARRAY_LENGTH(ARRAY_AGG(DISTINCT CASE WHEN FLAG_NEW THEN SE.CUST_ID END IGNORE NULLS) OVER (相同分区排序逻辑)) AS NEW_METRIC
FROM SE
GROUP BY SE.MARKET_ID, SE.LOCAL_POS_ID, SE.BC_ID, LEFT(SE.SALE_CREATION_DATE,6)

方案说明 #

1. 核心逻辑是先用ARRAY_AGG(DISTINCT ... IGNORE NULLS)把窗口范围内符合flag条件的客户ID去重汇总为数组，再用ARRAY_LENGTH获取数组长度，等价于你原本要实现的COUNT(DISTINCT)累计计数效果，同时避开了语法限制
2. 全查询仅扫描一次原表，不需要额外中间表或多个WITH子句，适配5亿行级别的大数据量计算场景
3. 如果你可接受极小的近似误差，想要进一步提升计算性能，也可以替换为BigQuery原生支持的近似去重窗口函数写法：

   APPROX_COUNT_DISTINCT(CASE WHEN FLAG THEN SE.CUST_ID END)
   OVER (
       PARTITION BY SE.MARKET_ID, SE.LOCAL_POS_ID, SE.BC_ID, LEFT(SE.SALE_CREATION_DATE,4)
       ORDER BY LEFT(SE.SALE_CREATION_DATE,6)
   ) AS NB_ACTIVE_CUSTOMERS
   

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