Re: Counting booleans in GROUP BY sections
От | Adrian Klaver |
---|---|
Тема | Re: Counting booleans in GROUP BY sections |
Дата | |
Msg-id | 8580a1cd-7a65-9dd1-50eb-d446aa8ea5c9@aklaver.com обсуждение исходный текст |
Ответ на | Re: Counting booleans in GROUP BY sections (Alexander Farber <alexander.farber@gmail.com>) |
Список | 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
В списке pgsql-general по дате отправления: