POSTGRES transpose week columns into rows?

Question

My forecast table stores the current week, and then forecasted amounts for 26 weeks out:

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
);

For example forecasting a single item, I select a single row with the most recent current_week_date and then look at relative weeks.

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

I would like to do this with Postgres, essentially fetching a row and transposing each weeks number to a row with a date column and count column:

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

Please clarify. asin, valid_as_of (you mean current_week_date?), and weekcurrent(weekplus0?) are not in your table definition. And what's with the "month" in the title? I lent a hand and replaced the list of columns with a CREATE TABLE statement we can work with. And some syntax fixes. Provide some sample values and your version of Postgres. I am pretty sure there is an elegant solution ... — Erwin Brandstetter
– Erwin Brandstetter, Commented Oct 5, 2017 at 1:27
@ErwinBrandstetter yeah i have slimmed the question down to a simple what is a better way to transpose this weekly data from column values into separated rows. — Blair Anderson
– Blair Anderson, Commented Oct 5, 2017 at 15:19

Erwin Brandstetter · Accepted Answer · 2017-10-06 02:35:05Z

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;

The key feature is a CROSS JOIN LATERAL to generate_series() to generate the desired rows. Use an ARRAY constructor like you already had in the subquery. The generated index w is used to add weeks to the base date as well as to access respective array items. Take note of a lurking off-by-1 error, since Postgres array indexes are 1-based by default. Related:

Since week_current_date does seem to allow NULL values, you may want to use ORDER BY week_current_date DESCNULLS LAST to sort rows with NULL last which would otherwise be on top. See:

PostgreSQL sort by datetime asc, null first?

Collectives™ on Stack Overflow

POSTGRES transpose week columns into rows?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related