首页 文章

PostgreSQL交叉表查询

提问于
浏览 次
155

有没有人知道如何在PostgreSQL中创建交叉表查询？
例如，我有下表：

Section    Status    Count
A          Active    1
A          Inactive  2
B          Active    4
B          Inactive  5

我想查询返回以下交叉表：

Section    Active    Inactive
A          1         2
B          4         5

这可能吗？

sql postgresql pivot case crosstab

6 回答

  • 4

    每个数据库安装一次additional module tablefunc，提供函数 crosstab() . 从Postgres 9.1开始，您可以使用CREATE EXTENSION：

    CREATE EXTENSION IF NOT EXISTS tablefunc;

    改进了测试用例

    CREATE TABLE tbl (
   section   text
 , status    text
 , ct        integer  -- "count" is a reserved word in standard SQL
);

INSERT INTO tbl VALUES 
  ('A', 'Active', 1), ('A', 'Inactive', 2)
, ('B', 'Active', 4), ('B', 'Inactive', 5)
                    , ('C', 'Inactive', 7);  -- ('C', 'Active') is missing

    简单形式 - 不适合缺少属性

    crosstab(text) 带 1 输入参数：

    SELECT *
FROM   crosstab(
   'SELECT section, status, ct
    FROM   tbl
    ORDER  BY 1,2'  -- needs to be "ORDER BY 1,2" here
   ) AS ct ("Section" text, "Active" int, "Inactive" int);

    返回：

    Section | Active | Inactive
---------+--------+----------
 A       |      1 |        2
 B       |      4 |        5
 C       |      7 |           -- !!

    • 无需投射和重命名 .

    • 注意 C 的 incorrect 结果：第一列填写了值 7 . 有时，这种行为是可取的，但不适用于此用例 .

    • 简单表单也仅限于提供的输入查询中的三列：row_name，category，value . 如下面的2参数替代方案中没有额外列的空间 .

    安全表格

    crosstab(text, text) 带 2 输入参数：

    SELECT *
FROM   crosstab(
   'SELECT section, status, ct
    FROM   tbl
    ORDER  BY 1,2'  -- could also just be "ORDER BY 1" here

  , $$VALUES ('Active'::text), ('Inactive')$$
   ) AS ct ("Section" text, "Active" int, "Inactive" int);

    返回：

    Section | Active | Inactive
---------+--------+----------
 A       |      1 |        2
 B       |      4 |        5
 C       |        |        7  -- !!

    • 注意 C 的正确结果 .

    • 第二个参数可以是任何查询，每个属性返回一行，最后匹配列定义的顺序 . 通常，您需要查询基础表中的不同属性，如下所示：

    'SELECT DISTINCT attribute FROM tbl ORDER BY 1'

    这是在手册中 .

    由于您必须拼写列定义列表中的所有列（预定义的 crosstabN() 变体除外），因此在 VALUES 表达式中提供短列表通常更为有效，如下所示：

    $$VALUES ('Active'::text), ('Inactive')$$)

    或（不在手册中）：

    $$SELECT unnest('{Active,Inactive}'::text[])$$  -- short syntax for long lists

    • 我使用了dollar quoting来简化报价 .

    • 您甚至可以使用带有 crosstab(text, text) 的 different data types 列输出 - 只要值列的文本表示是目标类型的有效输入 . 这样，您可能具有不同类型的属性，并为各个属性输出 text ， date ， numeric 等 . 在chapter crosstab(text, text) in the manual的末尾有一个代码示例 .

    db <>小提琴here

    高级示例

    psql中

    \ crosstabview

    Postgres 9.6 将此元命令添加到其默认交互终端psql . 您可以运行您将用作第一个 crosstab() 参数的查询并将其提供给 \crosstabview （立即或在下一步中） . 喜欢：

    db=> SELECT section, status, ct FROM tbl \crosstabview

    与上面类似的结果，但它只是客户端的表示功能 . 输入行的处理方式略有不同，因此不需要 ORDER BY . \crosstabview in the manual.的详细信息该页面底部有更多代码示例 .

    关于dba.SE的相关答案，作者：DanielVérité（psql特性的作者）：

    之前接受的答案已过时 .

    • 函数 crosstab(text, integer) 的变体已过时 . 第二个 integer 参数被忽略 . 我引用current manual：

    crosstab（text sql，int N）...交叉表（文本）的过时版本 . 现在忽略参数N，因为值列的数量总是由调用查询确定

    • 无需投射和重命名 .

    • 如果某行没有所有属性，则会失败 . 请参阅上面两个输入参数的安全变体，以正确处理缺少的属性 .

    crosstab() 的单参数形式中需要

    实际上，SQL查询应始终指定ORDER BY 1,2，以确保输入行的顺序正确

    回复于
  • 0

    您可以使用additional module 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);
    回复于
  • 18
    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
    回复于
  • 257

    使用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
    回复于
  • 26

    对不起，这是不完整的，因为我不能在这里测试，但它可能会让你走向正确的方向 . 我正在翻译我使用的一些类似的查询：

    select mt.section, mt1.count as Active, mt2.count as Inactive
from mytable mt
left join (select section, count from mytable where status='Active')mt1
on mt.section = mt1.section
left join (select section, count from mytable where status='Inactive')mt2
on mt.section = mt2.section
group by mt.section,
         mt1.count,
         mt2.count
order by mt.section asc;

    我正在使用的代码是：

    select m.typeID, m1.highBid, m2.lowAsk, m1.highBid - m2.lowAsk as diff, 100*(m1.highBid - m2.lowAsk)/m2.lowAsk as diffPercent
from mktTrades m
   left join (select typeID,MAX(price) as highBid from mktTrades where bid=1 group by typeID)m1
   on m.typeID = m1.typeID
   left join (select typeID,MIN(price) as lowAsk  from mktTrades where bid=0 group by typeID)m2
   on m1.typeID = m2.typeID
group by m.typeID, 
         m1.highBid, 
         m2.lowAsk
order by diffPercent desc;

    这将返回一个typeID，最高价格出价和最低价格以及两者之间的差异（正差异意味着可以买入少于可以出售的东西） .

    回复于
  • 0

    Crosstab 功能在 tablefunc 扩展名下可用 . 您必须为数据库创建一次此扩展 .

    CREATE EXTENSION tablefunc ;

    您可以使用以下代码使用交叉表创建数据透视表：

    create table test_Crosstab( section text,

    status text,

    count numeric)


    insert into test_Crosstab values ( 'A','Active',1)
                
    ,( 'A','Inactive',2)
                
    ,( 'B','Active',4)
                
    ,( 'B','Inactive',5)

select * from crosstab(

    'select section
    
    ,status
    
    ,count
    
    from test_crosstab'
    
    )as ctab ("Section" text,"Active" numeric,"Inactive" numeric)
    回复于

相关问题