Korzystanie z funkcji opóźnienia w MySQL

MySQL wersja 8.0 Wprowadzono funkcje okna MySQL, umożliwiając wykonywanie zapytań w łatwiejszej i zorganizowanej metodzie. W ten sposób zwiększając przetwarzanie i wydajność. Takie funkcje obejmują: rank (), row_rank (), last_value () i wiele innych.

W tym samouczku skupimy się na użyciu jednej z funkcji MySQL: lag (). Jest to funkcja okna, która umożliwia dostęp i pobieranie wartości poprzednich wierszy z bieżącego wiersza w tym samym zestawie wyników.

Podstawowa składnia

Ogólna składnia do korzystania z funkcji MySQL lag () to:

LAG (wyrażenie, wartości offsetValue, DefaultVar)
Partycja przez [wyrażenie]
Zamów według wyrażenia [ASC | dec]
);

Poświęćmy chwilę na wyjaśnienie niektórych parametrów w składni funkcji LAG ().

Są następujące:

Wyrażenie: Jest to wartość zwrócona przez funkcję z wiersza, która prowadzi bieżący wiersz według określonej wartości przesunięcia.

WVIRUE OFFSETEM: Ta wartość reprezentuje liczbę wierszy poprzedzających bieżący wiersz, z którego można uzyskać wartość. Ta wartość musi być 0 lub wartość wyższa niż 0.

NOTATKA: Wartość 0 reprezentuje bieżący wiersz.

DefaultVar: Ta wartość jest zwracana jako wartość domyślna przez funkcję, jeśli nie istnieje poprzedni wiersz. Jeśli wartość domyślna jest niezdefiniowana w parametrze funkcji i nie istnieje poprzedni wiersz, funkcja zwraca wartość zerową.

Partycja:: Partycja według klauzuli dzieli wiersze w logicznym zestawie partycji. Funkcja opóźnienia jest następnie stosowana do podzielonych partycji.

ZAMÓW PRZEZ: Jak zwykle ta wartość określa kolejność wierszy w dostępnych partycjach.

Przykład przypadków użycia

Spójrzmy na przykładowe przypadki użycia funkcji LAG (), aby zrozumieć, jak to działa. Zacznij od utworzenia przykładowej bazy danych o nazwie STARK_DB;

Z upuść bazę danych, jeśli istnieje próbka_database;
Utwórz bazę danych próbki_database;
Użyj próbki_database;
Stół zrzutu, jeśli istnieje użytkownicy;
Utwórz użytkowników tabeli
(
Id int klucz podstawowy auto_increment,
Nazwa varchar (255),
Wynik int,
Data rejestracji_date
);
Wstaw do użytkowników (ID, Nazwa, wynik, Everoll_Date)
Wartości (1, „Alexandra”, 99, „2021-01-10”),
(2, „Jacob”, 81, „2021-05-20”),
(3, „Leonard”, 67, „2020-01-02”),
(4, „Peter”, 88, „2021-03-03”),
(5, „Amy”, 100, „2021-05-05”);
Wybierz * od użytkowników;

Teraz, gdy mamy przykładową bazę danych do pracy, możemy kontynuować i zilustrować, jak pracować z funkcją opóźnienia MySQL.

Przykład 1: Funkcja opóźnienia bez wartości domyślnej
Rozważ poniższy przykład, który stosuje funkcję opóźnienia w rejestru_date z wartością przesunięcia 1.

Wybierz *, lag (Enteroll_Date, 1) ponad (zamówienie według id ASC) jako poprzedni_date z próbki_database.użytkownicy;

Po wykonaniu powyższego zapytania otrzymujemy nową kolumnę poprzednią_date, która utrzymuje poprzednią wartość wiersza określoną z wartością przesunięcia 1. Ponieważ w pierwszym rzędzie nie ma wcześniejszej wartości, wartość jest null.

Uwaga: Możesz określić wartość domyślną, jeśli wiersz nie ma poprzedniej wartości.

Wyjście jest jak pokazano poniżej:

Przykład 2: Funkcja opóźnienia o wartości domyślnej
Możesz także określić wartość domyślną dla wiersza, w której poprzednia wartość nie istnieje. W naszym przykładzie ustawimy wartość domyślną w bieżącym dniu.

Uwaga: W tym przykładzie ustawym również wartość przesunięcia jako 2 zamiast 1.

Rozważ pytanie poniżej:

Wybierz *, lag (Enteroll_Date, 2, curdate ()) Over (rzędu według id ASC) jako poprzedni_date z próbki_database.użytkownicy;

Po wykonaniu powyższego zapytania otrzymamy wartości z wartością przesunięcia wynoszącą dwie i bieżącą datą jako wartości domyślnych dla wartości zerowych.

Wyjście jest jak pokazano poniżej:

Przykład 3: Funkcja opóźnienia z partycją przez
Możemy użyć funkcji lag () z partycją według klauzuli. Ta klauzula najpierw grupuje dane na różne logiczne podzbiory, a następnie stosuje funkcję opóźnienia do partycji.

Przed kontynuowaniem zobaczmy dane w tabeli użytkownika. Rozważ następujące zapytanie:

Wstaw do użytkowników (ID, Nazwa, wynik, Everoll_Date)
Wartości (1, „Alexandra”, 99, „2021-01-10”),
(2, „Jacob”, 81, „2021-05-20”),
(3, „Leonard”, 67, „2020-01-02”),
(4, „Peter”, 88, „2021-03-03”),
(5, „Amy”, 100, „2021-05-05”),
(6, „Tobias”, 100, „2020-06-06”),
(7, „Kurtzman”, 67, „2020-07-10”),
(8, „Immortal”, 50, „2021-03-01”),
(9, „Anthony”, 81, „2021-01-01”),
(10, „James”, 77, „2021-02-03”);

Teraz, gdy mamy tabelę z 10 wartościami, możemy podzielić dane według wyniku, a następnie zastosować funkcję opóźnienia.

Powyższa operacja jest zilustrowana w pytaniu poniżej:

Wybierz *, lag (Everoll_Date, 1, curdate ()) Over (partycja według kolejności wyniku według id ASC) jako poprzedni_date z próbki_database.użytkownicy;

W powyższym zapytaniu zaczynamy od partycjonowania danych na podstawie wyniku, a następnie zastosowania funkcji opóźnienia z wartością przesunięcia 1. Ustawiamy również wartość domyślną jako bieżącą datę. Wynik wyjściowy jest jak pokazano poniżej:

Uwaga: możesz również zauważyć, że pierwszy wiersz każdej partycji zawiera bieżącą datę, co oznacza, że ​​nie ma wcześniejszej wartości w ustawionym wierszu.

Wniosek

W tym samouczku omówiono, jak działa funkcja lag (), aby uzyskać wartości poprzednich wierszy w bieżącym rzędzie.

Przypomnę:

• Funkcja MySQL to funkcja okna, która otrzymuje wartość z poprzedniego wiersza na podstawie określonej wartości przesunięcia. Co oznacza, że ​​jeśli wartość przesunięcia wynosi 1, otrzymuje wartość bezpośrednio nad nią.
• Domyślnie funkcja lag () używa wartości przesunięcia 1, chyba że wyraźnie określono.
• Jeśli dane są poza zasięgiem (żadna wcześniejsza wartość w określonym przesunięciu), wartość jest ustawiona na NULL.
• Funkcja lag () akceptuje również partycję według klauzuli, która grupuje dane na różne partycje logiczne na podstawie określonej kolumny lub warunku.

Dziękuję za przeczytanie.