在Sqlserver下巧用行列转换日期的数据统计
- WBOYオリジナル
- 2016-06-07 15:25:58935ブラウズ
在Sqlserver下巧用行列转换日期的数据统计 前言 在SQLSERVER中有很多统计函数的基础语法，有使用Group By或partition by后配合Sum,Count(*)等用法。常应用于统计网站的PV流量、合同项目中月收入等业务场景中。在文中我分享下最近做过的统计小案例，和大家互
在Sqlserver下巧用行列转换日期的数据统计
前言
在SQLSERVER 中有很多统计函数的基础语法，有使用Group By 或 partition by 后配合Sum,Count(*) 等用法。常应用于统计网站的PV流量、合同项目中月收入等业务场景中。在文中我分享下最近做过的统计小案例，和大家互相学习下：）
背景
合同中行项目按月收入的统计
1.业务逻辑及需求
1.1 表业务逻辑
合同是公司间互相签署的法律契约，一份合同从诞生起，就开始流转于公司的各个部门，最核心的还是盈亏的数值。盈亏是结果，数据的产生源于每个自然月或其他时段的汇总。 往往在实际业务中，例如有些广告行业，立项是分为固定排期和合同活动收入。
固定排期一般以一个自然月为周期，例如[201503,201504]间产生的预收入；活动收入表中的活动是指收入周期不固定，可能ConfirmDate 发生在一个月中的若干天中，也可能在间隔一个月后发生。
无论是固定排期还是活动收入都和行项目有关，行项目是一个编号，一个行项目可以对应多次排期或活动收入的统计,在我给大家介绍的Demo中，将暂时考虑固定排期的情况。
1.2 项目的需求
统计合同中行项目的金额：分为结转金额数据汇总，和按自然月条件下金额的汇总。
2.准备的基础表
2.1 合同信息表
<span>CREATE</span> <span>TABLE</span> ContractInfo <span>--</span><span>基本信息表</span>
<span>(
</span><span>[</span><span>ContractCode</span><span>]</span> <span>[</span><span>varchar</span><span>]</span>(<span>50</span>) <span>Primary</span> <span>key</span><span>
,</span><span>[</span><span>CustomName</span><span>]</span> <span>[</span><span>varchar</span><span>]</span>(<span>100</span>) <span>NULL</span><span>,
)
</span><span>insert</span> <span>into</span><span> ContractInfo
(ContractCode,CustomName)
</span><span>values</span>(<span>'</span><span>30100013000861</span><span>'</span>,<span>'</span><span>弘化四方</span><span>'</span><span>)
,(</span><span>'</span><span>30100013000862</span><span>'</span>,<span>'</span><span>明心见性</span><span>'</span><span>)
,(</span><span>'</span><span>30100013000863</span><span>'</span>,<span>'</span><span>心绽莲花</span><span>'</span>)
2.2 合同行项目表
<span>CREATE</span> <span>TABLE</span> ContractLine <span>--</span><span>合同行项目表</span>
<span>(
</span><span>[</span><span>LineID</span><span>]</span> <span>[</span><span>int</span><span>]</span> <span>IDENTITY</span>(<span>1</span>,<span>1</span>) <span>Primary</span> <span>Key</span> <span>NOT</span> <span>NULL</span><span>,
</span><span>[</span><span>ContractCode</span><span>]</span> <span>[</span><span>varchar</span><span>]</span>(<span>50</span>) <span>NOT</span> <span>NULL</span><span>,
)
</span><span>insert</span> <span>into</span><span> ContractLine
(ContractCode)
</span><span>values</span>(<span>'</span><span>30100013000861</span><span>'</span><span>)
,(</span><span>'</span><span>30100013000862</span><span>'</span><span>)
,(</span><span>'</span><span>30100013000862</span><span>'</span><span>)
,(</span><span>'</span><span>30100013000863</span><span>'</span><span>)
,(</span><span>'</span><span>30100013000863</span><span>'</span>)
2.3 合同固定排期表
<span>CREATE</span> <span>TABLE</span> ContractSchedule <span>--</span><span>合同固定排期表(</span>
<span>[</span><span>ScheduleID</span><span>]</span> <span>[</span><span>int</span><span>]</span> <span>Primary</span> <span>key</span> <span>NOT</span> <span>NULL</span><span>,-- 排期ID
</span><span>[</span><span>LineID</span><span>]</span> <span>[</span><span>int</span><span>]</span> <span>NOT</span> <span>NULL</span><span>, -- 行项目ID
</span><span>[</span><span>Period</span><span>]</span> <span>[</span><span>int</span><span>]</span> <span>NOT</span> <span>NULL</span><span>, --时间段
</span><span>[</span><span>Amount</span><span>]</span> <span>[</span><span>decimal</span><span>]</span>(<span>18</span>, <span>2</span>) <span>NOT</span> <span>NULL</span><span>, --交易金额
)
</span><span>insert</span> <span>into</span><span> ContractSchedule
(ScheduleID,LineID,Period,Amount)
</span><span>values</span><span>
(</span><span>89106</span>,<span>1</span>,<span>201507</span>,<span>90900.00</span><span>)
,(</span><span>89107</span>,<span>1</span>,<span>201508</span>,<span>9453.00</span><span>)
,(</span><span>89108</span>,<span>1</span>,<span>201510</span>,<span>13000.00</span><span>)
,(</span><span>89109</span>,<span>2</span>,<span>201501</span>,<span>12000.00</span><span>)
,(</span><span>89110</span>,<span>2</span>,<span>201503</span>,<span>11000.00</span><span>)
,(</span><span>89111</span>,<span>3</span>,<span>201509</span>,<span>9000.00</span><span>)
,(</span><span>89112</span>,<span>4</span>,<span>201510</span>,<span>8500.00</span>)
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月份之后的按月份统计*/
代码片段
<span> 1</span> <span>/*</span><span>---------------计算时间的基础序列------------</span><span>*/</span> <span> 2</span> <span> 3</span> <span>/*</span><span>获取日期序列起始值</span><span>*/<br> <span>DECLARE @sdate CHAR(10); <br> DECLARE @edate CHAR(10); </span></span> <span> 4</span> <span>SET</span> <span>@sdate</span> <span>=</span> <span>'</span><span>2015-08-01</span><span>'</span><span>--</span><span>开始日期</span> <span> 5</span> <span>SET</span> <span>@edate</span> <span>=</span> <span>'</span><span>2015-12-1</span><span>'</span> <span> 6</span> <span> 7</span> <span>/*</span><span>存入临时表</span><span>*/</span> <span> 8</span> <span>SELECT</span> <span>*</span> <span>into</span><span> #DateArr </span><span> 9</span> <span>from</span><span> ( </span><span>10</span> <span>select</span> <span>11</span> <span>CONVERT</span>(<span>varchar</span>(<span>6</span>),<span>DATEADD</span>(<span>MONTH</span>,a.<span>number</span>,<span>@sdate</span>),<span>112</span><span>) totalDate </span><span>12</span> <span>FROM</span> master..spt_values a <span>--</span><span>系统表</span> <span>13</span> <span>WHERE</span> a.type <span>=</span> <span>'</span><span>P</span><span>'</span> <span>14</span> <span>AND</span> <span>number</span> <span>BETWEEN</span> <span>0</span> <span>AND</span> (<span>select</span> <span>DATEDIFF</span>(<span>MONTH</span>,<span>@sdate</span>,<span>@edate</span><span>)) </span><span>15</span> <span>)a </span><span>16</span> <span>17</span> <span>select</span> <span>*</span> <span>from</span> #DateArr
<span>1</span> <span>/*</span><span>格式化日期序列,用@Months接收</span><span>*/</span> <span>2</span> <span>DECLARE</span> <span>@Months</span> <span>VARCHAR</span>(<span>1000</span><span>); </span><span>3</span> <span>DECLARE</span> <span>@SQL</span> <span>NVARCHAR</span>(<span>MAX</span><span>); </span><span>4</span> <span>5</span> <span>SET</span> <span>@SQL</span> <span>=</span> <span>'</span><span>SELECT @Months=STUFF((SELECT DISTINCT </span><span>''</span><span>,[</span><span>''</span><span>+totalDate+</span><span>''</span><span>]</span><span>''</span><span> FROM #DateArr s </span><span>6</span> <span> FOR XML PATH(</span><span>''''</span><span>)),1,1,</span><span>''''</span><span>)</span><span>'</span><span>; </span><span>7</span> <span>EXECUTE</span> sp_executesql <span>@SQL</span>,N<span>'</span><span>@Months VARCHAR(1000) OUTPUT</span><span>'</span>,<span>@Months</span><span> OUTPUT; </span><span>8</span> <span>9</span> <span>print</span> <span>@Months</span>
<span> 1</span> <span>/*</span><span>未关联时间序列前的基础数据</span><span>*/</span> <span> 2</span> <span>with</span> tab <span>as</span><span>( </span><span> 3</span> <span>select</span> <span> 4</span> <span> c.ContractCode </span><span> 5</span> <span> ,c.CustomName </span><span> 6</span> <span> ,cl.LineID </span><span> 7</span> ,<span>ISNULL</span>(b.TheEndYearAmount,<span>0</span>) <span>as</span><span> NearAYearAgo </span><span> 8</span> <span> ,cs.Amount </span><span> 9</span> <span> ,cs.Period </span><span>10</span> <span>from</span><span> ContractInfo c </span><span>11</span> <span>left</span> <span>join</span> <span>12</span> <span> ContractLine cl </span><span>13</span> <span>on</span> c.ContractCode<span>=</span><span>cl.ContractCode </span><span>14</span> <span>left</span> <span>join</span> <span>15</span> <span> ContractSchedule cs </span><span>16</span> <span>on</span> cs.LineID<span>=</span><span>cl.LineID </span><span>17</span> <span>--</span><span>计算8月份之前的统计</span> <span>18</span> <span>left</span> <span>join</span> <span>19</span> <span> ( </span><span>20</span> <span>select</span> LineID,<span>Sum</span>(Amount) <span>as</span><span> TheEndYearAmount </span><span>21</span> <span>from</span> <span>22</span> <span> ContractSchedule </span><span>23</span> <span>where</span> Period <span>between</span> <span>201508</span> <span>and</span> <span>201512</span> <span>24</span> <span>group</span> <span>by</span><span> LineID </span><span>25</span> <span>--</span><span>select * from ContractSchedule</span> <span>26</span> )b <span>on</span> b.LineID<span>=</span><span>cl.LineID </span><span>27</span> ) <span>select</span> <span>*</span> <span>from</span> tab
<span> 1</span> <span>/*</span><span>--------添加日期序列后的统计 --------</span><span>*/</span> <span> 2</span> <span>SET</span> <span>@SQL</span><span>=</span><span>'</span> <span> 3</span> <span>with tab as( </span><span> 4</span> <span>select c.CustomName </span><span> 5</span> <span> ,ISNULL(b.TheEndYearAmount,0) as NearAYearAgo </span><span> 6</span> <span> ,c.ContractCode --合同号 </span><span> 7</span> <span> ,cl.LineID --合同的行ID </span><span> 8</span> <span> ,cs.Amount --待计算的数量 </span><span> 9</span> <span> ,cs.Period --统计的日期 </span><span>10</span> <span> from ContractInfo c </span><span>11</span> <span> left join </span><span>12</span> <span> ContractLine cl </span><span>13</span> <span> on c.ContractCode=cl.ContractCode </span><span>14</span> <span> left join </span><span>15</span> <span> ContractSchedule cs </span><span>16</span> <span> on cs.LineID=cl.LineID </span><span>17</span> <span> --计算8月份之前的统计 </span><span>18</span> <span> left join </span><span>19</span> <span> ( </span><span>20</span> <span> select LineID,Sum(Amount) as TheEndYearAmount </span><span>21</span> <span> from </span><span>22</span> <span> ContractSchedule </span><span>23</span> <span> where Period between 201412 and 201508 </span><span>24</span> <span> group by LineID </span><span>25</span> <span> --select * from ContractSchedule </span><span>26</span> <span> )b on b.LineID=cl.LineID </span><span>27</span> <span> ) select * from tab </span><span>28</span> <span> PIVOT (SUM(Amount) FOR Period </span><span>29</span> <span> IN( </span><span>30</span> <span>'</span><span>+</span><span>@Months</span><span>+</span><span>'</span> <span>31</span> <span> ))b </span><span>32</span> <span>'</span> <span>33</span> <span>EXEC</span> (<span>@SQL</span>)
查询后结果 脚本下载
思考
留下的思考
1. 对空值的处理： select * from tab PIVOT (SUM(Amount)...
这里我尝试用ISNULL（SUM(Amount)，0.00） 去处理，但语法没有通过，我将继续尝试..
2. 脚本片段中获取日期序列，或许在其他统计脚本中也会复用，我准备写到标量函数或表值函数中试一下。
3. 常用的业务统计脚本中关联的表比较多，如何能有效避免重复，在最后结果集中减少使用 distinct ，而使用Group by 去过滤重复字段
这一个知识点我比较薄弱，不断总结，在分享经验给大家，少走弯路。
感谢
我的好朋友欢，一直致力于SQL方面的统计，他给了我很多建议{
1.理解需求并开始写之前，要知道每个表里会出现什么数据
2.出现问题后，先查表与表之间是什么关联，关联从少到多，去检查错误
3.最核心的想清楚再写sql，如果脑子里不清楚就上手写，万一出现一个错误的想法，再纠正就麻烦了
}
博学的龙叔，总是第一时间帮助大家理清混乱的逻辑。
永远的涛哥，在不断修改涛哥的统计脚本中，使自己受益匪浅。
声明：
この記事の内容はネチズンが自主的に寄稿したものであり、著作権は原著者に帰属します。このサイトは、それに相当する法的責任を負いません。盗作または侵害の疑いのあるコンテンツを見つけた場合は、admin@php.cn までご連絡ください。
前の記事：程序猿是如何解决SQLServer占CPU100%的次の記事：MySQL 同步（三）