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
