一道SQL题，window function，请高手解答下。根据cus_id和date生成count，count 的意思是过去7天（不包含当天），有几
个不同的cus_id出现
cus_id, date, count
c1, 12/1, 0
c1, 12/2, 1
c2, 12/3, 1
c3, 12/9, 2
这个题利用derived column会非常难做，
利用while loop 去做
Answer
select
ROW_NUMBER()over(order by date) as rownumber,
*,
DATEADD (day, -1, date) as PreviousDate,
DATEADD (day, -7, date) as [7daysback],
null as [Count]
into #tem
from customer
declare @i int = 1,
@7daysback date,
@count int
while @i <= 4
begin
set @previousdate = (select PreviousDate from #tem where rownumber = @i)
set @7daysback = (select [7daysback] from #tem where rownumber =@i)
set @count = (
select
count(distinct id) as tot
from #tem
where rownumber < @i and date <= @previousdate and date >= @7daysback
)
update a
set [count] =@count
from #tem a
where rownumber = @i
set @i = @i +1
end
select * from #tem
首先count（）over（） 一旦加了over就不可以count distinct 了。
Question :
Problem: Member can make purchase via either mobile or desktop platform. Using the following data table to determine
the total number of member and revenue for mobile-only, desktop_only and mobile_desktop.
The input spending table is
member_id date channel spend
1001 1/1/2018 mobile 100
1001 1/1/2018 desktop 100
1002 1/1/2018 mobile 100
1002 1/2/2018 mobile 100
1003 1/1/2018 desktop 100
1003 1/2/2018 desktop 100
The output data is
date channel total_spend total_members
1/1/2018 desktop 100 1
1/1/2018 mobile 100 1
1/1/2018 both 200 1-google 1point3acres
1/2/2018 desktop 100 1
1/2/2018 mobile 100 1
1/2/2018 both 0 0
Answer：
;with scope
as
(
select
'both' as channel,
customer_id,
date,
sum(spend) as Sum_2,
iif(count(channel)<2,0,count(channel)) as tot_channel
from dbo.spend
group by customer_id,
date
),Isolate
as(
select
date,
channel,
max(iif(tot_channel <2,0, sum_2)) as tot_spend,
max(tot_channel) as count1
from scope
group by date, channel
),FINAL
as(
select * from isolate
union
select
date,
channel,
sum(spend) as tot_spend,
count(customer_id) as count1
from dbo.spend
group by date, channel,customer_id
)
s elect
*
from FINAL
order by date, case when channel ='both' then'9'
when channel = 'mobile' then '1'
when channel = 'destop' then '2'
end ASC