Oracle 21c递归查询：统计近3周每周均有骑行的客户累计数

需求说明 #

现有数据表包含Ride、Client、Date列，已新增Weeknum列（按日期计算的周数）。需要对每个客户的每一条记录做以下处理：

• 回溯近3周（当前周及前两周），检查这3周内每周是否至少有1次骑行记录
• 若符合条件，将该客户的累计达标次数加1，最终生成匹配需求的Final Result列

示例逻辑（以Client 1为例）：

• Weeknum=4时，第2、3、4周均有骑行，达标，Final Result记为1
• Weeknum=5时，第3、4、5周均有骑行，达标，累计为2
• Weeknum=6时，第4、5、6周均有骑行，达标，累计为3

解决方案（非递归实现） #

利用Oracle窗口函数和CTE（公共表表达式）即可实现，无需递归查询。以下是完整SQL代码：

WITH client_weekly AS (
    -- 去重：每个客户每周仅保留一条记录（只要有骑行就算数）
    SELECT DISTINCT
        Client,
        Weeknum,
        TRUNC(Date, 'IW') AS week_start  -- 标记每周的起始日期，避免跨年周数冲突
    FROM your_table  -- 替换为你的实际表名
),
qualified_weeks AS (
    -- 判断当前周是否满足"近3周每周都有骑行"的条件
    SELECT
        Client,
        Weeknum,
        CASE
            WHEN (
                -- 检查当前周、前1周、前2周是否都存在记录
                COUNT(DISTINCT CASE WHEN Weeknum IN (cw.Weeknum, cw.Weeknum-1, cw.Weeknum-2) THEN Weeknum END)
                OVER (PARTITION BY Client ORDER BY Weeknum ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
                = 3
            ) THEN 1
            ELSE 0
        END AS is_qualified
    FROM client_weekly cw
),
cumulative_result AS (
    -- 按客户分组，累计达标次数
    SELECT
        Client,
        Weeknum,
        SUM(is_qualified) OVER (PARTITION BY Client ORDER BY Weeknum) AS final_result
    FROM qualified_weeks
)
-- 关联回原表，将最终结果匹配到每条原始记录
SELECT
    t.Ride,
    t.Client,
    t.Date,
    t.Weeknum,
    cr.final_result
FROM your_table t
JOIN cumulative_result cr
    ON t.Client = cr.Client
    AND t.Weeknum = cr.Weeknum
ORDER BY t.Client, t.Weeknum;

代码说明 #

1. client_weekly CTE：对原始数据按客户+周数去重，同时标记每周的起始日期，避免跨年时周数重复的问题。
2. qualified_weeks CTE：通过窗口函数COUNT(DISTINCT ...)检查当前周及前两周的记录完整性，判断是否达标。
3. cumulative_result CTE：用窗口SUM()按客户分组、周数排序，累计客户的达标次数，得到最终的final_result。
4. 最后关联原表，将累计结果映射到每条原始骑行记录上。

适配跨年场景的优化 #

如果你的Weeknum是按自然年计算（每年周数从1开始），建议用日期范围替代周数加减，避免跨年判断错误：

-- 将qualified_weeks中的CASE判断替换为以下逻辑
CASE
    WHEN EXISTS (
        SELECT 1
        FROM client_weekly cw2
        WHERE cw2.Client = cw.Client
          AND cw2.week_start BETWEEN cw.week_start - 14 AND cw.week_start
        HAVING COUNT(DISTINCT cw2.week_start) = 3
    ) THEN 1
    ELSE 0
END AS is_qualified

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