PostgreSQL - 返回表中所有列的最常见值

Question

我是一名有用的助手，可以为您翻译文本。

我有一个表格，其中包含许多列，我想运行一个查询来查找每列中最常见的值。

通常情况下，对于单个列，我会运行类似以下的命令：

SELECT country
FROM users
GROUP BY country
ORDER BY count(*) DESC
LIMIT 1

PostgreSQL有没有内置函数可以做到这一点，或者有没有人可以建议我运行哪个查询来实现这个功能？

- erniereg

---

@Denis：OP想要获取每列的众数，而不仅仅是country列。虽然我不确定这是否特别关联。 - Kevin

@Keven：啊，是的，它开始运转了。OP可能应该研究一下窗口函数。http://www.postgresql.org/docs/current/static/tutorial-window.html - Denis de Bernardy

另一种方法是在表格经过适当分析后，使用pg_stats中的most_common_vals。 - Kouber Saparev

不要以为使用窗口函数可以更轻松地实现它。每列至少需要一个count(*)/或OVER。因此最好坚持使用简单易读的SQL。请查看我的答案。 - Filipe Roxo

一个人应该如何打破平局？ - Denis de Bernardy

Answer 1

使用相同的查询语句，如果需要查询多个列，应该进行以下操作：

SELECT *
FROM
(
    SELECT country
    FROM users
    GROUP BY 1
    ORDER BY count(*) DESC
    LIMIT 1
) country
,(
    SELECT city
    FROM users
    GROUP BY 1
    ORDER BY count(*) DESC
    LIMIT 1
) city

这适用于任何类型，并将在同一行返回所有值，其中列的原始名称。

如果有更多的列，只需添加更多的子查询即可：

,(
    SELECT someOtherColumn
    FROM users
    GROUP BY 1
    ORDER BY count(*) DESC
    LIMIT 1
) someOtherColumn

编辑：

你也可以使用窗口函数来实现。然而，从性能和可读性两方面考虑，这种方法都不会更好。

- Filipe Roxo

---

这样做更好，更接近于原帖作者想要的。 - Kevin

你能告诉我这些逗号分隔的子查询的使用文档在哪里吗？我想了解更多。 - sedot

逗号分隔符是笛卡尔积，我认为它属于SQL标准。这是FROM子句的基本用法：https://www.postgresql.org/docs/9.5/static/queries-table-expressions.html - Filipe Roxo

Answer 2

从PG 9.4开始，有一个聚合函数可以实现此功能：

mode() WITHIN GROUP (ORDER BY sort_expression)

返回最常见的输入值（如果有多个同样频繁的结果，则任意选择第一个）

对于早期版本，您可以创建一个...

  CREATE OR REPLACE FUNCTION mode_array(anyarray)
            RETURNS anyelement AS
$BODY$
    SELECT a FROM unnest($1) a GROUP BY 1 ORDER BY COUNT(1) DESC, 1 LIMIT 1;
$BODY$
LANGUAGE SQL IMMUTABLE;



CREATE AGGREGATE mode(anyelement)(
   SFUNC = array_append, --Function to call for each row. Just builds the array
   STYPE = anyarray,
   FINALFUNC = mode_array, --Function to call after everything has been added to array
   INITCOND = '{}'--Initialize an empty array when starting
) ;

用法：SELECT mode(column) FROM table;

- alexkovelsky

---

从这个例子中并不清楚，但是对于 mode() WITHIN GROUP (ORDER BY *sort_expression*) 的调用故意没有向 mode 函数发送任何参数。例如，聚合函数的正确使用方式应该是 mode() WITHIN GROUP (ORDER BY col_name DESC) AS most_common_entry。 - BrDaHa

@BrDaHa 你可能想要写信给PostgreSQL支持团队，因为这是他们文档中的一个例子。 - alexkovelsky

Answer 3

此窗口函数版本将读取用户表和计算表各一次。相关子查询版本将为每个列读取一次用户表。如果列很多（如OP的情况），那么我猜这会更快。 SQL Fiddle

select distinct on (country_count, age_count) *
from (
    select
        country,
        count(*) over(partition by country) as country_count,
        age,
        count(*) over(partition by age) as age_count
    from users
) s
order by country_count desc, age_count desc
limit 1

- Clodoaldo Neto

---

你能提供“EXPLAIN”结果来支持这个说法吗？ - Kevin

@Kevin 这不是一个“断言”。这只是一个“猜测”。你会构建数据吗？ - Clodoaldo Neto

对我来说，查询规划器应该能够（部分）优化重复的子查询。我只是好奇这是否真的是这种情况。您不需要大量数据来查看它是否进行了20层深度的嵌套迭代。 - Kevin

Answer 4

如果我在做这件事，我会编写像这样的查询：

SELECT 'country', country
FROM users
GROUP BY country
ORDER BY count(*) DESC
LIMIT 1
UNION ALL
SELECT 'city', city
FROM USERS
GROUP BY city
ORDER BY count(*) DESC
LIMIT 1
-- etc.

需要注意的是，这只在所有列都是兼容类型时才有效。如果不是，则可能需要其他解决方案。

- Kevin

---

你的回答很好，就像你所说的，将所有数据放在同一列且类型相同是可以的。然而，我认为这并不是被问到的内容。 - Filipe Roxo