Блог

У меня есть таблица mysql, в которой хранятся все заказы клиентов следующим образом (упрощенно для вопроса):

  ---------- --------------- ------ 
| Field    | Type          | Null | 
 ---------- --------------- ------ 
| id (pk)  | int(10)       | NO   |
| cust_id  | int(10)       | NO   |
| total    | decimal(10,2) | NO   |
| created  | datetime)     | NO   |
 ---------- --------------- ------ 
  

В одном запросе я хочу получить первый заказ каждого пользователя и общее количество заказов, а также их самый последний заказ и общее количество заказов

Так что у меня должны быть такие результаты, как:

  ---------- ------------------ --------------- ------------------ --------------- 
| cust_id  | first_ord_total  | first_ord_date| last_ord_total   | last_ord_date |
 ---------- ------------------ --------------- ------------------ --------------- 
| 123      | 150.48           | 2018-03-01    | 742.25           | 2020-05-19    |
| 456      | 20.99            | 2019-08-01    | 67.22            | 2020-09-17    | 
| 789      | 259.99           | 2019-01-01    | 147.15           | 2020-08-31    | 
 ---------- ------------------ --------------- ------------------ --------------- 
  

Кажется, я могу получить даты первого и последнего заказа, используя MIN и MAX, но я не могу связать их обратно, чтобы также указать общую сумму заказа из того же заказа / записи

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

Если ваша версия MySQL поддерживает оконные функции, с помощью MIN(), MAX() и FIRST_VALUE():

 select distinct cust_id,
       first_value(total) over (partition by cust_id order by created) first_order_total,
       min(created) over (partition by cust_id) first_order_date,
       first_value(total) over (partition by cust_id order by created desc) last_order_total,
       max(created) over (partition by cust_id) last_order_date
from customers
  

Без оконных функций используйте запрос, который повторно включает даты первого и последнего заказа каждого клиента, и присоедините его к таблице, в которой вы используете условную агрегацию:

 select c.cust_id,
       max(case when c.created = t.min_created then c.total end) first_order_total,
       max(case when c.created = t.min_created then c.created end) first_order_date,
       max(case when c.created = t.max_created then c.total end) last_order_total,
       max(case when c.created = t.max_created then c.created end) last_order_date
from customers c 
inner join (
  select cust_id, min(created) min_created, max(created) max_created
  from customers
  group by cust_id  
) t on t.cust_id = c.cust_id and c.created in (t.min_created, t.max_created)
group by c.cust_id
  

В MySQL 8 ROW_NUMBER здесь пригодится:

 WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY created) rn_first,
              ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY created DESC) rn_last
    FROM orders
)

SELECT
    cust_id,
    MAX(CASE WHEN rn_first = 1 THEN total END)   AS first_ord_total,
    MAX(CASE WHEN rn_first = 1 THEN created END) AS first_ord_date,
    MAX(CASE WHEN rn_last = 1  THEN total END)   AS last_ord_total,
    MAX(CASE WHEN rn_last = 1  THEN created END) AS last_ord_date
FROM cte
GROUP BY
    cust_id;
  

Здесь используется stategy ROW_NUMBER для каждого клиента для идентификации первой и последней записей транзакций в CTE. Затем мы агрегируем по клиентам, чтобы найти первые и последние общие суммы и даты.