红移数据库中的多列动态旋转 [英] Multiple columns dynamic pivoting in redshift database

问题描述

我有一个如下所示的输入红移数据集：

car_id | driver_id | salesman | tyre_id | price_min | price_max | price_avg
-------+-----------+----------+---------+-----------+-----------+----------
A      |         1 |        1 |       9 |         1 |         1 |         1
A      |         2 |        1 |       9 |         1 |         1 |         1
A      |         3 |        1 |       9 |         1 |         1 |         1
A      |         4 |        1 |       9 |         1 |         1 |         1
A      |         1 |        2 |       7 |         0 |         1 |         1
A      |         2 |        2 |       7 |         0 |         0 |         1
A      |         3 |        2 |       7 |         0 |         1 |         1
A      |         4 |        2 |       7 |         0 |         0 |         0

我希望透视多个列，这些列的值可能是动态的。我想用值Price_min、Price_avg、Price_max透视列Salesman，tyre_id。基本上，每个driver_id将有1行。

我可以用python实现它，但是我需要一个查询来满足我的要求。

这是预期输出：

我试着将Salesman和tyreId连接起来。它部分起作用，但我需要专家社区的帮助才能完全实现这一点。有人能帮助我并引导我完成这件事吗？

这是我尝试的查询：

CREATE TABLE public.temp_car_id 
(
  car_id      VARCHAR(10),
  driver_id   INT,
  salesman    INT,
  tyre_id     INT,
  price_min   INT,
  price_max   INT,
  price_avg   INT
);

INSERT INTO public.temp_car_id
VALUES
('A',1,1,9,0.61,0.89,0.91),
('A',2,1,9,0.63,0.93,0.58),
('A',3,1,9,0.91,0.83,0.99),
('A',4,1,9,0.53,0.84,0.79),
('A',1,2,7,0.12,0.97,0.87),
('A',2,2,7,0.13,0.30,0.84),
('A',3,2,7,0.17,0.62,0.63),
('A',4,2,7,0.09,0.01,0.19);
     

SELECT car_id,
       driver_id,
       CONCAT('.',CONCAT(CONCAT(salesman,'.'), tyre_id)) AS sales_tyre,
       price_min,
       price_max,
       price_avg INTO #temp_car_id_unpivot
FROM public.temp_car_id;

SELECT *
FROM #temp_car_id_unpivot PIVOT (MAX(price_min) 
     FOR
     sales_tyre IN ('.1.9','.2.7'));

sql

对于您给出的示例，我下面有一些更通用的推荐答案，但这是针对您在示例中放入的静电案例。不确定您是否在寻找通用/动态透视功能，但如果是这样，您将需要创建某种类型的SQL生成器，该生成器将您想要透视的列值作为输入。这个生成器可以是RedShift外部代码或存储过程，但不能在RedShift的Basic SQL中实现。下面的代码可以用作与这样的SQL生成器一起使用的模板。

设置(您将一些十进制值定义为int)：

CREATE TABLE public.temp_car_id 
(
  car_id      VARCHAR(10),
  driver_id   INT,
  salesman    INT,
  tyre_id     INT,
  price_min   decimal(8,2),
  price_max   decimal(8,2),
  price_avg   decimal(8,2)
);

INSERT INTO public.temp_car_id
VALUES
('A',1,1,9,0.61,0.89,0.91),
('A',2,1,9,0.63,0.93,0.58),
('A',3,1,9,0.91,0.83,0.99),
('A',4,1,9,0.53,0.84,0.79),
('A',1,2,7,0.12,0.97,0.87),
('A',2,2,7,0.13,0.30,0.84),
('A',3,2,7,0.17,0.62,0.63),
('A',4,2,7,0.09,0.01,0.19);

SQL代码：

select car_id, driver_id,
  min(avg_price_min_s1_t9) as avg_price_min_s1_t9,
  min(avg_price_max_s1_t9) as avg_price_max_s1_t9,
  min(avg_price_avg_s1_t9) as avg_price_mavg_s1_t9,
  min(avg_price_min_s2_t7) as avg_price_min_s2_t7,
  min(avg_price_max_s2_t7) as avg_price_max_s2_t7,
  min(avg_price_avg_s2_t7) as avg_price_mavg_s2_t7
from (
  SELECT car_id,
    driver_id,
    avg(case when tyre_id = 9 and salesman = 1 then price_min end) over( partition by car_id, driver_id, salesman, tyre_id)
        as avg_price_min_s1_t9,
    avg(case when tyre_id = 9 and salesman = 1 then price_max end) over( partition by car_id, driver_id, salesman, tyre_id)
        as avg_price_max_s1_t9,
    avg(case when tyre_id = 9 and salesman = 1 then price_avg end) over( partition by car_id, driver_id, salesman, tyre_id)
        as avg_price_avg_s1_t9,
    avg(case when tyre_id = 7 and salesman = 2 then price_min end) over( partition by car_id, driver_id, salesman, tyre_id)
        as avg_price_min_s2_t7,
    avg(case when tyre_id = 7 and salesman = 2 then price_max end) over( partition by car_id, driver_id, salesman, tyre_id)
        as avg_price_max_s2_t7,
    avg(case when tyre_id = 7 and salesman = 2 then price_avg end) over( partition by car_id, driver_id, salesman, tyre_id)
        as avg_price_avg_s2_t7
  FROM public.temp_car_id ) a
group by 1,2
order by 1,2;

不清楚您要使用哪个聚合函数来组合这些值，因为每种类型只有1个值，所以我使用avg()。

这篇关于红移数据库中的多列动态旋转的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！