Funkcje LEAD i LAG
Część sekcji Podstawy ścieżki SQL w Coddy. Lekcja 61 z 72.
Funkcje LEAD i LAG pozwalają nam uzyskać dostęp do wartości bieżącego wiersza sprzed n kroków lub za n kroków.
Na przykład, jeśli chcemy obliczyć stosunek przychodu firmy w bieżącym wierszu do przychodu sprzed miesiąca, możemy pobrać wartość z poprzedniego miesiąca:
| id | revenue | month |
| 1 | 532 | 5 |
| 2 | 492 | 6 |
| 3 | 393 | 7 |
| 4 | 723 | 8 |
SELECT id, revenue, LAG(revenue, 1) OVER (ORDER BY MONTH) as prev_month_revenue
FROM table1 ORDER BY idSpowoduje to utworzenie następującej tabeli:
| id | revenue | prev_month_revenue |
| 1 | 532 | |
| 2 | 492 | 532 |
| 3 | 393 | 492 |
| 4 | 723 | 393 |
W ten sposób możemy obliczyć stosunek prev_month_revenue/revenue.
Gdybyśmy zamiast tego użyli funkcji LEAD, pobrałaby przychód z następnego miesiąca dla każdego wiersza:
SELECT id, revenue, LEAD(revenue, 1) OVER (ORDER BY MONTH) as next_month_revenue
FROM table1 ORDER BY id| id | revenue | next_month_revenue |
| 1 | 532 | 492 |
| 2 | 492 | 393 |
| 3 | 393 | 723 |
| 4 | 723 |
Wyzwanie
ŁatwyDostępne tabele i kolumny:
air_conditioners:id,efficiency,strength,month
Porównaj wydajność każdego klimatyzatora z poprzednim.
Napisz zapytanie, które wyświetla wydajność każdego klimatyzatora wraz z wydajnością poprzednio zainstalowanego klimatyzatora (na podstawie id). Oblicz także różnicę wydajności między bieżącym a poprzednim klimatyzatorem. Posortuj wynik rosnąco według id i efficiency.
Oczekiwane kolumny wyniku:
idefficiencyprevious_efficiency(z użyciem LAG)efficiency_difference(bieżąca wydajność - poprzednia wydajność)
Na koniec opakuj zapytanie i odfiltruj pierwszy wiersz, w którym previous_efficiency (a tym samym efficiency_difference) jest puste, czyli ma wartość NULL:
SELECT * FROM (
-- Your query here
)
WHERE previous_efficiency IS NOT NULLSpróbuj swoich sił
Sprawdź sięTa lekcja zawiera krótki quiz. Zacznij lekcję, żeby na niego odpowiedzieć i śledzić swoje postępy.
Wszystkie lekcje w sekcji Podstawy
4Więcej słów kluczowych
Słowo kluczowe INSłowo kluczowe BETWEENSłowo kluczowe LIKESłowo kluczowe ASPowtórzenie – modele telefonów komórkowych
2Instrukcje warunkowe
Podstawy instrukcji warunkowychSłowo kluczowe ANDSłowo kluczowe ORSłowo kluczowe NOTŁączenie wielu warunkówNawiasyWartości logiczne8Statystyka
Wbudowane funkcje agregujące — część 1Wbudowane funkcje agregujące — część 2Grupowanie — część 1Grupowanie — część 2Podzapytania — część 1Podzapytania — część 2Powtórzenie — sklep z całkowitym zyskiemPowtórzenie — sklep ze skuteramiPowtórzenie — kawiarnia11Funkcje okna, część 1
Funkcja ROW_NUMBERKryterium ORDER BYKryterium PARTITION BYPARTITION i ORDERFunkcje LEAD i LAGPowtórka — LEAD i LAGPowtórka — obrazkiPowtórka — pudełka3Określony format zwracanych danych
Wartości nullSortowanie wyników — część 1Sortowanie wyników — część 2Powtórzenie — firma zajmująca się cyberbezpieczeństwemOgraniczanie liczby rekordówPowtórzenie — fabryka pojazdów6Wyzwania na początek
Powtórka – wybory parlamentarnePowtórka – zatrzymanie przestępcy przez policjęPowtórka – pojemnik na napój w barzePowtórka – inżynier: nowe kolumnyPoćwicz samodzielnie: Edytor online SQL