创建一个 20 年的 DB2 日历表，其中的列依赖于原始日期

问题描述

我正在尝试创建一个 20 年的日历表，范围从 2000 年到 2020 年.目标是每天有一行以及其他一些列，这些列将使用基于生成的日历日期的逻辑.例如，将一列作为日历日期 (2000-01-01)，将年份列从日历日期列 (2000) 中的值中读取年份.

表格代码如下:

CREATE TABLE TEST.CALENDAR(CALENDAR_DATE 日期非空，CALENDAR_YEAR INTEGER 非空，CALENDAR_MONTH_NUMBER 整数非空，CALENDAR_MONTH_NAME VARCHAR(100),CALENDAR_DAY_OF_MONTH 整数非空，CALENDAR_DAY_OF_WEEK 整数非空，CALENDAR_DAY_NAME VARCHAR(100),CALENDAR_YEAR_MONTH 整数非空)；

目前，我有一堆插入语句，可以手动为这个表插入行超过 20 年.我希望用变量创建一个插入语句，这个插入语句将以每日增量插入数据，直到开始日期变量不小于结束日期变量.

目前，我根本无法让它工作，更不用说包含任何其他列的任何逻辑了.

变量插入语句的代码:

declare startdate DATE, enddate DATEset startdate = '2000-01-01'设置结束日期 = DATEADD(yy,20,startdate)而开始日期

有人知道我如何让它工作吗?

解决方案

您可以使用 DB2 递归查询和日期函数来做到这一点:

考虑:

with cte (日历日期，公历年，calendar_month_number,calendar_month_name,calendar_day_of_month，calendar_day_of_week,calendar_day_name) 作为 (选择日历日期，年(日历日期)，月(日历日期)，月份名称(日历日期)，月日(日历日期)，星期几(日历日期)，日期名称(日历日期)from (values(date('2000-01-01'))) as t(calendar_date)联合所有选择日历日期 + 1,年(日历日期 + 1)，月(calendar_date + 1)，月份名称(日历日期 + 1)，dayofmonth(calendar_date + 1),星期几(calendar_date + 1)，日期名称(日历日期 + 1)从 cte where calendar_date 

注意:我不清楚 CALENDAR_YEAR_MONTH 列是什么意思，所以我把它分开了.

DB Fiddle 演示前 10 天:

CALENDAR_DATE |CALENDAR_YEAR |CALENDAR_MONTH_NUMBER |CALENDAR_MONTH_NAME |CALENDAR_DAY_OF_MONTH |CALENDAR_DAY_OF_WEEK |CALENDAR_DAY_NAME------------: |------------: |--------------------: |------------------: |--------------------: |-------------------: |----------------:2000-01-01 |2000 |1 |一月 |1 |7 |周六2000-01-02 |2000 |1 |一月 |2 |1 |星期日2000-01-03 |2000 |1 |一月 |3 |2 |周一2000-01-04 |2000 |1 |一月 |4 |3 |周二2000-01-05 |2000 |1 |一月 |5 |4 |周三2000-01-06 |2000 |1 |一月 |6 |5 |周四2000-01-07 |2000 |1 |一月 |7 |6 |星期五2000-01-08 |2000 |1 |一月 |8 |7 |周六2000-01-09 |2000 |1 |一月 |9 |1 |星期日2000-01-10 |2000 |1 |一月 |10 |2 |周一

I'm trying to create a calendar table for 20 years ranging from 2000 - 2020. The aim is to have one row per day along with some other columns that will use logic based on the calendar date generated. An example of this would be having One column as the calendar date (2000-01-01) and the year column reading the year from the values within the calendar date column (2000).

The code for the table is below:

CREATE TABLE TEST.CALENDAR(
CALENDAR_DATE         DATE    NOT NULL,
CALENDAR_YEAR         INTEGER NOT NULL,
CALENDAR_MONTH_NUMBER INTEGER NOT NULL,
CALENDAR_MONTH_NAME   VARCHAR(100),
CALENDAR_DAY_OF_MONTH INTEGER NOT NULL,
CALENDAR_DAY_OF_WEEK  INTEGER NOT NULL,
CALENDAR_DAY_NAME     VARCHAR(100),
CALENDAR_YEAR_MONTH   INTEGER NOT NULL);

At the moment, I have a bunch of insert statements that manually insert rows for this table over 20 years. I'm looking to make an insert statement with variables instead and this insert statement would insert data in daily increments until the start date variable is not less than the end date variable.

Currently, I cannot get this to work at all let alone include any logic for any other columns.

Code for the variable insert statement:

declare startdate DATE, enddate DATEset startdate = '2000-01-01'
set enddate = DATEADD(yy,20,startdate)
while startdate < enddate
begin insert into TEST.CALENDAR (CALENDAR_DATE) select startdate
set startdate = DATEADD(dd,1,startdate) end

Would anyone have any ideas of how I can get this to work?

解决方案

You can do this with a DB2 recursive query and date functions:

Consider:

with cte (
    calendar_date,
    calendar_year,
    calendar_month_number,
    calendar_month_name,
    calendar_day_of_month,
    calendar_day_of_week,
    calendar_day_name
) as (
    select 
        calendar_date,
        year(calendar_date),
        month(calendar_date),
        monthname(calendar_date),
        dayofmonth(calendar_date),
        dayofweek(calendar_date),
        dayname(calendar_date)
    from (values(date('2000-01-01'))) as t(calendar_date)
    union all
    select 
        calendar_date + 1,
        year(calendar_date + 1),
        month(calendar_date + 1),
        monthname(calendar_date + 1),
        dayofmonth(calendar_date + 1),
        dayofweek(calendar_date + 1),
        dayname(calendar_date + 1)
    from cte where calendar_date < date('2021-01-01')
)
select * from cte

Note: it is unclear to me what column CALENDAR_YEAR_MONTH means, so I left it apart.

Demo on DB Fiddle for the first 10 days:

CALENDAR_DATE | CALENDAR_YEAR | CALENDAR_MONTH_NUMBER | CALENDAR_MONTH_NAME | CALENDAR_DAY_OF_MONTH | CALENDAR_DAY_OF_WEEK | CALENDAR_DAY_NAME
------------: | ------------: | --------------------: | ------------------: | --------------------: | -------------------: | ----------------:
   2000-01-01 |          2000 |                     1 |             January |                     1 |                    7 |          Saturday
   2000-01-02 |          2000 |                     1 |             January |                     2 |                    1 |            Sunday
   2000-01-03 |          2000 |                     1 |             January |                     3 |                    2 |            Monday
   2000-01-04 |          2000 |                     1 |             January |                     4 |                    3 |           Tuesday
   2000-01-05 |          2000 |                     1 |             January |                     5 |                    4 |         Wednesday
   2000-01-06 |          2000 |                     1 |             January |                     6 |                    5 |          Thursday
   2000-01-07 |          2000 |                     1 |             January |                     7 |                    6 |            Friday
   2000-01-08 |          2000 |                     1 |             January |                     8 |                    7 |          Saturday
   2000-01-09 |          2000 |                     1 |             January |                     9 |                    1 |            Sunday
   2000-01-10 |          2000 |                     1 |             January |                    10 |                    2 |            Monday

这篇关于创建一个 20 年的 DB2 日历表，其中的列依赖于原始日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！