在Sqlserver下巧用行列转换日期的数据统计

浏览数：40 / 时间：2015年06月12日

在Sqlserver下巧用行列转换日期的数据统计

前言

在SQLSERVER 中有很多统计函数的基础语法，有使用Group By 或 partition by 后配合Sum,Count(*) 等用法。常应用于统计网站的PV流量、合同项目中月收入等业务场景中。在文中我分享下最近做过的统计小案例，和大家互相学习下：）

背景

合同中行项目按月收入的统计

1.业务逻辑及需求

1.1 表业务逻辑

合同是公司间互相签署的法律契约，一份合同从诞生起，就开始流转于公司的各个部门，最核心的还是盈亏的数值。盈亏是结果，数据的产生源于每个自然月或其他时段的汇总。往往在实际业务中，例如有些广告行业，立项是分为固定排期和合同活动收入。

固定排期一般以一个自然月为周期，例如[201503,201504]间产生的预收入；活动收入表中的活动是指收入周期不固定，可能ConfirmDate 发生在一个月中的若干天中，也可能在间隔一个月后发生。

无论是固定排期还是活动收入都和行项目有关，行项目是一个编号，一个行项目可以对应多次排期或活动收入的统计,在我给大家介绍的Demo中，将暂时考虑固定排期的情况。

1.2 项目的需求

统计合同中行项目的金额：分为结转金额数据汇总，和按自然月条件下金额的汇总。

2.准备的基础表

2.1 合同信息表

CREATE TABLE  ContractInfo --基本信息表
(
[ContractCode] [varchar](50) Primary key
,[CustomName] [varchar](100) NULL,
)

insert into ContractInfo
(ContractCode,CustomName)
values(‘30100013000861‘,‘弘化四方‘)
    ,(‘30100013000862‘,‘明心见性‘)
    ,(‘30100013000863‘,‘心绽莲花‘)

2.2 合同行项目表

CREATE TABLE ContractLine --合同行项目表
(
  [LineID] [int] IDENTITY(1,1) Primary Key NOT NULL,
  [ContractCode] [varchar](50) NOT NULL,  
)

insert into ContractLine
(ContractCode)
values(‘30100013000861‘)
      ,(‘30100013000862‘)
      ,(‘30100013000862‘) 
      ,(‘30100013000863‘)
      ,(‘30100013000863‘)

2.3 合同固定排期表

CREATE TABLE ContractSchedule  --合同固定排期表(
  [ScheduleID] [int]  Primary key  NOT NULL,-- 排期ID
    [LineID] [int] NOT NULL, -- 行项目ID
    [Period] [int] NOT NULL, --时间段
    [Amount] [decimal](18, 2) NOT NULL, --交易金额
)

insert into ContractSchedule
(ScheduleID,LineID,Period,Amount)
values
(89106,1,201507,90900.00)
,(89107,1,201508,9453.00)
,(89108,1,201510,13000.00)
,(89109,2,201501,12000.00)
,(89110,2,201503,11000.00)
,(89111,3,201509,9000.00)
,(89112,4,201510,8500.00)

3.补充其他（待）

基础知识点：

1.FOR XML PATH //用于统计时转换行列的格式，

参考：王波洋老师的灵活运用 FOR XML PATH

2.PIVOT (SUM(Amount)) For Period //用于基础表基础上的行列转换，

参考：大志若愚老师的纵表、横表互转的SQL

3.Select SUM(Amount) From ContractSchedule

group by LineID // 根据条件汇总数据

实现思路

逻辑

/*计算时间的基础序列*/ ->/*格式化日期序列*/ -> /*关联逻辑表，查询计算8月份之前的汇总,8月份之后的按月份统计*/

代码片段

 1 /*---------------计算时间的基础序列------------*/
 2 
 3 /*获取日期序列起始值*/
    DECLARE @sdate CHAR(10); 
    DECLARE @edate CHAR(10);      
 4  SET @sdate = ‘2015-08-01‘--开始日期
 5  SET @edate = ‘2015-12-1‘ 
 6  
 7  /*存入临时表*/
 8   SELECT * into #DateArr 
 9   from (
10      select 
11      CONVERT(varchar(6),DATEADD(MONTH,a.number,@sdate),112)             totalDate
12      FROM master..spt_values a --系统表
13      WHERE a.type = ‘P‘ 
14      AND number BETWEEN 0 AND (select DATEDIFF(MONTH,@sdate,@edate))
15 )a
16 
17 select * from #DateArr

1 /*格式化日期序列,用@Months接收*/
2       DECLARE @Months VARCHAR(1000);
3       DECLARE @SQL NVARCHAR(MAX);
4       
5     SET @SQL = ‘SELECT @Months=STUFF((SELECT DISTINCT ‘‘,[‘‘+totalDate+‘‘]‘‘ FROM #DateArr s
6     FOR XML PATH(‘‘‘‘)),1,1,‘‘‘‘)‘;
7     EXECUTE sp_executesql @SQL,N‘@Months VARCHAR(1000) OUTPUT‘,@Months OUTPUT;
8 
9    print @Months

 1 /*未关联时间序列前的基础数据*/
 2 with tab as(
 3 select 
 4        c.ContractCode 
 5        ,c.CustomName
 6        ,cl.LineID
 7        ,ISNULL(b.TheEndYearAmount,0) as NearAYearAgo
 8        ,cs.Amount
 9        ,cs.Period
10         from ContractInfo c
11     left join
12     ContractLine cl 
13     on c.ContractCode=cl.ContractCode
14     left join
15     ContractSchedule cs
16     on cs.LineID=cl.LineID
17             --计算8月份之前的统计
18         left join
19         (
20          select LineID,Sum(Amount) as TheEndYearAmount
21          from  
22          ContractSchedule
23          where Period between 201508 and 201512
24          group by LineID
25           --select * from ContractSchedule
26         )b on b.LineID=cl.LineID
27  ) select * from tab

 1 /*--------添加日期序列后的统计 --------*/
 2 SET @SQL=‘
 3 with tab as(
 4 select c.CustomName
 5        ,ISNULL(b.TheEndYearAmount,0) as NearAYearAgo
 6        ,c.ContractCode  --合同号
 7        ,cl.LineID  --合同的行ID
 8        ,cs.Amount  --待计算的数量
 9        ,cs.Period  --统计的日期
10         from ContractInfo c
11     left join
12     ContractLine cl 
13     on c.ContractCode=cl.ContractCode
14     left join
15     ContractSchedule cs
16     on cs.LineID=cl.LineID
17             --计算8月份之前的统计
18         left join
19         (
20          select LineID,Sum(Amount) as TheEndYearAmount
21          from  
22          ContractSchedule
23          where Period between 201412 and 201508  
24          group by LineID
25           --select * from ContractSchedule
26         )b on b.LineID=cl.LineID
27  ) select * from tab
28  PIVOT (SUM(Amount) FOR Period
29  IN(
30    ‘+@Months+‘
31  ))b
32  ‘
33 EXEC (@SQL)