Re: consolidating data with window functions

On 1.8.2016 18:48, Andrew Geery wrote:
> 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

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