如果记录从未出现在以前的时间线中，则为SQL计数。
如果记录从未出现在以前的时间线中，则为SQL计数。
提问于 2020-04-10 04:59:54
我有一张这样的桌子：
customer_num Grade month amount
2 100 5 12
2 100 5 65
2 100 5 47
1 200 6 54
2 100 6 34
1 200 6 76
1 200 6 56
2 100 6 23
4 200 7 34
3 200 7 32
3 200 7 32
1 200 7 45我需要SQL代码来创建以下输出：
Grade month Number of payment Number of New Customer
100 5 3 1
100 6 2 0
200 6 3 1
200 7 4 2定义如下：
- 付款数目:本月份/职系的付款总额
- 新客户:本月首次付款的新客户数量。
非常感谢你的帮助
回答 4
Stack Overflow用户
发布于 2020-04-10 05:30:09
请您检查这个新客户计数的数目，没有映射到预期的输出。
SQL查询
DECLARE @table TABLE
(
customer_num INT,
Grade INT,
month INT,
amount INT
)
INSERT INTO @table VALUES(
2,100,5,12),
(2,100,5,65),
(2,100,5,47),
(1,200,6,54),
(2,100,6,34),
(1,200,6,76),
(1,200,6,56),
(2,100,6,23),
(4,200,7,34),
(3,200,7,32),
(3,200,7,32),
(1,200,7,45)
SELECT DISTINCT [month],
Grade,
COUNT(amount) AS [Number of payment],
COUNT(customer_num) AS [Number of New Customer]
FROM @table
GROUP BY Grade,[month]产出如下
month Grade Number of payment Number of New Customer
----------- ----------- ----------------- ----------------------
5 100 3 3
6 100 2 2
6 200 3 3
7 200 4 4
Stack Overflow用户
发布于 2020-04-10 06:59:49
使用聚合和相关的子查询：
select t.Grade, t.Month,
count(*) [Number of payment],
(
select count(distinct tt.customer_num) from tablename tt
where tt.Month = t.Month and tt.Grade = t.Grade
and not exists (
select 1 from tablename
where customer_num = tt.customer_num and Grade = t.Grade and Month < tt.Month
)
) [Number of New Customer]
from tablename t
group by t.Grade, t.Month见演示。
结果：
> Grade | Month | Number of payment | Number of New Customer
> ----: | ----: | ----------------: | ---------------------:
> 100 | 5 | 3 | 1
> 100 | 6 | 2 | 0
> 200 | 6 | 3 | 1
> 200 | 7 | 4 | 2Stack Overflow用户
发布于 2020-04-10 07:00:04
我认为这样做是可行的，但第一行的新客户数量显然应该是1而不是3。注意：“散列”提示似乎使这一操作在12k记录上运行得更快。
select T.Grade, T.month,
sum(N) as [Number of payment],
sum(case when T.month=F.First then 1 else 0 end) as [Number of New Customer]
from
( select Grade, month, customer_num, count(*) as N from @table
group by Grade, month, customer_num
) as T
inner hash join
( select customer_num, Grade, min(month) as First
from @table group by customer_num, Grade
) as F on F.customer_num = T.customer_num and F.Grade = T.Grade
group by T.Grade, T.month 页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/61134287
复制相关文章
相似问题
腾讯云开发者
