PostgreSQL高效统计列不同值总数实现分页查询方案

需要查询某列取值相同的多行数据，同时基于该列的不同值实现分页，并获取不同值的总数量。不希望使用SELECT COUNT(DISTINCT(dept_name)) FROM data_set语句统计不同值总数，因为该语句会扫描全表所有记录。
示例中使用row_no字段按分页规则拉取数据，应用层会将数据分组为dept_a -> LIST<name,age>结构，同时需要不同值的总数量计算总页数，需要在PostgreSQL中通过窗口函数或其他方式高效获取该总数值。

表结构 #

dept_name |  name  | age
-----------+--------+-----
 dept_a    | name11 |  10
 dept_a    | name12 |  11
 dept_a    | name13 |  10
 dept_a    | name14 |  12
 dept_a    | name15 |  11
 dept_b    | name21 |  10
 dept_b    | name22 |  11
 dept_b    | name23 |  10
 dept_b    | name24 |  12
 dept_b    | name25 |  11

预期输出 #

dept_name |  name  | age | row_no | count
-----------+--------+-----+--------+-------
 dept_a    | name11 |  10 |      1 |     2
 dept_a    | name12 |  11 |      1 |     2
 dept_a    | name13 |  10 |      1 |     2
 dept_a    | name14 |  12 |      1 |     2
 dept_a    | name15 |  11 |      1 |     2

现有方案缺陷 #

现有SQL可返回符合要求的结果，但需要额外执行distinct count做全表统计，其中count(1) OVER () AS full_count只会统计所有行的总数（示例中为10），无法直接得到dept_name的不同值数量，现有SQL如下：

WITH row_no_tab AS (
  SELECT *, 
    DENSE_RANK() OVER (
      ORDER BY 
        ds.dept_name
    ) AS row_no 
  FROM data_set ds
) 
SELECT 
  * 
FROM 
  row_no_tab 
CROSS JOIN (
    SELECT COUNT(DISTINCT(dept_name)) FROM data_set
  ) AS distinct_count 
WHERE 
  row_no > 0 
  AND row_no <= 1

直接利用窗口函数即可在单次表扫描中同时拿到分页序号和去重后的总数，不需要额外执行全表distinct统计，优化后SQL如下：

WITH row_no_tab AS (
  SELECT *,
    DENSE_RANK() OVER (ORDER BY dept_name) AS row_no,
    MAX(DENSE_RANK() OVER (ORDER BY dept_name)) OVER () AS distinct_count
  FROM data_set
)
SELECT *
FROM row_no_tab
WHERE row_no BETWEEN 1 AND 1;

实现说明 #

• 核心逻辑是DENSE_RANK()会为每个不同的dept_name分配连续递增的不重复序号，全局窗口下取该序号的最大值，结果就等于dept_name的去重总数量
• 所有计算在一次表扫描过程中完成，去掉了原方案中额外的全表distinct count子查询，执行效率更高
• 如果dept_name字段上建有序索引，该查询可以直接走索引顺序扫描，不需要对全表数据做排序操作，性能会进一步提升

内容的提问来源于stack exchange，提问作者Shashank V C