在项目开发中对数据分时统计的一个SQL

最新推荐文章于 2021-03-01 16:17:05 发布
最新推荐文章于 2021-03-01 16:17:05 发布
阅读量1.1k 收藏
点赞数
分类专栏： Oracle 文章标签： sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xdpost/article/details/3134851
版权

/* Formatted on 2008/10/24 08:23 (Formatter Plus v4.8.8) */
SELECT   t2.userid userid, COUNT (t2.rono) AS totalnum,
         SUM (t2.pass_num7_9) a1pass, SUM (ng_num7_9) a1ng,
         SUM (pass_num9_11) a2pass, SUM (ng_num9_11) a2ng,
         SUM (pass_num11_14) a3pass, SUM (ng_num11_14) a3ng,
         SUM (pass_num14_16) a4pass, SUM (ng_num14_16) a4ng,
         SUM (pass_num16_17) a5pass, SUM (ng_num16_17) a5ng,
         SUM (pass_num17_19) a6pass, SUM (ng_num17_19) a6ng,
         SUM (pass_num19_22) a7pass, SUM (ng_num19_22) a7ng,
         SUM (pass_num22_24) a8pass, SUM (ng_num22_24) a8ng,
         SUM (pass_num0_3) a9pass, SUM (ng_num0_3) a9ng,
         SUM (pass_num3_6) a10pass, SUM (ng_num3_6) a10ng,
         SUM (pass_num6_7) a11pass, SUM (ng_num6_7) a11ng
    FROM (SELECT userid, rono,
                   DECODE (SIGN (hours - 7),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (9 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'PASS', 1, 0) AS pass_num7_9,
                   DECODE (SIGN (hours - 7), 1, 1, 0, 1, -1, 0)
                 * DECODE (SIGN (9 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'NG', 1, 0) AS ng_num7_9,
                   DECODE (SIGN (hours - 9),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (11 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'PASS', 1, 0) AS pass_num9_11,
                   DECODE (SIGN (hours - 9), 1, 1, 0, 1, -1, 0)
                 * DECODE (SIGN (11 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'NG', 1, 0) AS ng_num9_11,
                   DECODE (SIGN (hours - 11),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (14 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'PASS', 1, 0) AS pass_num11_14,
                   DECODE (SIGN (hours - 11),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (14 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'NG', 1, 0) AS ng_num11_14,
                   DECODE (SIGN (hours - 14),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (16 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'PASS', 1, 0) AS pass_num14_16,
                   DECODE (SIGN (hours - 14),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (16 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'NG', 1, 0) AS ng_num14_16,
                   DECODE (SIGN (hours - 16),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (17 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'PASS', 1, 0) AS pass_num16_17,
                   DECODE (SIGN (hours - 16),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (17 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'NG', 1, 0) AS ng_num16_17,
                   DECODE (SIGN (hours - 17),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (19 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'PASS', 1, 0) AS pass_num17_19,
                   DECODE (SIGN (hours - 17),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (19 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'NG', 1, 0) AS ng_num17_19,
                   DECODE (SIGN (hours - 19),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (22 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'PASS', 1, 0) AS pass_num19_22,
                   DECODE (SIGN (hours - 19),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (22 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'NG', 1, 0) AS ng_num19_22,
                   DECODE (SIGN (hours - 22),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (24 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'PASS', 1, 0) AS pass_num22_24,
                   DECODE (SIGN (hours - 22),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (24 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'NG', 1, 0) AS ng_num22_24,
                   DECODE (SIGN (hours - 0),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (3 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'PASS', 1, 0) AS pass_num0_3,
                   DECODE (SIGN (hours - 0), 1, 1, 0, 1, -1, 0)
                 * DECODE (SIGN (3 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'NG', 1, 0) AS ng_num0_3,
                   DECODE (SIGN (hours - 3),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (6 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'PASS', 1, 0) AS pass_num3_6,
                   DECODE (SIGN (hours - 3), 1, 1, 0, 1, -1, 0)
                 * DECODE (SIGN (6 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'NG', 1, 0) AS ng_num3_6,
                   DECODE (SIGN (hours - 6),
                           1, 1,
                           0, 1,
                           -1, 0
                          )
                 * DECODE (SIGN (7 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'PASS', 1, 0) AS pass_num6_7,
                   DECODE (SIGN (hours - 6), 1, 1, 0, 1, -1, 0)
                 * DECODE (SIGN (7 - hours), 1, 1, 0, 0, -1, 0)
                 * DECODE (RESULT, 'NG', 1, 0) AS ng_num6_7
            FROM (SELECT userid, rono, seqno, station, enddt, RESULT,
                         TO_NUMBER (TO_CHAR (enddt, 'HH24'), '99') AS hours
                    FROM ertbroflwd t1
                   WHERE station = :station
                     AND enddt >= bs_to_timestamp_tz (:startdt)
                     AND enddt <= bs_to_timestamp_tz (:enddt)
                     AND (RESULT = 'PASS' OR RESULT = 'NG')
                     AND seqno =
                            (SELECT MAX (seqno) seqno
                               FROM ertbroflwd t0
                              WHERE t1.rono = t0.rono
                                AND t1.station = t0.station
                                AND t0.enddt >= bs_to_timestamp_tz (:startdt)
                                AND t0.enddt <= bs_to_timestamp_tz (:enddt)
                                AND (RESULT = 'PASS' OR RESULT = 'NG')))) t2
GROUP BY t2.userid
ORDER BY t2.userid

xdpost
关注
专栏目录
SQL进阶技巧：如何分区间或分时段统计数据？| 等距分桶问题应用
石榴姐yyds
06-15 3000
0 需求 有一批交易数据，并希望每5秒中汇总一下这些数据。trx_log数据如下： trx_id trx_date trx_cnt 1 28-JUL-2005 19:03:07 44 2 28-JUL-2005 19:03:08 18 3 28-JUL-2005 19:03:09 23 4 28-JUL-2005 19:03:10 29 5 28-JUL-2005 19:03:11 27 6 28-JU
数据库管理与开发项目教程-教案-05项目四：使用T-SQL查询表数据-任务1简单查询.docx
最新发布
12-18
简单查询主要涉及SELECT语句，它是SQL中最基础也是最重要的部分，用于从一个或多个表中提取数据。SELECT语句的基本语法格式如下： ```sql SELECT [DISTINCT] 选择列表 FROM 表或视图 [WHERE 检索的条件] [GROUP BY ...
SQL分时段统计
生命因你而精彩！明天更美好！
01-18 3536
pupstar (v5star) 等　级： 结帖率：100.00% 楼主发表于：2010-01-02 16:42:10我现在有一张表：   列名1                时间 03174190188 2009-11-01 07:17:39.217 015224486575 2009-11-01 08:01
mysql 分时统计_如何写一条SQL，分时段统计结果？
weixin_39796878的博客
01-19 506
One SQL by Case When你的需求用case when不就是可以做到的么？一条SQL搞定！！！！selectsum(casewhentime>= xx and time < xx + (yy - xx) / 10 then1else0end)as time_1,sum(casewhentime>= xx + (yy - xx) / 10 a...
使用SQL 的CASE 语句做分时段统计
业精于勤，荒于嬉
03-01 1247
某日老板突然要求，想看各时段的订单数量。心想，订单表中有纪录时间点，这应该不成问题，只是这 SQL 语句该如何下？如果一个小时统计一次，总不能分24次吧！ 订单数据表 orders 如下： 希望得出的结果如下： 利用 CASE 语句就能完成任务。 SELECT CASE WHEN DATE_FORMAT(T1.CreateTime, "%H") = 0 THEN "H00" WHEN DATE_FORMAT(T1.CreateTime, "%H") = 1 THEN "H01"
按时间分组统计的SQL语句
热门推荐
michealcxt的专栏
05-05 1万+
如下表table1： 　　日期(exportDate)               数量(amount) 　　 --------------                    ----------- 　　14-2月 -08                       20 　　10-3月 -08                       2 　    14-4月 -08      
sqlserver/mysql按天、按小时、按分钟统计连续时间段数据【推荐】
09-09
在SQL Server和MySQL数据库中，按天、按小时或按分钟统计连续时间段数据是一项常见的需求，这有助于分析数据峰值和趋势。以下是如何实现这一目标的详细步骤和知识点： 1. **master..spt_values表**： - `master.....
SQL在森林资源二类调查数据整理中的应用——以广西贺州市八步区项目为例.pdf
09-19
SQL以记录集合作为操作对象，所有语句都是在对集合进行操作，其输出可以作为另一语句的输入，从而实现了语句的嵌套，使得SQL语言具有很强的灵活性和功能强大。 SQL语言简单易学，且结构简洁，功能强大，已经成为...
“火炬统计报表系统”中平衡关系在SQL Server中的实现.pdf
09-19
在深入探讨《“火炬统计报表系统”中平衡关系在SQL Server中的实现》这篇文献中的知识点之前，我们首先需要了解什么是平衡关系、数据完整性，以及它们在数据库管理系统SQL Server中的应用和重要性。 平衡关系在统计...
行业类别数据字典SQL
10-29
在这个特定的案例中，"行业类别数据字典SQL"是一个用于存储国民经济行业分类信息的SQL脚本，这通常是为了帮助开发者快速构建与行业类别相关的数据库，并进行高效开发。 首先，我们要理解国民经济行业分类。这是国家...
sql按分时段统计
weixin_34413065的博客
03-11 1112
SELECT SUBSTRING(replace(replace(replace(CONVERT(varchar, ScanTime, 120 ),'-',''),' ',''),':',''),9,3) ctime, count(1) num FROM [eschool_1].[dbo].kg_Kaoqin group by SUBSTRING(replace(replace...
sql查询值按分钟平均,小时平均,天平均
qq_32814555的博客
12-28 2719
按分钟平均 select id,ROUND(AVG(value),2) as avg, date_format(time,'%Y-%m-%d %H:%i') as time from " + tablename + " where id in (:indexIds) and   time>=:starttime and time  按小时平均 select id,ROUND(AVG(
mysql - date_format
快乐
10-28 140
按年汇总，统计：select sum(mymoney) as totalmoney, count(*) as sheets from mytable group by date_format(col, '%Y');按月汇总，统计： select sum(mymoney) as totalmoney, count(*) as sheets from mytable group by date_f...
Java统计各个时段上的人数
weixin_40434637的博客
09-26 2842
Java统计各个时段上的人数 场景重现： 我们将一天分割成24份，每份间隔1小时，[0-1]，[1-2]，[2-3]…，[23-0] 根据考勤时间，然后我们统计一下，在各个时段上加班的人数。 实现步骤： 步骤一、时间段分割： 将个人加班时间，分割成多个，匹配上面的24个时段。 步骤二、将每个人分割后的时间片段汇总到一个集合中，然后统计出每个时间片段的总数。 步骤一： 给你个开始时间，一个结束时...
Hadoop，Spark面试题汇总
rosefun96的博客
09-24 1390
Hadoop 有哪些组件？ （1）HDFS集群：负责海量数据的存储，集群中的角色主要有 NameNode / DataNode/SecondaryNameNode。 （2）YARN集群：负责海量数据运算时的资源调度，集群中的角色主要有 ResourceManager /NodeManager （3）MapReduce：它其实是一个应用程序开发包。 ———————————————— 版权声明：...
微众银行数据分析项目实战：SQL与数据开发方案
本实战项目聚焦于银行业务的关键数据统计与分析，包括授信金额、贷款产品不良率、逾期率、用户画像以及账龄分析等，这些都是数据分析师和数据工程师在面试及工作中可能遇到的实际问题。 首先，关于需求1：“授信...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值