星空网 > 软件开发 > 数据库

全天各个时间段产品销量情况统计

数据库环境: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')

View Code

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       )

View Code

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

View Code

来看一下最终效果,只有1天的数据,可能看起来不是很直观。

全天各个时间段产品销量情况统计

本文的技术点有2个:

  1.利用数字辅助表补全缺失的记录

  2.pivot行转列函数的使用

 




原标题:全天各个时间段产品销量情况统计

关键词:时间

*特别声明:以上内容来自于网络收集,著作权属原作者所有,如有侵权,请联系我们: admin#shaoqun.com (#换成@)。

亚马逊全球收款服务结账日怎么算?:https://www.kjdsnews.com/a/1444179.html
欧盟UFI百度百科,哪里做SDS欧洲最新版本Safety Data Sheet:https://www.kjdsnews.com/a/1444180.html
做外贸如何应对客户询价后的反应:https://www.kjdsnews.com/a/1444181.html
亚马逊如何提高商品listing转化率?测评自养号优势和技巧有哪些?:https://www.kjdsnews.com/a/1444182.html
谷歌广告关键词调研的注意事项:https://www.kjdsnews.com/a/1444183.html
WhatsApp营销软件和Instagram社交媒体营销工具的比较,谁更胜一筹:https://www.kjdsnews.com/a/1444184.html
永康白云风景区怎么走 白云山风景区怎么去??:https://www.vstour.cn/a/363181.html
2022世界杯门票如何买?:https://www.vstour.cn/a/363182.html
相关文章
我的浏览记录
最新相关资讯
海外公司注册 | 跨境电商服务平台 | 深圳旅行社 | 东南亚物流