PostgreSQL - объединение нескольких строк с несколькими атрибутами в одну строку?

У меня есть такая таблица:

DATE        ID    ScoreA    ScoreB    ScoreC
20180101    001   91        92        25
20180101    002   81        82        35
20180101    003   71        52        45
20180102    001   82        15        66
20180102    002   69        67        77
...
20180131    003   88        65        73

Возьмем, например, данные за месяц. Я хочу объединить их в отчет о показателях MAX и MIN, используя только одну строку для каждого ID, Как:

ID    ScoreA       Date_A              ScoreB        Date_B            ...
001   MAX(ScoreA)  MAX(ScoreA).DATE    MAX(ScoreB)   MAX(ScoreB).DATE  ...
002   MAX(ScoreA)  MAX(ScoreA).DATE    MAX(ScoreB)   MAX(ScoreB).DATE  ...
003   MAX(ScoreA)  MAX(ScoreA).DATE    MAX(ScoreB)   MAX(ScoreB).DATE  ...

куда MAX(ScoreA).DATE означает DATE когда появилось соответствующее значение MAX или MIN (если значение MAX появилось в несколько дат, просто выберите один случайным образом)

Не похожий на обычные случаи комбинированных строк, он включает несколько столбцов одновременно. И так как будет много ID и сотни Score s (я имею в виду, есть ScroeAScroreB... ScoreZ... Score1Score2... Score100...), я надеюсь избежать использования операций потребления, таких как JOIN Таблица. Так есть хорошие идеи?

Источник

3 ответа

Решение

Если вы хотите избежать присоединений, я бы предложил такую ​​конструкцию

WITH cte AS (
    SELECT DATE, ID, ScoreA, ScoreB, ScoreC,
        row_number() over (partition by ID order by ScoreA desc) rnA,
        row_number() over (partition by ID order by ScoreB desc) rnB,
        row_number() over (partition by ID order by ScoreC desc) rnC,
    FROM ...
    WHERE DATE BETWEEN ... AND ...
), ids AS (
    SELECT DISTINCT ID FROM cte
)
SELECT ID, 
    (SELECT ScoreA FROM cte t2 WHERE t2.ID = t.ID AND rnA = 1) ScoreA, 
    (SELECT DATE FROM cte t2 WHERE t2.ID = t.ID AND rnA = 1) Date_A,
    (SELECT ScoreB FROM cte t2 WHERE t2.ID = t.ID AND rnB = 1) ScoreB, 
    (SELECT DATE FROM cte t2 WHERE t2.ID = t.ID AND rnB = 1) Date_B,
    (SELECT ScoreC FROM cte t2 WHERE t2.ID = t.ID AND rnC = 1) ScoreC, 
    (SELECT DATE FROM cte t2 WHERE t2.ID = t.ID AND rnC = 1) Date_C
FROM ids t

Когда вам нужна дата или какой-либо другой атрибут значения max/min, разумно использовать нумерацию строк вместо агрегирующих функций: row_number() over (...) as rn с последующим условием rn = 1

UPD

Как только @TaurusDang хочет генерировать код, мое решение позволяет postgres выполнять почти всю работу:

WITH cols AS
(
    SELECT column_name
    FROM information_schema.columns
    WHERE table_schema = 'your_schema'
      AND table_name   = 'your_table'
      AND column_name like 'Score%'
)
-- first part: rows for cte subquery
SELECT ',row_number() over (partition by ID order by ' || column_name || ' desc) rn' || column_name
FROM cols
UNION ALL
-- second part: rows for final query
SELECT ',(SELECT ' || column_name || ' FROM cte t2 WHERE t2.ID = t.ID AND rn' || column_name || ' = 1) ' || column_name || ', (SELECT DATE FROM cte t2 WHERE t2.ID = t.ID AND rn' || column_name || ' = 1) Date_' || column_name
FROM cols

Просто скопируйте сгенерированные строки в исходный запрос: первую половину - в cte, а вторую - в основной запрос.

Источник

Это еще один пример кода, который предоставит вам все необходимые данные:

select *
from (select
        distinct on (id) id,
        first_value(scorea) over w as a_min,
        last_value(scorea) over w as a_max,
        first_value(date) over w as a_min_d,
        last_value(date) over w as a_max_d
    from the_table
    window w as (partition by id order by scorea)
    order by 1,3 desc) a
join (select
        distinct on (id) id,
        first_value(scoreb) over w as b_min,
        last_value(scoreb) over w as b_max,
        first_value(date) over w as b_min_d,
        last_value(date) over w as b_max_d
    from the_table
    window w as (partition by id order by scoreb)
    order by 1,3 desc) b using(id)
join (select
        distinct on (id) id,
        first_value(scorec) over w as c_min,
        last_value(scorec) over w as c_max,
        first_value(date) over w as c_min_d,
        last_value(date) over w as c_max_d
    from the_table
    window w as (partition by id order by scorec)
    order by 1,3 desc) c using(id)

Обратите внимание, что есть 3 отдельных подзапроса, по одному для каждого столбца оценки. Здесь происходит некоторое волшебство относительно оконных функций и разделов, о которых было бы полезно прочитать. Одна сложная часть здесь заключается в том, что различные разделы будут мешать друг другу, если помещены в один и тот же запрос (по крайней мере, на моей странице 9.3.22).

Источник

Попробуй это

with max_score as
(
    Select    distinct id
        , max(ScoreA) over( partition by id ) as max_ScoreA
        , max(ScoreB) over( partition by id ) as max_ScoreB
        , max(ScoreC) over( partition by id ) as max_Scorec
    from TABLE_NAME
)
Select 
    cte.id
    , max_ScoreA, tbl_a.DATE
    , max_ScoreB, tbl_b.DATE
    , max_ScoreC, tbl_c.DATE
from 
max_score cte
join TABLE_NAME tbl_a
on cte.id = tbl_a.id
and cte.max_ScoreA = tbl_a.ScoreA
join TABLE_NAME tbl_b
on cte.id = tbl_b.id
and cte.max_ScoreB = tbl_b.ScoreB
join TABLE_NAME tbl_c
on cte.id = tbl_c.id
and cte.max_ScoreC = tbl_c.ScoreC
order by 1
Источник
Другие вопросы по тегам