[GENERAL] consolidating data with window functions

[Andrew Geery]

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

[Ladislav Lenart]

Post by Andrew Geery
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.
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)
)
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

Hello.

The same transformation with a recursive CTE:


WITH RECURSIVE
source AS (
SELECT *
FROM (
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)
) AS t(a,b,c)
)
, grouped_source AS (
SELECT
source.*
, 1 AS r
FROM source
WHERE source.a = 1
UNION ALL
SELECT
source.*
, (CASE WHEN grouped_source.c IS NULL AND source.c IS NULL
THEN grouped_source.r
ELSE grouped_source.r + 1
END) AS r
FROM
grouped_source
JOIN source ON source.a = grouped_source.a + 1
)
SELECT
r AS a,
string_agg(b, '') AS b,
MIN(c) AS c
FROM grouped_source
GROUP BY r
ORDER BY r


Though I do not know which one is easier to read / understand (for you) nor
which one performs better.

HTH,

Ladislav Lenart