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

在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，如果脑子里不清楚就上手写，万一出现一个错误的想法，再纠正就麻烦了 

  }

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

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