By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE orders (
customer_id int,
reg_date timestamp
);
INSERT INTO orders VALUES
(1, '2019-05-13 12:34:34'),
(1, '2019-05-13 12:35:34'), -- no duplicate, more than 1 second
(2, '2019-05-13 12:34:34'),
(2, '2019-05-13 12:34:34'), -- duplicate, identical
(3, '2019-05-13 12:34:34'),
(3, '2019-05-13 12:34:35'), -- duplicate, 1 second diff
(4, '2019-05-13 12:34:34'),
(4, '2019-05-13 12:34:34'),
(4, '2019-05-13 12:34:35'), -- three duplicates
(5, '2019-05-13 12:34:34'),
(5, '2019-05-13 12:34:35'),
(5, '2019-05-13 12:35:34'), -- one duplicate, one not
(6, '2019-05-13 12:35:34'); -- only one record, no duplicate
SELECT * FROM orders;
13 rows affected
| customer_id | reg_date |
|---|---|
| 1 | 2019-05-13 12:34:34 |
| 1 | 2019-05-13 12:35:34 |
| 2 | 2019-05-13 12:34:34 |
| 2 | 2019-05-13 12:34:34 |
| 3 | 2019-05-13 12:34:34 |
| 3 | 2019-05-13 12:34:35 |
| 4 | 2019-05-13 12:34:34 |
| 4 | 2019-05-13 12:34:34 |
| 4 | 2019-05-13 12:34:35 |
| 5 | 2019-05-13 12:34:34 |
| 5 | 2019-05-13 12:34:35 |
| 5 | 2019-05-13 12:35:34 |
| 6 | 2019-05-13 12:35:34 |
SELECT
*,
reg_date - lag(reg_date) OVER (PARTITION BY customer_id ORDER BY reg_date) <= interval '1 second' as is_duplicate
FROM
orders
| customer_id | reg_date | is_duplicate |
|---|---|---|
| 1 | 2019-05-13 12:34:34 | null |
| 1 | 2019-05-13 12:35:34 | f |
| 2 | 2019-05-13 12:34:34 | null |
| 2 | 2019-05-13 12:34:34 | t |
| 3 | 2019-05-13 12:34:34 | null |
| 3 | 2019-05-13 12:34:35 | t |
| 4 | 2019-05-13 12:34:34 | null |
| 4 | 2019-05-13 12:34:34 | t |
| 4 | 2019-05-13 12:34:35 | t |
| 5 | 2019-05-13 12:34:34 | null |
| 5 | 2019-05-13 12:34:35 | t |
| 5 | 2019-05-13 12:35:34 | f |
| 6 | 2019-05-13 12:35:34 | null |
SELECT
customer_id,
reg_date
FROM (
SELECT
*,
reg_date - lag(reg_date) OVER (PARTITION BY customer_id ORDER BY reg_date) <= interval '1 second' as is_duplicate
FROM
orders
) s
WHERE is_duplicate
| customer_id | reg_date |
|---|---|
| 2 | 2019-05-13 12:34:34 |
| 3 | 2019-05-13 12:34:35 |
| 4 | 2019-05-13 12:34:34 |
| 4 | 2019-05-13 12:34:35 |
| 5 | 2019-05-13 12:34:35 |