sql - 表中每个字段的值计数

在PostgresSQL中，我试图获取每个字段和值对的值计数。举例来说，我有两列，但我真的有100列。所以，我需要一个程序化的解决方案。
一张桌子，

| id | name  | gender |
-----------------------
| 1  | john  | male   |
| 2  | susan | female |
| 3  | john  | male   |
| 4  | john  | male   |
| 5  | susan | female |
| 6  | john  | male   |
| 7  | susan | female |
| 8  | julie | female |
| 9  | julie | female |

我知道

select name, count(*) as value_count
from table_name
group by name

给我一个专栏想要的：

| name  | value_count |
-----------------------
| john  | 4           |
| susan | 3           |
| julie | 2           |

但是，我如何能够以编程方式遍历所有列并生成它，例如：

| column_name | value_name | value_count |
------------------------------------------
| name        | john       | 4           |
| name        | susan      | 3           |
| name        | julie      | 2           |
| gender      | male       | 4           |
| gender      | female     | 5           |

最佳答案

使用grouping sets：

select name, gender, count(*) as value_count
from my_table
group by grouping sets ((name), (gender))

 name  | gender | value_count
-------+--------+-------------
 john  |        |           4
 julie |        |           2
 susan |        |           3
       | female |           5
       | male   |           4
(5 rows)

如果您决定以上述格式获取结果：

select
    case when gender is null then 'name' else 'gender' end as column_name,
    coalesce(gender, name) as value_name,
    value_count
from (
    select name, gender, count(*) as value_count
    from my_table
    group by grouping sets ((name), (gender))
    ) s

 column_name | value_name | value_count
-------------+------------+-------------
 name        | john       |           4
 name        | julie      |           2
 name        | susan      |           3
 gender      | female     |           5
 gender      | male       |           4
(5 rows)

关于sql - 表中每个字段的值计数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/58000451/