如何为每个PersonId生成订阅起止日期间的月度日期列表

方案一：直接关联用户表与日历表 #

这种方式直观易懂，适合快速实现需求：

SELECT 
    ps.PersonId,
    DATEADD(month, DATEDIFF(month, 0, rc.Date), 0) AS MonthStartDate
FROM 
    dbo.PersonSubscription ps
CROSS JOIN 
    dbo.ReportingCalendar rc
WHERE 
    rc.Date >= ps.FirstSubDate
    AND rc.Date <= ps.LastSubDate
GROUP BY 
    ps.PersonId,
    DATEADD(month, DATEDIFF(month, 0, rc.Date), 0)
ORDER BY 
    ps.PersonId,
    MonthStartDate

方案二：先预生成全局月度日期（性能更优） #

当日历表数据量较大时，先一次性生成所有需要的月度日期再关联，能减少重复计算，提升查询效率：

WITH AllMonths AS (
    SELECT 
        DATEADD(month, DATEDIFF(month, 0, date), 0) AS MonthStartDate
    FROM 
        dbo.ReportingCalendar
    WHERE 
        Date >= (SELECT MIN(FirstSubDate) FROM dbo.PersonSubscription)
        AND Date <= (SELECT MAX(LastSubDate) FROM dbo.PersonSubscription)
    GROUP BY 
        DATEADD(month, DATEDIFF(month, 0, date), 0)
)
SELECT 
    ps.PersonId,
    am.MonthStartDate
FROM 
    dbo.PersonSubscription ps
JOIN 
    AllMonths am 
        ON am.MonthStartDate >= DATEADD(month, DATEDIFF(month, 0, ps.FirstSubDate), 0)
        AND am.MonthStartDate <= DATEADD(month, DATEDIFF(month, 0, ps.LastSubDate), 0)
ORDER BY 
    ps.PersonId,
    am.MonthStartDate

关键逻辑说明 #

• 月度日期提取：DATEADD(month, DATEDIFF(month, 0, date), 0) 用于将任意日期转换为当月第一天，确保生成的记录是标准的月度起始节点，方便和ReportingCalendar关联。
• 订阅期匹配：
  • 方案一通过日历日期直接匹配用户的订阅起止范围，再通过分组去重得到每个用户的月度唯一记录。
  • 方案二先将用户的订阅起止日期转换为当月第一天，再和预生成的全局月度日期做范围匹配，逻辑更清晰，避免了大量无效的交叉关联计算。
• 结果验证：以PersonId 1186为例，会生成从2020-08-01到2022-08-01的25条月度记录，完全满足PowerBI报表做月度去重计数统计的需求。

内容的提问来源于stack exchange，提问作者Anthony