如何统计全天各个时间段产品销量情况(sqlserver)
程序员文章站
2023-11-22 13:37:10
数据库环境:sql server 2005
现有一个产品销售实时表,表数据如下:
字段name是产品名称,字段type是销售类型,1表示售出,2表示退货,字段num...
数据库环境:sql server 2005
现有一个产品销售实时表,表数据如下:
字段name是产品名称,字段type是销售类型,1表示售出,2表示退货,字段num是数量,字段ctime是操作时间。
要求:
在一行中统计24小时内所有货物的销售(售出,退货)数据,把日期考虑在内。
分析:
这实际上是行转列的一个应用,在进行行转列之前,需要补全24小时的所有数据。补全数据可以通过系统的数字辅助表
spt_values来实现,进行行转列时,根据type和处理后的ctime分组即可。
1.建表,导入数据
create table snake (name varchar(10 ),type int,num int, ctime datetime ) insert into snake values(' 方便面', 1,10 ,'2015-08-10 16:20:05') insert into snake values(' 香烟a ', 2,2 ,'2015-08-10 18:21:10') insert into snake values(' 香烟a ', 1,5 ,'2015-08-10 20:21:10') insert into snake values(' 香烟b', 1,6 ,'2015-08-10 20:21:10') insert into snake values(' 香烟b', 2,9 ,'2015-08-10 20:21:10') insert into snake values(' 香烟c', 2,9 ,'2015-08-10 20:21:10')
2.补全24小时的数据
/*枚举0-23自然数列*/ with x0 as ( select number as h from master..spt_values where type = 'p' and number >= 0 and number <= 23 ),/*找出表所有的日期*/ x1 as ( select distinct convert(varchar(100), ctime, 23) as d from snake ),/*补全所有日期的24小时*/ x2 as ( select x1.d , x0.h from x1 cross join x0 ), x3 as ( select name , type , num , datepart(hour, ctime) as h from snake ),/*整理行转列需要用到的数据*/ x4 as ( select x2.d , x2.h , x3.name , x3.type , x3.num from x2 left join x3 on x3.h = x2.h )
3.行转列
select isnull([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 , d as date from ( select d , h , type , num from x4 ) t pivot( sum(num) for h in ( [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23] ) ) t where type is not null
来看一下最终效果,只有1天的数据,可能看起来不是很直观。
本文的技术点有2个:
1.利用数字辅助表补全缺失的记录
2.pivot行转列函数的使用
以上内容是如何统计全天各个时间段产品销量情况(sqlserver)的全部内容,希望大家喜欢。