计算每月滚动用户数

Question

我有这样的交易数据：

User_ID	Purchase_Date
1234	2022-0802
12231	2022-026-25
12231	2022-07-15
13421	2022-07-12
223132	2022-05-02
15231	2022-04-09

您可以使用CTE来计算所需的辅助数据。对于您的起始数据集，我将执行以下操作：

with data as (
select
User_ID,
Purchase_Date,
DATE_TRUNC(Purchase_Date, WEEK(MONDAY)) as start_week_date,
DATE_ADD(DATE_TRUNC(Purchase_Date, WEEK(MONDAY)), INTERVAL 6 DAY) as end_week_date,
from your_database
)
select distinct
count(distinct User_ID) over (partition by first_day_week, last_day_week) as Unique_User_ID_Count,
first_day_week,
last_day_week,
from data

这应该行得通。

我想你需要的是这样的东西。。

select 
DATEADD(DAY, 1-DATEPART(WEEKDAY, DateField)+1, convert(int,DateField)),DATEADD(DAY, 1-DATEPART(WEEKDAY, DateField)+7, convert(int,DateField)),count(*)
from Table1
group by DATEADD(DAY, 1-DATEPART(WEEKDAY, DateField)+1, convert(int,DateField)),DATEADD(DAY, 1-DATEPART(WEEKDAY, DateField)+7, convert(int,DateField))

如果数据很大。。我会将date转换为float，然后将div转换为7，然后转换为int。我认为这可以将相同的结果分组。。但你会在前端遇到更多麻烦。。