欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

SQL:累计求和的例子 博客分类: sql  

程序员文章站 2024-03-21 08:14:58
...

例一、譬如实现如下结果

月份(month) 数量(number)   累计数量(total)

2015-03               1                   1

2015-04               2                   3

2015-05               3                   7

2015-03               1                   1

2015-04               2                   3

2015-05               3                   7

-------------------------------------------------------

sql示例如下:

select

 b.month,

 b.number,

 sum(a.month)

from TABLE a

join TABLE b

on a.month=b.month

group by b.month,b.number

小结:其实实现累计的核心就是将相同的表在做一次关联,让里面的值作为比较参数。

 

 

 

例二、有以下表

日期 增加 减少

2015-04-24 18709 12608

2015-04-25 28508 11412

2015-04-26 39092 20858

2015-04-27 80146 57995

2015-04-28 53581 19584

2015-04-29 50609 26319

2015-04-30 52969 28884

2015-05-01 79146 70007

2015-05-02 50536 38031

2015-05-03 58161 42329

2015-05-04 27287 14137

我需要用SQL语句得到下面的东西:

日期 增加 减少 留存率

2015-04-24 18709 12608 =(18709-12608)

2015-04-25 28508 11412 =(18709-12608)+(28508-11412)

2015-04-26 39092 20858 =(18709-12608)+(28508-11412)+( 39092-20858)

2015-04-27 80146 57995 下面依次类推

2015-04-28 53581 19584 …

2015-04-29 50609 26319 …

2015-04-30 52969 28884 …

2015-05-01 79146 70007 …

2015-05-02 50536 38031 …

2015-05-03 58161 42329 …

2015-05-04 27287 14137 …

非递归的写法(应该所有的数据库都支持)

SELECT t1.日期, t1.增加, t1.减少,
           SUM(t2.增加-t2.减少) 留存率
      FROM table1 t1
      JOIN table1 t2
        ON t2.日期 <= t1.日期
  GROUP BY t1.日期, t1.增加, t1.减少

 递归的写法(SQL Server)

WITH t0 AS (
    SELECT *,
           ROW_NUMBER() OVER(ORDER BY 日期) rn
      FROM table1
)
,t AS (
    SELECT rn,
           日期, 增加, 减少,
           (增加-减少) 留存率
      FROM t0
     WHERE rn = 1

    UNION ALL

    SELECT t2.rn,
           t2.日期, t2.增加, t2.减少,
           t1.留存率 + (t2.增加-t2.减少) 留存率
      FROM t t1
      JOIN t0 t2
        ON t2.rn = t1.rn + 1
)
SELECT 日期, 增加, 减少, 留存率
  FROM t

 

 

 

例三(自己写的):

create table #p  
(  
    id int,  
    year varchar(4),  
    month varchar(2),  
    qty int  
)  
   
insert into #p values (1,'2012','1',10);  
insert into #p values (2,'2012','2',15);  
insert into #p values (3,'2012','3',20);  
insert into #p values (4,'2013','5',30);  
insert into #p values (5,'2013','6',35);  
insert into #p values (6,'2013','7',40);  
insert into #p values (7,'2013','8',45)  
insert into #p values (8,'2013','9',50)  
insert into #p values (9,'2013','12',100)  
insert into #p values (10,'2014','1',10);  
insert into #p values (11,'2014','3',15);  
insert into #p values (12,'2014','4',20);  
insert into #p values (13,'2014','5',30);  
insert into #p values (14,'2014','7',40);  
insert into #p values (15,'2014','8',45);  
insert into #p values (16,'2014','9',50);  
insert into #p values (17,'2015','5',30);  
insert into #p values (18,'2016','7',40);  
insert into #p values (19,'2017','8',45);  
insert into #p values (120,'2017','9',50);  
  
select * from #p  

select t1.year,t1.month,SUM(t2.qty) from #p t1 join #p t2 on t1.year=t2.year and cast(t2.month as int)<=cast(t1.month as int) group by t1.year,t1.month order by cast(t1.year as int),cast(t1.month as int)