Oracle滚动窗口Partition by+Count Distinct报错ORA-30487/ORA-01427求助

需求 #

按is_approved、is_activated_card、is_valid_card、is_first_txn四个字段分区，统计每个分区内，以当前login_created_at为终点的30天滚动窗口内，去重的account_id数量。

第一次尝试及报错 #

尝试用窗口函数实现，触发ORA-30487: ORDER BY not allowed here错误：

select login_created_at, count(distinct account_id) over 
            ( partition by is_approved,
            is_activated_card,
            is_valid_card,
            is_first_txn
            order by login_created_at
            RANGE BETWEEN INTERVAL '29' DAY PRECEDING AND CURRENT ROW)
from data

报错原因 #

Oracle的窗口函数中，count(distinct)不支持结合ORDER BY和窗口范围（RANGE/ROWS）使用，这类聚合函数的去重语法无法在滑动窗口场景下生效。

第二次尝试及报错 #

改用关联子查询实现，触发ORA-01427: single-row subquery returns more than one row错误：

SELECT 
    day, IS_APPROVED, IS_ACTIVATED_CARD, IS_VALID_CARD, IS_FIRST_TXN,
    (
        SELECT
            COUNT(DISTINCT account_id)
        FROM
            data t2
        WHERE
                t2.login_created_at >= t.day - 29
            AND t2.login_created_at <= t.day
        group by             
        is_approved,
        is_activated_card,
        is_valid_card,
        is_first_txn
           ) AS login_rolling_30
FROM
    t
WHERE
    t.day = trunc(sysdate - 1);

报错原因 #

子查询中使用GROUP BY会返回多个分组的统计结果，但主查询的每行只能接收单个值；同时子查询未关联主查询的分区字段，无法匹配当前行的分组条件，导致返回多行结果。

可行解决方案 #

方案1：用窗口函数结合DENSE_RANK实现去重统计 #

利用DENSE_RANK给每个分区内的account_id在滚动窗口内标记唯一排名，再统计排名为1的数量（即去重后的数量）：

WITH ranked_data AS (
    SELECT 
        login_created_at,
        account_id,
        is_approved,
        is_activated_card,
        is_valid_card,
        is_first_txn,
        DENSE_RANK() OVER (
            PARTITION BY is_approved, is_activated_card, is_valid_card, is_first_txn, account_id
            ORDER BY login_created_at
        ) AS rnk
    FROM data
)
SELECT 
    login_created_at,
    is_approved,
    is_activated_card,
    is_valid_card,
    is_first_txn,
    SUM(CASE WHEN rnk = 1 THEN 1 ELSE 0 END) OVER (
        PARTITION BY is_approved, is_activated_card, is_valid_card, is_first_txn
        ORDER BY login_created_at
        RANGE BETWEEN INTERVAL '29' DAY PRECEDING AND CURRENT ROW
    ) AS distinct_account_cnt
FROM ranked_data;

方案2：修正关联子查询，关联分区字段 #

调整子查询，确保只返回当前行分组对应的统计结果，去掉子查询的GROUP BY，改为在WHERE条件中关联主查询的分区字段：

SELECT 
    t.day, 
    t.IS_APPROVED, 
    t.IS_ACTIVATED_CARD, 
    t.IS_VALID_CARD, 
    t.IS_FIRST_TXN,
    (
        SELECT COUNT(DISTINCT t2.account_id)
        FROM data t2
        WHERE 
            t2.login_created_at >= t.day - INTERVAL '29' DAY
            AND t2.login_created_at <= t.day
            AND t2.is_approved = t.is_approved
            AND t2.is_activated_card = t.is_activated_card
            AND t2.is_valid_card = t.is_valid_card
            AND t2.is_first_txn = t.is_first_txn
    ) AS login_rolling_30
FROM t
WHERE t.day = TRUNC(SYSDATE - 1);

示例数据 #

ACCOUNT_ID| LOGIN_CREATED_AT   | LOGIN_ID
42890      26-JAN-22  00:00:00  F7EB1B3E-070E-45D2-9B75-46D459E4A9F4
166568     26-JAN-22  00:00:00  85F80D5E-DEA2-4A4E-ABB9-46D97069E87B
260237     26-JAN-22  00:00:00  746DD48C-00A6-4FE5-B93B-46DE9E4B8D9F
145998     26-JAN-22  00:00:00  8BBDF71A-DD4E-4983-B43F-46DF6F27361F
148912     26-JAN-22  00:00:00  BE6F37CE-C4B0-4749-976D-46E1EA07E95A
189686     26-JAN-22  00:00:00  2A084C2A-F45D-497F-9A81-46E2ADACF89F

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