查询2020年1月连续购物3天的客户数量

2022-08-14 00:00:00 sql group-by window-functions oracle

我有一个名为Orders的表格,其中包含客户ID及其订单日期(注意:同一客户在一天内可以有多个订单)

create table orders (Id char, order_dt date)

insert into orders values
('A','1/1/2020'),
('B','1/1/2020'),
('C','1/1/2020'),
('D','1/1/2020'),
('A','1/1/2020'),
('B','1/1/2020'),
('A','2/1/2020'),
('B','2/1/2020'),
('C','2/1/2020'),
('B','2/1/2020'),
('A','3/1/2020'),
('B','3/1/2020')

我正在尝试编写一个SQL查询来查找2020年1月连续3天购物的客户数量

根据上述顺序值,输出应为:2

我提出了其他类似的问题,但仍无法得出确切的解决方案


解决方案

这是我的解决方案,即使一天内有多个客户的订单也能正常工作;

构建测试环境的一些脚本:

create table orders (Id varchar2(1), order_dt date);

insert into orders values('A',to_date('01/01/2020','dd/mm/yyyy'));
insert into orders values('B',to_date('01/01/2020','dd/mm/yyyy'));
insert into orders values('C',to_date('01/01/2020','dd/mm/yyyy'));
insert into orders values('D',to_date('01/01/2020','dd/mm/yyyy'));
insert into orders values('A',to_date('01/01/2020','dd/mm/yyyy'));
insert into orders values('B',to_date('01/01/2020','dd/mm/yyyy'));
insert into orders values('A',to_date('02/01/2020','dd/mm/yyyy'));
insert into orders values('B',to_date('02/01/2020','dd/mm/yyyy'));
insert into orders values('C',to_date('02/01/2020','dd/mm/yyyy'));
insert into orders values('B',to_date('02/01/2020','dd/mm/yyyy'));
insert into orders values('A',to_date('03/01/2020','dd/mm/yyyy'));
insert into orders values('B',to_date('03/01/2020','dd/mm/yyyy'));


 select distinct id,  count_days from (
    select id,
           order_dt,
           count(*) over(partition by id order by order_dt range between 1  preceding  and 1 following  )  count_days
    from orders group by  id, order_dt
)
where count_days = 3;

--  Insert for test more days than 3 consecutive

insert into orders values('A',to_date('04/01/2020','dd/mm/yyyy'));

相关文章