在PostgreSQL中为表中的列计算循环中的平均值

Question

我来自Python世界，在那里很多事情都是丰富多彩且简单的。现在我正努力进入SQL，因为我想在熊猫之外挑战自己，并获得SQL方面的重要经验。话虽如此，我有以下问题。我有以下片段：

do 
$do$
declare i varchar(50); 
declare average int; 
begin
for i in (
select column_name
FROM information_schema.columns
where table_schema = 'public'
and table_name = 'example_table' 
and column_name like '%suffix') loop 
--raise notice 'Value: %', i; 
select AVG(i) as average from example_table; 
raise notice 'Value: %', i;
end loop; 
end; 
$do$

正如我在SQL文档中了解到的那样，我发现for循环只能在do块中实现，并且必须声明某些变量。我这样做是为了I变量，它包含我要迭代的列的名称。但我想得到列的平均值，并将其作为一行添加到一个有两列的表中——一列用于特性(I变量(，另一列用于该列的平均数。我原以为上面的代码片段可以做到这一点，但我收到了一条错误消息，上面写着Function avg(character varying) does not exist。当我在for循环之外为单个列使用函数AVG时，它确实会检索这个数字列的平均值，但当我在for循环中使用它时，它表示这个聚合函数不存在。有人能帮我一下吗？

更新：我后退了一步，试图把故事写得更短：

select column_name
FROM information_schema.columns
where table_schema = 'public'
and table_name = 'my_table' 
and column_name like '%wildcard'; 

此代码段生成一个表，该表包含一个名为column_name的列填充where语句中所述约束的列。我只想添加一列，其中包含这些列的平均值。

Answer 1

如果您只需要一个表，您可以使用：

select x.col, avg(x.value::numeric)
from example_table t
cross join lateral (
select col, value
from jsonb_each(to_jsonb(t)) as e(col, value)
where jsonb_typeof(e.value) = 'number'
) x
group by x.col;

"；魔术；是将表中的每一行转换为JSON值。to_jsonb(t)就是这样做的(t是主查询中表的别名(。所以我们得到了类似{"name": "Bla", "value": 3.14, "length": 10, "some_date": "2022-03-02"}的东西。因此，每个列名都是JSON值中的一个键。

然后使用jsonb_each()函数将这个json转换为每列一行(=键(，但只保留具有数值的行(=列(。因此，派生表在表中的每列和每行返回一行。然后，外部查询只是将其按列聚合。缺点是，您需要为每个表编写一个查询。

---

如果您需要对模式中的所有表进行某种报告，您可以使用此答案的变体

with all_selects as (
select table_schema, table_name, 'select '||string_agg(format('avg(%I) as %I', column_name, column_name), ', ')||format(' from %I.%I', table_schema, table_name) as query
from information_schema.columns
where table_schema = 'public'
and data_type in ('bigint', 'integer', 'double precision', 'smallint', 'numeric', 'real')
group by table_schema, table_name
), all_aggregates as (
select table_schema, table_name, 
query_to_xml(query, true, true, '') as result
from all_selects
)
select ag.table_schema, ag.table_name, r.column_name, nullif(r.average, '')::numeric as average
from all_aggregates ag
cross join xmltable('/row/*' passing result
columns column_name text path 'local-name()', 
average text path '.') as r

这有点棘手。第一部分all_selects为模式public中的每个表构建查询，以将avg()聚合应用于可以包含数字(where data type in (...)(的每个列

因此，例如，这返回一个字符串select avg(value) as value, avg(length) as length from example_table

下一步是通过query_to_xml()运行每个查询(遗憾的是没有内置的query_to_jsonb()(。

query_to_xml()会返回类似以下内容：

<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<value>12.345</balance>
<length>42</user_id>
</row>

因此，每列一个标签(这是avg(..)函数的结果(。

最后的选择使用xmltable()将XML结果中的每个标记转换为一行，返回列名和值

在线示例

---

当然你也可以在PL/pgSQL中这样做：

do 
$do$
declare 
l_rec record;
l_sql text;
l_average numeric;
begin
for l_rec in 
select table_schema, table_name, column_name
from information_schema.columns
where table_schema = 'public'
and data_type in ('bigint', 'integer', 'double precision', 'smallint', 'numeric', 'real')
loop 
l_sql := format('select avg(%I) from %I.%I', l_rec.column_name, l_rec.table_schema, l_rec.table_name);
execute l_sql
into l_average;
raise notice 'Average for %.% is: %', l_rec.table_name, l_rec.column_name, l_average;
end loop; 
end; 
$do$

请注意列data_type上的条件，以仅处理可求平均值的列。然而，这成本更高，因为它每个列运行一个查询，而不是每个表。