Re: STRING_AGG and GROUP BY

[Alexander Farber]

Thank you, David -

On Fri, Mar 16, 2018 at 5:40 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Fri, Mar 16, 2018 at 9:10 AM, Alexander Farber <
> alexander.far...@gmail.com> wrote:
>
>> ​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.
>
>
I've come up with the following query, wonder if you meant something
similar -

http://sqlfiddle.com/#!17/4ef8b/48

WITH cte1 AS (
SELECT
    mid,
    STRING_AGG(x->>'letter', '') AS tiles
FROM (
        SELECT
            mid,
            JSONB_ARRAY_ELEMENTS(m.tiles) AS x
        FROM moves m
        WHERE m.gid = 1
) AS z
GROUP BY mid),
cte2 AS (
        SELECT
        mid,
        STRING_AGG(y, ', ') AS words
    FROM (
        SELECT
            mid,
            FORMAT('%s (%s)', s.word, s.score) AS y
        FROM scores s
        WHERE s.gid = 1
) AS z
GROUP BY mid)
SELECT mid, tiles, words
FROM cte1 JOIN cte2 using (mid) ORDER BY mid ASC;

Regards
Alex

P.S. Below is the complete test data in case SQL Fiddle link stops working:

CREATE TABLE players (
    uid SERIAL PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE games (
    gid SERIAL PRIMARY KEY,
    player1 integer NOT NULL REFERENCES players ON DELETE CASCADE,
    player2 integer NOT NULL REFERENCES players ON DELETE CASCADE
);

CREATE TABLE moves (
    mid BIGSERIAL PRIMARY KEY,
    uid integer NOT NULL REFERENCES players ON DELETE CASCADE,
    gid integer NOT NULL REFERENCES games ON DELETE CASCADE,
    played timestamptz NOT NULL,
    tiles jsonb NOT NULL
);

CREATE TABLE scores (
    mid     bigint  NOT NULL REFERENCES moves ON DELETE CASCADE,
    uid     integer NOT NULL REFERENCES players ON DELETE CASCADE,
    gid     integer NOT NULL REFERENCES games ON DELETE CASCADE,
    word    text    NOT NULL CHECK(word ~ '^[A-Z]{2,}$'),
    score   integer NOT NULL CHECK(score >= 0)
);

INSERT INTO players (name) VALUES ('Alice'), ('Bob');
INSERT INTO games (player1, player2) VALUES (1, 2);

INSERT INTO moves (uid, gid, played, tiles) VALUES
(1, 1, now() + interval '1 min', '[{"col": 7, "row": 12, "value": 3,
"letter": "A"}, {"col": 8, "row": 12, "value": 10, "letter": "A"}, {"col":
9, "row": 12, "value": 1, "letter": "C"}, {"col": 10, "row": 12, "value":
2, "letter": "D"}]
'::jsonb),
(2, 1, now() + interval '2 min', '[{"col": 7, "row": 12, "value": 3,
"letter": "X"}, {"col": 8, "row": 12, "value": 10, "letter": "X"}, {"col":
9, "row": 12, "value": 1, "letter": "Z"}]
'::jsonb),
(1, 1, now() + interval '3 min', '[{"col": 7, "row": 12, "value": 3,
"letter": "K"}, {"col": 8, "row": 12, "value": 10, "letter": "K"}, {"col":
9, "row": 12, "value": 1, "letter": "M"}, {"col": 10, "row": 12, "value":
2, "letter": "N"}]
'::jsonb),
(2, 1, now() + interval '4 min', '[]'::jsonb),
(1, 1, now() + interval '5 min', '[{"col": 7, "row": 12, "value": 3,
"letter": "A"}, {"col": 8, "row": 12, "value": 10, "letter": "B"}, {"col":
9, "row": 12, "value": 1, "letter": "C"}, {"col": 10, "row": 12, "value":
2, "letter": "D"}]
'::jsonb),
(2, 1, now() + interval '6 min', '[{"col": 7, "row": 12, "value": 3,
"letter": "P"}, {"col": 8, "row": 12, "value": 10, "letter": "Q"}]
'::jsonb);

INSERT INTO scores (mid, uid, gid, word, score) VALUES
(1, 1, 1, 'AACD', 40),
(2, 2, 1, 'XXZ', 30),
(2, 2, 1, 'XAB', 30),
(3, 1, 1, 'KKMN', 40),
(3, 1, 1, 'KYZ', 30),
(5, 1, 1, 'ABCD', 40),
(6, 2, 1, 'PQ', 20),
(6, 2, 1, 'PABCD', 50);