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

SQL query to find the number of customers who shopped for 3 consecutive days in month of January 2020(查询2020年1月连续购物3天的客户数量)
本文介绍了查询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'));

这篇关于查询2020年1月连续购物3天的客户数量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Execute complex raw SQL query in EF6(在EF6中执行复杂的原始SQL查询)
Hibernate reactive No Vert.x context active in aws rds(AWS RDS中的休眠反应性非Vert.x上下文处于活动状态)
Bulk insert with mysql2 and NodeJs throws 500(使用mysql2和NodeJS的大容量插入抛出500)
Flask + PyMySQL giving error no attribute #39;settimeout#39;(FlASK+PyMySQL给出错误,没有属性#39;setTimeout#39;)
auto_increment column for a group of rows?(一组行的AUTO_INCREMENT列?)
Sort by ID DESC(按ID代码排序)