使用SQL查找表中每个用户最后n次购买的平均值

Question

我有一个有两个表的SQLite数据库:

用户

<表类>userIDfield1field2field3field4tbody><<tr>123

您可以使用ROW_NUMBER()窗口函数从最后一个开始对购买进行排序，然后连接到表users进行汇总:

SELECT u.*, COALESCE(AVG(p.price), 0) avgPrice
FROM users u
LEFT JOIN (
SELECT *, ROW_NUMBER() OVER (PARTITION BY userID ORDER BY timestamp DESC) rn
FROM purchases
) p ON p.userID = u.userID AND p.rn <= ?
GROUP BY u.userID

将?修改为号码n那是你想要的。

您可以使用相关子查询:

SELECT u.*,
(SELECT AVG(p.price)
FROM (SELECT p.price
FROM purchases p
WHERE p.userID = u.userID
ORDER BY p.timestamp DESC
LIMIT ?    -- whatever number you wnat
) p
) as avg_price
FROM users u;

为了提高性能，您需要在purchases(userID, timestamp desc, price)上建立索引。