Aggregate function in comparison of 2 rows in the same table (SQL)
Given the table definition:
create table mytable (
id integer,
mydate datetime,
myvalue integer )
I want to get the following answer by a single SQL query:
id date_actual value_actual date_previous value_previous
where:
date_previous is the maximum of all the dates preceeding date_a开发者_开发技巧ctual
for each id and values correspond with the two dates
{max(date_previous) < date_actual ?}
How can I achieve that?
Thanks for your hints
This is a variation of the common "greatest N per group" query which comes up every week on StackOverflow.
SELECT m1.id, m1.mydate AS date_actual, m1.myvalue AS value_actual,
m2.mydate AS date_previous, m2.myvalue AS value_previous
FROM mytable m1
LEFT OUTER JOIN mytable m2
ON (m1.id = m2.id AND m1.mydate > m2.mydate)
LEFT OUTER JOIN mytable m3
ON (m1.id = m3.id AND m1.mydate > m3.mydate AND m3.mydate > m2.mydate)
WHERE m3.id IS NULL;
In other words, m2
is all rows with the same id
and a lesser mydate
, but we want only the one such that there is no row m3
with a date between m1
and m2
. Assuming the dates are unique, there will only be one row in m2
where this is true.
Assuming I understood your requirements correctly, here's something you can try.
select a.id,
a.mydate as date_actual,
a.value as value_actual,
b.date as date_previous,
b.value as value_previous
from mytable a, mytable b
where a.id = b.id and
a.mydate > b.mydate and
b.mydate = (select max(mydate) from mytable c where c.id = a.id and c.mydate < a.mydate)
Apologies for the ugly SQL. I am sure there are better ways of doing this.
精彩评论