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)

（编辑：湖南网）

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