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

                 在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)

 

查询后结果   脚本下载

 

 

 

思考

 

留下的思考

1. 对空值的处理: select * from tab PIVOT (SUM(Amount)…

    这里我尝试用ISNULL(SUM(Amount),0.00) 去处理,但语法没有通过,我将继续尝试..

2. 脚本片段中获取日期序列,或许在其他统计脚本中也会复用,我准备写到标量函数或表值函数中试一下。

3. 常用的业务统计脚本中关联的表比较多,如何能有效避免重复,在最后结果集中减少使用 distinct ,而使用Group by 去过滤重复字段

这一个知识点我比较薄弱,不断总结,在分享经验给大家,少走弯路。

 

感谢

    我的好朋友欢,一直致力于SQL方面的统计,他给了我很多建议{

1.理解需求并开始写之前,要知道每个表里会出现什么数据

2.出现问题后,先查表与表之间是什么关联,关联从少到多,去检查错误

3.最核心的想清楚再写sql,如果脑子里不清楚就上手写,万一出现一个错误的想法,再纠正就麻烦了 

  }

   博学的龙叔,总是第一时间帮助大家理清混乱的逻辑。

   永远的涛哥,在不断修改涛哥的统计脚本中,使自己受益匪浅。

 

本文链接:在Sqlserver下巧用行列转换日期的数据统计,转载请注明。



You must enable javascript to see captcha here!

Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress

无觅相关文章插件,快速提升流量