Разбить записи на периоды

Тема в разделе "Базы данных", создана пользователем Q_BASIC, 16 ноя 2017.

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

    Q_BASIC

    Регистр.:
    30 ноя 2013
    Сообщения:
    452
    Симпатии:
    1.159
    Приветствую,

    Есть таблица со столбцами id и time. time типа INT(11), где хранится unix time. Надо для графика разбить записи по дням(за последние 30 дней) и по месяцам (последние 12). Сколько было записей вчера, сколько позавчера...

    Как это лучше сделать?

    Что-то типа такого надо: http://prntscr.com/hb9den (js сам сделаю, надо записи получить)
     
    Последнее редактирование: 16 ноя 2017
    dana77 нравится это.
  2. alex_me

    alex_me

    Регистр.:
    25 янв 2017
    Сообщения:
    161
    Симпатии:
    109
    так ?

    WHERE DATE(my_date) <= CURDATE() + 30
    GROUP BY DATE(my_date)

    ps
    или так возможно

    GROUP BY DATE(FROM_UNIXTIME(my_date))
     
    Q_BASIC нравится это.
  3. Q_BASIC

    Q_BASIC

    Регистр.:
    30 ноя 2013
    Сообщения:
    452
    Симпатии:
    1.159
    Код:
    SELECT COUNT(*) as `count`, FROM_UNIXTIME(`created`, '%e.%c.%y') as `date` FROM `accounts` WHERE `created` > 1 GROUP BY `date`
    В where через php поставлю time()-2592000. Запрос нормальный, не тяжелый будет?
     
    dana77 нравится это.
  4. Black Hat

    Black Hat

    Регистр.:
    15 май 2015
    Сообщения:
    163
    Симпатии:
    104
    А сами как думаете?
    Код:
    mysql> explain extended SELECT COUNT(*) as `count`, FROM_UNIXTIME(`created`, '%e.%c.%y') as `date` FROM `accounts` WHERE `created` > 1 GROUP BY `date`;
    +----+-------------+----------+------+---------------+------+---------+------+------+----------+----------------------------------------------+
    | id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                        |
    +----+-------------+----------+------+---------------+------+---------+------+------+----------+----------------------------------------------+
    |  1 | SIMPLE      | accounts | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where; Using temporary; Using filesort |
    +----+-------------+----------+------+---------------+------+---------+------+------+----------+----------------------------------------------+
    
    Тут полный набор всего того плохого, что может случиться.

    1. нет индекса (так как вы не предоставили DDL, полагаю, что так и есть)
    2. WHERE `created` > 1 - это значит полный проход таблицы (полагаю что записей с датой 0 и 1 нет или мало)
    3. над каждой строкой будет выполнена функция FROM_UNIXTIME (даже если бы были индексы, они не помогут)
    4. используется временная таблица
    5. эта временная таблица сортируется, снова без индексов

    как я поступаю в таких случаях:

    1. какого размера таблица? несколько тысяч - сотен тысяч? - забейте на оптимизацию, такое количество строк мускул пережует не подавится
    2. как можно использовать индекс? два способа
    - денормализация, добавим еще одну колонку, где уже будет значение типа DATE, добавить индекс по нему, актуальность можно поддерживать триггерами, выборка по такому столбцу будет быстрой. если интересно, покажу как сделать.
    - переделать запрос, он будет монстром, но работать довольно быстро:

    1) делаем индекс на `created`
    2) вот такой будет запрос для "Надо для графика разбить записи по дням(за последние 30 дней)"

    Код:
    (SELECT COUNT(*) as `count`, '2017-11-17' AS `date` FROM `accounts` WHERE `created` BETWEEN 1510866000 AND 1510952399)
    UNION ALL
    (SELECT COUNT(*) as `count`, '2017-11-16' AS `date` FROM `accounts` WHERE `created` BETWEEN 1510779600 AND 1510865999)
    ... продолжаем для остальных дней
    
    Вот так выглядит до создания индекса:
    Код:
    mysql> explain extended
        -> (SELECT COUNT(*) as `count`, '2017-11-17' AS `date` FROM `accounts` WHERE `created` BETWEEN 1510866000 AND 1510952399)
        -> UNION ALL
        -> (SELECT COUNT(*) as `count`, '2017-11-16' AS `date` FROM `accounts` WHERE `created` BETWEEN 1510779600 AND 1510865999);
    +----+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
    | id | select_type  | table      | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
    +----+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
    |  1 | PRIMARY      | accounts   | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where |
    |  2 | UNION        | accounts   | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using where |
    | NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL |             |
    +----+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
    
    Уже нет временной таблицы и сортировки.

    Добавим индекс:

    Код:
    ALTER TABLE `accounts` ADD KEY(`created`);
    mysql> explain extended
        -> (SELECT COUNT(*) as `count`, '2017-11-17' AS `date` FROM `accounts` WHERE `created` BETWEEN 1510866000 AND 1510952399)
        -> UNION ALL
        -> (SELECT COUNT(*) as `count`, '2017-11-16' AS `date` FROM `accounts` WHERE `created` BETWEEN 1510779600 AND 1510865999);
    +----+--------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
    | id | select_type  | table      | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
    +----+--------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
    |  1 | PRIMARY      | accounts   | index | created       | created | 4       | NULL |    2 |    50.00 | Using where; Using index |
    |  2 | UNION        | accounts   | index | created       | created | 4       | NULL |    2 |    50.00 | Using where; Using index |
    | NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL    | NULL    | NULL | NULL |     NULL |                          |
    +----+--------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
    
    Уже используется индекс.
     
    alex_me нравится это.