Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Distinct row aggregation with ordering - stackoverflow.com/q/43249053/4116017
--PostgreSQL 9.6 --'\\' is a delimiter CREATE TABLE events AS WITH args AS ( SELECT 30 AS scale_factor, -- feel free to reduce this to speed up local testing 1000 AS pa_count, 1 AS l_count_min, 29 AS l_count_rand, 10 AS c_count, 10 AS pr_count, 3 AS r_count, '10 days'::interval AS time_range -- edit 2017-05-02: the real data set has years worth of data here, but the query time ranges stay small (a couple days) ) SELECT p.c_id, 'ABC'||lpad(p.pa_id::text, 13, '0') AS pa_id, 'abcdefgh-'||((random()*(SELECT pr_count-1 FROM args)+1))::int AS pr_id, ((random()*(SELECT r_count-1 FROM args)+1))::int AS r, '2017-01-01Z00:00:00'::timestamp without time zone + random()*(SELECT time_range FROM args) AS t FROM ( SELECT pa_id, ((random()*(SELECT c_count-1 FROM args)+1))::int AS c_id, (random()*(SELECT l_count_rand FROM args)+(SELECT l_count_min FROM args))::int AS l_count FROM generate_series(1, (SELECT pa_count*scale_factor FROM args)) pa_id ) p JOIN LATERAL ( SELECT generate_series(1, p.l_count) ) l(id) ON (true); --CREATE INDEX ix_events ON events USING btree (c_id, t DESC, pr_id, pa_id, r); --CREATE INDEX ix_events2 ON events USING btree (c_id, t ASC, pr_id, pa_id, t DESC, r); CREATE INDEX ix_events3 ON events USING btree (c_id, pa_id, pr_id, t DESC, r); --EXPLAIN (ANALYZE, VERBOSE) SELECT pr_id, r, count(1) AS quantity FROM ( SELECT DISTINCT ON (pr_id, pa_id) pr_id, pa_id, r FROM events WHERE c_id = 5 AND t >= '2017-01-03Z00:00:00' AND t < '2017-01-06Z00:00:00' ORDER BY pr_id, pa_id, t DESC ) latest GROUP BY 1, 2 ORDER BY 3, 2, 1 DESC ; --EXPLAIN (ANALYZE, VERBOSE) WITH CTE_RN AS ( SELECT pa_id ,pr_id ,r ,ROW_NUMBER() OVER (PARTITION BY c_id, pa_id, pr_id ORDER BY t DESC) AS rn FROM events WHERE c_id = 5 AND t >= '2017-01-03Z00:00:00' AND t < '2017-01-06Z00:00:00' ) SELECT pr_id ,r ,COUNT(*) AS quantity FROM CTE_RN WHERE rn = 1 GROUP BY pr_id ,r ORDER BY quantity, r, pr_id DESC ;
run
|
edit
|
history
|
help
0
book suggestion
filme
Return IDs where there are exactly 3 occurrences
postgresql example hierarchy with addresses
Select the latest id from last week from each different contact without repeating barcodes.....
nasa2
w3c sql data
Join elements by key with json fields
Assignment 1(SET A)
user