SQL查询以按日期范围折叠重复的值

Question

我有一个具有以下结构的表：ID，Month，Year，Value，每个ID每个月一个条目的值，大多数月份都具有相同的值。

我想为该表创建一个视图，该视图折叠如下所示的相同值：ID，开始月，结束月，开始年，结束年，值，每个值每个ID一行。

要注意的是，如果值发生变化然后又回到原始值，则表中应该有两行

所以：

• 100 1 2008 80
• 100 2 2008 80
• 100 3 2008 90
• 100 4 2008 80

应该产生

• 100 1 2008 2 2008 80
• 100 3 2008 3 2008 90
• 100 4 2008 4 2008 80

当值返回原始值时，以下查询适用于除此特殊情况以外的所有情况。

select distinct id, min(month) keep (dense_rank first order by month) 
over (partition   by id, value) startMonth, 
max(month) keep (dense_rank first order by month desc) over (partition
by id, value) endMonth, 
value

数据库就是甲骨文

Answer 1

我得到它的工作如下。它专注于分析功能，并且特定于Oracle。

select distinct id, value,
decode(startMonth, null,
  lag(startMonth) over(partition by id, value order by startMonth, endMonth),  --if start is null, it's an end so take from the row before
startMonth) startMonth,

  decode(endMonth, null,
  lead(endMonth) over(partition by id, value order by startMonth, endMonth),  --if end is null, it's an start so take from the row after
endMonth) endMonth

from (
select id, value, startMonth, endMonth from(
select id, value, 
decode(month+1, lead(month) over(partition by id,value order by month), null, month)     
startMonth, --get the beginning month for each interval
decode(month-1, lag(month) over(partition by id,value order by month), null, month)     
endMonth --get the end month for each interval from Tbl
) a 
where startMonth is not null or endMonth is not null --remain with start and ends only
)b

可能可以稍微简化一些内部查询

内部查询按如下方式检查月份是否是间隔的第一个月/最后一个月：如果月份+ 1 ==该分组的下个月（滞后），则由于存在下个月，因此本月显然不是结束月份。否则，它
是 间隔的最后一个月。相同的概念用于检查第一个月。

外部查询首先过滤掉所有不是开始月或结束月（where startMonth is not null or endMonth is not null）的行。然后，每行要么是开始月份，要么是结束月份（或两者兼有），具体取决于开始或结束不为空。如果月份是开始月份，则通过获取该id的下一个（提前）endMonth（由endMonth排序）来获得相应的结束月份；如果是endMonth，则通过查找前一个startMonth（滞后）来获取startMonth。