Mysql: группировка по диапазонам дат

Скажем, у нас есть таблица Products со столбцами id, title, status, expires_at, created_at.

Я могу получить значения за одну неделю, указав все параметры:

SELECT
    count(id)
FROM 
    products
WHERE
    status = 'APPROVED' AND
    expires_at >= '2018-09-10 09:00:00' AND
    created_at <= '2018-09-10 09:00:00' AND
    deleted_at IS NULL;

Как мы могли бы запустить запрос, который группирует продукты по неделям года 1-52 - ГДЕ каждую неделю показывает продукты, срок действия которых не истек: created_at <= week-end AND expires_at <= week-end или активный:

Active_Products | Week_Of_Year 
----------------|---------------
274             | 2018-01
----------------|---------------
1011            | 2018-02
----------------|---------------
180             | 2018-03
----------------|---------------
990             | 2018-04
----------------|---------------
765             | 2018-05

Я обновил этот вопрос с помощью скрипта SQL: http://sqlfiddle.com/#!9/e4830b/4

Возможный дубликат Как сгруппировать по неделям в MySQL?

Goodbye StackExchange 10.09.2018 09:46

Не могли бы вы предоставить образцы данных и ожидать результата?

D-Shih 10.09.2018 09:50

Это не дубликат, если вы потратите время, чтобы прочитать полную спецификацию вопроса.

AndrewMcLagan 10.09.2018 14:32
0
3
75
3
Перейти к ответу Данный вопрос помечен как решенный

Ответы 3

Ответ принят как подходящий

Попробуйте запрос ниже, вычислив дату начала и дату окончания недели

select week(expires_at),count(id) as active_products
from product
where expires_at>SUBDATE(expires_at, weekday(expires_at)) and expires_at<DATE(expires_at + INTERVAL (6 - WEEKDAY(expires_at)) DAY)
group by week(expires_at)

Вы можете использовать функцию YEARWEEK(date) (https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_yearweek), чтобы показать номер недели в читаемом виде включая год (yyyymm).

SELECT 
count(id) as active_products, YEARWEEK(expires_at)
FROM products
GROUP BY YEARWEEK(expires_at)

Использование WEEK() не повлияет на разницу между годами одной и той же недели.

Я считаю, что логика, которую вы хотите использовать для активных продуктов на данной неделе, больше похожа на *created_at* >= week-start AND expires_at < week-end.

Если у вас есть продукты, созданные в течение всех недель, вы можете использовать коррелированный подзапрос:

select week_of_year,
       (select count(*)
        from products t2
        where yearweek(t2.created_at) >= w.week_of_year and
              yearweek(t2.expires_at) < w.week_of_year
       ) as num_actives
from (select distinct yearweek(created_at) as week_of_year
      from products
     ) w;

Я думаю, это действительно очень близко. Хотя сложно понять, что это за разные имена таблиц? где исходная таблица Products вписывается в этот запрос?

AndrewMcLagan 10.09.2018 14:35

@AndrewMcLagan. . . t - это сокращение, которое я использую для общего имени таблицы. Теперь, когда вы указали таблицу, я обновил ответ.

Gordon Linoff 10.09.2018 14:40

Не удалось заставить этот запрос работать. Я создал SQLFiddle sqlfiddle.com/#!9/e4830b/4

AndrewMcLagan 10.09.2018 15:59

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