SQL Server中的Last_Value

2020-02-22 sql sql-server
with cte
as
(
SELECT 
    year(h.orderdate)*100+month(h.orderdate) as yearmonth,
    YEAR(h.orderdate) as orderyear,
    sum(d.OrderQty*d.UnitPrice) as amount

  FROM [AdventureWorks].[Sales].[SalesOrderDetail] d
        inner join sales.SalesOrderHeader h
        on d.SalesOrderID=h.SalesOrderID
 group by
    year(h.orderdate)*100+month(h.orderdate),
    year(h.orderdate)
)

select
    c.*,
    last_value(c.amount) over (partition by c.orderyear order by c.yearmonth) as lastvalue,
    first_value(c.amount) over (partition by c.orderyear order by c.yearmonth) as firstvalue
from cte c
order by c.yearmonth

我期望看到每年的最后一个值(例如12月的值),类似于每年的一次的值(jan值)。但是,last_value根本不起作用。它只返回该月的相同值。我做错什么了?

谢谢您的帮助。

在此处输入图片说明

Answers

您的问题是LAST_VALUE的默认行范围是LAST_VALUE RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ,因此您获得的值是当前月份的值(即该范围内的最后一个值)。为了使LAST_VALUE能够查看分区中的所有值,您需要扩展范围以包括当前行之后的行。因此,您需要将查询更改为:

last_value(c.amount) over (partition by c.orderyear order by c.yearmonth
                           rows between unbounded preceding and unbounded following) as lastvalue,

Related