如何在PostgreSQL中创建透视表

uqxowvwt 于 2023-02-15 发布在 PostgreSQL

关注(0)|答案(2)|浏览(141)

我期待基本上创建一个透视图使用PostgreSQL，这样的下表：
| 色谱柱A|B栏|
| - ------|- ------|
| 快乐|悲伤|
| 悲伤|快乐|
| 快乐|悲伤|
变成
| 计数|色谱柱A|B栏|
| - ------|- ------|- ------|
| 快乐|第二章|1个|
| 悲伤|1个|第二章|
我已经能够使用case/when操作符足够多，以至于我可以看到独立列下的计数，

SELECT
    COUNT(CASE WHEN column1 = 'Happy' THEN 1 END) AS column1_Happy_count,
    COUNT(CASE WHEN column1 = 'Sad' THEN 1 END) AS column1_Sad_count,
    COUNT(CASE WHEN column2 = 'Happy' THEN 1 END) AS column2_Happy_count,
    COUNT(CASE WHEN column2 = 'Sad' THEN 1 END) AS column2_Sad_count,
    COUNT(CASE WHEN column3 = 'Happy' THEN 1 END) AS column3_Happy_count,
    COUNT(CASE WHEN column3 = 'Sad' THEN 1 END) AS column3_Sad_count
FROM your_table;

但是缺少基本上垂直地每对列的步骤。
我无法使用扩展，如tablefunc和crosstab。

postgresql

来源：https://stackoverflow.com/questions/75455169/how-to-create-a-pivot-table-in-postgresql

2条答案

按热度按时间

vwkv1x7d1#

尝试this：

CREATE TABLE my_table (
  column_a varchar(10),
  column_b varchar(10)
);

INSERT INTO my_table (column_a, column_b)
VALUES ('Happy', 'Sad'),
       ('Sad', 'Happy'),
       ('Happy', 'Sad'),
       ('Good', 'Bad');

WITH DataSource (col, val) AS
(
     SELECT 'a', column_a
     FROM my_table
     UNION ALL
     SELECT 'b', column_b
     FROM my_table
)
SELECT uniq.val AS "Count"
      ,MAX(case when counts.col = 'a' then counts end) AS "Column A"
      ,MAX(case when counts.col = 'b' then counts end) AS "Column B"
FROM
(
     SELECT DISTINCT val
     FROM DataSource
) uniq
INNER JOIN
(
   SELECT col
         ,val
         ,COUNT(*) counts
   FROM DataSource
   GROUP BY col
          ,val
) counts
  ON uniq.val = counts.val
GROUP BY uniq.val

会给予你这个

赞(0）回复(0）举报 2023-02-15

t5zmwmid2#

您可以先聚合ColumnA，再聚合ColumnB，然后执行完全联接，如下所示：

select coalesce(A.ColumnA ,B.ColumnB) as "Count",
       A.cnt as "Column A",
       B.cnt as "Column B"
from
(
  select ColumnA, count(*) cnt
  from tbl_name
  group by ColumnA
) A
full join 
(
  select ColumnB, count(*) cnt
  from tbl_name
  group by ColumnB
) B
on A.ColumnA = B.ColumnB

如果ColumnA中的非重复值与ColumnB中的非重复值相同，则可以使用 join 代替 full join。
See demo .

赞(0）回复(0）举报 2023-02-15

我来回答

如何在PostgreSQL中创建透视表

2条答案

相关问题

热门标签

最新问答