POSTGRES переносит недельные столбцы в строки?

В моей таблице прогнозов хранится текущая неделя, а затем прогнозируемые суммы на 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.

postgresql sql unpivot generate-series
person Blair Anderson    schedule 04.10.2017    source источник
comment
Просьба уточнить. asin, valid_as_of (вы имеете в виду current_week_date?) и weekcurrent(weekplus0?) не входят в определение вашей таблицы. А что за месяц в названии? Я помог и заменил список столбцов оператором CREATE TABLE, с которым мы можем работать. И некоторые исправления синтаксиса. Укажите несколько примеров значений и свою версию Postgres. Я почти уверен, что есть элегантное решение...   -  person Erwin Brandstetter    schedule 05.10.2017
comment
@ErwinBrandstetter да, я сократил вопрос до простого, как лучше перенести эти еженедельные данные из значений столбца в отдельные строки.   -  person Blair Anderson    schedule 05.10.2017
comment
Так у вас есть ответ?   -  person Erwin Brandstetter    schedule 26.03.2020

Ответы (1)


arrow_upward
0
arrow_downward 

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() для создания нужных строк. Используйте конструктор ARRAY. как у вас уже было в подзапросе. Сгенерированный индекс w используется для добавления недель к базовой дате, а также для доступа к соответствующим элементам массива. Обратите внимание на скрытую ошибку смещения на 1, поскольку индексы массива Postgres по умолчанию отсчитываются от 1. Связанный:

Поскольку week_current_date, похоже, допускает значения NULL, вы можете использовать ORDER BY week_current_date DESCNULLS LAST для сортировки строк с NULL последними, которые в противном случае были бы сверху. Видеть:

person Erwin Brandstetter    schedule 05.10.2017