我有一个具有以下结构的表: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发生变化.如果表中没有这个eid,那么它就是null.
由于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, last_am, 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)
只要我们有null,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, 3, 2, 5, 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在评论中问道,
如果数据存在差距,您的查询会发生什么情况(例如,2007-12的条目值为80,2007-10的条目为2007-10,而2007-11的条目不是一个？问题不明白应该在那里发生什么.
嗯,你是完全正确的,OP没有说明.也许存在(未提及的)前提条件,即没有间隙.在没有要求的情况下,我们不应该尝试编写可能不存在的内容.但事实是,差距使得"加入"战略失败; 在这些条件下,"重新计算"策略不会失败.我会说更多,但这将揭示我在上面提到的技巧问题中的诀窍.
京公网安备 11010802040832号 | 京ICP备19059560号-6 