我在这里看到了一些精彩的答案，我需要修复。我现在还不想创建表或ETL，我希望创建一个简单的数据库视图，用户可以首先访问该视图进行测试。
表具有item#、item_ticket_color#、maint_date和其他列tick_col有时会更新但是表中的其他列也是。如果更新或更改了其他列tick_col迭代。我需要筛选这些数据以获得以下预期结果，并需要一些帮助试图弄清楚我该怎么做。如果可能的话，我正在寻找直接的SQL
ITEM......................TICK_COL................. MAINT_DATE
21524804....................RIBG...................1/1/0001
21524804....................RIBG...................6/15/2008 6:52:57 AM
21524804....................RIBG...................6/25/2008 11:31:03 AM
21524804....................RIBG...................6/28/2008 4:12:21 AM
21524804....................RIWH...................9/20/2008 6:36:24 AM
21524804....................RIGR...................9/23/2008 6:36:44 AM
21524804....................RIGR...................9/30/2008 6:37:42 AM
21524804....................RIWH...................10/31/2008 6:37:27 AM
21524804....................RIWH...................11/1/2008 6:36:41 AM
21524804....................RIGR...................3/11/2009 6:01:43 PM
21524804....................RIGR...................7/28/2009 6:37:11 AM
21524804....................RIGR...................10/8/2009 6:37:00 AM
21524804....................RIBS...................11/20/2009 6:37:58 AM
21524804....................RIBS...................5/18/2010 6:37:07 AM
21524804....................RIBS...................9/16/2010 6:38:11 AM
21524804....................RIBS...................8/13/2012 10:39:44 AM
21524804....................RIBS...................3/12/2013 6:46:08 AM
21524804....................RIBS...................3/17/2013 9:25:31 AM
21524804....................RIBS...................3/27/2013 6:52:57 AM
21524804....................RIBS...................7/25/2013 6:41:51 AM
我期待着在下面看到这个，它显示了开始以及票证颜色发生变化的每个场景的结束日期
21524804.....RIBG.....10101........20080919
21524804.....RIWH.....20080920.....20080922
21524804.....RIGR.....20080923.....20081030
21524804.....RIWH.....20081031.....20090310
21524804.....RIGR.....20090311.....20091119
21524804.....RIBS.....20091120.....20130725
SQLFiddle在这里。
这是新代码，基于Jasti的贡献，我做了一些额外的更改，但这正是我想要的
SELECT item,
tick_col,
from_dt,
CASE
WHEN LEAD (from_dt) OVER (PARTITION BY item ORDER BY from_dt) - 1
IS NULL
THEN
SYSDATE
ELSE
LEAD (from_dt) OVER (PARTITION BY item ORDER BY from_dt) - 1
END
TO_DATE
FROM ( SELECT ITEM,
TICK_COL,
MIN (MAINT_DATE) AS from_dt,
MAX (MAINT_DATE) AS to_dt
FROM (SELECT SUM (start_of_group) OVER (ORDER BY maint_date) AS sm,
ITEM,
TICK_COL,
maint_date
FROM (SELECT ITEM,
TICK_COL,
maint_date,
CASE
WHEN LAG (TICK_COL, 1, TICK_COL)
OVER (ORDER BY maint_date) =
TICK_COL
THEN
0
ELSE
1
END
start_of_group
FROM mytable))
GROUP BY ITEM, TICK_COL, sm
ORDER BY sm)
我也为您的第一部分添加了查询。以下查询应同时满足这两个要求。此查询对项目进行分组，直到TICK_COL更改，并计算该特定组的最大和最小日期
select ITEM, TICK_COL, MIN(MAINT_DATE) AS from_dt, MAX(MAINT_DATE) AS to_dt from
(
select sum(start_of_group) over (order by maint_date) as sm, ITEM, TICK_COL, maint_date from
(
select ITEM, TICK_COL,maint_date,
case
when
lag(TICK_COL,1,TICK_COL) over (order by maint_date) = TICK_COL then 0
else 1
end start_of_group
from mytable
)
) group by ITEM, TICK_COL,sm order by sm
此处更新了SQL Fiddle
您可以使用min和max来获得第二部分。除了标准选择之外，我不确定你在第一部分中想要什么。
SELECT ITEM, TICK_COL, MIN(MAINT_DATE) AS startDate, MAX(MAINT_DATE) AS endDate
FROM yourTableName
GROUP BY ITEM, TICK_COL
试着看看这个查询是否有效。
select item,
tick_col,
MAINT_DATE maint_start_date,
(case when tick_col <> next_tick_col then next_maint_date end) maint_end_date
from (
select item,
tick_col,
lead(MAINT_DATE) over (partition by item
order by MAINT_DATE asc) next_maint_date,
lead(tick_col) over (partition by item
order by MAINT_DATE asc) next_tick_col,
from my_table
) where tick_col <> next_tick_col;
基于检测时段的开始和结束，然后计算所需日期的变量。（在这里可以找到几乎相同的解决方案。）
SQLFiddle
with marked_set as (
-- mark start and end of each maintenance period
select
item,
tick_col,
maint_date,
decode( nvl(prev_tick, tick_col||'x'), tick_col, 0, 1 ) is_start,
decode( nvl(next_tick, tick_col||'x'), tick_col, 0, 1 ) is_end
from (
select
item,
tick_col,
maint_date,
lag(tick_col) over (partition by item order by maint_date) prev_tick,
lead(tick_col) over (partition by item order by maint_date) next_tick
from
mytable
)
),
boundary_set as (
-- Leave only start and end of each period
-- and get dates from previous and next lines
select
item,
tick_col,
maint_date,
is_start,
is_end,
( lag(maint_date) over (
partition by item order by maint_date)
) prev_maint_date,
( lead(maint_date,1,maint_date) over (
partition by item order by maint_date
)
) next_maint_date
from
marked_set
where
1 in (is_start, is_end)
),
maintenance_sequence as (
-- Calculate start and end dates for each maintenance period
select distinct
item,
tick_col,
decode( is_start,
1, maint_date,
prev_maint_date
) start_date,
decode( is_end,
1, next_maint_date,
( lead(next_maint_date,1,next_maint_date)
over (partition by item order by maint_date)
)
) end_date
from
boundary_set
)
select
-- Final formatting and ordering.
-- For end date of last period get exact date, for others get previous date
-- because it's date of start for maintenance of next item.
item,
tick_col,
to_char(start_date, 'yyyymmdd') maint_start,
decode( (lead(end_date) over (partition by item order by start_date)),
null, to_char(end_date, 'yyyymmdd'),
to_char(end_date - 1, 'yyyymmdd')
)
maint_end
from
maintenance_sequence
order by
maint_start