Oracle SQL查询连续下单用户的代码实现求助

我是数据库领域新手，最近被一个问题困扰多日：需要找出存在连续多天下单记录的用户（按用户+公司维度，例如20230715、20230716这类连续日期的下单用户），排除无连续下单行为的用户（如仅在20230715、20230719下单的用户）。希望获取对应的Oracle SQL代码，后续还需基于这些用户和日期计算消费金额。

我曾尝试使用ROW_NUMBER()、lag()和lead()函数，但效果有限，以下是我的尝试代码：

WITH ranked_data AS (
    SELECT
        "USER",
        "COMPANY",
        "CREATIONDATE",
        ROW_NUMBER() OVER (PARTITION BY "USER", "COMPANY" ORDER BY "CREATIONDATE") AS rn
    FROM
        P_TEST_P
)
SELECT
    "USER",
    "COMPANY",
    LISTAGG("CREATIONDATE", ', ') WITHIN GROUP (ORDER BY "CREATIONDATE") AS "CONSECUTIVE_DATES"
FROM
    ranked_data
GROUP BY
    "USER",
    "COMPANY";

输入示例数据如下：

USER;Company;creationdate
Liza;Amigurumi KFT;20230712
Liza;Amigurumi KFT;20230713
Liza;Amigurumi KFT;20230714
Liza;Amigurumi KFT;20230715
Liza;Amigurumi KFT;20230728
Liza;Amigurumi KFT;20230718
Liza;Amigurumi KFT;20230722
Liza;Amigurumi KFT;20230723
Liza;Amigurumi KFT;20230724
Liza;Amigurumi KFT;20230729
Liza;Amigurumi KFT;20230729
Liza;Amigurumi KFT;20230729
Liza;Bubu btk;20230703
Liza;Bubu btk;20230703
Liza;Bubu btk;20230708
Liza;Bubu btk;20230711
Liza;Bubu btk;20230722
Liza;PGGNG;20230728
Liza;PGGNG;20230728
Liza;PGGNG;20230728
Liza;PGGNG;20230730
Patrik;YNWA;20230701
Patrik;YNWA;20230706
Patrik;YNWA;20230708
Patrik;YNWA;20230709
Patrik;HUNF;20230725
Patrik;HUNF;20230729
Laszlo;FOUR GRG;20230712
Laszlo;FOUR GRG;20230713
Laszlo;FOUR GRG;20230713
Laszlo;FOUR GRG;20230714

核心思路 #

要找出连续日期的下单记录，关键是先对每个用户+公司的日期去重（同一天多次下单只算一次），然后通过日期减去行号的方式，将连续日期归为同一分组——因为连续日期的行号递增和日期递增同步，差值会保持一致。

步骤1：筛选有连续下单的用户+公司 #

以下SQL会先标记出所有存在连续下单的用户和公司组合：

WITH distinct_dates AS (
    -- 去重：同一用户+公司同一天的下单只保留一条
    SELECT DISTINCT
        "USER",
        "COMPANY",
        TO_DATE("CREATIONDATE", 'YYYYMMDD') AS order_date  -- 转换为日期类型方便计算
    FROM P_TEST_P
),
date_groups AS (
    -- 对每个用户+公司的日期排序，计算日期与行号的差值，连续日期的差值相同
    SELECT
        "USER",
        "COMPANY",
        order_date,
        order_date - ROW_NUMBER() OVER (PARTITION BY "USER", "COMPANY" ORDER BY order_date) AS group_id
    FROM distinct_dates
),
valid_groups AS (
    -- 筛选出包含至少2个连续日期的分组
    SELECT "USER", "COMPANY"
    FROM date_groups
    GROUP BY "USER", "COMPANY", group_id
    HAVING COUNT(*) >= 2
)
-- 最终得到有连续下单行为的用户+公司
SELECT DISTINCT "USER", "COMPANY"
FROM valid_groups;

步骤2：关联原表计算消费金额 #

如果需要基于这些用户和日期计算消费金额（假设表中有AMOUNT字段记录单笔消费），可以用以下SQL：

WITH distinct_dates AS (
    SELECT DISTINCT
        "USER",
        "COMPANY",
        TO_DATE("CREATIONDATE", 'YYYYMMDD') AS order_date
    FROM P_TEST_P
),
date_groups AS (
    SELECT
        "USER",
        "COMPANY",
        order_date,
        order_date - ROW_NUMBER() OVER (PARTITION BY "USER", "COMPANY" ORDER BY order_date) AS group_id
    FROM distinct_dates
),
valid_groups AS (
    SELECT "USER", "COMPANY", group_id, order_date
    FROM date_groups
    GROUP BY "USER", "COMPANY", group_id, order_date
    HAVING COUNT(*) OVER (PARTITION BY "USER", "COMPANY", group_id) >= 2
)
-- 计算每个用户+公司在连续日期内的总消费金额
SELECT
    v."USER",
    v."COMPANY",
    LISTAGG(TO_CHAR(v.order_date, 'YYYYMMDD'), ', ') WITHIN GROUP (ORDER BY v.order_date) AS consecutive_dates,
    SUM(p."AMOUNT") AS total_amount
FROM valid_groups v
JOIN P_TEST_P p 
    ON v."USER" = p."USER" 
    AND v."COMPANY" = p."COMPANY"
    AND TO_DATE(p."CREATIONDATE", 'YYYYMMDD') = v.order_date
GROUP BY v."USER", v."COMPANY";

结果说明 #

• 针对示例数据，上述代码会筛选出：
  • Liza + Amigurumi KFT（有20230712-20230715、20230722-20230724两组连续日期）
  • Patrik + YNWA（有20230708-20230709一组连续日期）
  • Laszlo + FOUR GRG（有20230712-20230714一组连续日期）
• 排除掉没有连续下单的组合（如Liza+Bubu btk、Liza+PGGNG、Patrik+HUNF）

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