使用postgres将不同的行数据透视/交叉到列 [英] pivot/cross distinct row data to colums with postgres

问题描述

例如，我有不同的数据要旋转/交叉

I have distinct data that I want to pivot/cross, for instance

将表A赋予

name  tag
Bob   sport
Bob   action
Bob   comedy
Tom   action
Tom   drama
Sue   sport

我想要一个将数据转换为的查询

I'd like a query that transforms the data to

name  sport  action  comedy  drama
Bob   1      1       1       0
Tom   0      1       0       1
Sue   1      0       0       0

对于任意数量的n个不同的标签.

For any number n of distinct tags.

如果我在开始之前不了解不同的标记，如何使用sql创建此转换.

How would I create this transformation using sql if I didn't know the distinct tags before I begin.

推荐答案

一些适用于某些情况的简单解决方案.使用此表(SQL Fiddle现在无法正常工作)

Some simple solutions adequate for some cases. Using this table (SQL Fiddle is not working right now)

create table a (
    name text,
    tag text
);
insert into a (name, tag) values
('Bob', 'sport'),
('Bob', 'action'),
('Bob', 'comedy'),
('Tom', 'action'),
('Tom', 'drama'),
('Sue', 'sport');

一个简单的数组聚合(如果可以在其他地方拆分)

A simple arrays aggregation if they can be split somewhere else

select
    name,
    array_agg(tag order by tag) as tags,
    array_agg(total order by tag) as totals
from (
    select name, tag, count(a.name) as total
    from
        a
        right join (
            (select distinct tag from a) t
            cross join
            (select distinct name from a) n
        ) c using (name, tag)
    group by name, tag
) s
group by name
order by 1
;
 name |            tags             |  totals   
------+-----------------------------+-----------
 Bob  | {action,comedy,drama,sport} | {1,1,0,1}
 Sue  | {action,comedy,drama,sport} | {0,0,0,1}
 Tom  | {action,comedy,drama,sport} | {1,0,1,0}

对于支持JSON的客户端，一组JSON对象

For JSON aware clients a set of JSON objects

select format(
    '{%s:{%s}}',
    to_json(name),
    string_agg(o, ',')
)::json as o
from (
    select name,
    format(
        '%s:%s',
        to_json(tag),
        to_json(count(a.name))
    ) as o
    from
        a
        right join (
            (select distinct tag from a) t
            cross join
            (select distinct name from a) n
        ) c using (name, tag)
    group by name, tag
) s
group by name
;
                          o                          
-----------------------------------------------------
 {"Bob":{"action":1,"comedy":1,"drama":0,"sport":1}}
 {"Sue":{"action":0,"comedy":0,"drama":0,"sport":1}}
 {"Tom":{"action":1,"comedy":0,"drama":1,"sport":0}}

或单个JSON对象

select format('{%s}', string_agg(o, ','))::json as o
from (
    select format(
        '%s:{%s}',
        to_json(name),
        string_agg(o, ',')
    ) as o
    from (
        select name,
        format(
            '%s:%s',
            to_json(tag),
            to_json(count(a.name))
        ) as o
        from
            a
            right join (
                (select distinct tag from a) t
                cross join
                (select distinct name from a) n
            ) c using (name, tag)
        group by name, tag
    ) s
    group by name
) s
;
                                                                            o                                                                            
---------------------------------------------------------------------------------------------------------------------------------------------------------
 {"Bob":{"action":1,"comedy":1,"drama":0,"sport":1},"Sue":{"action":0,"comedy":0,"drama":0,"sport":1},"Tom":{"action":1,"comedy":0,"drama":1,"sport":0}}

这篇关于使用postgres将不同的行数据透视/交叉到列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！