在Sqlserver下巧用行列转换日期的数据统计
? ? ? ? ? ? ? ? ?在Sqlserver下巧用行列轉換日期的數據統計
?
前言
????在SQLSERVER?中有很多統計函數的基礎語法，有使用Group By?或?partition by?后配合Sum,Count(*)?等用法。常應用于統計網站的PV流量、合同項目中月收入等業務場景中。在文中我分享下最近做過的統計小案例，和大家互相學習下：）?
背景?
?????? 合同中行項目按月收入的統計
??1.業務邏輯及需求?
? 1.1 表業務邏輯?
??? 合同是公司間互相簽署的法律契約，一份合同從誕生起，就開始流轉于公司的各個部門，最核心的還是盈虧的數值。盈虧是結果，數據的產生源于每個自然月或其他時段的匯總。?往往在實際業務中，例如有些廣告行業，立項是分為固定排期和合同活動收入。??
?? 固定排期一般以一個自然月為周期，例如[201503,201504]間產生的預收入；活動收入表中的活動是指收入周期不固定，可能ConfirmDate??發生在一個月中的若干天中，也可能在間隔一個月后發生。
?? 無論是固定排期還是活動收入都和行項目有關，行項目是一個編號，一個行項目可以對應多次排期或活動收入的統計,在我給大家介紹的Demo中，將暫時考慮固定排期的情況。
?1.2 項目的需求
?? 統計合同中行項目的金額：分為結轉金額數據匯總，和按自然月條件下金額的匯總。
?
?? 2.準備的基礎表
?
? ? 2.1 合同信息表?
CREATE TABLE ContractInfo --基本信息表 ( [ContractCode] [varchar](50) Primary key ,[CustomName] [varchar](100) NULL, )insert into ContractInfo (ContractCode,CustomName) values('30100013000861','弘化四方'),('30100013000862','明心見性'),('30100013000863','心綻蓮花')??? 2.2?合同行項目表
CREATE TABLE ContractLine --合同行項目表 ([LineID] [int] IDENTITY(1,1) Primary Key NOT NULL,[ContractCode] [varchar](50) NOT NULL, )insert into ContractLine (ContractCode) values('30100013000861'),('30100013000862'),('30100013000862') ,('30100013000863'),('30100013000863')?2.3?合同固定排期表
CREATE TABLE ContractSchedule --合同固定排期表([ScheduleID] [int] Primary key NOT NULL,-- 排期ID[LineID] [int] NOT NULL, -- 行項目ID[Period] [int] NOT NULL, --時間段[Amount] [decimal](18, 2) NOT NULL, --交易金額 )insert into ContractSchedule (ScheduleID,LineID,Period,Amount) values (89106,1,201507,90900.00) ,(89107,1,201508,9453.00) ,(89108,1,201510,13000.00) ,(89109,2,201501,12000.00) ,(89110,2,201503,11000.00) ,(89111,3,201509,9000.00) ,(89112,4,201510,8500.00)?
??? 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月份之后的按月份統計*/
?
代碼片段
1 /*---------------計算時間的基礎序列------------*/ 2 3 /*獲取日期序列起始值*/DECLARE @sdate CHAR(10);
DECLARE @edate CHAR(10);?? 4 SET @sdate = '2015-08-01'--開始日期 5 SET @edate = '2015-12-1' 6 7 /*存入臨時表*/ 8 SELECT * into #DateArr 9 from ( 10 select 11 CONVERT(varchar(6),DATEADD(MONTH,a.number,@sdate),112) totalDate 12 FROM master..spt_values a --系統表 13 WHERE a.type = 'P' 14 AND number BETWEEN 0 AND (select DATEDIFF(MONTH,@sdate,@edate)) 15 )a 16 17 select * from #DateArr
?
1 /*格式化日期序列,用@Months接收*/ 2 DECLARE @Months VARCHAR(1000); 3 DECLARE @SQL NVARCHAR(MAX); 4 5 SET @SQL = 'SELECT @Months=STUFF((SELECT DISTINCT '',[''+totalDate+'']'' FROM #DateArr s 6 FOR XML PATH('''')),1,1,'''')'; 7 EXECUTE sp_executesql @SQL,N'@Months VARCHAR(1000) OUTPUT',@Months OUTPUT; 8 9 print @Months?
1 /*未關聯時間序列前的基礎數據*/ 2 with tab as( 3 select 4 c.ContractCode 5 ,c.CustomName 6 ,cl.LineID 7 ,ISNULL(b.TheEndYearAmount,0) as NearAYearAgo 8 ,cs.Amount 9 ,cs.Period 10 from ContractInfo c 11 left join 12 ContractLine cl 13 on c.ContractCode=cl.ContractCode 14 left join 15 ContractSchedule cs 16 on cs.LineID=cl.LineID 17 --計算8月份之前的統計 18 left join 19 ( 20 select LineID,Sum(Amount) as TheEndYearAmount 21 from 22 ContractSchedule 23 where Period between 201508 and 201512 24 group by LineID 25 --select * from ContractSchedule 26 )b on b.LineID=cl.LineID 27 ) select * from tab 1 /*--------添加日期序列后的統計 --------*/ 2 SET @SQL=' 3 with tab as( 4 select c.CustomName 5 ,ISNULL(b.TheEndYearAmount,0) as NearAYearAgo 6 ,c.ContractCode --合同號 7 ,cl.LineID --合同的行ID 8 ,cs.Amount --待計算的數量 9 ,cs.Period --統計的日期 10 from ContractInfo c 11 left join 12 ContractLine cl 13 on c.ContractCode=cl.ContractCode 14 left join 15 ContractSchedule cs 16 on cs.LineID=cl.LineID 17 --計算8月份之前的統計 18 left join 19 ( 20 select LineID,Sum(Amount) as TheEndYearAmount 21 from 22 ContractSchedule 23 where Period between 201412 and 201508 24 group by LineID 25 --select * from ContractSchedule 26 )b on b.LineID=cl.LineID 27 ) select * from tab 28 PIVOT (SUM(Amount) FOR Period 29 IN( 30 '+@Months+' 31 ))b 32 ' 33 EXEC (@SQL)?
查詢后結果?? 腳本下載
?
?
?
思考
?
留下的思考
1. 對空值的處理：?select * from tab PIVOT (SUM(Amount)...
??? 這里我嘗試用ISNULL（SUM(Amount)，0.00） 去處理，但語法沒有通過，我將繼續嘗試..
2. 腳本片段中獲取日期序列，或許在其他統計腳本中也會復用，我準備寫到標量函數或表值函數中試一下。
3. 常用的業務統計腳本中關聯的表比較多，如何能有效避免重復，在最后結果集中減少使用 distinct ，而使用Group by 去過濾重復字段
這一個知識點我比較薄弱，不斷總結，在分享經驗給大家，少走彎路。
?
感謝
????我的好朋友歡，一直致力于SQL方面的統計，他給了我很多建議{
1.理解需求并開始寫之前，要知道每個表里會出現什么數據
2.出現問題后，先查表與表之間是什么關聯，關聯從少到多，去檢查錯誤
3.最核心的想清楚再寫sql，如果腦子里不清楚就上手寫，萬一出現一個錯誤的想法，再糾正就麻煩了???}
?? 博學的龍叔，總是第一時間幫助大家理清混亂的邏輯。
???永遠的濤哥，在不斷修改濤哥的統計腳本中，使自己受益匪淺。
?
posted on 2015-03-31 10:06 NET未來之路 閱讀(...) 評論(...) 編輯 收藏轉載于:https://www.cnblogs.com/lonelyxmas/p/4380084.html
《新程序員》：云原生和全面數字化實踐50位技術專家共同創作，文字、視頻、音頻交互閱讀總結
以上是生活随笔為你收集整理的在Sqlserver下巧用行列转换日期的数据统计的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： java执行时的两个常见问题（无法加载主
- 下一篇： python requests