Pięć najważniejszych funkcji okna SQL, które powinieneś znać w wywiadach dotyczących nauki o danych

SQL jest uniwersalnym językiem w świecie danych i jest najważniejszą umiejętnością, którą należy zdobyć jako profesjonalista ds. danych.

Powodem, dla którego SQL jest tak ważny, jest to, że jest to główna umiejętność wymagana podczas fazy przetwarzania danych. Wiele eksploracji danych, manipulacji danymi, opracowywania potoku i tworzenia pulpitów nawigacyjnych odbywa się za pomocą SQL.

To, co odróżnia wybitnych analityków danych od dobrych analityków danych, to fakt, że wielcy analitycy danych mogą kłócić się z danymi na tyle, na ile pozwalają na to możliwości SQL. Duża część możliwości pełnego wykorzystania wszystkiego, co ma do zaoferowania SQL, polega na umiejętności korzystania z funkcji okna.

Powiedziawszy to, zanurzmy się w to!

1. Delta z LEAD() i LAG()

 
LEAD() i LAG() są najczęściej używane przy porównywaniu jednego okresu z poprzednim okresem dla danej metryki. Aby podać kilka przykładów…

• Możesz uzyskać deltę między sprzedażą każdego roku a sprzedażą z poprzedniego roku
• Możesz uzyskać deltę liczby rejestracji/konwersji/odwiedzin strony z miesiąca na miesiąc
• Możesz porównać churn użytkowników w ujęciu miesięcznym

Przykład:

Poniższe zapytanie pokazuje, jak można sprawdzić miesięczną zmianę procentową kosztów

with monthly_costs as ( SELECT date , monthlycosts , LEAD(monthlycosts) OVER (ORDER BY date) as previousCosts FROM costs )SELECT date , (monthlycosts - previousCosts) / previousCosts * 100 AS costPercentChange FROM monthly_costs

2. Sumy skumulowane z SUM() lub COUNT()

 
Obliczanie sum bieżących można po prostu wykonać za pomocą funkcji systemu Windows, która zaczyna się od SUM() lub COUNT(). Jest to potężne narzędzie, gdy chcesz pokazać wzrost określonej metryki w czasie. Dokładniej, jest to przydatne w następujących okolicznościach:

• Uzyskaj bieżącą sumę przychodów i kosztów w czasie
• Uzyskaj bieżący łączny czas spędzony w aplikacji na użytkownika
• Uzyskaj bieżącą sumę konwersji w czasie

Przykład:

Poniższy przykład pokazuje, jak można uwzględnić kolumnę sumy skumulowanych kosztów miesięcznych:

SELECT date , monthlycosts , SUM(monthlycosts) OVER (ORDER BY date) as cumCosts FROM cost_table

3. Średnie kroczące z AVG()

 
AVG() jest naprawdę potężny w funkcjach systemu Windows, ponieważ pozwala na obliczenia średnie ruchome w czasie.

Średnie kroczące to prosty, ale skuteczny sposób prognozowania wartości w krótkim okresie. Są również niezwykle przydatne przy wygładzaniu krzywych niestabilnych na wykresie. Ogólnie rzecz biorąc, średnie kroczące służą do oceny ogólnego kierunku, w którym poruszają się rzeczy.

Dokładniej…

• Można je wykorzystać do określenia ogólnego trendu sprzedaży tygodniowej (czy średnia rośnie z czasem?). To wskazywałoby na wzrost jako firma.
• Można je również wykorzystać do uzyskania ogólnego trendu cotygodniowych konwersji lub wizyt w witrynie.

Przykład:

Poniższe zapytanie jest przykładem uzyskania 10-dniowej średniej ruchomej dla konwersji.

SELECT Date , dailyConversions , AVG(dailyConversions) OVER (ORDER BY Date ROWS 10 PRECEDING) AS 10_dayMovingAverage FROM conversions

4. NUMER_WIERSZA()

 
ROW_NUMBER() jest szczególnie przydatna, gdy chcesz uzyskać pierwszy lub ostatni rekord. Na przykład, jeśli masz tabelę, w której członkowie siłowni przyszli na siłownię i chcesz uzyskać datę pierwszego dnia, w którym przyszli na siłownię, możesz PARTYCJA WG klienta (imię/identyfikator) i ZAMÓW WEDŁUG daty zakupu. Następnie, aby uzyskać pierwszy wiersz, możesz po prostu odfiltrować wiersze z numerem wiersza równym jeden.

Przykład:

Ten przykład pokazuje, w jaki sposób można użyć ROW_NUMBER(), aby uzyskać pierwszą datę wizyty każdego członka (użytkownika).

with numbered_visits as ( SELECT memberId , visitDate , ROW_NUMBER() OVER (PARTITION BY customerId ORDER BY purchaseDate) as rowNumber FROM gym_visits )SELECT * FROM numbered_visits WHERE rowNumber = 1

Podsumowując, jeśli kiedykolwiek będziesz potrzebować pierwszego lub ostatniego rekordu, ROW_NUMBER() to świetny sposób, aby to osiągnąć.

5. Rekordowy ranking z DENSE_RANK()

 
DENSE_RANK() jest podobna do ROW_NUMBER() z tą różnicą, że zwraca tę samą pozycję dla równych wartości. Gęsty ranking jest bardzo przydatny, jeśli chodzi o wyszukiwanie najlepszych rekordów, na przykład:

• Jeśli chcesz wyciągnąć 10 najchętniej oglądanych programów Netflix w tym tygodniu
• Jeśli chcesz zdobyć 100 najlepszych użytkowników na podstawie wydanych dolarów
• Jeśli chcesz zobaczyć zachowanie 1000 najmniej aktywnych użytkowników

Przykład:

Jeśli chcesz uszeregować swoich najlepszych klientów według łącznej sprzedaży, DENSE_RANK() byłaby odpowiednią funkcją do użycia.

SELECT customerId , totalSales , DENSE_RANK() OVER (ORDER BY totalSales DESC) as rank FROM customers

Dziękuje za przeczytanie!

 
I to wszystko! Mam nadzieję, że to pomoże ci w przygotowaniu się do rozmowy kwalifikacyjnej — jestem pewien, że jeśli znasz te 5 koncepcji od podszewki, poradzisz sobie świetnie, jeśli chodzi o większość problemów z funkcjami okien SQL.

Jak zawsze życzę ci wszystkiego najlepszego w twoich wysiłkach edukacyjnych!

 
 
Terence'a Shina jest entuzjastą danych z ponad 3-letnim doświadczeniem w SQL i ponad 2-letnim doświadczeniem w Pythonie oraz blogerem Towards Data Science i KDnuggets.

Oryginalny. Przesłane za zgodą.

Źródło: https://www.kdnuggets.com/2022/01/top-five-sql-window-functions-know-data-science-interviews.html