Re: STRING_AGG and GROUP BY

Поиск
Список
Период
Сортировка
От David G. Johnston
Тема Re: STRING_AGG and GROUP BY
Дата
Msg-id CAKFQuwb0XniewZr+42vHs=A=x=w72gaSZ4wCoXSRSLqjDAXE_w@mail.gmail.com
обсуждение исходный текст
Ответ на Re: STRING_AGG and GROUP BY  (Alexander Farber <alexander.farber@gmail.com>)
Ответы Re: STRING_AGG and GROUP BY
Список pgsql-general
On Fri, Mar 16, 2018 at 9:10 AM, Alexander Farber <alexander.farber@gmail.com> wrote:

But you say that "tiles" and  "word (score)" are unrelated and this does not seem true to me:

For each move id aka "mid" there is a JSON value, describing how the player played the letter tiles.
And for the same "mid" there is a list of one or more "word (score)"s achieved...

​For each mid you want to know all tiles played and all word scores achieved - but you want to forget/ignore that a given tile achieved a given word score.  IOW, you are intentionally forgetting/ignoring the fact that the tiles and the corresponding word scores are related to each other, beyond the simple/incomplete relationship that both share the same mid.  You thus need to write a query that only relates tiles and word scores to mid and not to each other.


Maybe it is possible to GROUP BY tiles somehow or some kind of special SQL JOIN?


​I do not know if the tables or columns below match your model but the concept should still come across intact.​

​SELECT mid,
(SELECT string_agg(tiles, '; ') FROM tiles WHERE ta.mid = moves.mid) AS mid_tiles,
(SELECT string_agg(words, '; ') FROM words WHERE words.mid = moves.mid) AS mid_words
FROM moves​

There are other ways to write that that could perform better but the idea holds.

David J.

В списке pgsql-general по дате отправления:

Предыдущее
От: Alexander Farber
Дата:
Сообщение: Re: STRING_AGG and GROUP BY
Следующее
От: Charlin Barak
Дата:
Сообщение: ora2pg and invalid command \N