Sql 连续几天购买的客户

下面是我try 列出连续购买10天或以上的客户.

我正在try 获取输出(见下文)，需要一些帮助.我知道这可能可以用Match_Recognition来完成，但我不太熟悉它，所以我更愿意提高我当前的try 或确定性，并接受任何其他可以实现我想要的输出的建议.

下面是我的测试用例.提前感谢所有回复的人.

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;

/*desired output */

CUSTOMER_ID FIRST_NAME  LAST_NAME   FIRST_DATE  LAST_DATE   DAY_COUNT   PURCHASE_COUNT
2   Lisa    Saladino    14-APR-2023 00:00:00    24-APR-2023 00:00:00    11  11
3   Micheal Palmice 02-MAR-2022 00:00:00    16-MAR-2022 00:00:00    15  15
3   Micheal Palmice 22-APR-2023 00:00:00    14-MAY-2023 00:00:00    23  23
4   Joseph  Zaza    01-JAN-2023 00:00:00    13-JAN-2023 00:00:00    13  60