有效的列的不同元素的全部计数

发布于 2025-02-10 09:08:52 字数 1547 浏览 0 评论 0 原文

需要在列中选择具有相同值的多个行，以及与不同值的分页的全面计数。但是，想从data_set 中避免使用选择选择计数（DISTICT（DEPT_NAME）），因为它将扫描所有记录。

在下面的示例中，我使用 row_no 根据分页获取多个记录，在应用程序层中，我将分组 dept_a-＆gt; list＆lt; name，age＆gt; 。还需要完全计数来确定目前的潜在页面数量。

有没有一种方法可以在Postgres中使用窗口函数或其他任何其他方式获取完整的计数？

表：

 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

可以给出结果但需要从data_set 中使用选择选择计数（DISTICS（DEPT_NAME））。 count（1）over（）作为full_count ，将完整计数作为 10 ，因为它考虑了所有行，而不是 dintife > dept_name。

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

原文

Need to select multiple rows having the same value in a column along with full count of the distinct values with pagination on the distinct values.
But would like to avoid taking distinct count usingSELECT COUNT(DISTINCT(dept_name)) FROM data_set as it would scan all the records.

In the below example I'm using row_no for fetching multiple records according to the pagination and in the application layer I would be grouping dept_a -> LIST<name,age>. Also need full count to decide the number of potential pages present.

Is there a way to get the full count using window functions or any other way efficiently in postgres?

Table:

 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

Expected output:

 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

Query that would give the result but need to use SELECT COUNT(DISTINCT(dept_name)) FROM data_set.
count(1) OVER () AS full_count , gives the full count as 10 as it considers all the rows and not the distinct of dept_name.

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

分享到QQ

分享到微博