How do I get entries in the next month from oracle sql? -
Suppose I have a table that has "user_id, date, score", and every user has exactly one score per month, but not always the same day.
I want a query that contains "user_id, date, score_delta", where the score_data is, how much will change between "date" and next month? Will I have to lame for some time (to_char (date, ...?
here in a way (Score is as an exercise for the reader):
create table score (user_id VARCHAR2 (32), test_date DATE, score number); Score value (IN 'U1' , INSERT ('U1', SYSDATE-31, 89) in the values of digits, INSERT ('U1', SYSDATE, 92) in the values of digits; INSERT ('U2' SYSDATE-61, 65); INSERT ('U2', SYSDATE-31, 89) in the properties of numbers; INSERT ('U2', SYSDATE, 84) in the values of numbers; D; SELECT s1.user_id, s1.test_date, s2.score-s1 (score: score by score) at s2 (s1.user_id = s2.user_id and trunc (plus_memo (s1.test_date, 1), 'mm' ) = S2.test_date); USER_ID TEST_DATE DELTA -------------------------------- -------- - ---------- U 9/15/2009 3 U1 8/16/2009 4 U2 9/18/2009 -5 U2 8/19/2009 24Edit : This is a slow afternoon, so I decided to check out this analytical work content, which is 10G offer (dragging myself into the next century ;-), And using the LAG function again writes the above: Score Delta, Lag (Score, 1, Zero) Over (score):
test user_id, test_date, score, lag (score, 1, zero) over (by exam_data) According to user_id ORDER BY test_date DESC) Ordering by scores to the next score is 1, 2 DESC;
Which produces:
USER_ID TEST_DATE SCORE DELTA NEXT_SCORE ------------------ --- ----------- ----------- ---------- ---------- ----- --- - U1 10/19/2009 92 U19/18/2009 89 3 92 U 1 8/19/2009 85 4 89 U2 10/19/2009 84 U2 9/18/2009 89 -5 84 U2 8/19/2009 65 24 89 Look, ma! No self involved! Now it is clever ;-) (On one side, the plan to explain is not as efficient as self-assembly).
As a springboard, I started with it.