主页 / dba / 问题 / 311053
Accepted
Brylie Christopher Oxley
Asked: 2022-04-18 08:03:43 +0800 CST

如何将一列中的值转换为具有另一列值的列？

  • 772

我有一个具有以下结构的数据库：

日期 角色 类型 期间
2022-04-16 护士 准备食材 45
2022-04-17 护士 打扫 30
2022-04-17 志愿者 打扫 20
2022-04-17 护士 准备食材 60

注意：我事先不知道“类型”列中的值，因为它们是由用户定义的。此外，可以有多个具有重叠日期、角色和类型的行。

我正在使用一个图表库，希望将数据分组如下：

角色 准备食材 打扫
护士 105 30
志愿者 无效的 20

到目前为止，我可以使用以下查询对数据进行分组

select 
    role,
    type, 
    sum(duration) as total_minutes
from work
group by role, type;
角色 类型 总分钟数
护士 打扫 45
护士 准备食材 20
志愿者 打扫 15
志愿者 准备食材 43

如何“透视”/“转置”数据，以便每一行代表一个角色，其中一列包含每种工作类型的分钟总和？

实际上，我想转置类似于 Pandas DataFrame.pivot_table函数的数据，但只使用 SQL。

postgresql group-by

1 个回答

  1. Best Answer

    首先，您需要使用该create extension tablefunc;命令安装 tablefunc 扩展，否则枢轴功能crosstab将不起作用。

    即使在阅读了这个答案之后，还是建议您在此处阅读 PostgreSQL on crosstab 的官方文档

    至于如何做到这一点：

    select *
from crosstab(
    'select
    role,
    type,
    sum(duration) as total_minutes
from work
group by role, type
order by type',
    'select distinct type from work order by type'
) as ct(
    role text,
    "Cleaning" text,
    "Food preparation" text
);

    注意order by两个查询中的显式子句，这是必须的，否则它可能会错误地映射值，因为没有它，SQL 不能保证数据的顺序。

    您必须type在别名中指定列的每个可能输出。

    上述更动态的版本（尽管无论如何都不完美）：

    create or replace function get_dynamic_transpose()
  returns text
  language plpgsql
as
$$
declare
    v_output_columns text;
begin
    select array_to_string(array_agg(distinct quote_ident(type) || ' ' || pg_typeof(type) || E' \n'),',','null')
    into v_output_columns
    from testing;

    return format(
'select *
from crosstab(
    ''select
    role,
    type,
    sum(duration) as total_minutes
from testing
group by role, type
order by type'',
    ''select distinct type from testing order by type''
) as ct(
    role text,
    %s
);', v_output_columns
    );
end;
$$;

    此函数将返回您需要执行的查询以获得所需的结果。它将动态构建输出所需的可能列的列表。这个函数绝对可以像这里所做的那样变得更通用，但是这样做的工作量并不小，因为 PostgreSQL 不能返回一个它事先不知道它的定义的集合。

    此函数还有另一个选项，而不是返回查询字符串，它可以返回一个 json 对象数组，每个对象代表一行，并且您可以将此 json 拆分为应用程序端的普通行和列。如果这样的解决方案是可以接受的，那么这很好：

    create or replace function get_dynamic_transpose_jsonb()
  returns jsonb
  language plpgsql
as
$$
declare
    v_output_columns text;
    v_query text;
    v_result jsonb;
begin
    select array_to_string(array_agg(distinct quote_ident(type) || ' ' || pg_typeof(type) || E' \n'),',','null')
    into v_output_columns
    from testing;

    v_query = format(
'select jsonb_agg(ct)
from crosstab(
    ''select
    role,
    type,
    sum(duration) as total_minutes
from testing
group by role, type
order by type'',
    ''select distinct type from testing order by type''
) as ct(
    role text,
    %s
);', v_output_columns
    );

    execute v_query into v_result;

    return v_result;
end;
$$;

    此函数的结果将类似于以下内容

    [{"role": "Nurse", "Cleaning": "30", "Food preparation": null}, {"role": "Volunteer", "Cleaning": null, "Food preparation": "55"}]
    • 3

相关问题