postgresql系列之窗口函数详细介绍:
https://blog.csdn.net/qq_31156277/article/details/83957166
例:要根据每个组中行数的百分比检索行,可以使用两个窗口函数:一个用于计算行,另一个用于为它们提供唯一编号。
SELECT gp,
val
FROM (
SELECT gp,
val,
COUNT(*) OVER (PARTITION BY gp) AS cnt,
ROW_NUMBER() OVER (PARTITION BY gp ORDER BY val DESC) AS rn
FROM temp
) t
WHERE rn / cnt <= 0.75;
例:
查找前50%的数据
类似: SELECT TOP 50 PERCENT * FROM temp_table
SELECT rn,
count
FROM (
SELECT ROW_NUMBER() OVER(ORDER BY xxx_column) AS rn,
COUNT(*) OVER()
FROM temp_table
WHERE xxx_column2 = 'xxx') t
WHERE ROUND(rn::numeric/count::numeric,2) <= 0.5