时间戳

首页 > 解决方案 > 使用 PostgreSQL 进行数据透视

postgresql - 使用 PostgreSQL 进行数据透视

问题描述

我在 PostgreSQL 12 中进行数据透视时遇到了麻烦。我必须使用一个构造笨拙的表。这是一个类似于我创建的表（仅用于简单表示）-

CREATE TABLE test(Product_num INT, SN INT, Attribute VARCHAR(100), Value DECIMAL, Note VARCHAR(50) );

下一步是使用 COPY 功能导入 CSV 文件 -

COPY public.test from 'C:\File Location\test.csv' DELIMITER ',' csv HEADER;

我得到一个超过 30k 行的表格，看起来像 -

Product_num |    SN    |   Attribute   |   Value   |  Note  |
    100         9225       Unit sold       50          USA
    100         9225       Unit price      4.99
    100         9225       Num_boxes       2.5
    101         9226       Unit sold       1           GER
    101         9226       Unit price      920
    101         9226       Num_boxes       2

我想要一张类似于下一张桌子的桌子-

Product_num |    SN    |   Unit Sold   |   Unit price   |  Num_boxes |   Note
    100     |    9225  |        50     |       4.99     |    2.5     |   USA           
    101     |    9226  |        1      |       920      |     2      |   GER

我尝试了几种方法，包括 crosstab() 并尝试聚合一些列，但遇到了一些问题。该表有一些问题需要考虑——

  1. 每个 SN 的属性列都不相同——这意味着每个 SN 具有不同的属性，有些相同，有些则不同。
  2. 有很多空值。
  3. 有很多 0 值。

我希望我能尽可能地解释自己谢谢

标签： postgresql

解决方案

我会将所有属性聚合到一个 JSON 结构中，然后在最终查询中提取它们：

select product_num, sn, 
       (attributes ->> 'Num_boxes')::decimal as num_boxes,
       (attributes ->> 'Unit sold')::decimal as unit_sold,
       (attributes ->> 'Unit price')::decimal as unit_price,
       note
from  (
  select product_num, sn, max(note) as note,
         jsonb_object_agg(attribute, value) as attributes
  from test
  group by product_num, sn
) t
order by product_num, sn;

如果添加了新属性，您将需要扩展外部 SELECT 列表以反映这一点。在不更改查询的情况下，您无法获得动态的列列表。在 SQL 中，在运行语句之前必须知道查询的列数和类型。

推荐阅读