给出了一个带有时间戳列的表,我想获得10个最新记录,特定列是唯一的。
如何完成?
示例:
数据:
purchases
-----------------------------------------------------------------
timestamp first_name last_name customer_id product_name purchase_amount
如何获得购买购买的5位最新客户,以及product_name
和purchase_amount
?
结果:
James, Jackson, 1234, 'foo', 432.123
Tim, McTimothy, 321, 'bar', 5124.11
Bob, Bobbertson, 55, 'foo', 432.123
Claire, Rando, 191, 'tv', 700.00
Jimbo, Manman, 631, 'ps4', 450.00
我尝试的是:
此查询失败,因为它需要在product_name
和purchase_amount
上进行汇总,但我们不需要聚合,只是最新值。
SELECT first_name, last_name, customer_id, product_id
FROM purchases
GROUP BY first_name, last_name, customer_id
ORDER BY timestamp DESC
LIMIT 10
我认为这会做到:
SELECT p.*
FROM
(
SELECT customer_id, MAX(timestamp) last_purchase_time
FROM purchases
GROUP BY customer_id
ORDER BY MAX(timestamp) DESC
LIMIT 5
) lp
INNER JOIN purchases p ON p.customer_id = lp.customer_id and p.timestamp = lp.last_purchase_time
嵌套查询为每个客户提供了最后的购买时间,并将嵌套结果设置为5个最新客户。然后,我们将其连接到购买表,以便我们可以看到每个客户的最后购买数据。这确实使客户在完全相同的实例上不能进行两次购买,但这可能是安全的。
您可以尝试添加将充当id
的rank
列,类似的内容:
SELECT first_name, last_name, customer_id, product_name
FROM
(SELECT first_name, last_name, customer_id, product_name, @rank := @rank + 1 AS rank
FROM purchases p1, (SELECT @rank := 0) r
ORDER BY timestamp DESC) p2
GROUP BY first_name, last_name, customer_id
LIMIT 10;
我假设customer_id是唯一的。因此,我们只需要在组条款
select * from purchases p2 where
concat(UNIX_TIMESTAMP((p2.timestamp)),'_',p2.customer_id)
in (
select concat(UNIX_TIMESTAMP(max(p.timestamp)),'_',p.customer_id)
from purchases p group by p.customer_id order by p.timestamp desc
) limit 10