2

As a follow-up question to my previous question on difficult row categorisation, I have another problem which I'm sure will be easily solvable. After this I plan on reading a good book or two on databases and SQL but for the time being I need to overcome this brick wall.

The table in question has the following layout:

TaskID             -  -  -  1. On-site
DateStart         |         2. Return-to-base
DateEnd           |         3. Chargeable
Type -  -  -  -  -          4. Complaint
Reference                   5. Rebook
Requester
Approver
Cost
...

I want to select all "repeat" TaskID. TaskID is a unique ID but a "repeat" is classed as any two tasks that share the same Reference and where the DateEnd of each task is within a 30 day period, so, I'm thinking something along the lines of:

SELECT BOTH TaskID, Reference
FROM   Tasks
WHERE  Row1.Reference = Row2.Reference AND ABS(Row1.DateEnd - Row2.DateEnd) <= 30;

Obviously the above isn't valid but hopefully it illustrates how I want to select the data. I'm guessing it will involve a self-join but I have no clue where to start... having the query return both TaskIDs with the reference is ideal but if it only returns one TaskID then that's no problem.

I'm version of PostgreSQL that I'm using is 9.1.3, if that helps.

Thanks for your help.

Improve this question

2 Answers 2

Reset to default
2 
SELECT *
FROM   task t1
WHERE  EXISTS (
   SELECT 1
   FROM   task t2
   WHERE  t2.reference = t1.reference  -- same reference
   AND    t2.task_id  <> t1.task_id    -- different task_id (exclude self-join)
   AND    t2.end_date BETWEEN t1.end_date - 30
                      AND     t1.end_date + 30 
   )

An EXISTS semi-join should be the fastest way to arrive at DISTINCT rows, because you don't get duplicates to begin with. Also, in the event of multiple matching rows (many matching tasks withing 30 days) evaluation can stop after the first match is found. I would expect this query to beat anything you have so far. Test with EXPLAIN ANALYZE.

In a WHERE clause or JOIN condition don't use an expression like:

abs(t1.end_date - t2.end_date) < 30

If you can avoid it. As the left hand expression is derived from values of two tables, the only way for the query planner is to form a limited CROSS JOIN (after applying other conditions) and compute a value for every possible combination. This is a well known anti-pattern for good performance!

As long as there are only few rows per reference, it won't matter much. But the cost grows with O(N²) with more rows per reference. I rewrote the condition to:

t2.end_date BETWEEN t1.end_date - 30 AND t1.end_date + 30

In my tests, the first form went from 5x slower to 500x slower quickly when I narrowed down the id-space for reference (-> more matching rows).

The second form can also more easily use indexes. If you want to select a small sample of the table, with conditions like:

reference > 450
date_end > now()::date

And an index like:

CREATE INDEX test_idx ON task (reference, date_end);
CREATE INDEX test_idx2 ON task (date_end);

The performance gap becomes even more overwhelming.

Improve this answer
6
  • 1
    Thanks for the answer and explanation. I understand the problems with the previous attempt now. So far, I have found at most 6 references within 30 days. I EXPLAINed both queries and your solution is far simpler (and faster). It takes only 900ms to look for all repeats using your query but about 2300ms to look for all repeats using Chris's approach. Like I've mentioned in other questions, I'm not a DBA so reading advice like this is incredibly useful. I had never really thought about the implications of putting two values from two tables on the same side of an expression before (wrt X JOINS). Commented Oct 6, 2012 at 23:02
  • @dreamlax: Thanks for the feedback with results. It's always good to get confirmation. Commented Oct 8, 2012 at 9:24
  • Just wondering, is it possible to use windows/aggregates for this? The reason I ask is that I am trying to "lay blame" on the second to last occurrence of any group of repeat tasks. I am using SELECT * FROM (SELECT row_number() OVER (PARTITION BY t.reference ORDER BY t.date DESC) AS r, t.* FROM repeat_tasks_view t) x WHERE x = 2 (where repeat_tasks_view is the view based on the query in your answer) and this works at finding which task ids have blame, but it is slow, it is about 300ms to run this query even though it is only 100ms to run the repeat_tasks_view query. Any ideas? Commented Nov 1, 2012 at 1:06
  • Basically, the last person to work on a task is the person who was supposed to have completed it successfully, so if there is a repeat task, the person who was last working on it has "blame". Commented Nov 1, 2012 at 1:10
  • (those times were based on one months worth of data, e.g. both queries had WHERE end_date BETWEEN '2012-10-01'::DATE AND '2012-10-31'::DATE) Commented Nov 1, 2012 at 1:14
3

how about this:

 SELECT t1.task_id, t1.reference
   FROM tasks t1
   JOIN tasks t2 
        ON t1.reference = t2.reference 
           AND abs(t1.end_date - t2.end_date) < 30;

Note this assumes that end_date is a date, and not a timestamp. If a timestamp cast to date first.

Improve this answer
6
  • This is returning single instances as well as repeats, because for single instances the self join condition is met (the references from t1 and t2 are the same and the difference between the dates is less than 30), but, if I put abs(t1.dateend - t2.dateend) between 1 and 30) then it rules out single instances. I'm checking through the data now to see if there are legitimate repeats on the same day but if this is unlikely then this will be the fastest / easiest approach. Commented Oct 5, 2012 at 3:54
  • if you get rid of the abs() it will get rid of the first instance. Commented Oct 5, 2012 at 4:03
  • and between is probably a good thing too. Commented Oct 5, 2012 at 4:14
  • This is working really well now, it's returning extra rows still even after removing the ABS(), e.g. when there are four repeats within 30 days, I get (t1+t2), (t1+t3), (t1+t4), (t2+t3), (t2+t4), (t3+t4), but I'll work around this. Commented Oct 5, 2012 at 5:15
  • another approach you could use is array_agg and group by. Something like select t1.reference, array_agg(t1.task_id) from tasks t1 join tasks t2 on t1.reference=t2.reference and t1.end_date - t2.end_date between 1 and 30 group by t1.reference But then you will get a list id's for a reference. Commented Oct 5, 2012 at 5:34

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.