首頁

資料庫

mysql教程

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:25 PM

sqlsqlserver數據統計日期轉換

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

查询后结果 脚本下载

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

思考

留下的思考

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

MySQL的角色：Web應用程序中的數據庫Apr 17, 2025 am 12:23 AM

MySQL在Web應用中的主要作用是存儲和管理數據。 1.MySQL高效處理用戶信息、產品目錄和交易記錄等數據。 2.通過SQL查詢，開發者能從數據庫提取信息生成動態內容。 3.MySQL基於客戶端-服務器模型工作，確保查詢速度可接受。

mysql：構建您的第一個數據庫Apr 17, 2025 am 12:22 AM

構建MySQL數據庫的步驟包括：1.創建數據庫和表，2.插入數據，3.進行查詢。首先，使用CREATEDATABASE和CREATETABLE語句創建數據庫和表，然後用INSERTINTO語句插入數據，最後用SELECT語句查詢數據。

MySQL：一種對數據存儲的初學者友好方法Apr 17, 2025 am 12:21 AM

MySQL適合初學者，因為它易用且功能強大。 1.MySQL是關係型數據庫，使用SQL進行CRUD操作。 2.安裝簡單，需配置root用戶密碼。 3.使用INSERT、UPDATE、DELETE、SELECT進行數據操作。 4.複雜查詢可使用ORDERBY、WHERE和JOIN。 5.調試需檢查語法，使用EXPLAIN分析查詢。 6.優化建議包括使用索引、選擇合適數據類型和良好編程習慣。

MySQL初學者友好嗎？評估學習曲線Apr 17, 2025 am 12:19 AM

MySQL適合初學者，因為：1)易於安裝和配置，2)有豐富的學習資源，3)SQL語法直觀，4)工具支持強大。儘管如此，初學者需克服數據庫設計、查詢優化、安全管理和數據備份等挑戰。

SQL是一種編程語言嗎？澄清術語Apr 17, 2025 am 12:17 AM

是的，sqlisaprogramminglanguges pecialized fordatamanage.1）它具有焦點，focusingonwhattoachieveratherthanhow.2）sqlisessential forquerying forquerying，插入，更新，更新，和detletingdatainrelationalDatabases.3）

解釋酸的特性（原子，一致性，隔離，耐用性）。Apr 16, 2025 am 12:20 AM

ACID屬性包括原子性、一致性、隔離性和持久性，是數據庫設計的基石。 1.原子性確保事務要么完全成功，要么完全失敗。 2.一致性保證數據庫在事務前後保持一致狀態。 3.隔離性確保事務之間互不干擾。 4.持久性確保事務提交後數據永久保存。

MySQL：數據庫管理系統與編程語言Apr 16, 2025 am 12:19 AM

MySQL既是數據庫管理系統（DBMS），也與編程語言緊密相關。 1）作為DBMS，MySQL用於存儲、組織和檢索數據，優化索引可提高查詢性能。 2）通過SQL與編程語言結合，嵌入在如Python中，使用ORM工具如SQLAlchemy可簡化操作。 3）性能優化包括索引、查詢、緩存、分庫分錶和事務管理。