Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Blog
Distinct row aggregation with ordering - stackoverflow.com/q/43249053/...
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
--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 ;
absolute service time: 8,31 sec
edit mode
|
history
|
discussion
pr_id
r
quantity
1
abcdefgh-1
3
178
2
abcdefgh-10
3
179
3
abcdefgh-10
1
181
4
abcdefgh-1
1
193
5
abcdefgh-6
1
289
6
abcdefgh-9
3
294
7
abcdefgh-2
1
299
8
abcdefgh-7
1
307
9
abcdefgh-5
3
307
10
abcdefgh-3
3
307
11
abcdefgh-8
1
310
12
abcdefgh-2
3
311
13
abcdefgh-7
3
313
14
abcdefgh-6
3
316
15
abcdefgh-8
3
317
16
abcdefgh-1
2
333
17
abcdefgh-5
1
338
18
abcdefgh-10
2
343
19
abcdefgh-4
3
348
20
abcdefgh-9
1
350
21
abcdefgh-4
1
352
22
abcdefgh-3
1
360
23
abcdefgh-4
2
599
24
abcdefgh-3
2
631
25
abcdefgh-8
2
635
26
abcdefgh-7
2
637
27
abcdefgh-5
2
642
28
abcdefgh-9
2
659
29
abcdefgh-2
2
662
30
abcdefgh-6
2
684
pr_id
r
quantity
1
abcdefgh-1
3
178
2
abcdefgh-10
3
179
3
abcdefgh-10
1
181
4
abcdefgh-1
1
193
5
abcdefgh-6
1
289
6
abcdefgh-9
3
294
7
abcdefgh-2
1
299
8
abcdefgh-7
1
307
9
abcdefgh-5
3
307
10
abcdefgh-3
3
307
11
abcdefgh-8
1
310
12
abcdefgh-2
3
311
13
abcdefgh-7
3
313
14
abcdefgh-6
3
316
15
abcdefgh-8
3
317
16
abcdefgh-1
2
333
17
abcdefgh-5
1
338
18
abcdefgh-10
2
343
19
abcdefgh-4
3
348
20
abcdefgh-9
1
350
21
abcdefgh-4
1
352
22
abcdefgh-3
1
360
23
abcdefgh-4
2
599
24
abcdefgh-3
2
631
25
abcdefgh-8
2
635
26
abcdefgh-7
2
637
27
abcdefgh-5
2
642
28
abcdefgh-9
2
659
29
abcdefgh-2
2
662
30
abcdefgh-6
2
684