来自ORACLE板的查询在DB2中不起作用 [英] Query from ORACLE board is not working in DB2
问题描述
大家好！
这个查询应该是从当年开始计算的连续年数
没有OLAP。
输入表：
ID DateCol
1 02/01/2006
1 01/01/2006
1 01/01/2005
1 01/01/2004
1 01/01/1999
2 02/01/2006
2 01/01/2005
3 04/01/2006
3 04/01/1999
4 06/30/2000
4 08/01/1999
请求的输出：
ID ConYears
1 3
2 2
3 1
解决方案使用CROSS JOIN和LEFT外联。 CROSS JOIN创建了ID和年份的所有可能组合
。然后LEFT OUTER JOIN尝试
将每个这样的ID和年份组合匹配到数据表中的一行。
WITH DATATABLE（ID，DateCol）AS
（VALUES（1，''2006-02-01''），
（1，''2006-01-01''），
（1，''2005-01-01''），
（1，''2004-01-01''），
（1 ，''1999-01-01''），
（2，''2006-02-01''），
（2，''2005-01 -01''），
（3，''2006-04-01''），
（3，'''1999-04-01''），
（4，''2000-06-30''），
（4，''1999-08-01''）），
INTEGERS（I）AS
（VALUES（0），（1），（2），（3），（4），（5），（6），（7）， （8），（9））
选择X.ID，max（X.yr）为FirstMissing，year（current_date） - max（X.yr）为
ConYears
来自（选择ID，年份（current_date） - 我为年份
来自整数
交叉加入
（从数据表中选择不同的ID）因为I）因为X
离开外部
j在T.ID = X.ID上的数据表为T
和年（T.DateCol）= X.yr
其中T. ID为空
分组由X.ID
有年（current_date） - 最大（X.yr）0;
名为X的派生表包含ID和年份的每个组合。这是外连接中左表的
，并且它连接到数据表，例如它与数据的ID和年份匹配的
。请注意，如果
多于一行的数据表匹配，则无关紧要，就像您在ID 1和2006年的原始
数据中的情况一样。
如果找不到匹配的行，使用WHERE
子句中的IS NULL条件，将保留ID和year的组合（匹配的行是
过滤掉了），然后，使用GROUP BY，只选择每个ID未找到的最大年份
，以及连续年份数
$ b为每个ID计算$ b。最后，HAVING子句拒绝任何ID，例如4
，这些ID从当前年份开始连续0年。
ID FirstMissing ConYears
1 2003 3
2 2004 2
3 2005 1
当我测试此查询时：我得到一个空输出：
ID FIRSTMISSING CONYEARS
----------- ------------ -------- ---
0条记录被选中。
知道为什么它不起作用吗？
提前致谢。 Leny G.
-
通过DBMonster.com发布消息
http://www.dbmonster.com/Uwe/Forums....m-db2/200805/1
Hi everybody!
This query is supposed to count consecutive years from the current year
without OLAP.
Input Table:
ID DateCol
1 02/01/2006
1 01/01/2006
1 01/01/2005
1 01/01/2004
1 01/01/1999
2 02/01/2006
2 01/01/2005
3 04/01/2006
3 04/01/1999
4 06/30/2000
4 08/01/1999
Requested output:
ID ConYears
1 3
2 2
3 1
The solution uses a CROSS JOIN and a LEFT OUTER JOIN. The CROSS JOIN creates
all possible combinations of ID and year. Then the LEFT OUTER JOIN attempts
to match each such ID and year combination to a row in the data table.
WITH DATATABLE(ID, DateCol) AS
(VALUES(1, ''2006-02-01''),
(1, ''2006-01-01''),
(1, ''2005-01-01''),
(1, ''2004-01-01''),
(1, ''1999-01-01''),
(2, ''2006-02-01''),
(2, ''2005-01-01''),
(3, ''2006-04-01''),
(3, ''1999-04-01''),
(4, ''2000-06-30''),
(4, ''1999-08-01'')),
INTEGERS(I) AS
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))
select X.ID , max(X.yr) as FirstMissing, year(current_date) - max(X.yr) as
ConYears
from (select ID, year(current_date) - i as yr
from integers
cross join
(select distinct ID from datatable) as I) as X
left outer
join datatable as T
on T.ID = X.ID
and year(T.DateCol) = X.yr
where T.ID is null
group by X.ID
having year(current_date) - max(X.yr) 0;
The derived table called X contains each combination of ID and year. This is
the left table in the outer join, and it is joined to the data table, such
that it matches the ID and year of the data. Note that it doesn''t matter if
more than one row of the data table matches, as is the case in your original
data for ID 1 and year 2006.
Where a matching row is not found, using the IS NULL condition in the WHERE
clause, that combination of ID and year is retained (matching rows are
filtered out), and then, using a GROUP BY, only the maximum year which was
not found for each ID is chosen, and the number of consecutive years
calculated for each ID. Finally, the HAVING clause rejects any IDs like 4
which had 0 consecutive years from the current year.
ID FirstMissing ConYears
1 2003 3
2 2004 2
3 2005 1
When i tested this query: i got an empty output:
ID FIRSTMISSING CONYEARS
----------- ------------ -----------
0 record(s) selected.
Any idea why it is not working?
Thank''s in advance. Leny G.
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200805/1
推荐答案
lenygold通过DBMonster.com写道：
lenygold via DBMonster.com wrote:
$ b $嗨大家好！
这个查询应该是从当年开始计算的连续年数
没有OLAP。
输入表：
ID DateCol
1 02/01/2006
1 01/01/2006
1 01/01/2005
1 01/01/2004
1 01/01/1999
2 02/01/2006
2 01/01/2005
3 04/01/2006
3 04/01/1999
4 06 / 30/2000
4 08/01/1999
请求的输出：
ID ConYears
1 3
2 2
3 1
解决方案使用CROSS JOIN和a LEFT OUTER JOIN。 CROSS JOIN创建了ID和年份的所有可能组合
。然后LEFT OUTER JOIN尝试
将每个这样的ID和年份组合匹配到数据表中的一行。
WITH DATATABLE（ID，DateCol）AS
（VALUES（1，''2006-02-01''），
（1，''2006-01-01''），
（1，''2005-01-01''），
（1，''2004-01-01''），
（1 ，''1999-01-01''），
（2，''2006-02-01''），
（2，''2005-01 -01''），
（3，''2006-04-01''），
（3，'''1999-04-01''），
（4，''2000-06-30''），
（4，''1999-08-01''）），
INTEGERS（I）AS
（VALUES（0），（1），（2），（3），（4），（5），（6），（7）， （8），（9））
选择X.ID，max（X.yr）为FirstMissing，year（current_date） - max（X.yr）为
ConYears
来自（选择ID，年份（current_date） - 我为年份
来自整数
交叉加入
（从数据表中选择不同的ID）为I）为X
left outer
将数据表加入为T
$ b T.ID = X.ID上的$ b
和年（T.DateCol）= X.yr
其中T.ID为空
由X.ID组成
有年份（current_date） - max（X.yr）0;
名为X的派生表包含ID的每个组合和年。这是外连接中左表的
，并且它连接到数据表，例如它与数据的ID和年份匹配的
。请注意，如果
多于一行的数据表匹配，则无关紧要，就像您在ID 1和2006年的原始
数据中的情况一样。
如果找不到匹配的行，使用WHERE
子句中的IS NULL条件，将保留ID和year的组合（匹配的行是
过滤掉了），然后，使用GROUP BY，只选择每个ID未找到的最大年份
，以及连续年份数
$ b为每个ID计算$ b。最后，HAVING子句拒绝任何ID，例如4
，这些ID从当前年份开始连续0年。
ID FirstMissing ConYears
1 2003 3
2 2004 2
3 2005 1
当我测试此查询时：我得到一个空输出：
ID FIRSTMISSING CONYEARS
----------- ------------ ---- -------
0条记录被选中。
知道为什么它不起作用吗？
提前感谢''。 Leny G.
Hi everybody!
This query is supposed to count consecutive years from the current year
without OLAP.
Input Table:
ID DateCol
1 02/01/2006
1 01/01/2006
1 01/01/2005
1 01/01/2004
1 01/01/1999
2 02/01/2006
2 01/01/2005
3 04/01/2006
3 04/01/1999
4 06/30/2000
4 08/01/1999
Requested output:
ID ConYears
1 3
2 2
3 1
The solution uses a CROSS JOIN and a LEFT OUTER JOIN. The CROSS JOIN creates
all possible combinations of ID and year. Then the LEFT OUTER JOIN attempts
to match each such ID and year combination to a row in the data table.
WITH DATATABLE(ID, DateCol) AS
(VALUES(1, ''2006-02-01''),
(1, ''2006-01-01''),
(1, ''2005-01-01''),
(1, ''2004-01-01''),
(1, ''1999-01-01''),
(2, ''2006-02-01''),
(2, ''2005-01-01''),
(3, ''2006-04-01''),
(3, ''1999-04-01''),
(4, ''2000-06-30''),
(4, ''1999-08-01'')),
INTEGERS(I) AS
(VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9))
select X.ID , max(X.yr) as FirstMissing, year(current_date) - max(X.yr) as
ConYears
from (select ID, year(current_date) - i as yr
from integers
cross join
(select distinct ID from datatable) as I) as X
left outer
join datatable as T
on T.ID = X.ID
and year(T.DateCol) = X.yr
where T.ID is null
group by X.ID
having year(current_date) - max(X.yr) 0;
The derived table called X contains each combination of ID and year. This is
the left table in the outer join, and it is joined to the data table, such
that it matches the ID and year of the data. Note that it doesn''t matter if
more than one row of the data table matches, as is the case in your original
data for ID 1 and year 2006.
Where a matching row is not found, using the IS NULL condition in the WHERE
clause, that combination of ID and year is retained (matching rows are
filtered out), and then, using a GROUP BY, only the maximum year which was
not found for each ID is chosen, and the number of consecutive years
calculated for each ID. Finally, the HAVING clause rejects any IDs like 4
which had 0 consecutive years from the current year.
ID FirstMissing ConYears
1 2003 3
2 2004 2
3 2005 1
When i tested this query: i got an empty output:
ID FIRSTMISSING CONYEARS
----------- ------------ -----------
0 record(s) selected.
Any idea why it is not working?
Thank''s in advance. Leny G.
你能分享原始的Oracle查询吗？可能比调试语义更容易检测到
的翻译错误....
-
Serge Rielau
DB2解决方案开发
IBM多伦多实验室
Can you share the original Oracle query? May be easier to detect a
translation error than debug the semantics....
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
这是原始的ORACLE QUERY：
一次我们再次使用handy-dandy整数表来帮助。如果你没有
有一个整数表，你应该;它是小巧，高效且非常有用的。
创建表整数
（我整数不为空）
插入整数值
（0），（1），（2），（3），（4），（5），（6），（7），（8）， （9）
然后可以使用整数表生成过去10年：
选择年份（current_date） - 我为你年龄
来自整数
yr
2006
2005
2004
2003
2002
2001
2000
1999
1998
1997
选择X.ID
，max（X.yr）为FirstMissing
，年份（current_date ）
-max（X.yr）作为ConYears
来自（
选择ID
，年份（current_date） ） - 我和你一样
来自整数
十字
加入（
选择不同的ID
from datatable
）as
）as X
left outer
join datatable as T < br =>
on T.ID = X.ID
和年（T.DateCol）= X.yr
其中T.ID为空
组
由X.ID
有一年（current_date）
-max（X.yr）0
Serge Rielau写道：
Here is original ORACLE QUERY:
Once again we can use the handy-dandy integers table to help. If you don''t
have an integers table, you should; it''s small, efficient and very useful.
create table integers
(i integer not null )
insert into integers values
(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)
The integers table can then be used to generate the last 10 years:
select year(current_date) - i as yr
from integers
yr
2006
2005
2004
2003
2002
2001
2000
1999
1998
1997
select X.ID
, max(X.yr) as FirstMissing
, year(current_date)
-max(X.yr) as ConYears
from (
select ID
, year(current_date) - i as yr
from integers
cross
join (
select distinct ID
from datatable
) as I
) as X
left outer
join datatable as T
on T.ID = X.ID
and year(T.DateCol) = X.yr
where T.ID is null
group
by X.ID
having year(current_date)
-max(X.yr) 0
Serge Rielau wrote:
>大家好！
这个查询应该从当前年开始计算连续年数
>Hi everybody!
This query is supposed to count consecutive years from the current year
[引用文字剪辑 - 80行]
[quoted text clipped - 80 lines]
>知道为什么它不起作用吗？
提前感谢''。 Leny G.
> Any idea why it is not working?
Thank''s in advance. Leny G.
你能分享原始的Oracle查询吗？可能比调试语义更容易检测到翻译错误....
Can you share the original Oracle query? May be easier to detect a
translation error than debug the semantics....
-
通过DBMonster发布消息.com
http：// www.dbmonster.com/Uwe/Forums....m-db2/200805/1
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200805/1
lenygold通过DBMonster.com写道：
lenygold via DBMonster.com wrote:
这是原始的ORACLE QUERY：
我们再次使用handy-dandy整数表来帮助。如果你没有
没有整数表，你应该;它小巧，高效，而且b $ b非常有用。
Here is original ORACLE QUERY:
Once again we can use the handy-dandy integers table to help. If you
don''t have an integers table, you should; it''s small, efficient and
very useful.
Nah - 拥有整数表*功能更好* :-)
- INTEGERS（START，STOP ，STEP）
- 整数（开始，停止）
- 整数（停止）
--------- -------------------------------------------------- ----------
- 一个表函数，它返回一个包含整数
的列 - 值从START到FINISH（包括），通过STEP递增（或
- 递减）。如果省略STEP，则默认为1.如果
- START也被省略，它也默认为1.
---------- -------------------------------------------------- ---------
CREATE FUNCTION INTEGERS（START INTEGER，FINISH INTEGER，STEP INTEGER）
返回表（值整数）
特定整数1
语言SQL
确定性
无外部行动
包含SQL
返回
- 仅包含I列以防止关于
的警告 - 无限递归。它的范围涵盖了整个范围
- 32位有符号整数因此它不应该妨碍生成特定结果集
/>
WITH RANGE（I，VALUE）AS（
VALUES（INTEGER（-2147483648），START）
UNION ALL
SELECT I + 1，VALUE + STEP
FROM RANGE
我在哪里< = 2147483647 AND VALUE + STEP< = FINISH
）
从RANGE中选择值;
创建函数整数（START INTEGER，FINISH INTEGER）
返回表（值整数）
SPECIFIC INTEGERS2
语言SQL
确定性
无外部行动
包含SQL
返回
从表中选择值（INTEGERS（START，FINISH，1））AS T;
创建函数整数（完成） INTEGER）
返回表（值整数）
特定整数3
语言SQL
决定性
无外部行动
CO NTAINS SQL
返回
从表中选择值（INTEGERS（1，FINISH，1））AS T;
现在我们可以做简单的事了喜欢：
SELECT * FROM TABLE（INTEGERS（10））AS T
VALUE
------- ----
1
2
3
4
5
7
8
9
10
选择了10条记录。
或稍微复杂的事情：
选择价值作为YR
FROM TABLE（整数（年（当前日期） - 9，年（当前日期）））AS T
YR
----- ------
1999
2000
2001
2002
2003年
2004
2005
2006
2007
2008
选择了10条记录。
如果你想要实际的DATE值，那么让上述
适应工作并不困难日期和持续时间（对于STEP参数）编码为
DECIMAL（8,0）：
- 日期（开始，结束，步骤）
- 日期（开始，结束）
- 日期（开始）
--------------------------------- ------------------------------------
- 一个表函数返回包含日期的单个列
- 从START到FINISH（包括），逐步递增（或
- 递减）。 STEP是一个DECIMAL（8,0）值，它编码
- 步骤为YYYYMMDD。因此，1个增量增加一天，100个增量
- 一个月，10000个增量一年。如果省略STEP，则它为
- 默认为1.如果省略FINISH，则默认为当前
- 日期。请注意，这与上面的INTEGERS函数不同。
- 选择此参数布局，因为我怀疑使用日期
- 过去比使用时更常见将来的日期。
------------------------------------- --------------------------------
创建功能日期（开始日期，完成日期，步骤十分（8,0））
退货表（价值日期）
具体日期1
语言SQL
DETERMINISTIC
无外部行动
包含SQL
返回
- I列仅限包含以防止关于
的警告 - 无限递归。值37000被选为限制
- 允许该函数生成大约一个世纪的价值b $ b - 值日期。如果你需要更多，只需增加
- 限制。
带范围（I，VALUE）AS（
VALUES（1 ，START）
UNION ALL
SELECT I + 1，VALUE + STEP
来自RANGE
我在哪里< ; = 37000 AND VALUE + STEP< = FINISH
）
从范围中选择值;
创建功能日期（开始日期，结束日期）
退货表（价值日期）
特定日期2
语言SQL
确定性
无外部行动
包含SQL
返回
选择价值
来自表格（日期） （START，FINISH，DECIMAL（1,8,8）））AS T;
创建功能日期（开始日期）
退货表（价值日期） ）
具体日期3
语言SQL
决定性确认
无外部行动
CONTAINS SQL
返回
SELECT VALUE
FROM TABLE（日期（开始，当前日期，十进制（1,8,8）））AS T ;
所以，现在我们可以生成一个简单的日期范围：
从表中选择值（日期（当前日期 - 6天））AS T
价值
----------
25/05/2008
26/05/2008
27/05/2008
28/05/2008
29/05/2008
30/05/2008
31/05/2008
7条记录被选中。
或者第一个过去十年每年的日期：
选择价值
来自表格（日期（
当前日期 - （DAYOFYEAR） （当前日期） - 1）DAYS - 9年，
当前日期，十进制（10000,8,0）
））AS T
价值
----------
01/01/1999
01/01/2000
01/01/2001
01/01/2002
01/01/2003
01/01 / 2004
01/01/2005
01/01/2006
01/01/2007
01 / 01/2008
选择了10条记录。
无论如何，关于主要问题。你在原帖中说过你没有OLAP功能你想要这么做。一个有趣的学术运动，当然是（坚持我的懒人编码器帽子一秒钟），
有什么理由你不想拿简单的路线？
干杯，
戴夫。
Nah - much better to have an integers table *function* :-)
-- INTEGERS(START, STOP, STEP)
-- INTEGERS(START, STOP)
-- INTEGERS(STOP)
---------------------------------------------------------------------
-- A table function which returns a single column containing integer
-- values ranging from START to FINISH (inclusive), incrementing (or
-- decrementing) by STEP. If STEP is ommitted, it defaults to 1. If
-- START is also ommitted, it also defaults to 1.
---------------------------------------------------------------------
CREATE FUNCTION INTEGERS(START INTEGER, FINISH INTEGER, STEP INTEGER)
RETURNS TABLE(VALUE INTEGER)
SPECIFIC INTEGERS1
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
-- The I column is only included to prevent warnings about
-- infinite recursion. Its range covers the entire range of
-- 32-bit signed integers hence it shouldn''t get in the way
-- of producing a particular result set
WITH RANGE(I, VALUE) AS (
VALUES (INTEGER(-2147483648), START)
UNION ALL
SELECT I + 1, VALUE + STEP
FROM RANGE
WHERE I <= 2147483647 AND VALUE + STEP <= FINISH
)
SELECT VALUE FROM RANGE;
CREATE FUNCTION INTEGERS(START INTEGER, FINISH INTEGER)
RETURNS TABLE(VALUE INTEGER)
SPECIFIC INTEGERS2
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
SELECT VALUE FROM TABLE(INTEGERS(START, FINISH, 1)) AS T;
CREATE FUNCTION INTEGERS(FINISH INTEGER)
RETURNS TABLE(VALUE INTEGER)
SPECIFIC INTEGERS3
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
SELECT VALUE FROM TABLE(INTEGERS(1, FINISH, 1)) AS T;
Now we can do simple things like:
SELECT * FROM TABLE(INTEGERS(10)) AS T
VALUE
-----------
1
2
3
4
5
6
7
8
9
10
10 record(s) selected.
Or slightly more complicated things like:
SELECT VALUE AS YR
FROM TABLE(INTEGERS(YEAR(CURRENT DATE) - 9, YEAR(CURRENT DATE))) AS T
YR
-----------
1999
2000
2001
2002
2003
2004
2005
2006
2007
2008
10 record(s) selected.
If you want actual DATE values, it''s not difficult to adapt the above
to work with dates and durations (for the STEP parameter) encoded as
DECIMAL(8,0):
-- DATES(START, FINISH, STEP)
-- DATES(START, FINISH)
-- DATES(START)
---------------------------------------------------------------------
-- A table function which returns a single column containing dates
-- ranging from START to FINISH (inclusive), incrementing (or
-- decrementing) by STEP. STEP is a DECIMAL(8, 0) value which encodes
-- the step as YYYYMMDD. Hence, 1 increments by a day, 100 increments
-- by a month, 10000 increments by a year. If STEP is ommitted, it
-- defaults to 1. If FINISH is ommitted, it defaults to the current
-- date. Note that this differs from the INTEGERS function above.
-- This parameter layout was chosen as I suspect working with dates
-- in the past is more common than working with dates in the future.
---------------------------------------------------------------------
CREATE FUNCTION DATES(START DATE, FINISH DATE, STEP DECIMAL(8,0))
RETURNS TABLE(VALUE DATE)
SPECIFIC DATES1
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
-- The I column is only included to prevent warnings about
-- infinite recursion. The value 37000 was chosen as the limit
-- to allow the function to generate approximately a century''s
-- worth of dates. If you need more than this, just increase
-- the limit.
WITH RANGE(I, VALUE) AS (
VALUES (1, START)
UNION ALL
SELECT I + 1, VALUE + STEP
FROM RANGE
WHERE I <= 37000 AND VALUE + STEP <= FINISH
)
SELECT VALUE FROM RANGE;
CREATE FUNCTION DATES(START DATE, FINISH DATE)
RETURNS TABLE(VALUE DATE)
SPECIFIC DATES2
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
SELECT VALUE
FROM TABLE(DATES(START, FINISH, DECIMAL(1, 8, 0))) AS T;
CREATE FUNCTION DATES(START DATE)
RETURNS TABLE(VALUE DATE)
SPECIFIC DATES3
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN
SELECT VALUE
FROM TABLE(DATES(START, CURRENT DATE, DECIMAL(1, 8, 0))) AS T;
So, now we can generate a simple range of dates:
SELECT VALUE FROM TABLE(DATES(CURRENT DATE - 6 DAYS)) AS T
VALUE
----------
25/05/2008
26/05/2008
27/05/2008
28/05/2008
29/05/2008
30/05/2008
31/05/2008
7 record(s) selected.
Or the first date of each year for the last ten years:
SELECT VALUE
FROM TABLE(DATES(
CURRENT DATE - (DAYOFYEAR(CURRENT DATE) - 1) DAYS - 9 YEARS,
CURRENT DATE, DECIMAL(10000, 8, 0)
)) AS T
VALUE
----------
01/01/1999
01/01/2000
01/01/2001
01/01/2002
01/01/2003
01/01/2004
01/01/2005
01/01/2006
01/01/2007
01/01/2008
10 record(s) selected.
Anyway, regarding the main problem. You stated in the original post you
wanted to do this without OLAP functions. An interesting academic
exercise, certainly but (sticking on my "lazy coder" hat for a second),
is there any reason you don''t want to take the easy route?
Cheers,
Dave.
这篇关于来自ORACLE板的查询在DB2中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
