h***a 发帖数: 18 | 1 Table:
ID Time Event
1001 01/01/2010 0
1001 02/01/2010 0
1001 03/01/2010 1
1001 04/01/2010 1
1001 05/01/2010 1
1001 06/01/2010 0
1001 07/01/2010 0
1002 01/01/2010 0
1002 02/01/2010 1
1002 03/01/2010 1
1002 04/01/2010 0
1002 05/01/2010 1
1002 06/01/2010 1
1002 07/01/2010 1
1002 08/01/2010 0
1003 01/01/2010 0
1003 02/01/2010 0
1003 03/01/2010 0
1003 04/01/2010 1
1003 05/01/2010 1
1003 06/01/2010 1
1004 01/01/2010 0
1004 02/01/2010 0
1004 03/01/2010 0
1004 04/01/2010 0
1004 05/01/2010 0
1004 06/01/2010 0
Query的结果应该是:
ID Time
1001 03/01/2010
1002 05/01/2010
1003 04/01/2010
Query就是找每个ID的最后一个"event=1"系列的"第一个月". 有些ID有几个"event=1"
的系列. 比如ID=1002有两个系列(02/01/2010-03/01/2010, 05/01/2010-07/01/2010),
需要的结果是05/01/2010. 有些ID根本没有"event=1"发生, 比如ID=1004, query的
结果就不能有ID=1004.
请问大牛们, 这个query怎么写? 我用的是DB2. 谢谢! |
y****w 发帖数: 3747 | 2 try this,
with t(id, seed, begin)
as
(
select id, max(time), max(time) from test where event = 1 group by
id
union all
select t.id, t.seed, time from test, t where test.id = t.id and test
.time = t.begin - 1 month and test.event = 1
)
select id, min(begin) from t group by id
order by id
;
不需要保存结束时间的话把seed列去掉。放在这儿是为了理解方便。 |
h***a 发帖数: 18 | 3 Thank you very much! Although I do not 100% understand it, I will try it. |
B*****g 发帖数: 34098 | 4 一个系列必须是至少有2个月吗?
),
【在 h***a 的大作中提到】 : Table: : ID Time Event : 1001 01/01/2010 0 : 1001 02/01/2010 0 : 1001 03/01/2010 1 : 1001 04/01/2010 1 : 1001 05/01/2010 1 : 1001 06/01/2010 0 : 1001 07/01/2010 0 : 1002 01/01/2010 0
|
h***a 发帖数: 18 | 5 不一定. 有的ID只有一个系列, 有的ID有2个系列, 有的ID则多于2个系列. 当然也有ID
根本就没有系列. |
h***a 发帖数: 18 | 6 不一定. 可以只有1个月的系列
【在 B*****g 的大作中提到】 : 一个系列必须是至少有2个月吗? : : ),
|
y****w 发帖数: 3747 | 7 如果把需求改成“选出序列的第一个元素,该序列至少有连续两个元素具有event=1”.
这个题目就有意思多了,多一层嵌套。
【在 B*****g 的大作中提到】 : 一个系列必须是至少有2个月吗? : : ),
|
B*****g 发帖数: 34098 | 8 本人不懂DB2
不过俺以前说过问SQL问题请注明数据库和版本。版本也很重要,因为各大公司都在不
停的加新东西。如果DB2 9.5以上请往下看(假设DB2的文档资料是正确的)
俺以前说过本版90% SQL问题可以被partition by解决,请检查LAG在你的DB2里是否
work,如果work请往下看。
对于有LAG的数据库(没有DB2,不能测试)
WITH b AS (
SELECT a.*,
LAG(event, 1, 0) OVER (PARTITION BY ID ORDER BY TIME) AS prev
FROM TEST a)
SELECT ID, MAX(TIME) TIME, EVENT
FROM b
WHERE EVENT = 1 AND PREV <> 1
GROUP BY ID, EVENT
注意: 上述SQL还有不少exception没有handle
【在 h***a 的大作中提到】 : 不一定. 可以只有1个月的系列
|
y****w 发帖数: 3747 | 9 lag这种很大程度上是提供oracle兼容性的,9.7加了n多。 9.7甚至还提供了一个不完整的pl-sql解释器,用于方便migration项目,这些东西提供方便的同时也提供了相当的混乱。
对于问sql的,我其实更倾向于用具体数据库无关的方式解决。(当然小处差异除外,比如date function的差异)。通用方案的支持是会越来越好的,database/sql developer会越来越具体dbms无关的,这也是个趋势。
【在 B*****g 的大作中提到】 : 本人不懂DB2 : 不过俺以前说过问SQL问题请注明数据库和版本。版本也很重要,因为各大公司都在不 : 停的加新东西。如果DB2 9.5以上请往下看(假设DB2的文档资料是正确的) : 俺以前说过本版90% SQL问题可以被partition by解决,请检查LAG在你的DB2里是否 : work,如果work请往下看。 : 对于有LAG的数据库(没有DB2,不能测试) : WITH b AS ( : SELECT a.*, : LAG(event, 1, 0) OVER (PARTITION BY ID ORDER BY TIME) AS prev : FROM TEST a)
|
B*****g 发帖数: 34098 | 10 趋势就是不用SQL
完整的pl-sql解释器,用于方便migration项目,这些东西提供方便的同时也提供了相
当的混乱。
,比如date function的差异)。通用方案的支持是会越来越好的,database/sql
developer会越来越具体dbms无关的,这也是个趋势。
在不
【在 y****w 的大作中提到】 : lag这种很大程度上是提供oracle兼容性的,9.7加了n多。 9.7甚至还提供了一个不完整的pl-sql解释器,用于方便migration项目,这些东西提供方便的同时也提供了相当的混乱。 : 对于问sql的,我其实更倾向于用具体数据库无关的方式解决。(当然小处差异除外,比如date function的差异)。通用方案的支持是会越来越好的,database/sql developer会越来越具体dbms无关的,这也是个趋势。
|
B*****g 发帖数: 34098 | 11 BTW, lag是ANSI SQL:2008 standard,不支持是不正确的,sql server Denali 也会把
这个加上。
其实就是ORACLE rocks,oracle有的,以后就是standard。
大家现在加入CINAOUG还不晚
是否
【在 B*****g 的大作中提到】 : 趋势就是不用SQL : : 完整的pl-sql解释器,用于方便migration项目,这些东西提供方便的同时也提供了相 : 当的混乱。 : ,比如date function的差异)。通用方案的支持是会越来越好的,database/sql : developer会越来越具体dbms无关的,这也是个趋势。 : 在不
|
y****w 发帖数: 3747 | 12 oracle的recurcive CTE好像是最后一个完善起来的,呵呵
会把
【在 B*****g 的大作中提到】 : BTW, lag是ANSI SQL:2008 standard,不支持是不正确的,sql server Denali 也会把 : 这个加上。 : 其实就是ORACLE rocks,oracle有的,以后就是standard。 : 大家现在加入CINAOUG还不晚 : : 是否
|
B*****g 发帖数: 34098 | 13 至少比sql server, mysql早
【在 y****w 的大作中提到】 : oracle的recurcive CTE好像是最后一个完善起来的,呵呵 : : 会把
|
y****w 发帖数: 3747 | 14 我怎么记得是sql server更早些。oracle早些版本有cte但好像recurcive不起来,那是
connect by的时代。算了,就一个模糊印象而已,从没有考证过。
【在 B*****g 的大作中提到】 : 至少比sql server, mysql早
|