consolidating data with window functions

From: Andrew Geery <andrew(dot)geery(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: consolidating data with window functions
Date: 2016-08-01 16:48:19
Message-ID: CANdn4xLPYuUSLxQY5qGM43AevFi=oKUv5eKu9M7yr9CzBWdaxA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a data-set with
- a line number
- a name
- a value

I want to select the rows in line number order, but I want to consolidate
consecutive rows into a single row, concatenating the names, if the value
is null.

For example, here's my data:

values (
(1, 'a', 1),
(2, 'b', 2),
(3, 'c', null),
(4, 'd', null),
(5, 'e', 3),
(6, 'f', null),
(7, 'g', null),
(8, 'h', 4),
(9, 'i', null),
(10, 'j', 5)
)

I want to transform the data into this:

values (
(1, 'a', 1),
(2, 'b', 2),
(3, 'cd', null),
(4, 'e', 3),
(5, 'fg', null),
(6, 'h', 4),
(7, 'i', null),
(8, 'j', 5)
)

Below is what I came up with. The "u" table computes an amount to add to
get the next logical row number; the "x" table actually computes the
logical row number; finally we group by the logical row number and use
string_agg to get a single name for each row.

Is there an easier way to write this query, using some window function
functionality that I'm not aware of :)?

Thanks
Andrew

with
t (line_number, my_name, my_value) as (values
(1, 'a', 1),
(2, 'b', 2),
(3, 'c', null),
(4, 'd', null),
(5, 'e', 3),
(6, 'f', null),
(7, 'g', null),
(8, 'h', 4),
(9, 'i', null),
(10, 'j', 5)),
u as (
select
line_number,
my_name,
my_value,
case when lag(my_value, 1) over (order by line_number) is null then case
when my_value is null then 0 else 1 end else 1 end amount_to_add
from
t),
x as (
select
line_number,
my_name,
my_value,
sum(amount_to_add) over (order by line_number) logical_line
from
u)
select
logical_line,
string_agg(my_name, ''),
my_value
from
x
group by
logical_line,
my_value
order by
logical_line

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rakesh Kumar 2016-08-01 16:48:57 Can stored procedures be deployed online
Previous Message Alvaro Herrera 2016-08-01 15:52:20 Re: pg_archivecleanup standalone bash script