PostgreSQL交叉表查询

Question

PostgreSQL交叉表查询有没有人知道如何在PostgreSQL中创建交叉表查询？例如，我有下表：Section    Status    CountA          Active    1A          Inactive  2B          Active    4B          Inactive  5我想查询返回以下交叉表：Section    Active    InactiveA          1         2B          4         5这可能吗？

Answer 1

您可以使用附加模块tablefunc的crosstab()功能- 您必须为每个数据库安装一次。从PostgreSQL 9.1开始，你可以使用它：CREATE EXTENSION

CREATE EXTENSION tablefunc;

在你的情况下，我相信它看起来像这样：

CREATE TABLE t (Section CHAR(1), Status VARCHAR(10), Count integer);

INSERT INTO t VALUES ('A', 'Active',   1);

INSERT INTO t VALUES ('A', 'Inactive', 2);

INSERT INTO t VALUES ('B', 'Active',   4);

INSERT INTO t VALUES ('B', 'Inactive', 5);

SELECT row_name AS Section,

       category_1::integer AS Active,

       category_2::integer AS Inactive

FROM crosstab('select section::text, status, count::text from t',2)

            AS ct (row_name text, category_1 text, category_2 text);

Answer 2

SELECT section,

       SUM(CASE status WHEN 'Active' THEN count ELSE 0 END) AS active, --here you pivot each status value as a separate column explicitly

       SUM(CASE status WHEN 'Inactive' THEN count ELSE 0 END) AS inactive --here you pivot each status  value as a separate column explicitly

FROM t

GROUP BY section

Answer 3

使用JSON聚合的解决方案：

CREATE TEMP TABLE t (

  section   text

, status    text

, ct        integer  -- don't use "count" as column name.

);

INSERT INTO t VALUES 

  ('A', 'Active', 1), ('A', 'Inactive', 2)

, ('B', 'Active', 4), ('B', 'Inactive', 5)

                   , ('C', 'Inactive', 7); 

SELECT section,

       (obj ->> 'Active')::int AS active,

       (obj ->> 'Inactive')::int AS inactive

FROM (SELECT section, json_object_agg(status,ct) AS obj

      FROM t

      GROUP BY section

     )X