SQL——如何编写一个select语句来查找连续5天下订单的客户

Question

orderId、orderDate和customerId是此处的相关字段。

客户可能连续15天以上。

我希望输出看起来像这个

customerID  startDate       endDate      numDays 
1           2020/01/01      2020/01/05    5
1           2020/10/1       2020/10/10   10
101         2020/04/10      2020/04/15    6

到目前为止，这就是我所拥有的：

;
with t1 as (
select distinct o.idcustomer,orderdate, dateadd(dd,1,orderdate) nextOrderDate, 1 as tday, orderstatus
from orders o
join customers c on c.idcustomer=o.idcustomer
where orderstatus in (3,4) and c.customertype=0
), t2 as (
select * from t1
union all
select o2.idcustomer, o2.orderdate, dateadd(dd,1,o2.orderdate), o.tday+1, o2.orderstatus
from t1 o2
join t2 o on o2.idcustomer=o.idcustomer and o2.orderdate=o.nextOrderDate and o2.orderstatus in (3,4)
)
--select idcustomer, max(tday) DaysInARow, min(orderDate) StartDate, max(orderdate)  endDate
select idcustomer, dateadd(dd,-5,min(orderdate)) firstOrderDate, max(orderdate) lastOrderDate
from t2 
where tday>=5
group by idcustomer, tday
order by idcustomer

Answer 1

这是一个缺口和孤岛问题，您需要将客户有订单的连续几天组合在一起。

如果客户每天最多有一个订单，您可以使用日期算法根据递增序列建立组。假设您运行的是SQL Server，正如当前查询的语法所示：

select customer_id, min(orderdate) startdate, max(orderdate) enddate, count(*) cnt
from (
select c.customerid, o.orderdate, 
row_number() over(partition by customerid order by o.orderdate) rn
from orders o
inner join customers c on c.idcustomer = o.idcustomer
where o.orderstatus in (3, 4) and c.customertype = 0
) t
group by c.customer_id, dateadd(day, -rn, o.orderdate)

如果你只想显示5天或更长时间的条纹，只需添加一个having子句：

having count(*) >= 5

而且，如果你只想让每个客户拥有最大的连胜纪录(最小长度为5(：

select *
from (
select customer_id, min(orderdate) startdate, max(orderdate) enddate, count(*) cnt,
rank() over(partition by customer_id order by count(*) desc) rn2
from (
select c.customerid, o.orderdate, 
row_number() over(partition by customerid order by o.orderdate) rn
from orders o
inner join customers c on c.idcustomer = o.idcustomer
where o.orderstatus in (3, 4) and c.customertype = 0
) t
group by c.customer_id, dateadd(day, -rn, o.orderdate)
having count(*) >= 5
) t
where rn2 = 1

如果有重复的(customerid, orderdate)，那么我们使用dense_rank()而不是row_number()，使用count(distinct orderdate)而不是count(*)。

Answer 2

我将使用以下方法，并从以下查询开始：

SQLFiddle

WITH truncatedQueries as (
select distinct idcustomer, TRUNC(orderDate) as orderDate from orders 
),
rawData as (
select a.idcustomer,a.orderDate as order1, b.orderDate as order2, b.orderDate-a.orderDate  from truncatedQueries a inner join 
truncatedQueries b on 
a.idcustomer = b.idcustomer AND a.orderDate < b.orderDate  AND
b.orderDate-a.orderDate =1
),
intervals as (
select idCustomer as CustomerId, min(order1) as StartDate, max(order2) as EndDate from rawData
group by idCustomer order by idCustomer
)
select CustomerId,StartDate, EndDate, endDate - StartDate as numDays  from intervals where endDate - StartDate >=5;

算法如下：

1. 我从日期中截断时间，并执行distinct查询。通过这样做，我每个订单每个客户每个日期都有一个条目
2. 然后我做了一个自我加入，将这些结果相互联系，并连续几天寻找。我连续几天只保留(同一客户的(两个订单之间有1天差异的行
3. 然后我得到了如下结果：

ID    ORDERDATE   ORDERDATE   DIFFERENCE
1 22-APR-11   23-APR-11   1
1 23-APR-11   24-APR-11   1
2 22-APR-11   23-APR-11   1

然后我们有了基本数据，但我们还缺少两件事：

A( 间隔的开始B( 间隔的结束C( 它们之间的差异(天数(

4. 为此，我使用了min和max函数。如果我们从第一个orderDate列中获取min，从第二个orderDate列中获得max，我们就得到了结果。最后一部分是进行减法运算，并在必要时检查>=5

Answer 3

谢谢大家！

这是我需要的代码：

;
with t1 as (
select distinct o.idcustomer,orderdate
from orders o
join customers c on c.idcustomer=o.idcustomer
where orderstatus in (3,4) and c.customertype=0
)
select idCustomer, min(orderdate) startdate, max(orderdate) enddate, count(*) cnt
from (
select idcustomer, orderdate, 
row_number() over(partition by idcustomer order by orderdate) rn
from t1
) t
group by idcustomer, dateadd(day, -rn, orderdate)
having count(*) >= 5 
order by idcustomer, cnt

CTE给了我一个唯一的idCustomer和orderdate(消除了同一天的多个订单(，从那时起，我使用了@GMB(https://stackoverflow.com/users/10676716/gmb)创建输出的第一个示例。

使用row_number来计算日期分组的答案非常酷。