SQL: ostatnia wartość () zwraca zły wynik (ale pierwsza wartość () działa dobrze)

Oto szybkie zapytanie, aby zilustrować zachowanie:

select 
  v,

  -- FIRST_VALUE() and LAST_VALUE()
  first_value(v) over(order by v) f1,
  first_value(v) over(order by v rows between unbounded preceding and current row) f2,
  first_value(v) over(order by v rows between unbounded preceding and unbounded following) f3,
  last_value (v) over(order by v) l1,
  last_value (v) over(order by v rows between unbounded preceding and current row) l2,
  last_value (v) over(order by v rows between unbounded preceding and unbounded following) l3,

  -- For completeness' sake, let's also compare the above with MAX()
  max        (v) over() m1,
  max        (v) over(order by v) m2,
  max        (v) over(order by v rows between unbounded preceding and current row) m3,
  max        (v) over(order by v rows between unbounded preceding and unbounded following) m4
from (values(1),(2),(3),(4)) t(v)

Wyjście powyższego zapytania można zobaczyć tutaj ( sqlfiddle tutaj):

| V | F1 | F2 | F3 | L1 | L2 | L3 | M1 | M2 | M3 | M4 |
|---|----|----|----|----|----|----|----|----|----|----|
| 1 |  1 |  1 |  1 |  1 |  1 |  4 |  4 |  1 |  1 |  4 |
| 2 |  1 |  1 |  1 |  2 |  2 |  4 |  4 |  2 |  2 |  4 |
| 3 |  1 |  1 |  1 |  3 |  3 |  4 |  4 |  3 |  3 |  4 |
| 4 |  1 |  1 |  1 |  4 |  4 |  4 |  4 |  4 |  4 |  4 |

Niewiele osób myśli o niejawnych ramkach, które są stosowane do funkcji okien, które przyjmują klauzulę ORDER BY. W tym przypadku okna są domyślne do ramki RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. (Zakres nie jest dokładnie taki sam jak wiersze, ale to już inna historia). Pomyśl o tym w ten sposób:

• w rzędzie z v = 1 uporządkowaną ramką okna spans v IN (1)
• w wierszu z v = 2 ramka okna uporządkowanego rozciąga się v IN (1, 2)
• w wierszu z v = 3 rama okna uporządkowanego rozciąga się v IN (1, 2, 3)
• w wierszu z v = 4 rama okna uporządkowanego rozciąga się v IN (1, 2, 3, 4)

Jeśli chcesz zapobiec takiemu zachowaniu, masz dwie opcje:

• użyj jawnej klauzuli ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING dla uporządkowanych funkcji okien
• użyj klauzuli ORDER BY w tych funkcjach okien, które pozwalają na ich pominięcie (jako MAX(v) OVER())

Więcej szczegółów znajdziesz w tym artykule o LEAD(), LAG(), FIRST_VALUE() oraz LAST_VALUE()

Najprostszym sposobem jest powtórzenie zapytania za pomocą first_value, po prostu zrób porządek jako asc dla 1. przypadku i desc dla 2. przypadku.

SELECT A.EmpID,  
       First_Value(A.AverageAmount) OVER (PARTITION BY A.EmpID Order by A.DimYearMonthKey asc) AS  '200901AvgAmount', 
       First_Value(A.AverageAmount) OVER (PARTITION BY A.EmpID Order by A.DimYearMonthKey desc) AS '201112AvgAmount'

FROM  Emp_Amt  AS A