Выборка диапазона дат с отсутствующими датами

Тема в разделе "Базы данных", создана пользователем verfaa, 26 мар 2014.

Модераторы: latteo
  1. verfaa

    verfaa

    Регистр.:
    29 янв 2007
    Сообщения:
    373
    Симпатии:
    41
    Есть БД MySQL, в ней таблица, содержащая данные примерно следующего вида:

    2009-06-25 75
    2009-07-01 100
    2009-07-02 120

    Т.е. определенная дата будет отсутствовать, если для неё отсутствуют данные.
    Мне нужно выбрать данные за определенный промежуток времени и я задаю в запросе условие:
    ... AND date >= '2009-06-25' AND date =< '2009-07-02'
    Но он не работает. А мне нужно получить в результате данные вида:
    2009-06-25 75
    2009-06-26 0
    2009-06-27 0
    2009-06-28 0
    2009-06-29 0
    2009-06-30 0
    2009-07-01 100
    2009-07-02 120

    Т.е. там где нет данных подставить 0 и получить дату
     
  2. verfaa

    verfaa

    Регистр.:
    29 янв 2007
    Сообщения:
    373
    Симпатии:
    41
    сгенерировал таблицу-календарь, как советовали на другом форуме вида:
    [​IMG]
    с данными от 1970 до 2084 года.

    Но как теперь выбрать данные из таблицы вида (это небольшая партнерская программа:(:
    [​IMG]
    не могу понять..

    Допустим пользователь задал интервал для выборки своих данных: начальная дата 2013-06-18 и конечная дата 2013-06-23

    Как в этом случае будет выглядеть правильно запрос? Я пытался составить, но не получается - выдает ошибку
     
  3. verfaa

    verfaa

    Регистр.:
    29 янв 2007
    Сообщения:
    373
    Симпатии:
    41
    И ещё, когда на другом форуме советовали создать таблицу календарь, я использовал такой запрос:

    create or replace view v3 as select 1 n union all select 1 union all select 1;
    create or replace view v as select 1 n from v3 a, v3 b union all select 1;
    set @n = 0;
    drop table if exists calendar;
    create table calendar(dt datetime primary key);
    insert into calendar
    select cast('1970-1-1 00:00:00' + interval @n:=@n+1 hour as datetime) as dt
    from v a, v b, v c, v d, v e, v;


    Но он генерирует даты с временем с интервалом в 1 час. А мне я так понимаю нужно просто сгенерировать даты без времени просто даты. Как переписать запрос в этом случае?
     
  4. Bezhev

    Bezhev

    Регистр.:
    26 дек 2012
    Сообщения:
    362
    Симпатии:
    123
    запрос должен быть такого вида:
    Код:
    SELECT * FROM news
      WHERE created_at BETWEEN STR_TO_DATE('2008-08-14 00:00:00', '%Y-%m-%d %H:%i:%s')
      AND STR_TO_DATE('2008-08-23 23:59:59', '%Y-%m-%d %H:%i:%s');
    т.е. нужно привести строку к дате.
    Должно заработать - 99%
     
  5. verfaa

    verfaa

    Регистр.:
    29 янв 2007
    Сообщения:
    373
    Симпатии:
    41
    Сделал иначе - Сгенерировал календарь запросом:

    Код:
    create or replace view v3 as select 1 n union all select 1 union all select 1;
    create or replace view v as select 1 n from v3 a, v3 b union all select 1;
    set @n = 0;
    drop table if exists calendar;
    create table calendar(dt date primary key);
    insert into calendar
    select cast('1970-1-1' + interval @n:=@n+1 day as date) as dt
    from v a, v b, v c, v d, v e, v;
    Затем сделал выборку дат, по которым мне нужно выбрать данные
    Код:
    SELECT dt FROM calendar
                            WHERE dt >= '".$from_date."' AND dt <= '".$current_date."'
                            ORDER BY dt DESC
    И по полученным результирующим датам прохожусь циклом и для каждой даты выбираю необходимые данные. Все работает
     
    latteo нравится это.
  6. latteo

    latteo Эффективное использование PHP, MySQL

    Moderator
    Регистр.:
    28 фев 2008
    Сообщения:
    1.405
    Симпатии:
    1.185
    :facepalm: Для этого не надо было делать календарь - можно было сделать генерацию списка дат и на php (или любом другом) - что тоже крайне не производительно.
    Лучше недостающие даты просто добавлять на PHP в результат выборки.

    Но если всё же хочется сделать на базе SQL и с дополнительной табличкой calendar, то для такого случая в MySQL есть RIGHT или LEFT JOIN, пример:

    Код:
    #Было:
    # один раз
    SELECT dt FROM calendar
      WHERE dt >= '2013-11-06' AND dt <= '2013-11-12'
      ORDER BY dt DESC
    #(много раз)
    SELECT o.date as dt, o.uid FROM `date_uin` as o
    WHERE date = '***'
      ORDER BY dt DESC
    
    #Стало:
    SELECT IFNULL(o.date,c.dt) as dt, o.uid 
    FROM `date_uin` as o
    RIGHT JOIN calendar c ON o.date = c.dt
    WHERE c.dt >= '2013-11-06' AND c.dt <= '2013-11-12'
      ORDER BY dt DESC
    
    итого: один запрос вместо десятков.
     
    Последнее редактирование: 28 мар 2014
    verfaa нравится это.