我想为该表创建一个视图,该视图折叠相同的值,如：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)
无论我们在哪里有空,OP都希望“to”月与“from”月相同,所以我们只需使用coalesce：coalesce(last_am,am).由于last删除了任何空值,因此我们的连接不需要是外连接.
> select a.eid,b.m,b.y,c.m,c.y,a.v
from cm_result_data a
join cm_abs_month b
on ( a.eid = b.eid and a.am = b.am)
join cm_abs_month c
on ( a.eid = c.eid and
coalesce( a.last_am - 1,(select max(am) from cm_abs_month d where d.eid = a.eid )
) = c.am)
order by 1,3,2,5,4;
+-----+------+------+------+------+------+
| eid | m | y | m | y | v |
+-----+------+------+------+------+------+
| 100 | 1 | 2008 | 2 | 2008 | 80 |
| 100 | 3 | 2008 | 3 | 2008 | 90 |
| 100 | 4 | 2008 | 4 | 2008 | 80 |
| 200 | 1 | 2008 | 2 | 2008 | 80 |
| 200 | 3 | 2008 | 3 | 2008 | 90 |
| 200 | 4 | 2008 | 4 | 2008 | 80 |
+-----+------+------+------+------+------+
通过加入我们获得OP想要的输出.
不是说我们必须加入.碰巧,我们的absolute_month函数是双向的,所以我们可以重新计算年份并从中抵消月份.
首先,让我们来处理添加“封顶”月份：
> create or replace view cm_capped_result as select eid,am,coalesce( last_am - 1,(select max(b.am) from cm_abs_month b where b.eid = a.eid) ) as last_am,v from cm_result_data a;
现在我们得到按OP格式化的数据：
select eid,( (am - 1) % 12 ) + 1 as sm,floor( ( am - 1 ) / 12 ) as sy,( (last_am - 1) % 12 ) + 1 as em,floor( ( last_am - 1 ) / 12 ) as ey,v from cm_capped_result order by 1,4; +-----+------+------+------+------+------+ | eid | sm | sy | em | ey | v | +-----+------+------+------+------+------+ | 100 | 1 | 2008 | 2 | 2008 | 80 | | 100 | 3 | 2008 | 3 | 2008 | 90 | | 100 | 4 | 2008 | 4 | 2008 | 80 | | 200 | 1 | 2008 | 2 | 2008 | 80 | | 200 | 3 | 2008 | 3 | 2008 | 90 | | 200 | 4 | 2008 | 4 | 2008 | 80 | +-----+------+------+------+------+------+
还有OP想要的数据.所有sql都应该在任何RDBMS上运行,并且被分解为简单,易于理解且易于测试的视图.
是重新加入还是重新计算？我会把这个(这是一个技巧问题)留给读者.
(如果您的RDBMS不允许在视图中使用分组,则必须首先加入,然后分组或分组,然后使用相关子查询提取月份和年份.这留给读者练习.)
Jonathan Leffler在评论中问道,
What happens with your query if there
are gaps in the data (say there’s an
entry for 2007-12 with value 80,and
another for 2007-10,but not one for
2007-11? The question isn’t clear what
should happen there.
嗯,你是完全正确的,OP没有指明.也许存在(未提及的)前提条件,即没有间隙.在没有要求的情况下,我们不应该尝试围绕可能不存在的东西进行编码.但事实是,差距使得“加入”战略失败;在这些条件下,“重新计算”策略不会失败.我会说更多,但这将揭示我在上面提到的技巧问题中的诀窍.
