sql - 如何在大查询中应用 : count(distinct . ..) over (partition by ... order by)？

Question

我目前有这个 source table .

我正在尝试获取 this second table来自第一个表，在 GCP BigQuery 上的 SQL 中。

我的查询如下:

        SELECT
            SE.MARKET_ID,
            SE.LOCAL_POS_ID,
            SE.BC_ID,
            LEFT(SE.SALE_CREATION_DATE,6) AS DATE_ID_MONTH,

            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

        FROM
            SE
        GROUP BY
            SE.MARKET_ID, SE.LOCAL_POS_ID, SE.BC_ID, LEFT(SE.SALE_CREATION_DATE,6)

但是，我得到了这个我没有成功绕过的错误:

Window ORDER BY is not allowed if DISTINCT is specified at [12:107]

我无法使用以下请求创建以前的表:

SELECT DISTINCT
        SE.MARKET_ID,
        SE.LOCAL_POS_ID,
        SE.BC_ID,
        LEFT(SE.SALE_CREATION_DATE,6) AS DATE_ID_MONTH,
        CASE
            WHEN FLAG
            THEN SE.CUST_ID
            ELSE NULL
        END AS VALID_CUST_ID
FROM
        SE

为了在那之后使用 dense_rank() 因为我还有 50 个其他指标(和 500M 行)要添加到这个表(基于其他标志的指标)而且我显然不能为每个指标创建一个 WITH，我只需要在几个 WITH 或 none 中使用它(就像我当前的查询应该做的那样)。

有人知道我该如何处理吗？

Answer 1

考虑以下方法

select * except(ids), 
  array_length(array(
    select distinct id
    from unnest(split(ids)) id
  )) as nb_active_customers, 
  format('%t', array(
    select distinct id
    from unnest(split(ids)) id
  )) as distinct_values
from (
  select market_id, local_pos_id, bc_id, date_id_month,
    string_agg('' || ids) over(partition by market_id order by date_id_month) ids
  from (
    select market_id, local_pos_id, bc_id, left(sale_creation_date,6) AS date_id_month,
      string_agg('' || cust_id) ids
    from se
    where flag = 1
    group by market_id, local_pos_id, bc_id, date_id_month
  )
) t          

如果应用于您问题中的示例数据 - 输出为