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
filme
funkcia_transakcie
podd version
lol
Q2
enrj
avg with subquery
a
2
asd