如何从多个指定userID中分别获取各自最新的30条查询结果

结论 #

不需要在子查询中添加循环，使用窗口函数即可高效实现按用户分组取最新30条数据的需求，执行性能远高于循环实现。

实现逻辑 #

• 你之前直接写的LIMIT 30是全局生效，只会返回所有匹配用户里最新的30条记录，无法按用户维度拆分限制数量
• 用ROW_NUMBER()窗口函数，按person（用户ID字段）分组，分组内按most_recent_purchases倒序排序，给每个用户的购买记录生成序号
• 外层查询过滤每个用户序号≤30的记录，即可得到每个用户最新的30条数据

修正后代码示例 #

DEFINE MACRO userid
  '123abc',
  '321cba',
  '987zyx'
;

SELECT purchases
FROM (
  SELECT 
    purchases,
    -- 按用户分组，按购买时间倒序生成行号
    ROW_NUMBER() OVER (PARTITION BY person ORDER BY most_recent_purchases DESC) AS row_num
  FROM random.database
  WHERE person IN ($userid)
) t
WHERE row_num <= 30;

注：以上写法支持PostgreSQL、MySQL 8.0+、Spark SQL、BigQuery等所有支持标准SQL窗口函数的数据库。

低版本数据库兼容方案 #

如果你使用的是不支持窗口函数的旧版本数据库（如MySQL 5.7及更早版本），可以用关联子查询实现：

DEFINE MACRO userid
  '123abc',
  '321cba',
  '987zyx'
;

SELECT a.purchases
FROM random.database a
WHERE a.person IN ($userid)
AND (
  SELECT COUNT(*) 
  FROM random.database b 
  WHERE b.person = a.person 
  AND b.most_recent_purchases >= a.most_recent_purchases
) <=30
ORDER BY a.person, a.most_recent_purchases DESC;

内容的提问来源于stack exchange，提问作者hotsriracha