我试图从SQL查询中获取基于错误代码的计数及其在特定时间段内的出现的条件值,但是我编写的此查询返回所有记录的第一个满足条件。
我究竟做错了什么
SELECT ERROR_CODE,
CASE
WHEN (SELECT Count(ERROR_CODE)
FROM ERROR_LOG
WHERE CRET_DTIM > Trunc(SYSDATE - 2)
AND ERROR_CODE = '50001') > 50 THEN 'RED'
WHEN (SELECT Count(ERROR_CODE)
FROM ERROR_LOG
WHERE CRET_DTIM > Trunc(SYSDATE - 2)
AND ERROR_CODE = '50001') < 50 THEN 'GREEN'
WHEN (SELECT Count(ERROR_CODE)
FROM ERROR_LOG
WHERE CRET_DTIM > Trunc(SYSDATE - 1)
AND ERROR_CODE IS NULL) > 100 THEN 'RED'
WHEN (SELECT Count(ERROR_CODE)
FROM ERROR_LOG
WHERE CRET_DTIM > Trunc(SYSDATE - 1)
AND ERROR_CODE IS NULL) < 100 THEN 'GREEN'
END AS ALERT_COLOR
FROM ERROR_LOG
GROUP BY ERROR_CODE
样本数据
50005 05-JAN-15 11.05.51.570000000 AM
50001 05-JAN-15 02.39.57.840000000 PM
50001 05-JAN-15 02.31.06.700000000 PM
50001 05-JAN-15 02.21.49.350000000 PM
50001 05-JAN-15 01.59.13.910000000 PM
50001 05-JAN-15 01.50.05.900000000 PM
50001 05-JAN-15 01.30.19.270000000 PM
50001 05-JAN-15 01.11.10.510000000 PM
50001 05-JAN-15 12.00.00.720000000 PM
05-JAN-15 09.42.10.670000000 AM
05-JAN-15 09.37.31.590000000 AM
所需的输出(更改计数条件以查看所需的输出)COUNT
2
50001 8
50005 1
的SQL
SELECT ERROR_CODE,
CASE
WHEN (SELECT Count(ERROR_CODE)
FROM ERROR_LOG
WHERE CRET_DTIM > Trunc(SYSDATE - 2)
AND ERROR_CODE = '50001') between 5 and 100 THEN 'RED'
WHEN (SELECT Count(ERROR_CODE)
FROM ERROR_LOG
WHERE CRET_DTIM > Trunc(SYSDATE - 2)
AND ERROR_CODE = '50001') < 5 THEN 'GREEN'
WHEN (SELECT Count(ERROR_CODE)
FROM ERROR_LOG
WHERE CRET_DTIM > Trunc(SYSDATE - 1)
AND ERROR_CODE IS NULL) > 100 THEN 'RED'
WHEN (SELECT Count(ERROR_CODE)
FROM ERROR_LOG
WHERE CRET_DTIM > Trunc(SYSDATE - 1)
AND ERROR_CODE IS NULL) between 5 and 100 THEN 'GREEN'
END AS ALERT_COLOR
FROM ERROR_LOG
GROUP BY ERROR_CODE
结果
Error_code Alert_color
GREEN
50005 ''
50001 RED
您正在when
为表中的每一行重复每个子句中的子查询,而没有相关性。子查询:
(SELECT Count(ERROR_CODE)
FROM ERROR_LOG
WHERE CRET_DTIM > Trunc(SYSDATE - 2)
AND ERROR_CODE = '50001')
…对于表中的每一行,每次取值为8;因此,无论该行中实际存在的错误代码如何,案例的第一个分支都将始终匹配,因此您可以看到RED
每一行。
即使您将其关联,重复子查询的效率也很低。Oracle会优化/缓存它,但是仍然...最好一次生成计数:
select error_code,
count(case when cret_dtim > trunc(sysdate - 2) then 1 end) as cnt_2_day,
count(case when cret_dtim > trunc(sysdate - 1) then 1 end) as cnt_1_day
from error_log
where cret_dtim > trunc(sysdate - 2)
group by error_code
order by error_code nulls first;
ERROR_CODE CNT_2_DAY CNT_1_DAY
---------- --------- ----------
(null) 2 2
50001 8 8
50005 1 1
您的所有样本数据都不到一天,因此计数是相同的;如果您有两天前的数据,它们会有所不同。
然后,您可以一次使用它作为内部查询(如果愿意,也可以使用CTE),然后将结果用作另一级别的输入,case
以转换为您的颜色代码:
select error_code,
case
when error_code = '50001' and cnt_1_day between 5 and 100 then 'RED'
when error_code = '50001' and cnt_2_day < 5 then 'GREEN'
when error_code is null and cnt_1_day > 100 then 'RED'
when error_code is null and cnt_1_day between 5 and 100 then 'GREEN'
else null
end as alert_colour
from
(
select error_code,
count(case when cret_dtim > trunc(sysdate - 2) then 1 end) as cnt_2_day,
count(case when cret_dtim > trunc(sysdate - 1) then 1 end) as cnt_1_day
from error_log
where cret_dtim > trunc(sysdate - 2)
group by error_code
)
order by error_code nulls first;
ERROR_CODE ALERT_COLOUR
---------- ------------
(null) (null)
50001 RED
50005 (null)
这与您预期的结果不符,但是正如我在评论中所说,我不确定您如何从数字到显示的结果。您的存储桶排除了很多可能性;您可能希望第一个条件只是> 5
例如。您完全忽略了50005
您希望看到的。如果您的基本逻辑是任何代码集都使用2天计数,而空值使用1天计数(这是一个飞跃),则可以将其概括为:
select error_code,
case
when error_code is not null and cnt_not_null between 5 and 100 then 'RED'
when error_code is not null and cnt_not_null < 5 then 'GREEN'
when error_code is null and cnt_null > 100 then 'RED'
when error_code is null and cnt_null between 5 and 100 then 'GREEN'
else null
end as alert_colour
from (
select error_code,
count(case when cret_dtim > trunc(sysdate - 2)
and error_code is not null then 1 end) as cnt_not_null,
count(case when cret_dtim > trunc(sysdate - 1)
and error_code is null then 1 end) as cnt_null
from error_log
where cret_dtim > trunc(sysdate - 2)
group by error_code
)
order by error_code nulls first;
ERROR_CODE ALERT_COLOUR
---------- ------------
(null) (null)
50001 RED
50005 GREEN
那仍然不是您期望的结果,但是必须要做的是直到逻辑(和我的错误)得到解释,或者您的结果被更改为止。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句