使用交叉表和计数的数据透视表 [英] Pivot table using crosstab and count

问题描述

我必须显示如下表格：

年份	月	已交付	未送达	未收到
2021	1月	10	86	75
2021	2月份	13	36	96
2021	三月	49	7	61
2021	4月	3	21	72

使用此查询生成的原始数据：

SELECT 
    year,
    TO_CHAR( creation_date, 'Month') AS month,
    marking,
    COUNT(*) AS count 
FROM invoices
GROUP BY 1,2,3

我已尝试使用crosstab()，但遇到错误：

SELECT * FROM crosstab('
    SELECT 
        year,
        TO_CHAR( creation_date, ''Month'') AS month,
        marking,
        COUNT(*) AS count 
    FROM invoices
    GROUP BY 1,2,3
') AS ct(year text, month text, marking text)

我不希望手动键入所有标记值，因为它们很多。

ERROR:  invalid source data SQL statement
DETAIL:  The provided SQL must return 3 columns: rowid, category, and values.

推荐答案

1.marking限值静电解决方案：

SELECT year
     , TO_CHAR( creation_date, 'Month') AS month
     , COUNT(*) FILTER (WHERE marking = 'Delivered') AS Delivered
     , COUNT(*) FILTER (WHERE marking = 'Not delivered') AS "Not delivered"
     , COUNT(*) FILTER (WHERE marking = 'Not Received') AS "Not Received"
FROM invoices
GROUP BY 1,2

2.marking值列表完整的动态解决方案：

此建议是A和B中建议的crosstab解决方案的替代解决方案。

这里提出的解决方案只需要一个可以动态创建的专用composite type，然后依赖于jsonb类型和标准函数：

从统计每年、每月和marking值的查询开始：

• 使用jsonb_object_agg函数，结果行排在第一位 按年和月聚合为jsonb个对象，其jsonb keys 对应于marking值，其jsonb values 与计数对应。
• 然后使用jsonb_populate_record函数和专用复合类型将产生的jsonb对象转换为记录。

首先，我们动态创建一个composite type，它对应于marking值的有序列表：

CREATE OR REPLACE PROCEDURE create_composite_type() LANGUAGE plpgsql AS $$
DECLARE
  column_list text ;
BEGIN
  SELECT string_agg(DISTINCT quote_ident(marking) || ' bigint', ',' ORDER BY quote_ident(marking) || ' bigint' ASC)
    INTO column_list
    FROM invoices ;
  
  EXECUTE 'DROP TYPE IF EXISTS composite_type' ;
  EXECUTE 'CREATE TYPE composite_type AS (' || column_list || ')' ;
END ;
$$ ;

CALL create_composite_type() ;

则由以下查询提供预期结果：

SELECT a.year
     , TO_CHAR(a.year_month, 'Month') AS month
     , (jsonb_populate_record( null :: composite_type
                             , jsonb_object_agg(a.marking, a.count)
                             )
       ).*
 FROM
    ( SELECT year
           , date_trunc('month', creation_date) AS year_month
           , marking
           , count(*) AS count
        FROM invoices AS v
       GROUP BY 1,2,3
    ) AS a
GROUP BY 1,2
ORDER BY month

显然，如果marking值的列表可能随时间变化，那么您必须在执行查询之前重新调用create_composite_type()过程。如果您不更新composite_type，查询仍将工作(没有错误！)但是一些旧的标记值可能已经过时(不再使用)，而一些新的标记值可能在查询结果中丢失(不显示为列)。

在dbfiddle中查看完整演示。

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