PostgreSQL统计模式值

Question

PostgreSQL统计模式值

4

我正在使用SQL查询

    SELECT round(avg(int_value)) AS modal_value FROM t;

为了获得模态值，当然这不是正确的方法，但这是展示一些结果的第一种选择。

那么，我的问题是，“如何做正确的事情？”。

使用PostgreSQL 8.3+，我们可以使用这个用户定义的聚合函数来定义模式:

CREATE FUNCTION _final_mode(anyarray) RETURNS anyelement AS $f$
    SELECT a FROM unnest($1) a
    GROUP BY 1  ORDER BY COUNT(1) DESC, 1
    LIMIT 1;
$f$ LANGUAGE 'sql' IMMUTABLE;
CREATE AGGREGATE mode(anyelement) (
  SFUNC=array_append,  STYPE=anyarray,
  FINALFUNC=_final_mode, INITCOND='{}'
);

然而，作为用户自定义平均值，在处理大表时可能会变得很慢（与内置的AVG函数相比较sum/count）。在PostgreSQL 9+中，没有直接的（内置）函数来计算统计模式值吗？也许可以使用pg_stats... 如何实现类似的功能呢？

    SELECT (most_common_vals(int_value))[1] AS modal_value FROM t;

pg_stats视图可以用于这种任务吗（即使是手动的）？

- Peter Krauss

pg_stat 视图（像任何处理规划器统计信息的表/视图一样）仅包含估计值，而不是精确值。 - Ihor Romanchenko

5个回答

1

你可以尝试像这样做：

SELECT int_value, count(*)
FROM t
GROUP BY int_value
ORDER BY count(*) DESC
LIMIT 1;

这背后的思路是：获取每个 int_value 的计数，然后对它们进行排序（使最大的 count 排在第一位），然后将查询限制为仅返回第一行，以获取具有最高计数的 int_value。

- Ihor Romanchenko

谢谢！嗯，我明白我的“构建函数”的梦想只是一个梦想……是吗？关于你的查询，请检查它是否与我的引用链接中的_final_mode()函数使用的算法不“完全”（不进行聚合）相同。 - Peter Krauss

@PeterKrauss 是的，这个想法是一样的，只是直接应用，不需要收集到数组中再解除嵌套。 - Ihor Romanchenko

1

如果你想按组进行操作：

select
    int_value * 10 / (select max(int_value) from t) g,
    min(int_value) "from",
    max(int_value) "to",
    count(*) total
from t
group by 1
order by 4 desc

- Clodoaldo Neto

谢谢@ClonaldoNeto，这是一个很好的解决方案(!)用于检测“模态区间”的标量和度量。 - Peter Krauss

PS：对于读者，第一行（限制1）是模式；如果您将10更改为30或100，则会获得更多（而微小的）间隔；要列出间隔，请使用“按1排序”。 - Peter Krauss

0

在问题介绍中，我引用了this link一个很好的SQL编码解决方案（@IgorRomanchenko在这个答案中使用了相同的算法）。@ClodoaldoNeto展示了一个“新的解决方案”，但是正如我所评论的那样，它是针对scalars and measures而不是当前问题的答案。

过去了两个月，大约有40次浏览，没有新问题...

结论

仅使用此页面和引用链接的信息（以及缺乏进一步信息的证据），得出以下结论：

用户定义的聚合函数mode()已经足够了，我们不需要内置（编译）版本。
没有优化基础设施，内置函数与用户定义的函数执行的操作相同。

我测试了引用的SQL聚合函数，在各种情境下。

SELECT mode(some_value) AS modal_value FROM t;

在我的测试中，它非常快...因此，不需要像Oracle的STATS_MODE那样的“内置函数”，只需要在“统计包”需求上下文中使用--但是如果您愿意花时间和内存来安装一些东西，我建议使用R语言。

另一个隐含的问题是，是否有一个统计包“准备”或利用了一些PostgreSQL基础设施（如pg_stats）...一个“规范答案”的好线索在@IgorRomanchenko的评论中：“pg_stat（...）仅包含估计值，而不是确切值”。因此，模式函数不能利用基础设施，正如我所想象的那样。

注意：对于“模态区间”，我们可以使用另一个函数，请参见@ClodoaldoNeto的答案。

- Peter Krauss

嗨@Luffydude，请检查您的PostgreSQL版本！并参考引用链接（https://wiki.postgresql.org/wiki/Aggregate_Mode#mode.28.29_for_Postgres_9.3_or_earlier_.28superseded_in_9.4.29）（您可以复制/粘贴旧版本的函数）。 - Peter Krauss

0

模式是最有价值的，所以我覆盖了我在这里找到的函数，并做出了以下更改：

CREATE OR REPLACE FUNCTION _final_mode(anyarray)
  RETURNS anyelement AS
    $BODY$
    SELECT 
        CASE 
            WHEN t1.cnt <> t2.cnt THEN t1.a 
            ELSE NULL 
        END
        FROM
            (SELECT a, COUNT(*) AS cnt
             FROM unnest($1) a
             WHERE a IS NOT NULL
             GROUP BY 1 
             ORDER BY COUNT(*) DESC, 1
             LIMIT 1
            ) as t1, 
            (SELECT a,
             COUNT(*) AS cnt
             FROM unnest($1) a
             WHERE a IS NOT NULL
             GROUP BY 1 
             ORDER BY COUNT(*) DESC, 1
             LIMIT 2 OFFSET 1
            ) as t2
    $BODY$
LANGUAGE 'sql' IMMUTABLE;

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

- Bruno Mucelini Mergen

你好，Bruno。我在上面向IgorRomanchenko发表了一条评论，其中包含相同的维基链接...请检查我们的讨论是否涵盖了你的答案（还有我上面提到的_final_mode聚合函数）。问题不是关于“如何使用SQL重现mode”，而是“PostgreSQL内置函数”在哪里可以快速执行此类操作。 - Peter Krauss

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- ars · Accepted Answer

自 PostgreSQL 9.4 版本起，内置了聚合函数 mode。使用方式如下：

SELECT mode() WITHIN GROUP (ORDER BY some_value) AS modal_value FROM tbl;

点击此处阅读有关有序集合聚合函数的更多信息：

36.10.3. 有序集合聚合函数

内置的有序集合聚合函数

有关如何处理旧版本Postgres的其他答案，请参见。