Postgres 中多个字段的交叉表

Question

Windows 上的 Postgres 13。假设我有一个这样的表：

Job_type                Department      User_id     Op_code     Times_executed
-------------------------------------------------------------------------------------
ACCOUNTING MANAGER      Accounting     Alice        VA01        578
ACCOUNTING MANAGER      Accounting     Alice        FBL1N       2256
ACCOUNTING MANAGER      Accounting     Alice        FBL3N       16272
ACCOUNTING MANAGER      Accounting     Alice        ME23N       1682
ACCOUNT ASSISTANT       General        Bob          ME2L        876
ACCOUNT ASSISTANT       General        Bob          VA04        25700
ACCOUNT ASSISTANT       General        Bob          VF77        12393
ACCOUNT ASSISTANT       General        Bob          MEXX        5182

我想使用 Postgres 中的 crosstab 函数来获得这样的数据透视表：

Job_type            Department     User_id  Op1     times1  Op2     times2  Op3    times3   Op4    times4
ACCOUNTING MANAGER  Accounting     Alice    VA01    578     FBL1N   2256    FBL3N   16272   ME23N   1682
ACCOUNT ASSISTANT   General        Bob      ME2L    876     VA04    25700   VF77    12393   MEXX    5182

我尝试用我的交叉表基础知识，但我只设法获取操作代码，但不能获取时间:

SELECT * FROM crosstab(
  'select user_id, job_type, tcode 
   from mytable   
   order by 1,2')
AS ct(row_name text, category_1 text, category_2 text, category_3 text, category_4 text);   

可以按照上面的格式全部获取吗？

预先非常感谢。

Answer 1

这是 crosstab() 函数的替代解决方案：

您创建一个与要显示的列列表相对应的复合类型：

CREATE TYPE new_type AS (op1 text, times1 integer, op2 text, times2 integer, op3 text, times3 integer, op4 text, times4 integer, op5 text, times5 integer, op6 text, times6 integer, op7 text, times7 integer, op8 text, times8 integer, op9 text, times9 integer, op10 text, times10 integer, op11 text, times11 integer, op12 text, times12 integer, op13 text, times13 integer, op14 text, times14 integer, op15 text, times15 integer, op16 text, times16 integer, op17 text, times17 integer, op18 text, times18 integer, op19 text, times19 integer, op20 text, times20 integer)

以下查询将提供预期结果：

SELECT Job_type, Department, User_id
     , (jsonb_populate_record(null :: new_type, jsonb_object_agg(key1, val1) || jsonb_object_agg(key2, val2))).*
FROM
   ( SELECT Job_type, Department, User_id
          , 'op' || row_number () OVER w AS key1
          , Op_code AS val1
          , 'times' || row_number () OVER w AS key2
          , Times_executed AS val2
       FROM my_table AS t
       WINDOW w AS (PARTITION BY Job_type, Department, User_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
   ) AS a
GROUP BY Job_type, Department, User_id

请参阅 < a href="https://dbfiddle.uk/?rdbms=postgres_14&fiddle=b889de5b07bd2e04d8b78332a644fdd8" rel="nofollow noreferrer">dbfiddle。