March 1, 2002 at 12:39 am
I have a table which gathers measurements one / row asynchronically. That's why one column is datetime. Idnr is the primary key.
If I want to know daily average period between inserts, I must use this kind of self-join (sorry,I'm in Oracle mode now, syntax differs slightly)
SELECT t1.datetime,avg(t2.datetime-t1.datetime)
FROM testresult t1, testresult t2
WHERE t1.idnr +1 = t2.idnr AND other restrictions
GROUP BY to_char(datetime,'YYYY-MM-DD')
/* in T-SQL GROUP BY datepart(something) */
Is this the only way except using INNER JOIN ? Table is huge, 30e6 rows. Is this efficient query ?
I call this 'unconventional join' because t1.idnr != t2.idnr. What kind of unconventional joins have you made ?
March 1, 2002 at 11:17 am
March 1, 2002 at 5:17 pm
The only thing that may make this any better would be to make sure that t1.idnr +1 does = t2.idnr would be to do a subquery something like (SELECT TOP 1 FROM testresults as INTR WHERE INTR.idnr > t1.idnr) in case you have any gaps in the column as far as next item. However you will need to keep the join and the subquery may hurt performance quite a bit. I do not have an Oracle server to test on but you might try a decode to check that t1.idnr +1 = t2.idnr then use t1.idnr +1 = t2.idnr or if not use the subquery method, which should bennefit overall, but again you may need to fix this a bit based on Oracle. As for unconvential, I have ended up doing some amazingly bad joins like this that I had no way around. Hope this didn't come out unreadable.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply