在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，如果脑子里不清楚就上手写，万一出现一个错误的想法，再纠正就麻烦了
}
博学的龙叔，总是第一时间帮助大家理清混乱的逻辑。
永远的涛哥，在不断修改涛哥的统计脚本中，使自己受益匪浅。
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
AI Hentai Generator
免费生成ai无尽的。
热门文章
热工具
记事本++7.3.1
好用且免费的代码编辑器
SublimeText3汉化版
中文版，非常好用
禅工作室 13.0.1
功能强大的PHP集成开发环境
Dreamweaver CS6
视觉化网页开发工具
SublimeText3 Mac版
神级代码编辑软件(SublimeText3)
热门话题
微博怎么按日期查找以前的微博_微博按日期查找以前的微博方法
Mar 30, 2024 pm 07:26 PM
1、首先打开手机网络浏览器，搜索微博网页版，进入后点击左上角头像按钮。2、然后点击右上角设置。3、点击设置里面的版本切换选项。4、接着在版本切换里选择彩版选项。5、点击搜索，进入搜索页面。6、输入关键词后，点击找人。7、出来的搜索完成界面点击筛选。8、最后在发布时间栏输入具体日期后，点击筛选即可。
sqlserver数据库中已存在名为的对象怎么解决
Apr 05, 2024 pm 09:42 PM
对于 SQL Server 数据库中已存在同名对象，需要采取以下步骤：确认对象类型（表、视图、存储过程）。如果对象为空，可使用 IF NOT EXISTS 跳过创建。如果对象有数据，使用不同名称或修改结构。使用 DROP 删除现有对象（谨慎操作，建议备份）。检查架构更改，确保没有引用删除或重命名的对象。
sqlserver怎么导入mdf文件
Apr 08, 2024 am 11:41 AM
导入步骤如下：将 MDF 文件复制到 SQL Server 的数据目录（通常为 C:\Program Files\Microsoft SQL Server\MSSQL\DATA）。在 SQL Server Management Studio（SSMS）中，打开数据库并选择“附加”。单击“添加”按钮，选择 MDF 文件。确认数据库名称，点击确定按钮即可。
Hibernate 框架中 HQL 和 SQL 的区别是什么？
Apr 17, 2024 pm 02:57 PM
HQL和SQL在Hibernate框架中进行比较：HQL（1.面向对象语法，2.数据库无关的查询，3.类型安全），而SQL直接操作数据库（1.与数据库无关的标准，2.可执行复杂查询和数据操作）。
sqlserver服务无法启动怎么办
Apr 05, 2024 pm 10:00 PM
当 SQL Server 服务无法启动时，可采取以下步骤解决：检查错误日志以确定根本原因。确保服务帐户具有启动服务的权限。检查依赖项服务是否正在运行。禁用防病毒软件。修复 SQL Server 安装。如果修复不起作用，重新安装 SQL Server。
怎么查看sqlserver端口号
Apr 05, 2024 pm 09:57 PM
要查看 SQL Server 端口号：打开 SSMS，连接到服务器。在对象资源管理器中找到服务器名称，右键单击它，然后选择“属性”。在“连接”选项卡中，查看“TCP 端口”字段。
sqlserver数据库在哪里
Apr 05, 2024 pm 08:21 PM
SQL Server 数据库文件通常存储在以下默认位置：Windows: C:\Program Files\Microsoft SQL Server\MSSQL\DATALinux: /var/opt/mssql/data可通过修改数据库文件路径设置来自定义数据库文件位置。
sqlserver误删数据库怎么恢复
Apr 05, 2024 pm 10:39 PM
若误删 SQL Server 数据库，可采取以下步骤恢复：停止数据库活动；备份日志文件；检查数据库日志；恢复选项：从备份恢复；从事务日志恢复；使用 DBCC CHECKDB；使用第三方工具。请定期备份数据库并启用事务日志以防止数据丢失。
