当前位置: 动力学知识库 > 问答 > 编程问答 >

LAG function in SQL Server 2012

问题描述:

I am using SQL Server 2012.

Here is my query:

create table #t(id int,amt int)

insert into #t values(1, 100), (1, 200), (2, 300), (2, 500), (2, 800)

select

id, amt,

lag(amt, 1, null) over (partition by id order by amt) as prev_amt,

lead(amt, 1, null) over (partition by id order by amt) as next_value

from #t

Returning:

id amt prev_amt next_value

----------------------------

1 100 NULL 200

1 200 100 NULL

2 300 NULL 500

2 500 300 800

2 800 500 NULL

When I change the query to

select

id, amt,

lag(amt, 1, null) over (partition by id order by amt desc) as prev_amt,

lead(amt, 1, null) over (partition by id order by amt desc) as next_value

from #t

Result is:

id amt prev_amt next_value

1 200 NULL 100

1 100 200 NULL

2 800 NULL 500

2 500 800 300

2 300 500 NULL

I am not understanding why it gives this?

 id amt prev_amt next_value

1 200 NULL 100

Previous amount is supposed to be 100?

网友答案:

The key word here is WINDOW functions. Actually you have working windows and these functions are working in those windows.

For first statement you have window:

/---------\
| 1   100 |
| 1   200 |
\---------/

For row 1 the previous value is NULL because you are out of window(above), next is 200. For row 2 previous is 100, next is NULL because you are out of window(below).

In second statement window has changed:

/---------\
| 1   200 |
| 1   100 |
\---------/

For row 1 the previous value is still NULL because you are out of window(above), next is 100. For row 2 previous is 200, next is still NULL because you are out of window(below).

分享给朋友:
您可能感兴趣的文章:
随机阅读: