客户费用查询

Question

需要构建一个查询，显示客户每周、每月、每季度、6 个月和一年在每日在线游戏上花费了多少钱。当一位客户进入游戏时，有一个字段创建日期（日期时间）字段。

有人可以帮忙吗？

Answer 1

也许是这样的。

declare @T table (CreatedDate datetime, MoneySpent int)

insert into @T values
('20101231', 5),
('20100101', 10),
('20100102', 20),
('20100103', 30),
('20110104', 40),
('20110105', 50),
('20110106', 60),
('20110107', 70)

-- per year
select
  sum(MoneySpent) Spent,
  year(CreatedDate) [Year]
from @T
group by year(CreatedDate)

-- per month
select
  sum(MoneySpent) Spent,
  year(CreatedDate) [Year],
  month(CreatedDate) [Month]
from @T
group by year(CreatedDate), month(CreatedDate)

-- per half year
select
  sum(MoneySpent) Spent,
  year(CreatedDate) [Year],
  case when month(CreatedDate) <= 6 then 'First' else 'Second' end [HalfYear]
from @T
group by year(CreatedDate), case when month(CreatedDate) <= 6 then 'First' else 'Second' end

-- per quarter
select
  sum(MoneySpent) Spent,
  year(CreatedDate) [Year],
  ((month(CreatedDate)-1) % 4)+1 [Quarter]
from @T
group by year(CreatedDate), ((month(CreatedDate)-1) % 4)+1

-- per week
select
  sum(MoneySpent) Spent,
  year(CreatedDate) [Year],
  datepart(iso_week, CreatedDate) [Week]
from @T
group by year(CreatedDate), datepart(iso_week, CreatedDate)

结果

Spent       Year
----------- -----------
65          2010
220         2011

Spent       Year        Month
----------- ----------- -----------
60          2010        1
220         2011        1
5           2010        12

Spent       Year        HalfYear
----------- ----------- --------
60          2010        First
220         2011        First
5           2010        Second

Spent       Year        Quarter
----------- ----------- -----------
60          2010        1
220         2011        1
5           2010        4

Spent       Year        Week
----------- ----------- -----------
220         2011        1
5           2010        52
60          2010        53