Получить последнюю дату заказа после агрегирования моих строк

Question

У меня есть 2 таблицы:

• Customers стол (который имеет customer_id колонка)
• Orders стол (который имеет customer_id а также order_date колонны)

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

У меня уже есть это:

SELECT C.customer_id, O.order_date, COUNT(O.order_id) AS orders_count 
FROM customers AS C
LEFT JOIN orders AS O ON O.customer_id = C.customer_id
GROUP BY C.customer_id
ORDER BY COUNT(O.order_id) DESC, O.order_date DESC 

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

Answer 1

Использовать MAX() функция агрегации:

SELECT C.customer_id, MAX(O.order_date), COUNT(O.order_id) AS orders_count 
FROM customers C LEFT JOIN
     orders O
     ON O.customer_id = C.customer_id
GROUP BY C.customer_id
ORDER BY COUNT(O.order_id) DESC, MAX(O.order_date) DESC 

Answer 2

Если mysql 8.0+ version и maria db из 10.2 разрешают row_number(), что решит вашу проблему

select * from (SELECT C.customer_id, O.order_date, COUNT(O.order_id) AS orders_count,
 row_number() over(partition  by  C.customer_id order by O.order_date DESC) rn
FROM customers AS C 
LEFT JOIN orders AS O ON O.customer_id = C.customer_id
 group by  C.customer_id, O.order_date
) a where a.rn=1