postgresql 窗口函数

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