在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 기반 앱
AI Clothes Remover
사진에서 옷을 제거하는 온라인 AI 도구입니다.
Undress AI Tool
무료로 이미지를 벗다
Clothoff.io
AI 옷 제거제
AI Hentai Generator
AI Hentai를 무료로 생성하십시오.
인기 기사
뜨거운 도구
메모장++7.3.1
사용하기 쉬운 무료 코드 편집기
SublimeText3 중국어 버전
중국어 버전, 사용하기 매우 쉽습니다.
스튜디오 13.0.1 보내기
강력한 PHP 통합 개발 환경
드림위버 CS6
시각적 웹 개발 도구
SublimeText3 Mac 버전
신 수준의 코드 편집 소프트웨어(SublimeText3)
뜨거운 주제
7322
9
1625
14
1350
46
1262
25
1209
29
웨이보에서 이전 웨이보를 날짜별로 검색하는 방법_웨이보에서 이전 웨이보를 날짜별로 검색하는 방법
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을 사용하세요. 주의하세요. 백업을 권장합니다. 삭제되거나 이름이 바뀐 개체에 대한 참조가 없는지 확인하려면 스키마 변경 사항을 확인하세요.
mdf 파일을 sqlserver로 가져오는 방법
Apr 08, 2024 am 11:41 AM
가져오기 단계는 다음과 같습니다. MDF 파일을 SQL Server의 데이터 디렉터리(일반적으로 C:\Program Files\Microsoft SQL Server\MSSQL\DATA)에 복사합니다. SSMS(SQL Server Management Studio)에서 데이터베이스를 열고 연결을 선택합니다. 추가 버튼을 클릭하고 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 포트 필드를 확인하세요.
전각 영문자를 반각 형태로 변환하는 실용적인 팁
Mar 26, 2024 am 09:54 AM
전각 영문자를 반각 형태로 변환하는 실용팁 현대생활에서 우리는 영문자를 자주 접하게 되고, 컴퓨터나 휴대폰, 기타 기기를 사용할 때 영문자를 입력해야 하는 경우가 많습니다. 그러나 때로는 영어의 전각 문자를 접하게 되므로 반각 형식을 사용해야 합니다. 그렇다면 전각 영문자를 반각 형태로 변환하는 방법은 무엇일까요? 다음은 몇 가지 실용적인 팁입니다. 먼저, 전각 영문자 및 숫자는 입력방법에서 전각 위치를 차지하는 문자를 말하며, 반각 영문자 및 숫자는 전각 위치를 차지한다.
sqlserver에서 실수로 삭제한 데이터베이스를 복구하는 방법
Apr 05, 2024 pm 10:39 PM
실수로 SQL Server 데이터베이스를 삭제한 경우 다음 단계를 수행하여 복구할 수 있습니다. 데이터베이스 활동 중지, 데이터베이스 로그 확인, 백업에서 복원, DBCC CHECKDB 사용 파티 도구. 데이터 손실을 방지하려면 데이터베이스를 정기적으로 백업하고 트랜잭션 로깅을 활성화하십시오.
