SQLServer中分析和开窗函数的实际应用问题

Question

create table ta(日期  datetime,   金额 int)insert into ta
select '20130101',   20 union all
select '20130203',   50 union all
select '20130225',   35 union all
select '20130301',   40 union all
select '20130502',   80
select t1.日期,
       t1.金额,
       
       (select SUM(金额) from ta t2 where t2.日期 <= t1.日期) as 累计
from ta t1
/*
日期                      金额 累计
2013-01-01 00:00:00.000 20 20
2013-02-03 00:00:00.000 50 70
2013-02-25 00:00:00.000 35 105
2013-03-01 00:00:00.000 40 145
2013-05-02 00:00:00.000 80 225
*/

Answer 1

select a.* ,lj=(select SUM(money ) from #tb b where b.date <=a.date ) from #tb a

Answer 2

CREATE TABLE #a(日期 datetime,金额 int)
INSERT INTO #a
select '20130101',   20 UNION ALL
select '20130203',   50 UNION ALL
select '20130225',   35 UNION ALL
select '20130301',   40 UNION ALL
select '20130502',   80SELECT a.日期,a.金额,
(SELECT SUM(isnull(金额,0)) FROM 
(SELECT ROW_NUMBER()OVER(ORDER BY 日期)id,CONVERT(VARCHAR(50),日期,112) 日期,金额
FROM #a)b WHERE a.id>=b.id )as 累计 
FROM (
SELECT ROW_NUMBER()OVER(ORDER BY 日期)id,CONVERT(VARCHAR(50),日期,112) 日期,金额
FROM #a)a
---------------------------------------------------------
日期                                                 金额          累计
-------------------------------------------------- ----------- -----------
20130101                                           20          20
20130203                                           50          70
20130225                                           35          105
20130301                                           40          145
20130502                                           80          225(5 行受影响)

Answer 3

create table #ta(日期  datetime,   金额 int)
 
insert into #ta
select '20130101',   20 union all
select '20130203',   50 union all
select '20130225',   35 union all
select '20130301',   40 union all
select '20130502',   80select *
from #ta t1
CROSS APPLY (select SUM(金额) 累计 from #ta where 日期 <= t1.日期) t2

Answer 4

create table #tb (date varchar(12) ,money int)
insert into #tb 
select '20130101' ,20 union all
select '20130203' ,50 union all
select '20130225' ,35 union all
select '20130301' ,40 union all
select '20130502' ,80 select a.* ,lj=(select SUM(money ) from #tb b where b.date <=a.date ) from #tb aselect a.*,ab.lj  from #tb a
cross apply (select lj= SUM(money ) from #tb b where b.date <=a.date  ) abselect a.date ,lj=SUM(b.money ) from #tb a join #tb b on b.date <=a.date 
group by a.date order by a.date drop table #tb 

Answer 5

果然有高手！都是一个思路，就是根据每一条的ID，去sum这条之前的金额，不知道这样的效率和游标比起来如何？现在重要的效率

Answer 6

还有一种思路就是在A表上建立触发器，当A表插入数据的时候就用在触发器中累加之前的金额插入到B表中，这样就不会像思路一中笛卡尔积后然后比较时间进行累加

Answer 7

在服务器上跑了一下34万条数据，按照每一条都统计上面所有的sum，用时1小时16分钟，根据数据库跟踪显示的数据，CPU使用了4616219，Reads是91247318我们有个程序是有这个功能的，用的是游标从头到尾走一遍，累计值用变量存储，每一条记录先加上累计值，然后把记录和累计值写入插入另一张表，用时应该是几分钟的量级，看来还是游标的实际效果比较好

Answer 8

不可能吧，游标循环34万次，这里只进行一次sum，是不是没索引之类的？

Answer 9

再说一下，我们这个实际的表中，有两个金额字段需要sum，所以可能每个sum都写一个子查询消耗的时间比较大，还有就是当表中有10条记录的时候，计算最后一条记录，就需要sum出总共10条记录的值，如果100条，最后一条就要计算全部100条的值，这个运算量好像类似于行数的阶乘，但是如果用游标的话，可以一次循环就搞定，运算量应该是一个固定的系数C*行数，所以当行数多的时候，需要用的时间增长就越多

Answer 10

貌似只能上传图片啊，我是把那个计划保存出来了，就是一个XML文件

Answer 11

执行计划很大不？不大就传图片，大就直接贴XML吧

Answer 12

先看21楼这个图，索引假脱机，一般意味着，这里少了索引，你把鼠标移到上面，然后截图给我看看是哪两个表缺了索引，这个操作是在tempdb进行，如果tempdb性能很差，整个查询都很差

Answer 13

create table ta(日期  datetime,   金额 int)
go
insert into ta
select '20130101',   20 union all
select '20130203',   50 union all
select '20130225',   35 union all
select '20130301',   40 union all
select '20130502',   80
go
ALTER TABLE ta ADD tot INT, isupd BIT NOT NULL DEFAULT 0
GO
CREATE INDEX idx1 ON ta(日期)
go
DECLARE @n int
SET @n=0
--SET ROWCOUNT 3
--SELECT @n
--WHILE @@ROWCOUNT>0
UPDATE a  SET  @n=金额 + @n,tot=@n,isupd=1  FROM ta a WITH(INDEX=idx1) WHERE isupd=0
--SET ROWCOUNT 0
GO
select * from ta
/*
2013-01-01 00:00:00.000 20 20 1
2013-02-03 00:00:00.000 50 70 1
2013-02-25 00:00:00.000 35 105 1
2013-03-01 00:00:00.000 40 145 1
2013-05-02 00:00:00.000 80 225 1
*/
go
drop table ta
go如果感觉一次的io峰值很高， 可以去掉那四行的注释， 把set rowcount 3改为5000或者10000，  即每个批次处理这么多行。

Answer 14

如果不需要分批处理, isupd这个字段也不需加。

Answer 15

因为在以后的版本中 set rowcount n 不再对写操作有效，所以可以用WHILE @@ROWCOUNT>0
UPDATE TOP(3) a  SET  @n=金额 + @n,tot=@n,isupd=1  FROM ta a WITH(INDEX=idx1) WHERE isupd=0
这样的写法。

Answer 16

查询语句如下：
select FVOUID,FYEARNAME,FPERIODNAME,FVOUKEY,FEXP,FACCNUMBER,FDAMOUNT,FCAMOUNT,
(select sum(FDAMOUNT) from Voucher_ where FACCNUMBER = a.FACCNUMBER and FVOUID<=a.FVOUID) as SumD,
(select sum(FCAMOUNT) from Voucher_ where FACCNUMBER = a.FACCNUMBER and FVOUID<=a.FVOUID) as SumC
from Voucher_ a
Voucher_表有几个索引，FACCNUMBER 是非唯一索引，FVOUID没有加入索引,好像是这个原因

Answer 17

1,2,4楼的写法，经过优化分析，sql os进行的操作本质是一样的，没有必要比较优劣

Answer 18

把seek谓词那部分中，用到的表上的列，创建一个非聚集索引，再看看情况

Answer 19

測了下，好像遞歸累加的更慢
;with cte as 
(
  select rn=1, DATE= max(DATE),lj=SUM(money)  from (select top 1 * from #tb)  a
  union all
  select a.rn+1,b.date,a.lj +b.money    from cte a join (select rn=ROW_NUMBER () over (order by date),date,money from #tb ) b  on b.rn =a.rn +1 
)select * from cte 

Answer 20

create table #tb (date varchar(12) ,money int)
insert into #tb 
select '20130101' ,20 union all
select '20130203' ,50 union all
select '20130225' ,35 union all
select '20130301' ,40 union all
select '20130502' ,80 select date,sum(money) over(order by date) from #tb 
date         
------------ -----------
20130101     20
20130203     70
20130225     105
20130301     145
20130502     225(5 行受影响)(1285830 行受影响) 耗时43秒，环境：Xeon 5606 @2.13GHz win2008 sqlserver2012

Answer 21

这是个累积聚合的问题。首先，SQL 2008 R2及以前的版本中，sum后的开窗函数只能带partition by，不能带order by，即对sum后的开窗函数并未完整实现。但SQL Server 2012中，sum后的开窗函数已经完整实现。其次，不考虑开窗函数的话，无非两种解决方式：用子查询(即自关联)，或者用游标。子查询的复杂度是O(n^2)，即需要扫描两次；游标的复杂度是O(n)，即只需对表扫描一次。因此，如果表的规模小，子查询会更快；当表的规模大到一定程度后，用游标反而更快。绝大部分情况下，游标都会比基于集合的解决方案慢。但是连续聚合恰恰是个例外。

Answer 22

IF EXISTS(SELECT name FROM sys.objects WHERE name = 'test')
DROP TABLE test
go
CREATE TABLE test
(
dt nvarchar(10) ,

num int
)
GOINSERT INTO test (dt ,  num)
SELECT '20130101' , 20 UNION ALL
SELECT '20130203' , 50 UNION ALL
SELECT '20130225' , 35 UNION ALL
SELECT '20130502' , 40 UNION ALL
SELECT '20130502' , 80
---------------------------------執行查詢----------------------
SELECT dt , num , SUM(num) OVER(ORDER BY dt) FROM test/*
dt         num         
---------- ----------- -----------
20130101   20          20
20130203   50          70
20130225   35          105
20130502   40          225
20130502   80          225(5 row(s) affected)
*/