技术问询：基于时间序列每日聚合，求任意日期/时段有效会员数（禁用非等值连接）

先明确我们手头的数据集和核心要求：

数据集说明 #

会员有效期表 #

日期维度表 #

我们用到的是已有的 DIM.[Date] 表，这个表需要包含覆盖所有会员有效期的每日日期记录（核心字段为 [Date]）。

核心需求 #

• 生成每日聚合的时间序列，计算任意单个日期或日期区间内的有效会员总数
• 严格禁止使用非等值连接（比如 BETWEEN、>=/<= 这类跨表的范围连接逻辑）

实现思路与代码 #

要避开非等值连接，我们可以把会员的有效期区间转换成离散的"事件点"，再通过等值连接和累计求和来实现统计，具体方案如下：

完整SQL代码 #

WITH MembershipEvents AS (
    -- 生成会员生效事件：有效期起始日，会员数+1
    SELECT 
        ValidFromDate AS EventDate,
        1 AS MembershipChange
    FROM MembershipTable
    UNION ALL
    -- 生成会员失效事件：有效期结束的次日，会员数-1
    SELECT 
        DATEADD(DAY, 1, ValidToDate) AS EventDate,
        -1 AS MembershipChange
    FROM MembershipTable
),
DailyChanges AS (
    -- 用等值连接关联日期表和事件表，得到每日的会员变化量
    SELECT 
        d.[Date],
        ISNULL(SUM(me.MembershipChange), 0) AS DailyChange
    FROM DIM.[Date] d
    LEFT JOIN MembershipEvents me ON d.[Date] = me.EventDate
    -- 可选：添加日期范围过滤，比如 WHERE d.[Date] BETWEEN '1997-01-01' AND '2017-05-12'
    GROUP BY d.[Date]
)
-- 累计求和得到每日的有效会员总数
SELECT 
    [Date],
    SUM(DailyChange) OVER (ORDER BY [Date] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS MembershipCount
FROM DailyChanges
ORDER BY [Date];

代码分步解释 #

1. MembershipEvents 公共表表达式：
   • 把每个会员的有效期拆成两个关键事件：生效当天记为+1（新增有效会员），有效期结束的次日记为-1（会员失效不再有效），这样就把区间范围转换成了离散的日期点，为后续等值连接铺路。
2. DailyChanges 公共表表达式：
   • 用等值连接（d.[Date] = me.EventDate）把日期表和事件表关联起来，按日期聚合得到每日的会员变化量；没有事件的日期用ISNULL补0，保证每日都有记录。
3. 最终累计计算：
   • 使用窗口函数SUM() OVER(ORDER BY [Date])对每日变化量做累计求和，这样就能得到当天的有效会员总数——因为累计的过程就是把之前所有的新增和失效加起来，正好是当日的有效数量。

方案优势 #

• 完全符合禁用非等值连接的要求，所有表连接都是严格的等值匹配
• 性能更好：离散事件的等值连接比区间型的非等值连接查询效率高很多，数据量越大优势越明显
• 灵活性强：想要查询特定日期范围，只需要在DailyChanges里添加WHERE条件过滤日期即可

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