sql – 日期当前和之前的累积列值

发布时间：2020-12-12 06:34:42 所属栏目：MsSql教程来源：网络整理

导读：我有一张注册表,大约有300K的记录.我需要一个SQL语句来显示该特定日期的注册总数？ selectcount('x'),CONVERT(varchar(12),date_created,111)from reg group bycONVERT(varchar(12),111)order byCONVERT(varchar(12),111) 此查询的结果： 169 2011/03/243016

我有一张注册表,大约有300K的记录.我需要一个SQL语句来显示该特定日期的注册总数？

select
count('x'),CONVERT(varchar(12),date_created,111)
from reg group by
cONVERT(varchar(12),111)
order by
CONVERT(varchar(12),111)

此查询的结果：

169      2011/03/24
3016     2011/03/25
2999     2011/03/26

期望的结果：

2011/03/25  3016+169
 2011/03/26  2999+3016+169

如何才能做到这一点？

解决方法

这是两个版本.我已经在一台速度非常慢的计算机上测试了超过6000天的100000行,内存不足,这表明cte版本比循环版本更快.这里建议的其他版本(到目前为止)要慢得多,前提是我已正确理解了问题.

递归CTE(10秒)

-- Table variable to hold count for each day
declare @DateCount table(d int,c int,rn int)
insert into @DateCount
  select 
    datediff(d,date_created) as d,count(*) as c,row_number() over(order by datediff(d,date_created)) as rn
  from reg
  group by datediff(d,date_created)

-- Recursive cte using @DateCount to calculate the running sum
;with DateSum as
(
  select 
    d,c,rn
  from @DateCount
  where rn = 1
  union all
  select 
    dc.d,ds.c+dc.c as c,dc.rn
  from DateSum as ds
    inner join @DateCount as dc
      on ds.rn+1 = dc.rn  
)
select
  dateadd(d,d,0) as date_created,c as total_num
from DateSum
option (maxrecursion 0)

循环(14秒)

-- Table variable to hold count for each day
declare @DateCount table(d int,rn int,cr int)
insert into @DateCount
  select 
    datediff(d,date_created)) as rn,0
  from reg
  group by datediff(d,date_created)

declare @rn int = 1

-- Update cr with running sum
update dc set
  cr = dc.c  
from @DateCount as dc
where rn = @rn

while @@rowcount = 1
begin
  set @rn = @rn + 1

  update dc set
    cr = dc.c + (select cr from @DateCount where rn = @rn - 1)  
  from @DateCount as dc
  where rn = @rn
end

-- Get the result
select
  dateadd(d,cr as total_num
from @DateCount

编辑1真正快速的版本

The quirky update

-- Table variable to hold count for each day
declare @DateCount table(d int primary key,date_created)

declare @rt int = 0
declare @anchor int

update @DateCount set
  @rt = cr = @rt + c,@anchor = d
option (maxdop 1)

-- Get the result
select
  dateadd(d,cr as total_num
from @DateCount                
order by d

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!