SQL Server中如何高效计算30天滚动窗口内的去重用户数？

我需要计算30天滚动窗口内的去重用户数，事件表仅存储有事件发生日期的行，因此创建了date-spine（日期维度表），自2021-10-01起每个客户每天一行（提前90天满足滚动统计需求），涉及约5000个客户，该表共170万行。要求每天都有统计值，哪怕当天无用户活动。

现有查询结果符合预期，但针对所有客户执行时速度过慢。我是SQL Server新手，尝试为临时表DateValue添加非聚集索引后仍无改善，求优化建议。

现有查询语句 #

Create table #DateSpine
(
    DateValue date,
    ClientID nvarchar(100),
    UserId nvarchar(100)
);
create nonclustered index idx on #DateSpine (DateValue);

SET NOCOUNT ON;
BEGIN TRY

  TRUNCATE TABLE pre.DistinctUsers;

with clients as (
            SELECT distinct
                ClientId
            FROM dw.Clients
            where ClientId is not null
            
        ),

        date_spine as (
            select
                d.DateValue,
                c.ClientId
            from dw.DimDate d
            cross join clients c
            where 
                d.Datevalue 
            between 
                (select dateadd(day, -90, min(DateValue)) from dw.CustomerHealthScore)
            and 
                (select max(DateValue) from dw.CustomerHealthScore)
        ),

        measures as (
        SELECT
            WKEventDate,
            ClientId,
            UserId
        from dw.FactEvents f
        where EventType = 'render created'
        and RenderType = 'full'
        ),

        final as (
        select 
            d.DateValue,
            d.ClientId,
            UserId
        from date_spine d
        left join measures m 
        on
            d.DateValue = m.WKEventDate
        and
            d.ClientId = m.ClientId
        )

        insert into @DateSpine
        select
            DateValue,
            ClientId,
            UserId
        from final


INSERT INTO pre.DistinctUsersFR(
                DateValue,
                ClientId,
                NoOfUsersFR30DActual,
                _batchId)
    
        select
            f.DateValue,
            f.ClientId,
            count(distinct f2.BKUserId) as NoOfUsersFR30Actual,
            @BatchId
        from @DateSpine f
        left join 
            @DateSpine f2
        on 
            f.ClientId = f2.ClientId
        and 
            f2.DateValue 
        between 
            dateadd(day, -30, f.DateValue) 
        and 
            f.DateValue
        group by 
            f.DateValue, f.ClientId

date_spine表结构及示例数据 #

表DDL #

create table date_spine (
  DateValue date,
  ClientId nvarchar(100),
  UserId nvarchar(100))

示例数据 #

insert into date_spine (DateValue, ClientId, UserId)
values 
('2021-10-04', '1', 'xyz'),
('2021-10-04','2',null),
('2021-10-05','1',null),
('2021-10-05','2','lko'),
('2021-10-05','2','abc'),
('2021-10-06','1',null),
('2021-10-06','2',null),
('2021-10-07','1',null),
('2021-10-07','2',null),
('2021-10-08','1','asd'),
('2021-10-08','2','plo')

优化建议 #

1. 修正临时表/表变量混用问题
   代码中同时使用了#DateSpine（临时表）和@DateSpine（表变量），表变量在SQL Server中优化器行数估计不准确，建议统一使用临时表#DateSpine。

2. 创建精准的复合索引

   • 临时表#DateSpine的索引应覆盖关联和分组的核心字段，避免键查找：

     create nonclustered index idx_date_spine on #DateSpine (ClientId, DateValue) include (UserId);
     

   • 对dw.FactEvents创建过滤覆盖索引，直接满足查询需求无需回表：

     create nonclustered index idx_fact_events_render on dw.FactEvents (ClientId, WKEventDate) include (UserId)
     where EventType = 'render created' and RenderType = 'full';
     

3. 预计算日期范围
   提前计算日期范围，避免CTE中重复执行子查询：

   declare @StartDate date, @EndDate date;
   select @StartDate = dateadd(day, -90, min(DateValue)), @EndDate = max(DateValue)
   from dw.CustomerHealthScore;
   

   后续在date_spine的CTE中直接使用@StartDate和@EndDate。

4. 减少无效数据量
   原逻辑会生成大量UserId为null的行，这些行对去重统计无意义。先对measures按ClientId+WKEventDate去重用户，再关联date_spine，减少临时表数据量：

   measures as (
       SELECT distinct
           WKEventDate,
           ClientId,
           UserId
       from dw.FactEvents f
       where EventType = 'render created'
       and RenderType = 'full'
   ),
   

5. 替换低效自连接为窗口函数
   自连接计算滚动窗口的方式在170万行数据下效率极低：

   • 若使用SQL Server 2022+，直接支持窗口函数计算去重计数：

     count(distinct UserId) over (
         partition by ClientId 
         order by DateValue 
         rows between 29 preceding and current row
     )
     

   • 旧版本可先记录每个ClientId+UserId的所有活动日期，再结合日期维度表统计滚动窗口内的唯一用户数。

6. 统一数据类型
   若dw.Clients的ClientId是数值类型，建议将所有关联表的ClientId改为数值类型（如int/bigint），比nvarchar(100)的关联效率高很多。

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