Oracle SQL如何在Range窗口内实现Count(Distinct) Over分组统计

问题背景 #

需按cst_name、product_name分组，在以mm（日期日份）为基准的±2天Range窗口范围内，统计distinct的yyyydd数量。但Oracle SQL不支持COUNT(DISTINCT ...) OVER(...)语法，直接去除DISTINCT会统计重复日期数据，不符合需求。

测试数据 #

CREATE TABLE test_data (
    cst_name VARCHAR2(50),
    product_name VARCHAR2(50),
    yyyydd NUMBER(8),
    mm NUMBER(2)
);

INSERT INTO test_data VALUES ('张三', 'A产品', 20240501, 1);
INSERT INTO test_data VALUES ('张三', 'A产品', 20240501, 1);
INSERT INTO test_data VALUES ('张三', 'A产品', 20240502, 2);
INSERT INTO test_data VALUES ('张三', 'A产品', 20240504, 4);
INSERT INTO test_data VALUES ('李四', 'B产品', 20240610, 10);
INSERT INTO test_data VALUES ('李四', 'B产品', 20240612, 12);
INSERT INTO test_data VALUES ('李四', 'B产品', 20240612, 12);
INSERT INTO test_data VALUES ('李四', 'B产品', 20240613, 13);

错误SQL示例 #

-- Oracle报错：不支持COUNT(DISTINCT)与OVER窗口函数结合使用
SELECT
    cst_name,
    product_name,
    yyyydd,
    mm,
    COUNT(DISTINCT yyyydd) OVER (
        PARTITION BY cst_name, product_name
        ORDER BY mm
        RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING
    ) AS distinct_date_count
FROM test_data;

期望结果 #

可行解决方案 #

方案1：子查询去重+窗口函数+关联回原表 #

先对分组内的日期去重，再计算窗口内的日期数量，最后关联回原表保留所有原始数据：

WITH distinct_dates AS (
    SELECT DISTINCT
        cst_name,
        product_name,
        yyyydd,
        mm
    FROM test_data
),
window_count AS (
    SELECT
        cst_name,
        product_name,
        yyyydd,
        mm,
        COUNT(*) OVER (
            PARTITION BY cst_name, product_name
            ORDER BY mm
            RANGE BETWEEN 2 PRECEDING AND 2 FOLLOWING
        ) AS distinct_date_count
    FROM distinct_dates
)
SELECT
    t.cst_name,
    t.product_name,
    t.yyyydd,
    t.mm,
    wc.distinct_date_count
FROM test_data t
JOIN window_count wc
    ON t.cst_name = wc.cst_name
    AND t.product_name = wc.product_name
    AND t.yyyydd = wc.yyyydd
    AND t.mm = wc.mm
ORDER BY t.cst_name, t.product_name, t.mm;

方案2：使用MATCH_RECOGNIZE（Oracle 12c+支持） #

利用Oracle模式匹配功能，直接在分组内匹配±2天范围的日期并统计distinct数量：

SELECT
    cst_name,
    product_name,
    yyyydd,
    mm,
    distinct_date_count
FROM test_data
MATCH_RECOGNIZE (
    PARTITION BY cst_name, product_name
    ORDER BY mm
    MEASURES COUNT(DISTINCT yyyydd) AS distinct_date_count
    ALL ROWS PER MATCH
    PATTERN (PREV_DATES* CURRENT_DATE NEXT_DATES*)
    DEFINE
        PREV_DATES AS mm >= CURRENT_DATE.mm - 2,
        NEXT_DATES AS mm <= CURRENT_DATE.mm + 2
);

方案3：关联子查询（适用于小数据集） #

通过关联子查询直接统计每行对应窗口范围内的distinct日期数，代码简洁但性能依赖数据规模：

SELECT
    t1.cst_name,
    t1.product_name,
    t1.yyyydd,
    t1.mm,
    (
        SELECT COUNT(DISTINCT t2.yyyydd)
        FROM test_data t2
        WHERE t2.cst_name = t1.cst_name
          AND t2.product_name = t1.product_name
          AND t2.mm BETWEEN t1.mm - 2 AND t1.mm + 2
    ) AS distinct_date_count
FROM test_data t1
ORDER BY t1.cst_name, t1.product_name, t1.mm;

方案对比 #

• 方案1：性能最优，适合中等及大规模数据集，先去重减少窗口计算的数据量
• 方案2：Oracle 12c+支持，代码简洁，模式匹配逻辑直观
• 方案3：实现最简单，但大数据量下IO开销高，仅适合小数据集

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