Postgresql以指定字段分组后,查询每组的前N条记录
主函数:
ROW_NUMBER() OVER (PARTITION BY "省份名称","地市名称" ORDER BY arpu desc ,dou DESC) AS row_id
在原有数据表的基础上以 "省份名称","地市名称" 分组,按"arpu desc ,dou DESC"排序后,生成一个新字段row_id。然后再以row_id为筛选条件查询数据。<=3:只取前3天数据
select * from (
select *,ROW_NUMBER() OVER (PARTITION BY "省份名称","地市名称" ORDER BY arpu desc ,dou DESC) AS row_id
FROM "全国表" t
WHERE "省份名称" = '河南'
and "地市名称" = '开封'
) t
where t.row_id <=3