Oracle SQL差异：COUNT（*）与实际结果集 [英] Oracle SQL Discrepancy: COUNT(*) vs Actual Result Set
问题描述
我有一个应用程序，当文件被尝试从一个服务器移动到另一个，以及当它已经成功或失败时，跟踪一个尝试应该总是配对与成功或失败。然而，有63个孤儿尝试，意味着有没有任何成功或失败报告的尝试。我的第一个查询显示我开始使用 63 号码的位置：我对所有尝试进行计数，并减去成功 em>失败 -
SELECT
（
SELECT COUNT（*）FROM e_table
WHERE e_comment LIKE'％attempt％'
AND e_date> = '23 -MAY-2016'
AND e_date< = '26 -MAY-2016'
）
-
（
SELECT
（
SELECT COUNT（*）FROM e_table
WHERE e_comment LIKE'％success％'
AND e_date> = '23 -MAY-2016'
AND e_date< = '26 -MAY-2016'
）
+
（
SELECT COUNT（*）FROM e_table
WHERE e_comment LIKE'％failure％'
AND e_date> = '23 -MAY-2016'
AND e_date< = '26 -MAY-2016'
）FROM dual
）AS orphaned_attempts FROM dual;
所以我第二次查询获得63次尝试的具体 e_id 如下：
SELECT * FROM
（
SELECT e_id FROM e_table
WHERE e_comment LIKE '％attempts％'
AND e_date> = '23 -MAY-2016'
AND e_date< = '26 -MAY-2016'
）
MINUS
（
SELECT e_id FROM e_table
WHERE e_comment LIKE'％success％'
AND e_date> = '23-MAY-2016'
AND e_date <=' MAY-2016'
）
MINUS
（
SELECT e_id FROM e_table
WHERE e_comment LIKE'％failure％'
AND e_date> = '23 -MAY-2016'
AND e_date< = '26 -MAY-2016'
）;
我需要（并且基于第一个查询的结果集）我需要一个63行结果集，其中一列包含孤立尝试的e_id。相反，我从第二个查询只得到49行。非常感谢任何帮助。
SELECT
a.e_id，
coalesce（attempts，0）attempts，
coalesce（successes，0）successes，
coalesce（failures，0）failures
FROM
（
SELECT e_id ，计数（*）作为尝试FROM e_table
WHERE e_comment LIKE'％attempt％'AND e_date BETWEEN '23 -MAY-2016'AND '26 -MAY-2016'
GROUP BY e_id
）a
full outer join
（
SELECT e_id，count（*）as successes from e_table
WHERE e_comment LIKE'％success％'AND e_date BETWEEN '23 -MAY-2016 'AND '26 -MAY-2016'
GROUP BY e_id
）s
on s.e_id = a.e_id
全外连接
（
SELECT e_id，count（*）as failures FROM e_table
WHERE e_comment LIKE'％failure％'AND e_date BETWEEN '23 -MAY-2016'AND '26 -MAY-2016'
GROUP BY e_id
）f
on f.e_id = coalesce（a.e_id，s.e_id）
WHERE
coalesce（attempts，0）< coalesce（成功，0）+ coalesce（故障，0）
可以验证没有任何匹配尝试没有成功和/或失败。这应该让你找到 e_id s在日志记录中出现了问题。它应该更容易开始处理更精细的数字，而不只是id值列表。
其他人已经指出了多次尝试在同一个ID的潜力，但是可以想象成功和失败可以记录与在某种重试场景中说的相同的方式吗？我们不知道什么是完整的评论。作为一个可能的解释，单个评论可以包含多个单词attempt，success，failure？
考虑：您确定所有成功和失败事件都会在同一个日期范围内吗？换句话说，尝试后是否有一些延迟？如果这发生在午夜左右，它可能不必很长。您可能希望扩大成功和失败范围，以弥补这一点（并更改为左外联接。）
注意： 其中已修改为允许多次尝试（如注释中所述），现在只是查找尝试次数与成功和失败次数之间的平衡。 p>
I have an application that keeps track when a file is being "attempted" to move from one server to another, as well as when it has "succeeded" or "failed." An "attempt" should always be paired with a "success" or "failure." However, there are 63 "orphaned" attempts, meaning there have been attempts without any success or failure reported. My first query shows where I got the 63 number to begin with: I take a count of all of the attempts and subtract the successes and failures-
SELECT
(
SELECT COUNT(*) FROM e_table
WHERE e_comment LIKE '%attempt%'
AND e_date >= '23-MAY-2016'
AND e_date <= '26-MAY-2016'
)
-
(
SELECT
(
SELECT COUNT(*) FROM e_table
WHERE e_comment LIKE '%success%'
AND e_date >= '23-MAY-2016'
AND e_date <= '26-MAY-2016'
)
+
(
SELECT COUNT(*) FROM e_table
WHERE e_comment LIKE '%failure%'
AND e_date >= '23-MAY-2016'
AND e_date <= '26-MAY-2016'
) FROM dual
) AS orphaned_attempts FROM dual;
So my second query to get the specific e_id of the 63 attempts is as follows:
SELECT * FROM
(
SELECT e_id FROM e_table
WHERE e_comment LIKE '%attempt%'
AND e_date >= '23-MAY-2016'
AND e_date <= '26-MAY-2016'
)
MINUS
(
SELECT e_id FROM e_table
WHERE e_comment LIKE '%success%'
AND e_date >= '23-MAY-2016'
AND e_date <= '26-MAY-2016'
)
MINUS
(
SELECT e_id FROM e_table
WHERE e_comment LIKE '%failure%'
AND e_date >= '23-MAY-2016'
AND e_date <= '26-MAY-2016'
);
What I need (and expect based on the first query’s result set) is to have a 63-row result set with one column containing the e_id of the orphaned attempts. Instead, I am getting only 49 rows back from the second query. Any help is greatly appreciated.
SELECT
a.e_id,
coalesce(attempts, 0) attempts,
coalesce(successes, 0) successes,
coalesce(failures, 0) failures
FROM
(
SELECT e_id, count(*) as attempts FROM e_table
WHERE e_comment LIKE '%attempt%' AND e_date BETWEEN '23-MAY-2016' AND '26-MAY-2016'
GROUP BY e_id
) a
full outer join
(
SELECT e_id, count(*) as successes FROM e_table
WHERE e_comment LIKE '%success%' AND e_date BETWEEN '23-MAY-2016' AND '26-MAY-2016'
GROUP BY e_id
) s
on s.e_id = a.e_id
full outer join
(
SELECT e_id, count(*) as failures FROM e_table
WHERE e_comment LIKE '%failure%' AND e_date BETWEEN '23-MAY-2016' AND '26-MAY-2016'
GROUP BY e_id
) f
on f.e_id = coalesce(a.e_id, s.e_id)
WHERE
coalesce(attempts, 0) <> coalesce(successes, 0) + coalesce(failures, 0)
I changed to full outer joins so you can verify that there are no successes and/or failures without any matching attempt. This should let you find e_ids where something's going wrong in the logging. It should be easier to start dealing with finer numbers and not just listings of id values.
Others have pointed out the potential for multiple attempts on the same id but is it conceivable that a success and failure could both be recorded the same way as say in some kind of retry scenario? We don't know what the full comments look like. As a possible explanation, can a single comment can contain more than one of the words "attempt", "success", "failure"?
Here's something else to consider: Are you sure that all your success and failures events will fall within the same date window? In other words, is there some delay following the attempt? It might not have to be very long if this happens around midnight. You may want to widen the success and failure ranges enough to compensate for this (and change to left outer joins.)
Note: Condition in the where clause has been modified to allow for multiple attempts (as noted in comments) and now just looks for a balance in the number of attempts vs. successes and failures.
这篇关于Oracle SQL差异：COUNT（*）与实际结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
