Question

请允许我说,我有一个表格“用户——id,日期,分数”,每个用户每个月都有一个记分,但并非总在同一天。

我想问的是,在“日期”和下个月之间,“日期”和“日期”之间的分数会有多少变化? 我将不得不做一些事情,比如说什么?

Answer 1

这里有一种方式(斜体计算法留待读者):

CREATE TABLE scores (user_id VARCHAR2(32), test_date DATE, score NUMBER);

INSERT INTO scores VALUES( U1 ,SYSDATE-61, 85);
INSERT INTO scores VALUES( U1 ,SYSDATE-31, 89);
INSERT INTO scores VALUES( U1 ,SYSDATE, 92);
INSERT INTO scores VALUES( U2 ,SYSDATE-61, 65);
INSERT INTO scores VALUES( U2 ,SYSDATE-31, 89);
INSERT INTO scores VALUES( U2 ,SYSDATE, 84);

COMMIT;

SELECT s1.user_id, s1.test_date, s2.score-s1.score delta
  FROM scores s1 
       JOIN (SELECT user_id, trunc(test_date, MM ) test_date, score FROM scores) s2
         ON (s1.user_id = s2.user_id AND
             trunc(add_months(s1.test_date,1), MM ) = s2.test_date);

USER_ID                          TEST_DATE        DELTA
-------------------------------- ---------   ----------
U1                               9/15/2009            3
U1                               8/16/2009            4
U2                               9/18/2009           -5
U2                               8/19/2009           24

www.un.org/Depts/DGACM/index_spanish.htm 时间已晚,因此,我决定看看看一下10格人提出的分析功能(进一步把自己拖入本世纪;-)并利用LAG功能重新表述上述功能:

SELECT user_id, test_date, score
     , LAG(score, 1, NULL) OVER (PARTITION BY user_id ORDER BY test_date DESC) - score delta
     , LAG(score, 1, NULL) OVER (PARTITION BY user_id ORDER BY test_date DESC) AS next_score
  FROM scores
 ORDER BY 1, 2 DESC;

生产:

USER_ID                          TEST_DATE        SCORE      DELTA NEXT_SCORE
-------------------------------- ----------- ---------- ---------- ----------
U1                               10/19/2009          92            
U1                               9/18/2009           89          3         92
U1                               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

看看,马! 无自我参与! Now (除此以外,解释性计划表明自雇工作效率不高。)

作为跳板,我开始使用。

Answer 2

与此类似,应当把用户——id、日期和分数上下个月。

select user_id, date, score  from table where
date between ((select sysdate from dual)
and (select add_months(sysdate, 1) FROM dual));

友情链接