sql - 甲骨文 SQL : Previous MAX sore in all previous quarters

Question

用于获取所有季度中每个 ID 的 MAX 先前分数的最佳方法。

给定:

    ID  QTR SCORE 
    21  1   3 
    21  2   5 
    21  3   3 
    21  4   3 
    41  1   2 
    41  2   2 
    41  3   4 
    41  4   2 

预期:

    ID  QTR PREV_MAX_SCORE
    21  1   3   
    21  2   5   
    21  3   5   
    21  4   5   
    41  1   2   
    41  2   2   
    41  3   4
    41  4   4

Answer 1

可以尝试使用MAX窗函数来制作。

CREATE TABLE T(
  ID int,
  QTR int,
  SCORE int
);


insert into t values (21,1,3); 
insert into t values (21,2,5); 
insert into t values (21,3,3); 
insert into t values (21,4,3); 
insert into t values (41,1,2); 
insert into t values (41,2,2); 
insert into t values (41,3,4); 
insert into t values (41,4,2); 

查询 1:

SELECT t1.ID,
       t1.QTR,
       max(SCORE) over(partition by ID order by QTR) SCORE
FROM T t1

Results :

| ID | QTR | SCORE |
|----|-----|-------|
| 21 |   1 |     3 |
| 21 |   2 |     5 |
| 21 |   3 |     5 |
| 21 |   4 |     5 |
| 41 |   1 |     2 |
| 41 |   2 |     2 |
| 41 |   3 |     4 |
| 41 |   4 |     4 |