snippetMinor
How to query status changes in time series?
Viewed 0 times
querytimestatuschangeshowseries
Problem
Given
I want a query, which returns all rows, where the rating changes for a given person.
For example here I want to exclude the row ( 3, '2010-08-10' , 'G1') because the rating didn't change since ( 3, '2010-08-04' , 'G1').
I know a solution using a cursor, but I wonder, if it could be done without cursors.
I tried a bit with analytical functions, but didn't found a solution.
Besides the solution, please improve the terms with which I describe the problem. I guess it is some standard task.
Edit:
Here is the link to connect asking to add the lag and lead functions to SQL Server.
Please vote.
create table rating (
ID int identity(1,1) primary key,
PersonID int,
Ratingdate date,
rating varchar(2)
);
insert into rating values ( 1, '2010-08-04' , 'A3');
insert into rating values ( 1, '2010-08-14' , 'A1');
insert into rating values ( 2, '2010-08-04' , 'G2');
insert into rating values ( 2, '2010-08-14' , 'G1');
insert into rating values ( 3, '2010-08-04' , 'G1');
insert into rating values ( 3, '2010-08-10' , 'G1');
insert into rating values ( 3, '2010-08-14' , 'G3');
insert into rating values ( 2, '2010-08-20' , 'G4');I want a query, which returns all rows, where the rating changes for a given person.
For example here I want to exclude the row ( 3, '2010-08-10' , 'G1') because the rating didn't change since ( 3, '2010-08-04' , 'G1').
I know a solution using a cursor, but I wonder, if it could be done without cursors.
I tried a bit with analytical functions, but didn't found a solution.
Besides the solution, please improve the terms with which I describe the problem. I guess it is some standard task.
Edit:
Here is the link to connect asking to add the lag and lead functions to SQL Server.
Please vote.
Solution
It seems like you want something like
which yields the following
select *
from (select person_id,
rating_date,
rating,
lag(rating) over (partition by person_id
order by rating_date) prior_rating
from rating)
where rating != prior_rating
or prior_rating is null
order by person_id, rating_datewhich yields the following
SQL> ed
Wrote file afiedt.buf
1 select person_id,
2 rating_date,
3 rating
4 from (select person_id,
5 rating_date,
6 rating,
7 lag(rating) over (partition by person_id
8 order by rating_date) prior_rating
9 from rating)
10 where rating != prior_rating
11 or prior_rating is null
12* order by person_id, rating_date
SQL> /
PERSON_ID RATING_DAT RATING
---------- ---------- --------
1 2010-08-04 A3
1 2010-08-14 A1
2 2010-08-04 G2
2 2010-08-14 G1
2 2010-08-20 G4
3 2010-08-04 G1
3 2010-08-14 G3
7 rows selected.Code Snippets
select *
from (select person_id,
rating_date,
rating,
lag(rating) over (partition by person_id
order by rating_date) prior_rating
from rating)
where rating != prior_rating
or prior_rating is null
order by person_id, rating_dateSQL> ed
Wrote file afiedt.buf
1 select person_id,
2 rating_date,
3 rating
4 from (select person_id,
5 rating_date,
6 rating,
7 lag(rating) over (partition by person_id
8 order by rating_date) prior_rating
9 from rating)
10 where rating != prior_rating
11 or prior_rating is null
12* order by person_id, rating_date
SQL> /
PERSON_ID RATING_DAT RATING
---------- ---------- --------
1 2010-08-04 A3
1 2010-08-14 A1
2 2010-08-04 G2
2 2010-08-14 G1
2 2010-08-20 G4
3 2010-08-04 G1
3 2010-08-14 G3
7 rows selected.Context
StackExchange Database Administrators Q#3407, answer score: 5
Revisions (0)
No revisions yet.