Search Postgresql Archives

Re: Counting booleans in GROUP BY sections

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

 



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@xxxxxxxxxxx





[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