查询2020年1月连续购物3天的客户数量
我有一个名为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'));
相关文章