运行Count Distinct using Over Partition By
运行Count Distinct using Over Partition By
提问于 2019-06-07 07:19:24
我有一个数据集，其中包含随时间进行购买的用户I。我想要显示按州和国家划分的已购买用户的YTD不同计数。输出将有4列: Country、State、Year、Month、YTD Count of Users with purchase activity。
有没有办法做到这一点？当我从视图中排除月份并执行不同的计数时，以下代码可以正常工作：
Select Year, Country, State,
COUNT(DISTINCT (CASE WHEN ActiveUserFlag > 0 THEN MBR_ID END)) AS YTD_Active_Member_Count
From MemberActivity
Where Month <= 5
Group By 1,2,3;当用户有多个月的购买时，问题就会发生，因为我不能按月汇总然后求和，因为它重复了用户数量。
我需要查看一年中每个月的YTD计数，以便进行趋势分析。
回答 2
Stack Overflow用户
回答已采纳
发布于 2019-06-07 22:42:13
每个会员在购买的第一个月只返回一次，按月计数，然后应用累计和：
select Year, Country, State, month,
sum(cnt)
over (partition by Year, Country, State
order by month
rows unbounded preceding) AS YTD_Active_Member_Count
from
(
Select Year, Country, State, month,
COUNT(*) as cnt -- 1st purchses per month
From
( -- this assumes there's at least one new active member per year/month/country
-- otherwise there would be mising rows
Select *
from MemberActivity
where ActiveUserFlag > 0 -- only active members
and Month <= 5
-- and year = 2019 -- seems to be for this year only
qualify row_number() -- only first purchase per member/year
over (partition by MBR_ID, year
order by month --? probably there's a purchase_date) = 1
) as dt
group by 1,2,3,4
) as dt
;Stack Overflow用户
发布于 2019-06-07 07:36:58
在用户出现的第一个月计算用户数量：
select Country, State, year, month,
sum(case when ActiveUserFlag > 0 and seqnum = 1 then 1 else 0 end) as YTD_Active_Member_Count
from (select ma.*,
row_number() over (partition by year order by month) as seqnum
from MemberActivity ma
) ma
where Month <= 5
group by Country, State, year, month;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/56486191
复制相关文章
相似问题
腾讯云开发者
