mysql - 在mysql中创建一个日期范围

Question

动态创建日期范围以用于报告的最佳方式。

因此，如果给定日期没有事件，我可以避免在报告中出现空行。

主要是为了避免这个问题:What is the most straightforward way to pad empty dates in sql results (on either mysql or perl end)?

Answer 1

我的建议是:不要让你的生活变得更艰难，让它变得更轻松。只需为每个日历日创建一个包含一行的表格，其中的行数与您认为合理需要的行数一样多。在数据仓库中，这是常见的解决方案，并且以这种方式广泛实现，以至于没有它的 dwh 都有代码味道。

许多习惯于处理更传统的 oltp/数据输入应用程序的人自然而然地厌恶这个想法，因为感觉无论如何都会生成数据，因此不应该存储它。但是如果你确实创建了一个这样的表，你可以用许多有用的属性来装饰它，比如它是假期还是周末，你可以在里面存储许多常见的日期表示(iso、欧洲、美国格式等)，这可以在创建报告时为您节省大量时间(因为您不必费心弄清楚每个报告工具中的日期格式是如何工作的。或者您可以更进一步，每天更新日期表以标记标志当天、本周、当月、当年等 - 各种有用的工具可以让构建需要在某个日期范围内工作的报告变得非常非常容易。

MySQL示例代码根据评论中的要求:

delimiter //

DROP PROCEDURE IF EXISTS p_load_dim_date
//

CREATE PROCEDURE p_load_dim_date (
    p_from_date DATE
,   p_to_date   DATE
)
BEGIN
    DECLARE v_date DATE DEFAULT p_from_date;
    DECLARE v_month tinyint;
    CREATE TABLE IF NOT EXISTS dim_date (
        date_key               int          primary key
    ,   date_value             date
    ,   date_iso               char(10)
    ,   year                   smallint
    ,   quarter                tinyint
    ,   quarter_name           char(2)
    ,   month                  tinyint
    ,   month_name             varchar(10)
    ,   month_abbreviation     varchar(10)
    ,   week                   char(2)
    ,   day_of_month           tinyint
    ,   day_of_year            smallint
    ,   day_of_week            smallint
    ,   day_name               varchar(10)
    ,   day_abbreviation       varchar(10)
    ,   is_weekend             tinyint
    ,   is_weekday             tinyint
    ,   is_today               tinyint
    ,   is_yesterday           tinyint
    ,   is_this_week           tinyint
    ,   is_last_week           tinyint
    ,   is_this_month          tinyint
    ,   is_last_month          tinyint
    ,   is_this_year           tinyint
    ,   is_last_year           tinyint
    );
    WHILE v_date < p_to_date DO
        SET v_month := month(v_date);
        INSERT INTO dim_date(
            date_key
        ,   date_value
        ,   date_iso
        ,   year
        ,   quarter
        ,   quarter_name
        ,   month
        ,   month_name
        ,   month_abbreviation
        ,   week
        ,   day_of_month
        ,   day_of_year
        ,   day_of_week
        ,   day_name
        ,   day_abbreviation
        ,   is_weekend
        ,   is_weekday
        ) VALUES (
            v_date + 0
        ,   v_date
        ,   DATE_FORMAT(v_date, '%y-%c-%d')
        ,   year(v_date)
        ,   ((v_month - 1) DIV 3) + 1
        ,   CONCAT('Q', ((v_month - 1) DIV 3) + 1)
        ,   v_month
        ,   DATE_FORMAT(v_date, '%M')
        ,   DATE_FORMAT(v_date, '%b')
        ,   DATE_FORMAT(v_date, '%u')
        ,   DATE_FORMAT(v_date, '%d')
        ,   DATE_FORMAT(v_date, '%j')
        ,   DATE_FORMAT(v_date, '%w') + 1
        ,   DATE_FORMAT(v_date, '%W')
        ,   DATE_FORMAT(v_date, '%a')
        ,   IF(DATE_FORMAT(v_date, '%w') IN (0,6), 1, 0)
        ,   IF(DATE_FORMAT(v_date, '%w') IN (0,6), 0, 1)
        );
        SET v_date := v_date + INTERVAL 1 DAY;
    END WHILE;
    CALL p_update_dim_date();
END;
//

DROP PROCEDURE IF EXISTS p_update_dim_date;
//

CREATE PROCEDURE p_update_dim_date()
    UPDATE dim_date
    SET    is_today         = IF(date_value = current_date, 1, 0)
    ,      is_yesterday     = IF(date_value = current_date - INTERVAL 1 DAY, 1, 0)
    ,      is_this_week     = IF(year = year(current_date) AND week = DATE_FORMAT(current_date, '%u'), 1, 0)
    ,      is_last_week     = IF(year = year(current_date - INTERVAL 7 DAY) AND week = DATE_FORMAT(current_date - INTERVAL 7 DAY, '%u'), 1, 0)
    ,      is_this_month    = IF(year = year(current_date) AND month = month(current_date), 1, 0)
    ,      is_last_month    = IF(year = year(current_date - INTERVAL 1 MONTH) AND month = month(current_date - INTERVAL 1 MONTH), 1, 0)
    ,      is_this_year     = IF(year = year(current_date), 1, 0)
    ,      is_last_year     = IF(year = year(current_date - INTERVAL 1 YEAR), 1, 0)
    WHERE  is_today
    OR     is_yesterday
    OR     is_this_week
    OR     is_last_week
    OR     is_this_month
    OR     is_last_month
    OR     is_this_year
    OR     is_last_year
    OR     IF(date_value = current_date, 1, 0)
    OR     IF(date_value = current_date - INTERVAL 1 DAY, 1, 0)
    OR     IF(year = year(current_date) AND week = DATE_FORMAT(current_date, '%u'), 1, 0)
    OR     IF(year = year(current_date - INTERVAL 7 DAY) AND week = DATE_FORMAT(current_date - INTERVAL 7 DAY, '%u'), 1, 0)
    OR     IF(year = year(current_date) AND month = month(current_date), 1, 0)
    OR     IF(year = year(current_date - INTERVAL 1 MONTH) AND month = month(current_date - INTERVAL 1 MONTH), 1, 0)
    OR     IF(year = year(current_date), 1, 0)
    OR     IF(year = year(current_date - INTERVAL 1 YEAR), 1, 0)
    ;
//

delimiter ;

使用 p_load_dim_date，您最初会加载包含 25 年数据的 dim_date 表。每天，最好在午夜前后运行 p_update_dim_date。然后可以使用标志字段 is_today、is_yesterday、is_this_week、is_last_week 等来选择常用范围。当然，您应该修改此代码以满足您的特定需求，但这就是想法。因此，无需即时生成范围，您只需提前预加载足够长的时间。对于当天的时间，可以设置类似的设计 - 您应该能够通过此代码自行管理。

对于处理假期的更精美的日期维度，以及月份和日期的本地化名称，您可以查看: http://rpbouman.blogspot.com/2007/04/kettle-tip-using-java-locales-for-date.html和 http://rpbouman.blogspot.com/2010/01/easter-eggs-for-mysql-and-kettle.html