Server每月查询新订单和重复订单
Server每月查询新订单和重复订单
提问于 2018-04-20 18:10:38
我在Server 2008 R2中工作，很难收集新的和重复的客户订单。
我有这种格式的数据：
OrderID OrderDate Customer OrderAmount
-----------------------------------------------
1 1/1/2017 A $10
2 1/2/2017 B $20
3 1/3/2017 C $30
4 4/1/2017 C $40
5 4/2/2017 D $50
6 4/3/2017 D $60
7 1/6/2018 B $70我们想要的是：
- 新定义为:客户在过去的几个月中没有下任何订单。
- 重复定义为:客户在前一个月下了订单(即使是很多年前)。
这意味着，如果一个新客户在她的第一个月中下了多个订单，他们都将被视为“新”客户订单。随后几个月下的订单都被视为“重复”客户订单。
我们希望获得新订单(计数和和)和重复订单(计数和和)每年，每月：
Year Month NewCount NewSum RepeatCount RepeatSum
-----------------------------------------------------------------------------
2017 1 3 (A,B,C) $60 (10+20+30) 0 $0
2017 4 2 (D,D) $110 (50+60) 1 (C) $40 (40)
2018 1 0 $0 1 (B) $70 (70)( ()括号中的信息不是结果的一部分，只是为了清晰起见将其放在这里)
对于任何一个给定的月份，SQL都很容易编写，但我不知道如何在一次收集数年的月份时编写SQL.
如果有一个月没有任何类型的订单，那么年份的NULL或0值是首选的: month。
回答 2
Stack Overflow用户
回答已采纳
发布于 2018-04-21 07:09:01
您可以使用dense_rank查找新的和旧的客户。此查询返回所提供的输出。
declare @t table (OrderID int, OrderDate date, Customer char(1), OrderAmount int)
insert into @t
values (1, '20170101', 'A', 10)
, (2, '20170102', 'B', 20), (3, '20170103', 'C', 30)
, (4, '20170401', 'C', 40), (5, '20170402', 'D', 50)
, (6, '20170403', 'D', 60), (7, '20180106', 'B', 70)
select
[year], [month], NewCount = isnull(sum(case when dr = 1 then 1 end), 0)
, NewSum = isnull(sum(case when dr = 1 then OrderAmount end), 0)
, RepeatCount = isnull(sum(case when dr > 1 then 1 end), 0)
, RepeatSum = isnull(sum(case when dr > 1 then OrderAmount end), 0)
from (
select
*, [year] = year(OrderDate), [month] = month(OrderDate)
, dr = dense_rank() over (partition by Customer order by dateadd(month, datediff(month, 0, OrderDate), 0))
from
@t
) t
group by [year], [month]输出
year month NewCount NewSum RepeatCount RepeatSum
----------------------------------------------------------
2017 1 3 60 0 0
2018 1 0 0 1 70
2017 4 2 110 1 40如果您想在没有订单的情况下显示月份，首先必须将表中的每个年与所有月份结合起来。然后加入上层查询。
select
*
from
(select distinct y = year(OrderDate) from @t) t
cross join (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) q(m)Stack Overflow用户
发布于 2018-04-20 18:16:43
首先，从总结数据开始，每个客户每月记录一条记录。
然后，您可以使用一个自连接或类似的结构来获取所需的信息：
with cm as (
select customer, dateadd(day, 1 - day(orderdate), orderdate) as yyyymm
sum(orderamount) as monthamount, count(*) as numorders
from orders
group by customer
)
select year(cm.yyyymm) as yr, month(cm.yyyymm) as mon,
sum(case when cm.num_orders > 0 and cm_prev.customer is null then 1 else 0 end) as new_count,
sum(case when cm.num_orders > 0 and cm_prev.customer is null then monthamount else 0 end) as new_amount,
sum(case when cm.num_orders > 0 and cm_prev.customer > 0 then 1 else 0 end) as repeat_count,
sum(case when cm.num_orders > 0 and cm_prev.customer > 0 then monthamount else 0 end) as repeat_amount
from cm left join
cm cm_prev
on cm.customer = cm_prev.customer and
cm.yyyymm = dateadd(month, 1, cm_prev.yyyymm)
group by year(cm.yyyymm), month(cm.yyyymm)
order by year(cm.yyyymm), month(cm.yyyymm);在Server 2012中，这会更容易一些，在这里您可以使用lag()。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/49947508
复制相关文章
相似问题
腾讯云开发者
