Distinct row aggregation with ordering - stackoverflow.com/q/43249053/4116017, PostgreSQL

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