SQL查询以查找失败之间的成功次数
SQL查询以查找失败之间的成功次数
提问于 2016-10-31 16:19:03
我有一个包含测试结果记录的oracle数据库表。每个记录都包含测试START_TIME、测试所用的仪器以及测试过程中发生错误的ERROR_CODE等信息。
对于每条ERROR_CODE等于'5900‘、'6900’或'5905‘的记录，我需要在错误记录的日期之前确定在该仪器上发生的成功测试的次数(ERROR_CODE = null)。换句话说，在产生错误之前，我需要知道在仪器上成功测试的次数。
该数据库包含500多个仪器，每个仪器可有1至500 000份测试记录。
注:只对ERROR_CODES '5900‘、'6000’和'5905‘之前的成功次数感兴趣。有些仪器可能没有这些错误。有些仪器可能有多个连续的错误，但它们之间没有成功。该仪器的第一次或最后一次测试可能发生错误。
示例：
START_TIME INSTRUMENT ERROR_CODE
12/1/2015 22:15:03 A540 null
12/1/2015 22:17:14 A700 null
12/1/2015 22:17:53 A700 null
12/1/2015 22:19:24 A700 5905
12/1/2015 23:28:15 A700 null
12/1/2015 23:35:10 A540 6000
12/2/2015 02:15:13 A540 5900
12/2/2015 03:07:03 A540 null
12/2/2015 03:44:52 A540 null
12/2/2015 09:15:56 A700 null
12/2/2015 14:17:09 A700 5900
12/2/2015 17:15:42 A980 null
12/3/2015 08:17:53 A540 5900
12/3/2015 08:18:49 A540 5900
12/3/2015 11:17:57 A540 null应该给出以下结果
ERROR_TIME INSTRUMENT SUCCESSES_BEFORE_ERROR
12/1/2015 22:19:24 A700 2
12/1/2015 23:35:10 A540 1
12/2/2015 02:15:13 A540 1
12/2/2015 14:17:09 A700 4
12/3/2015 08:17:53 A540 3
12/3/2015 08:18:49 A540 3回答 3
Stack Overflow用户
回答已采纳
发布于 2016-10-31 16:40:57
下面是一种使用解析函数的方法：
WITH test_results AS (SELECT to_date('12/01/2015 22:15:03', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A540' instrument, NULL ERROR_CODE FROM dual UNION ALL
SELECT to_date('12/01/2015 22:17:14', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A700' instrument, NULL ERROR_CODE FROM dual UNION ALL
SELECT to_date('12/01/2015 22:17:53', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A700' instrument, NULL ERROR_CODE FROM dual UNION ALL
SELECT to_date('12/01/2015 22:19:24', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A700' instrument, 5905 ERROR_CODE FROM dual UNION ALL
SELECT to_date('12/01/2015 23:28:15', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A700' instrument, NULL ERROR_CODE FROM dual UNION ALL
SELECT to_date('12/01/2015 23:35:10', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A540' instrument, 6000 ERROR_CODE FROM dual UNION ALL
SELECT to_date('12/02/2015 02:15:13', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A540' instrument, 5900 ERROR_CODE FROM dual UNION ALL
SELECT to_date('12/02/2015 03:07:03', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A540' instrument, NULL ERROR_CODE FROM dual UNION ALL
SELECT to_date('12/02/2015 03:44:52', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A540' instrument, NULL ERROR_CODE FROM dual UNION ALL
SELECT to_date('12/02/2015 09:15:56', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A700' instrument, NULL ERROR_CODE FROM dual UNION ALL
SELECT to_date('12/02/2015 14:17:09', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A700' instrument, 5900 ERROR_CODE FROM dual UNION ALL
SELECT to_date('12/02/2015 17:15:42', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A980' instrument, NULL ERROR_CODE FROM dual UNION ALL
SELECT to_date('12/03/2015 08:17:53', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A540' instrument, 5900 ERROR_CODE FROM dual UNION ALL
SELECT to_date('12/03/2015 08:18:49', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A540' instrument, 5900 ERROR_CODE FROM dual UNION ALL
SELECT to_date('12/03/2015 11:17:57', 'mm/dd/yyyy hh24:mi:ss') start_time, 'A540' instrument, NULL ERROR_CODE FROM dual)
-- end of mimicking a table with data in it called "test_results"
-- for use in the following select statement:
SELECT start_time,
instrument,
running_total success_before_error
FROM (SELECT start_time,
instrument,
ERROR_CODE,
sum(CASE WHEN ERROR_CODE IS NOT NULL THEN 0
ELSE 1
END) OVER (PARTITION BY instrument ORDER BY start_time) running_total
FROM test_results)
WHERE ERROR_CODE IS NOT NULL -- this may need to be "error_code in (5900, 6000, 5905)"
ORDER BY start_time;
START_TIME INSTRUMENT SUCCESS_BEFORE_ERROR
------------------- ---------- --------------------
12/01/2015 22:19:24 A700 2
12/01/2015 23:35:10 A540 1
12/02/2015 02:15:13 A540 1
12/02/2015 14:17:09 A700 4
12/03/2015 08:17:53 A540 3
12/03/2015 08:18:49 A540 3Stack Overflow用户
发布于 2016-10-31 16:35:09
也许有一种方法可以用解析函数来实现这一点(这是毫无疑问的)。但是表达逻辑的最简单的方法--在我看来--是使用一个相关的子查询：
select t.*,
(select count(*)
from t t2
where t2.instrument = t.instrument and
t2.start_time < t.start_time and
t2.error_code is null
) as SUCCESSES_BEFORE_ERROR
from t
where t.error_code is not null;Stack Overflow用户
发布于 2016-10-31 16:57:44
我不知道源表的名称，我叫它table_one。
编辑：，正如我现在看到的，我犯了一个错误，我计算了成功测试的馀值。我把它留在原样上
ordered_tab as (
select START_TIME
,INSTRUMENT
,ERROR_CODE
,row_number() over (partition by INSTRUMENT order by START_TIME) rn
from table_one)
select START_TIME as ERROR_TIME
,INSTRUMENT
,SUCCESSES_BEFORE_ERROR
FROM (
select START_TIME
,INSTRUMENT
,ERROR_CODE
,rn -1
- nvl(last_value(nvl2(ERROR_CODE,rn,null) ignore nulls)
over (partition by INSTRUMENT order by START_TIME rows between unbounded preceding and 1 preceding),0) as SUCCESSES_BEFORE_ERROR
from ordered_tab
) where ERROR_CODE IN (5905, 5900, 6000)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/40346246
复制相关文章
相似问题
腾讯云开发者
