IBM DB2：生成两个日期之间的日期列表

Question

IBM DB2：生成两个日期之间的日期列表

6

我需要一个查询，可以输出两个给定日期之间的日期列表。

例如，如果我的起始日期是2016年2月23日，结束日期是2016年3月2日，我期望得到以下输出：

Date
----
23/02/2016
24/02/2016
25/02/2016
26/02/2016
27/02/2016
28/02/2016
29/02/2016
01/03/2016
02/03/2016

同样，我需要仅使用SQL（没有使用'WITH'语句或表格）来完成上述任务。请帮助。

- Goutam

1

你不想使用 WITH 子句是有特别的原因吗？还是只是为了让问题更具挑战性？ - Tom H

你在标记中同时使用了 db2400 和 db2-luw。这个平台是在哪个上运行的？ - Ian Bjorhovde

我正在寻找一个仅使用SQL的解决方案，因为WITH语句无法在SELECT语句内部工作。此外，我没有创建表的权限。 - Goutam

我不确定使用的平台，我在谷歌上搜索了一下并找到了这个查询语句“SELECT * FROM SYSIBMADM.ENV_INST_INFO;”，得到了“DB2 v10.5.0.3”的结果，这有帮助吗？ - Goutam

我尝试使用connect by和level编写查询，但它不起作用，每次都会出现错误，但没有适当的错误消息，我猜测是因为我的IDE，我正在使用DBVisualiser连接到DB2。不幸的是，我现在没有昨天写的示例查询。 - Goutam

顺便提一下：如果你还没有创建日期维度表，我非常强烈建议你创建一个；基本上，这是一个包含每个日期的表格，以及你想要索引的列数。它可以使某些分析查询更加容易，并且可以将此查询简化为 SELECT calendarDate FROM Calendar WHERE calendarDate >= :start AND calendarDate < :end 的范围查询检查。 - Clockwork-Muse

4个回答

3

为生成行，需要使用递归SQL。通常在DB2中是这样写的：

with temp (date) as (
select date('23.02.2016') as date from sysibm.sysdummy1
union all
select date + 1 day from temp
where date < date('02.03.2016') 
)

选择*从temp

由于某种原因，应该避免使用CTE（使用WITH）。一个可能的解决方法是设置

db2set DB2_COMPATIBILITY_VECTOR=8

该功能使得使用CONNECT BY时可以使用Oracle风格的递归。

SELECT date('22.02.2016') + level days  as dt
  FROM sysibm.sysdummy1 CONNECT BY date('22.02.2016') + level days <= date('02.03.2016')

请注意：设置 DB2_COMPATIBILITY_VECTOR 后需要重新启动实例。

- MichaelTiefenbacher

谢谢您的回复 :) 我其实已经尝试过使用connect by和level，但在我的环境中似乎无法工作。我的IDE也没有给我任何正确的错误信息，所以我很难进行调试。 - Goutam

0

这个解决方案不使用 WITH，但使用 WHILE 和临时表...希望仍然满足您的需求？

编辑-我在 SSMS 2014 中构建了此解决方案

DECLARE @Start DATE
DECLARE @End DATE

SET @Start = '2016-02-23'
SET @End = '2016-03-02'

CREATE TABLE #Dates ([Date] DATE)

WHILE @Start <= @End

BEGIN

INSERT INTO #Dates

SELECT @Start

SET @Start = DATEADD(Day,1,@Start)

END

SELECT * FROM #Dates

DROP TABLE #Dates

- TheGuyThatDoesn'tKnowMuch

抱歉，我需要一个仅限于SQL的解决方案。不幸的是，由于我所处的环境存在一些限制，我无法进行测试。但还是非常感谢您的努力 :) - Goutam

0

我假设AS400不支持递归CTE，这就是为什么您想要一种没有它们的解决方案。我不知道它是否支持以下任何构造，但这可能值得一试。首先，我们需要一个生成器，任何具有足够行数的表格都可以。如果您没有足够多天数的大表格，可以创建笛卡尔积。例如：

select row_number() over ()
from a_table
cross join a_table

扩展域的另一种方法是使用“group by cube”创建表的幂集，如下所示。

假设我们可以通过某种方式创建足够大的行集。您可以生成日期如下：

select date('23/02/2016') + n days
from (
    select row_number() over () as n
    from a_table
) as t
where n < 100
order by n

如果出于某种原因您不想使用现有的表格，那么使用立方体分组将生成一个基数等于属性幂集的关系。在这里，我使用4列将生成16行。

select date('2016-01-01') + row_number() over () days 
from sysibm.dual x 
group by cube(x.dummy, x.dummy, x.dummy, x.dummy)

如果您想生成100行数据，您需要在GROUP BY CUBE子句中使用7个属性（因为2^7=128），并使用FETCH FIRST 100 ROWS语句。

select date('2016-01-01') + row_number() over () days 
from sysibm.dual x 
group by cube(x.dummy, x.dummy, x.dummy, x.dummy, x.dummy, x.dummy, x.dummy)
order by 1
fetch first 100 rows only

- Lennart - Slava Ukraini

我尝试执行查询，如下：select '23/02/2016' + n days from ( select row_number() over () as n from sysibm.dual ) as t where n < 100; 但是我遇到了一个错误。你能帮我调试一下吗？ - Goutam

你可能需要将日期转换为日期类型，例如 select date('2016-02-23') + ...。 - Lennart - Slava Ukraini

将日期选为('20160223')，然后加上n天，did the casting and select date('20160223') + n days from (select row_number() over () as n from reporter_status) as t where n < 100; 出现错误："17:46:59 [SELECT - 0行，0.577秒] [错误代码：-180，SQL 状态：22007] DB2 SQL 错误：SQLCODE=-180，SQLSTATE=22007，SQLERRMC=null，DRIVER=4.18.60 ...执行了1个语句，0行受影响，执行/提取时间：0.577/0.000秒 [0成功，0警告，1错误]" 这有帮助吗？ - Goutam

谢谢Lennart，现在它正常工作:) 根据我的要求进行了一些更改，最终查询如下所示：选择日期('2016-01-31') - (n-1) 天从 ( 选择行编号() 超过 () 作为 n 来自 reporter_status ) 作为 t 其中n<= days ('2016-01-31') - days (date('2015-02-01')-1 天) - Goutam

DB2（所有最新版本）支持递归CTE。 - Clockwork-Muse

显示剩余2条评论

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Mita · Accepted Answer

我主要使用iSeries的DB2，所以我将为您提供一个仅基于SQL的解决方案。目前我无法访问服务器，因此该查询尚未经过测试，但它应该可以工作。编辑查询已经经过测试并且可行。

SELECT
    d.min + num.n DAYS
FROM
    -- create inline table with min max date
    (VALUES(DATE('2015-02-28'), DATE('2016-03-01'))) AS d(min, max)
INNER JOIN
    -- create inline table with numbers from 0 to 999
    (
        SELECT
            n1.n + n10.n + n100.n AS n
        FROM
            (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS n1(n)
        CROSS JOIN
            (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) AS n10(n)
        CROSS JOIN
            (VALUES(0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) AS n100(n)
    ) AS num
ON
    d.min + num.n DAYS<= d.max
ORDER BY
    num.n;

如果您不希望仅执行一次查询，那么您应该考虑创建一个包含循环值的真实表格：

CREATE TABLE dummy_loop AS (
    SELECT
        n1.n + n10.n + n100.n AS n
    FROM
        (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS n1(n)
    CROSS JOIN
        (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) AS n10(n)
    CROSS JOIN
        (VALUES(0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) AS n100(n)
) WITH DATA;

ALTER TABLE dummy_loop ADD PRIMARY KEY (dummy_loop.n);

这取决于您使用它的原因，但是您甚至可以创建一个持续100年的表。只需一个日期字段，表格将仅有100 * 365 = 36500行，因此对于连接来说，表格将非常小且快速。

CREATE TABLE dummy_dates AS (
    SELECT
        DATE('1970-01-01') + (n1.n + n10.n + n100.n) DAYS AS date
    FROM
        (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS n1(n)
    CROSS JOIN
        (VALUES(0),(10),(20),(30),(40),(50),(60),(70),(80),(90)) AS n10(n)
    CROSS JOIN
        (VALUES(0),(100),(200),(300),(400),(500),(600),(700),(800),(900)) AS n100(n)
) WITH DATA;

ALTER TABLE dummy_dates ADD PRIMARY KEY (dummy_dates.date);

选择查询语句可能如下所示：

SELECT
    *
FROM
    dummy_days
WHERE
    date BETWEEN(:startDate, :endDate);

编辑2：感谢@Lennart的建议，我已经将TABLE(VALUES(..,..,..))更改为VALES(..,..,..)，因为如他所说TABLE是LATERAL的同义词，这让我非常惊讶。

编辑3：感谢@godric7gt，我已经删除了TIMESTAMPDIFF，并将其从所有脚本中删除，因为文档中指出：

在将第二个参数（时间戳持续时间）转换为第一个参数指定的间隔类型时，使用这些假设。 返回的估计可能会相差几天。例如，如果要求'1997-03-01-00.00.00'和'1997-02-01-00.00.00'之间的天数（间隔16），则结果为30。这是因为时间戳之间的差异为1个月，一个月30天的假设适用。

这让我非常惊讶，因为我一直相信这个函数可以计算天数差异。