Re: Counting booleans in GROUP BY sections

[Alexander Farber <alexander.farber@xxxxxxxxx>]

My context is that I have a table of player moves with PK mid (aka "move id").

And I am able to find "interesting" moves by the high score or all 7 letter tiles used.

But I do some human reviewing and set a "puzzle" boolean for truly interesting moves.

For the reviewing tool I would like to display headers: a "Mon YYYY" plus the number of true puzzles per section.

Thanks to David's hint the following seems to work even though I wonder if it is the most optimal way to call TO_CHAR twice:

CREATE OR REPLACE FUNCTION words_list_puzzles(
                in_start interval,
                in_end interval
        ) RETURNS TABLE (
                out_label  text,
                out_count  bigint,
                out_puzzle boolean,
                out_mid    bigint,
                out_secret text,
                out_gid    integer,
                out_score  integer
        ) AS
$func$
    SELECT
        TO_CHAR(played, 'Mon YYYY') AS label,                                                                                -- used for header
        COUNT(NULLIF(puzzle, FALSE)) OVER (PARTITION BY TO_CHAR(played, 'Mon YYYY')), --used for header
        puzzle,
        mid,
        MD5(mid || 'my little secret') AS secret,
        gid,
        score
    FROM words_moves
    WHERE action = "">    AND LENGTH(hand) = 7
    AND (LENGTH(letters) = 7 OR score > 90)
    AND played > CURRENT_TIMESTAMP - in_start
    AND played < CURRENT_TIMESTAMP - in_end
    ORDER BY played DESC
$func$ LANGUAGE sql STABLE;

Regards

Alex

P.S: Below is my table description again and the output of the above function:

words_ru=> \d words_moves
                                      Table "public.words_moves"
 Column  |           Type           | Collation | Nullable |                 Default                  
---------+--------------------------+-----------+----------+------------------------------------------
 mid     | bigint                   |           | not null | nextval('words_moves_mid_seq'::regclass)
 action  | text                     |           | not null |
 gid     | integer                  |           | not null |
 uid     | integer                  |           | not null |
 played  | timestamp with time zone |           | not null |
 tiles   | jsonb                    |           |          |
 score   | integer                  |           |          |
 letters | text                     |           |          |
 hand    | text                     |           |          |
 puzzle  | boolean                  |           | not null | false
Indexes:
    "words_moves_pkey" PRIMARY KEY, btree (mid)
    "words_moves_gid_played_idx" btree (gid, played DESC)
    "words_moves_uid_action_played_idx" btree (uid, action, played)
    "words_moves_uid_idx" btree (uid)
Check constraints:
    "words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
    "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE
    "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE
Referenced by:
    TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid) ON DELETE CASCADE

words_ru=> select * from words_list_puzzles(interval '2 year', interval '1 year');
 out_label | out_count | out_puzzle | out_mid |            out_secret            | out_gid | out_score
-----------+-----------+------------+---------+----------------------------------+---------+-----------
 Nov 2018  |         1 | f          | 1331343 | 78467b5f3bde3d3f2291cf539c949f79 |   46134 |        28
 Nov 2018  |         1 | f          | 1326876 | e4928d3c34f50b8e6eabf7bad5b932fe |   46007 |        28
 Nov 2018  |         1 | f          | 1324466 | 6228ba509a7124f485feb5c1acbb6b68 |   45922 |        26
 Nov 2018  |         1 | f          | 1322050 | b67b091d383678de392bf7370c735cab |   45877 |        34
 Nov 2018  |         1 | f          | 1320017 | 35f03b0c7159cec070c00aa80359fd42 |   44255 |       120

.....

 May 2018  |         3 | f          |   95114 | e7e8bab64fab20f6fec229319e2bab40 |    7056 |        28
 May 2018  |         3 | f          |   88304 | 161c0638dede80f830a36efa6f428dee |    6767 |        40
 May 2018  |         3 | f          |   86180 | 4d47a65263331cf4e2d2956886b6a72f |    6706 |        26
 May 2018  |         3 | f          |   85736 | debb1efd673c91947a8aa7f38be4217c |    6680 |        28
 May 2018  |         3 | f          |   82522 | e55ec68a5a5dacc2bc463e397198cb1c |    6550 |        27
 Apr 2018  |         0 | f          |   78406 | f5d264ccfe94aaccd90ce6c019716d4d |    5702 |        58
 Apr 2018  |         0 | f          |   77461 | 404886e913b698596f9cf3648ddf6fa4 |    1048 |        26
(415 rows)