数据库环境:SQL SERVER 2005
现有一个产品销售实时表,表数据如下:
字段name是产品名称,字段type是销售类型,1表示售出,2表示退货,字段num是数量,字段ctime是操作时间。
要求:
在一行中统计24小时内所有货物的销售(售出,退货)数据,把日期考虑在内。
分析:
这实际上是行转列的一个应用,在进行行转列之前,需要补全24小时的所有数据。补全数据可以通过系统的数字辅助表
spt_values来实现,进行行转列时,根据type和处理后的ctime分组即可。
1.建表,导入数据
1
2
3
4
5
6
7
|
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小时的数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
/*枚举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.行转列
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
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)的全部内容,希望大家喜欢。