本文介紹了查詢2020年1月連續(xù)購(gòu)物3天的客戶數(shù)量的處理方法，對(duì)大家解決問(wèn)題具有一定的參考價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)吧！
問(wèn)題描述
我有一個(gè)名為Orders的表格，其中包含客戶ID及其訂單日期(注意：同一客戶在一天內(nèi)可以有多個(gè)訂單)
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')
我正在嘗試編寫(xiě)一個(gè)SQL查詢來(lái)查找2020年1月連續(xù)3天購(gòu)物的客戶數(shù)量
根據(jù)上述順序值，輸出應(yīng)為：2
我提出了其他類(lèi)似的問(wèn)題，但仍無(wú)法得出確切的解決方案
推薦答案
這是我的解決方案，即使一天內(nèi)有多個(gè)客戶的訂單也能正常工作；
構(gòu)建測(cè)試環(huán)境的一些腳本：
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'));
這篇關(guān)于查詢2020年1月連續(xù)購(gòu)物3天的客戶數(shù)量的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，
