-
-
Save dbenhur/951b7bae4e07d7998268 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Filter an array of events such that there is only one event with each event_id. | |
-- When more than one event with the same event_id, is present, take the latest one. | |
CREATE OR REPLACE FUNCTION dedupe_events_2(events HSTORE[]) RETURNS HSTORE[] AS $$ | |
SELECT array_agg(event) | |
FROM ( | |
-- select first of elements with the same event_id by position in the array, descending. | |
SELECT first_value(event) as event | |
OVER (PARTITION BY (event -> 'event_id')::BIGINT ORDER BY index DESC) | |
FROM ( | |
-- Use unnest instead of generate_subscripts to turn an array into a set. | |
SELECT event, row_number() as index | |
OVER (ORDER BY event -> 'time') | |
FROM unnest(events) AS event | |
) unnested_data | |
ORDER BY index ASC | |
) deduped_events | |
$$ LANGUAGE SQL IMMUTABLE; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment