在MySQL中创建日期范围

在MySQL中没有直接的方法可以做到这一点。您最好的选择是用您选择的服务器端语言生成一个日期范围数组,然后从数据库中提取数据,并将结果数组与使用日期作为键的日期范围数组合并。

您使用的是哪种服务器端语言?

编辑:

基本上你要做的是(伪代码):

// Create an array with all dates for a given range
dates = makeRange(startDate, endDate); 

getData = mysqlQuery('SELECT date, x, y, z FROM a WHERE a AND b AND c');

while (r = fetchRowArray(getData)) {

  dates[ date(r['date']) ] = Array ( x, y, z);

}

最后,您会得到一个可以循环使用的日期数组,其中包含或不包含与之关联的活动数据的日期。

可以方便地修改以按小时分组/筛选数据。

我的建议是:不要让你的生活更艰难,让它更容易。只需为每个日历日创建一个包含一行的表,其中包含您认为合理需要持续的行数。在数据仓库中,这是一个常见的解决方案,它被如此广泛地实现,以至于没有它的DWH具有代码味道。

许多习惯于处理更传统的OLTP/数据输入应用程序的人对这个想法感到自然的反感,因为这种感觉无论如何都可以生成数据,因此不应该存储它。但是,如果您确实创建了这样的表,那么您可以用许多有用的属性来修饰它,例如它是holdiday还是weekend,并且您可以在其中存储许多常见的日期表示(iso、european、us格式等),这可以在创建报表时节省大量时间(因为您不必费心弄清楚日期格式如何设置)在您所使用的每个报告工具中工作。或者,您可以更进一步,每天更新日期表,以标记当前日期、当前周、当前月、当前年等的标志-各种有用的工具,使构建需要针对某个日期范围工作的报告变得更容易、更容易。

注释中请求的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 你真的加载了 dim_date 表中有25年的数据。每天,你最好在午夜左右跑步 p_update_dim_date . 然后可以使用标记字段“今天”、“昨天”、“这周”、“最后一周”等来选择公共范围。当然,您应该修改此代码以满足您的特定需求,但这是您的想法。因此,没有通用范围的飞行,你只是预载足够长的时间。在一天中的某个时候,可以设置一个类似的设计——您应该能够通过这个代码自己管理这个设计。

对于处理假日的更复杂的日期维度,以及月份和日期的本地化名称,您可以查看: 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

我最近做了一些研究来寻找和评估可能的选择。 http://www.freeportmetrics.com/devblog/2012/11/02/how-to-quickly-add-date-dimension-to-pentaho-mondrian-olap-cube/ .

你可以使用:

• 水壶
• 退化维数
• Lucidb内置功能
• 即将推出的蒙德里安内置功能
• 要生成SQL的自定义脚本
• 前面提到的mysql脚本

有关详细信息,请查看日志。它还包含Roland的SQL脚本的改进版本,该脚本将自动计算给定列的日期范围,并将其与日期维度联接。

尝试在MySQL存储的例程中使用循环来创建日期范围:

   declare iterDate date;
   set iterDate = startDate;

   DROP TABLE IF EXISTS MyDates;
   create temporary table MyDates (
      theDate date
   );

   label1: LOOP
     insert into MyDates(theDate) values (iterDate); 
     SET iterDate = DATE_ADD(iterDate, INTERVAL 1 DAY);
     IF iterDate <= endDate THEN
        ITERATE label1;
     END IF;
     LEAVE label1;
   END LOOP label1;

   select * from MyDates;
   DROP TABLE IF EXISTS MyDates;

startDate 和 endDate 构成范围的端点,并作为例程的参数提供。