如何使用sqlserver统计全天各个时间段产品销量情况
本篇文章为大家展示了如何使用sqlserver统计全天各个时间段产品销量情况,内容简明扼要并且容易理解,绝对能使你眼前一亮,通过这篇文章的详细介绍希望你能有所收获。
现有一个产品销售实时表,表数据如下:
字段name是产品名称,字段type是销售类型,1表示售出,2表示退货,字段num是数量,字段ctime是操作时间。
要求:
在一行中统计24小时内所有货物的销售(售出,退货)数据,把日期考虑在内。
分析:
这实际上是行转列的一个应用,在进行行转列之前,需要补全24小时的所有数据。补全数据可以通过系统的数字辅助表
spt_values来实现,进行行转列时,根据type和处理后的ctime分组即可。
1.建表,导入数据
CREATETABLEsnake(nameVARCHAR(10),typeINT,numINT,ctimeDATETIME)INSERTINTOsnakeVALUES('方便面',1,10,'2015-08-1016:20:05')INSERTINTOsnakeVALUES('香烟A',2,2,'2015-08-1018:21:10')INSERTINTOsnakeVALUES('香烟A',1,5,'2015-08-1020:21:10')INSERTINTOsnakeVALUES('香烟B',1,6,'2015-08-1020:21:10')INSERTINTOsnakeVALUES('香烟B',2,9,'2015-08-1020:21:10')INSERTINTOsnakeVALUES('香烟C',2,9,'2015-08-1020:21:10')
2.补全24小时的数据
/*枚举0-23自然数列*/WITHx0AS(SELECTnumberAShFROMmaster..spt_valuesWHEREtype='P'ANDnumber>=0ANDnumber<=23),/*找出表所有的日期*/x1AS(SELECTDISTINCTCONVERT(VARCHAR(100),ctime,23)ASdFROMsnake),/*补全所有日期的24小时*/x2AS(SELECTx1.d,x0.hFROMx1CROSSJOINx0),x3AS(SELECTname,type,num,DATEPART(hour,ctime)AShFROMsnake),/*整理行转列需要用到的数据*/x4AS(SELECTx2.d,x2.h,x3.name,x3.type,x3.numFROMx2LEFTJOINx3ONx3.h=x2.h)
3.行转列
SELECTISNULL([0],0)AS[00],ISNULL([1],0)AS[01],ISNULL([2],0)AS[02],ISNULL([3],0)AS[03],ISNULL([4],0)AS[04],ISNULL([5],0)AS[05],ISNULL([6],0)AS[06],ISNULL([3],7)AS[07],ISNULL([8],0)AS[08],ISNULL([9],0)AS[09],ISNULL([10],0)AS[10],ISNULL([3],11)AS[11],ISNULL([12],0)AS[12],ISNULL([13],0)AS[13],ISNULL([14],0)AS[14],ISNULL([3],15)AS[15],ISNULL([16],0)AS[16],ISNULL([17],0)AS[17],ISNULL([18],0)AS[18],ISNULL([19],15)AS[19],ISNULL([20],0)AS[20],ISNULL([21],0)AS[21],ISNULL([22],0)AS[22],ISNULL([23],15)AS[23],type,dASdateFROM(SELECTd,h,type,numFROMx4)tPIVOT(SUM(num)FORhIN([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23]))tWHEREtypeISNOTNULL
来看一下最终效果,只有1天的数据,可能看起来不是很直观。
本文的技术点有2个:
1.利用数字辅助表补全缺失的记录
2.pivot行转列函数的使用
上述内容就是如何使用sqlserver统计全天各个时间段产品销量情况,你们学到知识或技能了吗?如果还想学到更多技能或者丰富自己的知识储备,欢迎关注亿速云行业资讯频道。
声明:本站所有文章资源内容,如无特殊说明或标注,均为采集网络资源。如若本站内容侵犯了原著者的合法权益,可联系本站删除。