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

我有一个具有以下结构的表：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

数据库是Oracle

解决方法

我也将在不使用Oracle扩展的情况下实现它,SQL应该在任何现代RBDMS上运行.所以没有保持,覆盖,分区,只是子查询和分组. (如果它在您的RDBMS上不起作用,请在评论中通知我.)

首先,表格,因为我没有创造力,我将调用month_value.由于id实际上不是唯一的id,我称之为“eid”.其他列是“m”onth,“y”ear和“v”alue：

create table month_value( 
   eid int not null,m int,y int,v int );

插入数据后,对于两个eid,我有：

> select * from month_value;
+-----+------+------+------+
| eid | m    | y    | v    |
+-----+------+------+------+
| 100 |    1 | 2008 |   80 |
| 100 |    2 | 2008 |   80 |
| 100 |    3 | 2008 |   90 |
| 100 |    4 | 2008 |   80 |
| 200 |    1 | 2008 |   80 |
| 200 |    2 | 2008 |   80 |
| 200 |    3 | 2008 |   90 |
| 200 |    4 | 2008 |   80 |
+-----+------+------+------+
8 rows in set (0.00 sec)

接下来,我们有一个实体,即月份,它表示为两个变量.那应该是一列(日期或日期时间,或者甚至是日期表的外键),所以我们将它列为一列.我们将这做为线性变换,使得它与(??y,m)相同排序,并且对于任何(y,m)元组,只有一个值,并且所有值都是连续的：

> create view cm_abs_month as 
select *,y * 12 + m as am from month_value;

这给了我们：

> select * from cm_abs_month;
+-----+------+------+------+-------+
| eid | m    | y    | v    | am    |
+-----+------+------+------+-------+
| 100 |    1 | 2008 |   80 | 24097 |
| 100 |    2 | 2008 |   80 | 24098 |
| 100 |    3 | 2008 |   90 | 24099 |
| 100 |    4 | 2008 |   80 | 24100 |
| 200 |    1 | 2008 |   80 | 24097 |
| 200 |    2 | 2008 |   80 | 24098 |
| 200 |    3 | 2008 |   90 | 24099 |
| 200 |    4 | 2008 |   80 | 24100 |
+-----+------+------+------+-------+
8 rows in set (0.00 sec)

现在,我们将在相关子查询中使用自联接来为每行查找值更改的最早后继月.我们将此视图基于我们创建的上一个视图：

> create view cm_last_am as 
   select a.*,( select min(b.am) from cm_abs_month b 
      where b.eid = a.eid and b.am > a.am and b.v <> a.v) 
   as last_am 
   from cm_abs_month a;

> select * from cm_last_am;
+-----+------+------+------+-------+---------+
| eid | m    | y    | v    | am    | last_am |
+-----+------+------+------+-------+---------+
| 100 |    1 | 2008 |   80 | 24097 |   24099 |
| 100 |    2 | 2008 |   80 | 24098 |   24099 |
| 100 |    3 | 2008 |   90 | 24099 |   24100 |
| 100 |    4 | 2008 |   80 | 24100 |    NULL |
| 200 |    1 | 2008 |   80 | 24097 |   24099 |
| 200 |    2 | 2008 |   80 | 24098 |   24099 |
| 200 |    3 | 2008 |   90 | 24099 |   24100 |
| 200 |    4 | 2008 |   80 | 24100 |    NULL |
+-----+------+------+------+-------+---------+
8 rows in set (0.01 sec)

last_am现在是第一个(最早的)月份(在当前行的月份之后)的“绝对月份”,其中值v发生变化.在表中没有月份的情况下,它就是空的.

由于last_am对于导致v的变化(发生在last_am)的所有月份是相同的,我们可以分组在last_am和v(当然也是eid),并且在任何组中,min(am)是绝对的连续第一个月有这个价值的月份：

> create view cm_result_data as 
  select eid,min(am) as am,last_am,v 
  from cm_last_am group by eid,v;

> select * from cm_result_data;
+-----+-------+---------+------+
| eid | am    | last_am | v    |
+-----+-------+---------+------+
| 100 | 24100 |    NULL |   80 |
| 100 | 24097 |   24099 |   80 |
| 100 | 24099 |   24100 |   90 |
| 200 | 24100 |    NULL |   80 |
| 200 | 24097 |   24099 |   80 |
| 200 | 24099 |   24100 |   90 |
+-----+-------+---------+------+
6 rows in set (0.00 sec)

现在这是我们想要的结果集,这就是为什么这个视图被称为cm_result_data.所有缺乏的东西都可以将绝对数月转换回(y,m)元组.

为此,我们将加入表month_value.

只有两个问题：
1)我们想在输出中的last_am之前的月份,和
2)我们的数据中没有下个月没有空值;为了满足OP的规范,那些应该是单月范围.

编辑：这些实际上可能比一个月更长的范围,但在每种情况下,他们意味着我们需要找到最新的月份,这是：

(select max(am) from cm_abs_month d where d.eid = a.eid )

因为视图会分解问题,所以我们可以通过添加另一个视图来添加一个月的“结束时间”,但我只是将其插入到coalesce中.哪个最有效取决于您的RDBMS如何优化查询.

为了获得一个月前,我们将加入(cm_result_data.last_am – 1 = cm_abs_month.am)

（编辑：西双版纳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!