Postgres转置周列成排

Question

我的预测表存储了当前一周，然后预测数量为26周：

CREATE TABLE forecast_listings (
   product    integer NOT NULL
 , current_week_date date 
 , weekplus0  integer NOT NULL DEFAULT 0
 , weekplus1  integer NOT NULL DEFAULT 0
 , weekplus2  integer NOT NULL DEFAULT 0
 , weekplus3  integer NOT NULL DEFAULT 0
 , weekplus4  integer NOT NULL DEFAULT 0
 -- etc
 , weekplus24 integer NOT NULL DEFAULT 0
 , weekplus25 integer NOT NULL DEFAULT 0
);

例如，预测单个项目，我选择一个具有最新current_week_date的一行，然后查看相对几周。

SELECT
unnest(
    array[
    to_char(week_current_date, 'YYYY-MM-DD'),
    to_char(week_current_date + interval '1 weeks', 'YYYY-MM-DD'),
    to_char(week_current_date + interval '2 weeks', 'YYYY-MM-DD'),
    to_char(week_current_date + interval '3 weeks', 'YYYY-MM-DD'),
    to_char(week_current_date + interval '4 weeks', 'YYYY-MM-DD')
    -- ...all the way to 25
    ]
) AS "Week",
unnest(
    array[
    weekplus0,
    weekplus1, 
    weekplus2, 
    weekplus3,
    weekplus4
    -- ...all the way to 25
    ]
) AS "Count"
FROM (
    SELECT * FROM forecast_listings 
    WHERE product_id = 1
    ORDER BY week_current_date DESC
    LIMIT 1
) as row

我想使用Postgres进行此操作，从本质上提取一行并将每个星期的编号转换为日期列和计数列：

：

week, count
2017-10-01,100
2017-10-08,200
2017-10-15,150
etc.

Answer 1

SELECT to_char(f.week_current_date + interval '1 week' * w, 'YYYY-MM-DD')
     , arr[w+1]
FROM  (
   SELECT week_current_date
        , ARRAY[weekplus0, weekplus1, weekplus2, weekplus3] AS arr  -- add all 26
   FROM   forecast_listings
   WHERE  product_id = 1
   ORDER  BY week_current_date DESC NULLS LAST  -- ?
   LIMIT  1
   ) f
CROSS  JOIN LATERAL generate_series(0, 25) w;

关键功能是CROSS JOIN LATERAL到generate_series()，以生成所需的行。使用像您在子查询中已经拥有的数组构造函数。生成的索引w用于将几周添加到基本日期以及访问各个数组项目。请注意潜伏的偏离1错误，因为Postgres数组索引默认情况下是基于1的。相关：

• postgresql中的横向和子查询有什么区别？
• 如何在Postgresql中脱离表格
• postgresql unnest（）带有元素编号
• 对1维数组的数组订阅标准标准归为标准，因此它们以1
开始
• 生成给定月的一系列周间隔

由于week_current_date似乎确实允许null值，因此您可能需要使用ORDER BY week_current_date DESC NULLS LAST 将行与Null last分类，否则将其排在上面。请参阅：

• datetime asc的postgresql排序，首先是null？