SQL, как подсчитывать различные идентификаторы в изменяющихся временных диапазонах
Я хочу подсчитать определенное количество fd_id за время между сегодняшним и вчерашним днем, между сегодняшним днем и 3 днями назад, между сегодняшним днем и 5 днями назад, между сегодняшним днем и 7 днями назад, между сегодняшним днем и 15 днями назад, между сегодняшним днем и 30 днями назад назад.
Моя таблица данных выглядит следующим образом:
user_id. fd_id. date
1. 123a. 20201010
1. 123a. 20201011
1. 124a. 20201011
...
И желаемый результат имеет следующий формат:
user_id count_fd_id_1d count_fd_id_3d ... count_fd_id_30d
В частности, я знаю, что могу сделать следующие 6 раз и соединить их вместе (некоторый метод привязки столбцов):
select user_id, count(distinct fd_id) as count_fd_id_1d
from table
where date <= today and date >= today-1 (#change this part for different dates)
select user_id, count(distinct fd_id) as count_fd_id_3d
from table
where date <= today and date >= today-3 (#change this part for different dates)
...
Мне интересно, как я могу сделать это за один раз, не запуская почти идентичный код 6 раз.
Ответы 2
Вы можете использовать условную агрегацию:
select user_id,
count(distinct case when date >= current_date - 1 day and date < current_date then fd_id end) as cnt_1d,
count(distinct case when date >= current_date - 3 day and date < current_date then fd_id end) as cnt_3d,
...
from mytable
goup by user_id
Вы можете поиграть с выражениями даты, чтобы установить нужные диапазоны. Вышеупомянутое работает для целых дней и не включает текущий день.
Если столбец date в таблице действительно выглядит так (не в формате даты/даты и времени), я думаю, вам нужно использовать STR_TO_DATE() для преобразования его в формат даты, а затем использовать DATEDIFF для проверки различий дат. Рассмотрим этот пример запроса:
SELECT user_id,
MAX(CASE WHEN ddiff=1 THEN cn END) AS count_fd_id_1d,
MAX(CASE WHEN ddiff=2 THEN cn END) AS count_fd_id_2d,
MAX(CASE WHEN ddiff=3 THEN cn END) AS count_fd_id_3d,
MAX(CASE WHEN ddiff=4 THEN cn END) AS count_fd_id_4d,
MAX(CASE WHEN ddiff=5 THEN cn END) AS count_fd_id_5d
FROM (SELECT user_id,
DATEDIFF(CURDATE(), STR_TO_DATE(DATE,'%Y%m%d')) ddiff,
COUNT(DISTINCT fd_id) cn
FROM mytable
GROUP BY user_id, ddiff) A
GROUP BY user_id;
На данный момент, если вы проверяете значение даты просто с помощью прямого вычитания, вы получите неверный результат. Например:
*your current date value - how many days:
'20201220' - 30 = '20201190' <-- this is not correct.
*if you convert the date value and using the same subtraction:
STR_TO_DATE('20201220','%Y%m%d') - 30 = '20201190' <-- still get incorrect.
*convert date value then uses INTERVAL for the date subtraction:
STR_TO_DATE('20201220','%Y%m%d') - INTERVAL 30 DAY = '2020-11-20'
OR
DATE_SUB(STR_TO_DATE('20201220','%Y%m%d'),INTERVAL 30 DAY) = '2020-11-20'
*IF your date column is storing standard date format value, then omit STR_TO_DATE
'2020-12-20' - INTERVAL 30 DAY = '2020-11-20'
OR
DATE_SUB('2020-12-20',INTERVAL 30 DAY) = '2020-11-20'
Узнайте больше об манипулировании датами в MySQL.
На вопрос, Сделал скрипку с кучей тестов.
Итак, столбец даты не хранит стандартный формат даты? Я имею в виду, что тип данных столбца не DATE или DATETIME?