代码之家  ›  专栏  ›  技术社区  ›  JR Lawhorne

SQL查询与日期范围中的日期匹配的记录数?

  •  2
  • JR Lawhorne  · 技术社区  · 15 年前

    CREATE TABLE sample (
      ix int unsigned auto_increment primary key,
      start_active datetime,
      last_active datetime
    );
    

    我使用的是MySQL,查询将从PHP运行,但我并不真正需要PHP代码,只需要查询。

    这是我的开始:

    SELECT COUNT(1) cnt, DATE(?each of last 30 days?) adate
    FROM sample
    WHERE adate BETWEEN start_active AND last_active
    GROUP BY adate;
    
    3 回复  |  直到 15 年前
        1
  •  5
  •   tpdi    15 年前

    没有桌子吗?做一张桌子。为了这个,我总是在附近放一张假桌子。

    create table artificial_range( 
      id int not null primary key auto_increment, 
      name varchar( 20 ) null ) ;
    
    -- or whatever your database requires for an auto increment column
    
    insert into artificial_range( name ) values ( null )
    -- create one row.
    
    insert into artificial_range( name ) select name from artificial_range;
    -- you now have two rows
    
    insert into artificial_range( name ) select name from artificial_range;
    -- you now have four rows
    
    insert into artificial_range( name ) select name from artificial_range;
    -- you now have eight rows
    
    --etc.
    
    insert into artificial_range( name ) select name from artificial_range;
    -- you now have 1024 rows, with ids 1-1024
    

    现在,使其便于使用,并将其限制在30天内,以期:

    编辑:JR劳霍恩注释:

    您需要将“date\u add”更改为“date\u sub”,以获取已创建视图中的前30天。

    谢谢JR!

    create view each_of_the_last_30_days as
    select date_sub( now(), interval (id - 1) day ) as adate
    from artificial_range where id < 32;
    

    现在在查询中使用它(我还没有实际测试过) 查询,我只是假设它工作正常):

    编辑:我应该以另一种方式加入:

    SELECT COUNT(*) cnt, b.adate
    FROM  each_of_the_last_30_days b
    left outer join sample a 
     on ( b.adate BETWEEN a.start_active AND a.last_active)
    GROUP BY b.adate;
    
        2
  •  0
  •   Bill Karwin    15 年前

    SQL在匹配存储在数据库中的值集方面非常出色,但在匹配 不是 在数据库中。因此,一个简单的解决方法是创建一个包含所需值的临时表:

    CREATE TEMPORARY TABLE days_ago (d SMALLINT);
    INSERT INTO days_ago (d) VALUES
      (0), (1), (2), ... (29), (30);
    

    现在,您可以比较 d 几天前到 start_active last_active 每行的。计算每个值的组中有多少匹配行 D 还有你的计数。

    SELECT CURRENT_DATE - d DAYS, COUNT(*) cnt, 
    FROM days_ago
     LEFT JOIN sample ON (CURRENT_DATE - d DAYS BETWEEN start_active AND last_active)
    GROUP BY d
    ORDER BY d DESC; -- oldest to newest
    

    GROUP BY 条款实际上,在标准SQL中,只有在 ORDER BY 子句,但MySQL支持在中使用别名 分组 HAVING 条款也是如此。

        3
  •  0
  •   James Black    15 年前

    在查询中,将日期转换为unix时间戳,即秒,然后只需查找差异为<=一个月内的秒数。

    您可以在此处找到更多信息: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_unix-timestamp

    [编辑]由于我对真正的问题感到困惑,我需要完成草坪,但在我忘记之前,我想把这个写下来。

    要按天计算数字,您需要将where子句设置为如上所述,限制为过去30天,但您需要按天分组,因此通过将每个开始转换为月份的某一天来进行选择,然后进行计数。

    这假设每次使用将限制在一天内,如果开始和结束日期可以跨越几天,那么它将更加棘手。