Борется с SQL-запросом клиента

#mysql #sql #datetime #aggregate-functions #greatest-n-per-group

#mysql #sql #дата и время #агрегатные функции #наибольшее число пользователей на группу

Вопрос:

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

между датой их последнего заказа и предпоследней датой заказа должен быть интервал 365 дней
заказан в 2020 году

Я борюсь с двумя частями. Честно говоря, я просто борюсь со всем этим.

Как получить предпоследнюю дату заказа для определенных клиентов, а затем использовать этот день для вычисления интервала
Если мне нужно создать таблицу клиентов или выполнить 2 отдельных запроса

Пока что у меня правильный интервал, но он берет максимум из всего столбца, а не от клиента.

 select datediff(day, select max(ship_date) where ship_date < select max(ship_date) :: date, select max(ship_date) :: date)

Мне нужно было бы подсчитать, где у клиентов интервалы> 365
и где (last_ship_date) или max (ship_date)> 1 января 2020 года

Ответ №1:

Вы можете использовать оконные функции:

 select customer_id
from (
    select t.*, 
        row_number() over(partition by customer_id order by ship_date desc) rn
    from mytable t
) t
where rn <= 2
group by customer_id
having max(ship_date) >= '2020-01-01' 
   and max(ship_date) > min(ship_date)   interval 1 year

Идея состоит в том, чтобы фильтровать по двум последним строкам для каждого клиента. Затем вы можете сравнить даты с помощью агрегирования.

1. Это похоже на то, что у меня было, но нужен способ выбрать первый заказ в 2020 году по сравнению с максимальным. Клиенты с несколькими заказами в 2020 году не проходят, потому что предпоследний заказ будет также в 2020 году и не будет соответствовать установленным нами параметрам.

Ответ №2:

Я бы пошел на lag() :

 select customer_id
from (select t.*, 
             lag(ship_date) over (partition by customer_id order by ship_date) as prev_ship_date
             row_number() over (partition by customer_id order by ship_date desc) as seqnum
       from t
      ) t
where seqnum = 1 and
      ship_date >= '2020-01-01' and
      prev_ship_date < ship_date - interval 1 year