Thread: Counting booleans in GROUP BY sections

On 11/29/19 8:38 AM, Alexander Farber wrote:
> Good evening,
> 
> I am trying to count the booleans per each GROUP BY section by the 
> following stored function:
> 
> 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,
>          COUNT(NULLIF(puzzle, FALSE)), -- this only returns 0 or 1, why?

If I am following it is because you have mid in GROUP BY and mid is a 
PK. Since mid will always be unique you will have at most on row per group.

>          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
>      GROUP BY label, puzzle, mid, secret, gid, score
>      ORDER BY played DESC
> 
> $func$ LANGUAGE sql STABLE;
> 
> But when I run it, I only get 0 or 1 in the out_count column:
> 
> 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  |         0 | f          | 1326876 | 
> e4928d3c34f50b8e6eabf7bad5b932fe |   46007 |        28
>   Nov 2018  |         0 | f          | 1324466 | 
> 6228ba509a7124f485feb5c1acbb6b68 |   45922 |        26
>   Nov 2018  |         0 | f          | 1322050 | 
> b67b091d383678de392bf7370c735cab |   45877 |        34
>   Nov 2018  |         0 | f          | 1320017 | 
> 35f03b0c7159cec070c00aa80359fd42 |   44255 |       120
>   Nov 2018  |         0 | f          | 1319160 | 
> 83df42f7ad398bbb060fc02ddfdc62c0 |   45031 |        95
> .....
>   May 2018  |         0 | f          |  264251 | 
> 2fff1154962966b16a2996387e30ae7f |   10946 |        99
>   May 2018  |         1 | t          |  257620 | 
> 645613db6ea40695dc967d8090ab3246 |   12713 |        93
>   May 2018  |         0 | f          |  245792 | 
> bb75bfd9cb443ff541b199d893c68117 |   12359 |        24
>   May 2018  |         1 | t          |  243265 | 
> d899a5d642ccd96d931194f48ef56d53 |   11950 |       123
>   May 2018  |         0 | f          |  231953 | 
> ad53b5b2c0d4cced3d50e8b44ad53e55 |   11910 |        32
> 
> - while I was hoping to get 2 for the "May 2018" section.
> 
> What am I doing wrong please, why don't the values add up? Below is the 
> table desc:
> 
> 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
> 
> Thank you
> Alex
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 11/29/19 11:47 AM, Alexander Farber wrote:
> Thank you Adrian, but -

> 
> if I remove GROUP BY mid, then I get the error:
> 
> ERROR:  42803: column "words_moves.mid" must appear in the GROUP BY 
> clause or be used in an aggregate function
> LINE 18:         mid,
>                   ^
> LOCATION:  check_ungrouped_columns_walker, parse_agg.c:1369

Yes because it is a case of opposing forces. When you remove mid from 
the GROUP BY you get an single row for each group that has an aggregated 
output where you can have count of > 1. In that case the database has 
more then one choice for the mid to display and so it throws the error.

To go forward it would help to know what it is you are trying to achieve?

> 
> Regards
> Alex


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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