Today I’ll talk about how the line error function (lag,lead) uses the window function.

Lag(exp_str,offset,defval) over() Lead(exp_str,offset,defval) over() -- the column exp_str is going to take --offset takes the first line of the offset --defval: No qualified default value

Here is the full record for table “test_student_score”.

SQL> select t.* from test_student_score t; STUDENT_ID SUBJECT_ID SCORE ---------- ---------- ---------- 1 1 90 3 4 91 3 1 93 3 3 94 3 2 94 1 4 95 2 2 95 2 4 97 2 1 Sentaku 2 2 3 98 1 3 99 12 line が Sentaku 2 2 2 98 Sentaku 2 3 99 2 Sentaku 2 2 2 2 98 2 3 99 12 line が Sentaku 2 2 2

Let’s look at the original output without these two functions:

SQL> select * from test_student_score t where t.subject_id = 3;

STUDENT_ID SUBJECT_ID      SCORE
---------- ---------- ----------
         1          3         99
         2          3         98
         3          3         94

Now we’re going to look not only at the score, but also at the score ahead of him.

SQL> select t.subject_id,
       t.subject_id,
       lag(t.score, 1, -1) over(order by t.score) as lags,
       t.score
  from test_student_score t
where t.subject_id = 3;  2    3    4    5    6

SUBJECT_ID SUBJECT_ID       LAGS      SCORE
---------- ---------- ---------- ----------
         3          3         -1         94
         3          3         94         98
         3          3         98         99

The “LAGs” are the “scores” of the previous one.

Now let’s take a look at the score that comes after him.

SQL> select t.subject_id, t.subject_id, lag(t.score, 1, -1) over(order by t.score) as lags, t.score, lead(t.score, 1, -1) over(order by t.score) as leads from test_student_score t where t.subject_id = 3; SUBJECT_ID SUBJECT_ID LAGS SCORE LEADS ---------- ---------- ---------- ---------- ---------- 3 3 -1 94 98 3 3 94 98 99 3, 3, 98, 99 minus 1

‘leads’ is the’ score ‘of the second one.

Do you get it?

2021/04/19 @ Dalian