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 раз.

Итак, столбец даты не хранит стандартный формат даты? Я имею в виду, что тип данных столбца не DATE или DATETIME?

FanoFN 21.12.2020 01:37
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для
В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...
Как построить CRUD-приложение в Laravel
Как построить CRUD-приложение в Laravel
Laravel - это популярный PHP-фреймворк, который позволяет быстро и легко создавать веб-приложения. Одной из наиболее распространенных задач в...
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
Освоение PHP и управление базами данных: Создание собственной СУБД - часть II
В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...
Документирование API с помощью Swagger на Springboot
Документирование API с помощью Swagger на Springboot
В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .
Роли и разрешения пользователей без пакета Laravel 9
Роли и разрешения пользователей без пакета Laravel 9
Этот пост изначально был опубликован на techsolutionstuff.com .
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM
В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.
1
1
930
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 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.

На вопрос, Сделал скрипку с кучей тестов.

Другие вопросы по теме