Search Postgresql Archives

STRING_AGG and GROUP BY

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Good afternoon,

I have prepared an SQL Fiddle for my question: http://sqlfiddle.com/#!17/4ef8b/2

Here are my 4 test tables:

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)
);

Here they are filled with test data (two players Alice and Bob interchangeably performing moves in game #1):

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": "P"}]
'::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, 'PP', 20),
(6, 2, 1, 'PABCD', 50);

For a PHP-script which would display all moves+words+scores played in a certain game I am trying:

SELECT
    mid,
    STRING_AGG(x->>'letter', '') AS tiles,
    STRING_AGG(DISTINCT y, ', ') AS words
FROM (
    SELECT
        mid,
        JSONB_ARRAY_ELEMENTS(m.tiles) AS x,
        FORMAT('%s (%s)', s.word, s.score) AS y
    FROM moves m
    LEFT JOIN scores s
    USING (mid)
    WHERE m.gid = 1
) AS z
GROUP BY mid;

Which produces a slightly wrong result (the played letters are duplicated):

mid   tiles                    words
1     AACD                 AACD (40)
2     XXZXXZ             XAB (30), XXZ (30)
3     KKMNKKMN      KKMN (40), KYZ (30)
5     ABCD                 ABCD (40)
6     PPPP                 PABCD (50), PP (20)

My expected result would actually be:

mid   tiles                    words
1     AACD                 AACD (40)
2     XXZ                    XAB (30), XXZ (30)
3     KKMN                KKMN (40), KYZ (30)
5     ABCD                 ABCD (40)
6     PP                      PABCD (50), PP (20)

Is that actually possible by the means of SQL or should I do it in the PHP script?

And I can not change the query to: STRING_AGG(DISTINCT x->>'letter', '') AS tiles,

Because for example in the last move with mid=6 the player Bob had played 2 tiles, both with letter-value "P" and has formed 2 words (PP and PABCD), but adding distinct would suggest he played a single tile "P".

Thank you
Alex






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux