Как посчитать количество записей за определённый период?

Question

[Андрей]

Имеется таблица: actions
id | user_id | type | created_at

В поле type может хранится одно из следующих значений add или remove. Необходимо сделать выборку за определённый интервал (7 дней), с выводом количества add и remove.
Вывод должен быть таким:
2016-09-09 | 10 | 15
2016-09-08 | 23 | 45

Где:
1. Дата
2. Количество записей у которых type = add и created_at равен дате.
3. Количество записей у которых type = remove и created_at равен дате.

Второй вопрос, какой лучше выбрать тип для поля created_at и какие необходимо указать ключи? На всём проекте стоит int(11). Количество записей в таблице может быть 1 000 - 10 000 000.

Сейчас у поля created_at стоит int(11) и составлен такой запрос, который при тысячи записей занимает около 4-5 сек из-за преобразования даты: DATE(FROM_UNIXTIME(`created_at`)) = `data`

SELECT
          	    DATE(FROM_UNIXTIME(`created_at`)) AS `data`,
                (
                    SELECT
        	            COUNT(`id`)
                    FROM
        	            `actions`
                    WHERE
        	            `user_id` = 1 AND
        	            `type` = 'add' AND
        	            DATE(FROM_UNIXTIME(`created_at`)) = `data`
                ) AS `add`,
                (
                    SELECT
        	            COUNT(`id`)
                    FROM
        	            `actions`
                    WHERE
	                    `user_id` = 1 AND
        	            `type` = 'remove' AND
        	            DATE(FROM_UNIXTIME(`created_at`)) = `data`
                )  AS `remove`
            FROM
	            `actions`
            WHERE
	          `created_at` > UNIX_TIMESTAMP(DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY))
            GROUP BY `data`

Answer 1

[Melkij]

select date(`created_at`) as create_date, sum(if(type = 'new', 1,0)) as new_count, sum(if(type = remove, 1,0)) as rm_count
from actions 
where user_id = 1 and created_at > DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY)
group by create_date

Ну а если надо часто такую аггрегацию делать - сделайте таблицу с предварительно агрегированными по дням данными.

Answer 2

[Abdula Magomedov]

1. Под запросы лишние. GROUP BY для того и нужен, чтоб группировать записи для функция типа COUNT.

SELECT
  DATE(FROM_UNIXTIME(`created_at`))     AS "data",
  COUNT(IF(`type` = "add", 1, NULL))    AS "add",
  COUNT(IF(`type` = "remove", 1, NULL)) AS "remove"
FROM
  `actions`
WHERE
  `created_at` > UNIX_TIMESTAMP(DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY))
GROUP BY data

условие выборки сами подберете, какой вам период нужен

Comments

[Андрей]

Как посчитать количество именно за тот день, по которому идёт выборка?

[Abdula Magomedov]

Андрей: щас SQL напишу

Answer 3

[idShura]

Написал за две минуты поэтому мог наделать ошибок...

Немного исправил твой запрос:

SELECT DATA,
       SUM(CASE WHEN USER_ID = 1 AND TYPE = 'add' 
            THEN 1 ELSE 0 END) AS ADD,
       SUM(CASE WHEN USER_ID = 1 AND TYPE = 'remove'  
            THEN 1 ELSE 0 END) AS REMOVE
  FROM ACTIONS
 WHERE CREATED_AT > UNIX_TIMESTAMP(DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY))
 GROUP BY DATA