postgresql - 为什么我在临时表列上应用时会得到错误的平均值

Question

我正在尝试根据 messages 表中的每个 message.body 字数获取字数的平均值

一个例子是

**message.body**
-------------------
-->"aaz aae aar"
-->"aaz"
-->"aaz aae"

输出必须是:AVG( 3 + 1 + 2 ) = 2

为此，我一直在应用以下查询

SELECT AVG(temp.words) FROM (SELECT (array_length(string_to_array(messages.body,' '),1)) AS words FROM messages) AS temp

message.body 只是文本。

我们将不胜感激。

Answer 1

给出你期望的结果:

t=# with messages(body) as (values('aaz aae aar'),('aaz'),('aaz aae')) SELECT AVG(temp.words) FROM (SELECT (array_length(string_to_array(messages.body,' '),1)) AS words FROM messages) AS temp;
        avg
--------------------
 2.0000000000000000
(1 row)

t=# with messages(body) as (values('aaz aae aar'),('aaz'),('aaz aae')) SELECT *FROM (SELECT (array_length(string_to_array(messages.body,' '),1)) AS words,messages.body FROM messages) AS temp;
 words |    body
-------+-------------
     3 | aaz aae aar
     1 | aaz
     2 | aaz aae
(3 rows)