Re: Counting booleans in GROUP BY sections - Mailing list pgsql-general

On 11/30/19 4:08 AM, Alexander Farber wrote:
> 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:

Given that played contains values, I assume, that are at multiple points 
in a month and you want the 'group' to be a month it looks alright to 
me. Though if it bothers you then another option is date_trunc():

test=# select date_trunc('month', '11/02/2019 13:00'::timestamp), 
date_trunc('month', '11/23/2019 13:00'::timestamp);
      date_trunc      |     date_trunc
---------------------+---------------------
  11/01/2019 00:00:00 | 11/01/2019 00:00:00


> 
> 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 = 'play'
>      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)


-- 
Adrian Klaver
adrian.klaver@aklaver.com