以下是我目前正在处理的数据示例:
101 N 4/14/2016
201 Y 4/15/2016
301 Y 4/16/2016
401 Y 4/20/2016
501 N 4/21/2016
201 Y 4/30/2016
701 Y 5/03/2016
301 N 5/03/2016
期望输出:
101 N 4/14/2016 0
501 N 4/21/2016 3
301 N 5/03/2016 2
我需要 2 个失败事件之间的成功案例计数来绘制 G 控制图。 我真的可以使用一些帮助来找到一种方法来计算成功的案例。
假设 DT 列(第三个(没有重复值 - 我更改了最后一个日期以反映在我的测试数据中 - 这可以通过计算内部查询中的"Y"行来解决(在分析 COUNT 中使用 CASE 表达式(,然后在外部查询中我们可以只过滤带有"N"的行并使用滞后差值(再次是分析函数应用程序(。
如果DT列中可以有联系,那么OP需要澄清要求 - 但无论它是什么,它都可以很容易地适应这个解决方案。
with
test_data ( id, success, dt ) as (
select 101, 'N', to_date('4/14/2016', 'mm/dd/yyyy') from dual union all
select 201, 'Y', to_date('4/15/2016', 'mm/dd/yyyy') from dual union all
select 301, 'Y', to_date('4/16/2016', 'mm/dd/yyyy') from dual union all
select 401, 'Y', to_date('4/20/2016', 'mm/dd/yyyy') from dual union all
select 501, 'N', to_date('4/21/2016', 'mm/dd/yyyy') from dual union all
select 201, 'Y', to_date('4/30/2016', 'mm/dd/yyyy') from dual union all
select 701, 'Y', to_date('5/03/2016', 'mm/dd/yyyy') from dual union all
select 301, 'N', to_date('5/04/2016', 'mm/dd/yyyy') from dual
)
-- End of simulated data (for testing purposes only).
-- Solution (SQL query) begins BELOW THIS LINE.
select id, success, dt, ct - lag(ct, 1, 0) over (order by dt) as success_count
from (
select id, success, dt,
count(case when success = 'Y' then 1 end) over (order by dt) as ct
from test_data
)
where success = 'N'
;
ID SUCCESS DT SUCCESS_COUNT
--- ------- ---------- -------------
101 N 14/04/2016 0
501 N 21/04/2016 3
301 N 04/05/2016 2
可以使用前面的"N"个事件的计数枚举组。 然后,您的查询只是一个聚合查询:
select min(col1) as col1, min('N') as col2, min(date) as col3,
sum(case when col2 = 'Y' then 1 else 0 end) as cnt
from (select t.*,
sum(case when col2 = 'N' then 1 else 0 end) over (order by col3) as grp
from t
) t
group by grp;