T-SQL:在首次成功之前计算失败次数(2) [英] T-SQL: Count number of failures before first success (2)
问题描述
我有一个数据库，其中包含用于任务尝试及其结果(失败或成功)的事件.对于每个用户，我想计算一次成功之前的失败次数.随后的失败和成功不应影响输出-我只对给定任务的首次成功感兴趣.此外，数据库包含具有其他事件的行，应将其忽略.
I have a DB with events for task attempts and their results (fail or success). For each user, I'd like to count the number of failures before the first success. Subsequent failures and successes shouldn't affect the output - I'm only interested in the first success of a given task. Also, the DB contains rows with other events that should be ignored.
如何在Vertica数据库上的T-SQL中对此进行表述?
How do I formulate this in T-SQL on a Vertica database?
(我最终想计算每个任务的平均尝试次数，但是为了使事情易于管理，让我们将其排除在此问题的范围之外.)
(I would eventually like to calculate the average number of attempts per task, but let's keep that out of the scope of this question to keep things manageable.)
这是问题的更新: T-SQL:计算直到首次成功的失败次数
在最初的问题中，我给出了结构较差的示例数据，这些数据不能完全反映我的使用场景，并导致得出的答案不适用于我的实际数据并且无法验证.
In the original question, I'd given poorly constructed sample data which didn't fully reflect my use scenario, and led to answers that weren't applicable with my actual data and that I couldn't verify.
该解决方案不应依赖行顺序-很有可能未按时间戳顺序填充行.
The solution shouldn't rely on row order - it's possible that rows aren't populated in timestamp order.
这是数据库设置:
CREATE TABLE events {
eventID int -- unused in this example, should be excluded from output
, eventName varchar(256)
, userName varchar(256)
, timestamp timestamp
, taskName varchar(256)
, sessionID int -- unused in this example, should be excluded from output
};
INSERT INTO events
VALUES
(2363460186192576512, 'beginSession', 'John', '2017-08-14 09:46:46.712', NULL, 145031357)
, (2363460852537008128, 'success', 'John', '2017-08-14 09:49:32.471', 'TaskOne', 145031357)
, (2363461162974437376, 'success', 'John', '2017-08-14 09:50:48.781', 'TaskOne', 145031357)
, (2363460390131740672, 'fail', 'John', '2017-08-14 09:47:37.349', 'TaskOne', 145031357)
, (2363460556662710272, 'fail', 'John', '2017-08-14 09:48:23.024', 'TaskOne', 145031357)
, (2363460730671505408, 'fail', 'John', '2017-08-14 09:48:58.646', 'TaskOne', 145031357)
, (2363461032111800320, 'fail', 'John', '2017-08-14 09:50:10.726', 'TaskOne', 145031357)
, (2363460389896859648, 'beginTask', 'John', '2017-08-14 09:47:05.32', 'TaskOne', 145031357)
, (2363460463137751040, 'beginTask', 'John', '2017-08-14 09:47:52.166', 'TaskOne', 145031357)
, (2363460556205531136, 'beginTask', 'John', '2017-08-14 09:48:12.615', 'TaskOne', 145031357)
, (2363460692671205376, 'beginTask', 'John', '2017-08-14 09:48:36.155', 'TaskOne', 145031357)
, (2363460852268572672, 'beginTask', 'John', '2017-08-14 09:49:12.047', 'TaskOne', 145031357)
, (2363460962524327936, 'beginTask', 'John', '2017-08-14 09:49:47.951', 'TaskOne', 145031357)
, (2363461162714390528, 'beginTask', 'John', '2017-08-14 09:50:23.645', 'TaskOne', 145031357)
, (2363474741421064192, 'beginSession', 'John', '2017-08-14 10:44:36.042', NULL, 145031392)
, (2363474885491200000, 'success', 'John', '2017-08-14 10:45:14.577', 'TaskTwo', 145031392)
, (2363475342389641216, 'success', 'John', '2017-08-14 10:47:04.098', 'TaskTwo', 145031392)
, (2363475473998635008, 'success', 'John', '2017-08-14 10:47:34.135', 'TaskOne', 145031392)
, (2363475822079254528, 'success', 'John', '2017-08-14 10:48:53.381', 'TaskTwo', 145031392)
, (2363476096949104640, 'success', 'John', '2017-08-14 10:50:07.441', 'TaskThree', 145031392)
, (2363475066098266112, 'fail', 'John', '2017-08-14 10:45:53.526', 'TaskTwo', 145031392)
, (2363475195152531456, 'fail', 'John', '2017-08-14 10:46:32.81', 'TaskTwo', 145031392)
, (2363475654638821376, 'fail', 'John', '2017-08-14 10:48:13.71', 'TaskThree', 145031392)
, (2363476247751114752, 'beginSession', 'Mike', '2017-08-14 10:50:37.67', NULL, 145030476)
, (2363476335819063296, 'success', 'Mike', '2017-08-14 10:51:06.841', 'TaskOne', 145030476)
, (2363476485643796480, 'success', 'Mike', '2017-08-14 10:51:41.086', 'TaskTwo', 145030476)
, (2363476806063038464, 'success', 'Mike', '2017-08-14 10:52:53.174', 'TaskTwo', 145030476)
, (2363477266119335936, 'success', 'Mike', '2017-08-14 10:54:32.053', 'TaskThree', 145030476)
, (2363477619191631872, 'success', 'Mike', '2017-08-14 10:56:01.783', 'TaskThree', 145030476)
, (2363476705131655168, 'fail', 'Mike', '2017-08-14 10:52:21.312', 'TaskThree', 145030476)
, (2363476939634896896, 'fail', 'Mike', '2017-08-14 10:53:28.906', 'TaskThree', 145030476)
, (2363477390937976832, 'fail', 'Mike', '2017-08-14 10:55:05.499', 'TaskThree', 145030476)
, (2363476335592570880, 'beginTask', 'Mike', '2017-08-14 10:50:50.074', 'TaskOne', 145030476)
, (2363476485501190144, 'beginTask', 'Mike', '2017-08-14 10:51:20.784', 'TaskTwo', 145030476)
, (2363476704779333632, 'beginTask', 'Mike', '2017-08-14 10:51:54.829', 'TaskThree', 145030476)
, (2363476805752659968, 'beginTask', 'Mike', '2017-08-14 10:52:34.001', 'TaskTwo', 145030476)
, (2363476939496484864, 'beginTask', 'Mike', '2017-08-14 10:53:06.468', 'TaskThree', 145030476)
, (2363477265938980864, 'beginTask', 'Mike', '2017-08-14 10:53:45.631', 'TaskThree', 145030476)
, (2363477390635986944, 'beginTask', 'Mike', '2017-08-14 10:54:44.706', 'TaskThree', 145030476)
, (2363477573427560448, 'beginTask', 'Mike', '2017-08-14 10:55:17.231', 'TaskThree', 145030476)
, (2363474885214375936, 'beginTask', 'John', '2017-08-14 10:44:44.702', 'TaskTwo', 145031392)
, (2363474985177161728, 'beginTask', 'John', '2017-08-14 10:45:31.133', 'TaskTwo', 145031392)
, (2363475195014119424, 'beginTask', 'John', '2017-08-14 10:46:10.098', 'TaskTwo', 145031392)
, (2363475342184120320, 'beginTask', 'John', '2017-08-14 10:46:45.357', 'TaskTwo', 145031392)
, (2363475473616953344, 'beginTask', 'John', '2017-08-14 10:47:17.911', 'TaskOne', 145031392)
, (2363475654437494784, 'beginTask', 'John', '2017-08-14 10:47:47.681', 'TaskThree', 145031392)
, (2363475771776864256, 'beginTask', 'John', '2017-08-14 10:48:27.1', 'TaskTwo', 145031392)
, (2363476006456762368, 'beginTask', 'John', '2017-08-14 10:49:06.151', 'TaskThree', 145031392)
;
有了这些数据，这就是我想要达到的结果:
With this data, here is the result I'm trying to achieve:
userName taskName numFailuresBeforeFirstSuccess
John TaskOne 3
John TaskTwo 0
John TaskThree 1
Mike TaskOne 0
Mike TaskTwo 0
Mike TaskThree 3
推荐答案
这里是一种方法:
select e.username, e.taskname,
sum(case when timestamp < first_success_ts and e.eventname = 'fail' then 1 else 0 end) as numFailuresBeforeSuccess
from (select e.*,
min(case when e.eventname = 'success' then e.timestamp end) over
(partition by e.username, e.taskname) as first_success_ts
from events e
) e
group by e.username, e.taskname
order by e.username, e.taskname;
这将使用窗口函数计算首次成功时间.这应该在两个数据库中都有效(至少在SQL Server 2012+中有效)
This calculates the first success time using a window function. This should work in both databases (as least in SQL Server 2012+)
这篇关于T-SQL:在首次成功之前计算失败次数(2)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
