In clickhouse, I have a table with columns line and time. How to find duplicate lines,…
- User
In clickhouse, I have a table with columns line and time. How to find duplicate lines, but only if time is <12s from the first one?
Assistant
To find duplicate lines in a ClickHouse table, where the duplicates occur within 12 seconds of each other (i.e., the second occurrence is within 12 seconds after the first), you can use a window function to compare rows with the same
lineand check time differences.Assuming your table looks like this:
CREATE TABLE my_table ( line String, time DateTime ) ENGINE = MergeTree ORDER BY time;Goal:
Find all rows that are duplicates (same
line) and occurred within 12 seconds after the first occurrence (or previous one).
✅ Solution Using Window Function
Here's a query that finds such duplicates:
SELECT * FROM ( SELECT line, time, prev_time, if(prev_time IS NOT NULL AND dateDiff('second', prev_time, time) <= 12, 1, 0) AS is_duplicate FROM ( SELECT *, lagInWindow(time, 1) OVER (PARTITION BY line ORDER BY time ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS prev_time FROM my_table ) AS t ) WHERE is_duplicate = 1;Explanation:
lagInWindow()gets the previous timestamp for the sameline.dateDiff('second', prev_time, time)computes the difference in seconds.- If the difference is ≤ 12 seconds, it marks as a duplicate (
is_duplicate = 1). - The outer query filters only those rows.
🧠 Notes:
- This finds subsequent occurrences within 12 seconds of the previous entry with the same
line. - If you want to group by time ranges or mark the first entry as not duplicate, you can adjust accordingly.
- You may want to add an
ORDER BY timeat the end to make the output easier to read.
Optional: Mark First Occurrence Only
If you want to filter out duplicates and keep only the first occurrence every >12 seconds, you can do:
SELECT * FROM ( SELECT *, lagInWindow(time, 1) OVER (PARTITION BY line ORDER BY time ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS prev_time FROM my_table ) WHERE prev_time IS NULL OR dateDiff('second', prev_time, time) > 12;This gives you the "first" message in each 12-second window per
line.
Let me know if you want to delete or count these duplicates!
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.