如何查询连续10天及以上有购买记录的客户（Oracle SQL）

问题描述 #

我尝试编写了一段代码，用于筛选出连续10天及以上有购买记录的客户，但目前输出缺少PURCHASE_COUNT（对应时间段内的总购买次数）字段。我希望得到指定格式的完整输出。我知道可以用MATCH_RECOGNIZE实现，但对这个语法不太熟悉，优先考虑优化现有代码，也欢迎任何能达成目标输出的方案。

测试用例与现有代码 #

数据准备SQL #

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

CREATE TABLE customers 
(CUSTOMER_ID, FIRST_NAME, LAST_NAME) AS
SELECT 1, 'Faith', 'Mazzarone' FROM DUAL UNION ALL
SELECT 2, 'Lisa', 'Saladino' FROM DUAL UNION ALL
SELECT 3, 'Micheal', 'Palmice' FROM DUAL UNION ALL
SELECT 4, 'Joseph', 'Zaza' FROM DUAL UNION ALL
SELECT 5, 'Jerry', 'Torchiano' FROM DUAL;

ALTER TABLE customers 
ADD CONSTRAINT customers_pk PRIMARY KEY (customer_id);

CREATE TABLE items 
(PRODUCT_ID, PRODUCT_NAME, PRICE) AS
SELECT 100, 'Black Shoes', 79.99 FROM DUAL UNION ALL
SELECT 101, 'Brown Pants', 111.99 FROM DUAL UNION ALL
SELECT 102, 'White Shirt', 10.99 FROM DUAL;

ALTER TABLE items 
ADD CONSTRAINT items_pk PRIMARY KEY (product_id);

create table purchases(
  ORDER_ID NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
  customer_id   number, 
  PRODUCT_ID NUMBER, 
  QUANTITY NUMBER, 
  purchase_date timestamp
);

ALTER TABLE purchases 
ADD CONSTRAINT order_pk PRIMARY KEY (order_id);

ALTER TABLE purchases ADD CONSTRAINT customers_fk FOREIGN KEY (customer_id) REFERENCES customers(customer_id);

ALTER TABLE purchases ADD CONSTRAINT items_fk FOREIGN KEY (PRODUCT_ID) REFERENCES items(product_id);

insert  into purchases (customer_id, product_id, quantity, purchase_date) 
SELECT 3, 102, 4,TIMESTAMP '2022-12-22 21:44:35' + NUMTODSINTERVAL ( LEVEL * 2, 'DAY') FROM    dual
CONNECT BY  LEVEL <= 15 UNION ALL 
select 1, 101,3, date '2023-03-29' + level * interval '2' day from dual
          connect by level <= 12
union all
select 2, 101,2, date '2023-01-15' + level * interval '8' hour from dual
          connect by level <= 15
union all
select 2, 102,2,date '2023-04-13' + level * interval '1 1' day to hour from dual
          connect by level <= 11
union all
select 3, 101,2, date '2023-02-01' + level * interval '1 05:03' day to minute from dual
          connect by level <= 10
union all
select 3, 101,1, date '2023-04-22' + level * interval '23' hour from dual
          connect by level <= 23
union all
select 3, 100,1,  date '2022-03-01' + level * interval '1 00:23:05' day to second from dual
          connect by level <= 15
union all
select 4, 102,1, date '2023-01-01' + level * interval '5' hour from dual
          connect by level <= 60;

现有查询代码 #

WITH t as (
     select distinct CUSTOMER_ID,
    trunc(PURCHASE_DATE) dat
       from purchases
    )
    ,tt as (
        select t.*
        ,row_number() over (partition by CUSTOMER_ID order by dat) rn
        from t
    )
   ,ttt as (
   select CUSTOMER_ID, 
         min(dat) start_date,
         max(dat) end_date,
         count(*) day_count
     from tt
     group by 
      CUSTOMER_ID, dat-rn
      having count(*) >= 10
   )
   select 
           c.customer_id,
           c.first_name,
           c.last_name,  
           ttt.start_date,
           ttt.end_date,
           ttt.day_count
     from   customers c, ttt
    where  
    c.customer_id = ttt.customer_id;

期望输出 #

优化方案（基于现有代码调整） #

要补充PURCHASE_COUNT字段，只需在现有CTE中关联原始purchases表统计购买次数，调整后的代码如下：

WITH t as (
     select distinct CUSTOMER_ID,
            trunc(PURCHASE_DATE) dat
       from purchases
    ),
    tt as (
        select t.*,
               row_number() over (partition by CUSTOMER_ID order by dat) rn
        from t
    ),
    ttt as (
        select CUSTOMER_ID, 
               min(dat) start_date,
               max(dat) end_date,
               count(*) day_count
        from tt
        group by CUSTOMER_ID, dat-rn
        having count(*) >= 10
    ),
    purchase_counts as (
        select p.customer_id,
               ttt.start_date,
               ttt.end_date,
               count(*) as purchase_count
        from purchases p
        join ttt on p.customer_id = ttt.customer_id
               and trunc(p.purchase_date) between ttt.start_date and ttt.end_date
        group by p.customer_id, ttt.start_date, ttt.end_date
    )
select 
    c.customer_id,
    c.first_name,
    c.last_name,  
    ttt.start_date as FIRST_DATE,
    ttt.end_date as LAST_DATE,
    ttt.day_count,
    pc.purchase_count
from customers c
join ttt on c.customer_id = ttt.customer_id
join purchase_counts pc on c.customer_id = pc.customer_id 
                       and ttt.start_date = pc.start_date 
                       and ttt.end_date = pc.end_date
order by c.customer_id, ttt.start_date;

方案说明 #

1. 新增purchase_counts CTE，关联purchases表和连续日期段结果ttt，统计每个客户对应时间段内的总购买次数。
2. 最后关联所有表，补充purchase_count字段，得到完整的期望输出。

备选方案：MATCH_RECOGNIZE实现 #

如果后续熟悉该语法，也可以用更简洁的MATCH_RECOGNIZE实现：

WITH daily_purchases AS (
    SELECT customer_id,
           trunc(purchase_date) AS purchase_day,
           COUNT(*) AS daily_count
    FROM purchases
    GROUP BY customer_id, trunc(purchase_date)
)
SELECT customer_id,
       first_name,
       last_name,
       first_day AS FIRST_DATE,
       last_day AS LAST_DATE,
       day_count AS DAY_COUNT,
       total_purchases AS PURCHASE_COUNT
FROM daily_purchases dp
JOIN customers c ON dp.customer_id = c.customer_id
MATCH_RECOGNIZE (
    PARTITION BY dp.customer_id
    ORDER BY purchase_day
    MEASURES
        FIRST(purchase_day) AS first_day,
        LAST(purchase_day) AS last_day,
        COUNT(purchase_day) AS day_count,
        SUM(daily_count) AS total_purchases
    PATTERN (consecutive{10,})
    DEFINE
        consecutive AS purchase_day = LAG(purchase_day) + 1 OR LAG(purchase_day) IS NULL
)
ORDER BY customer_id, first_day;

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