Pięć wzorców SQL, które zastępują pętlę Python – i dlaczego wygrywają

Najdroższy Python, który recenzuję, nie jest nietypowanym handlerem FastAPI. To porządek for w zamówieniach istnieje, ponieważ nikt nie napisał funkcji okna. Pętla wygląda uczciwie. W ten sam sposób raport, który zajął cztery sekundy, zamienia się w czterdzieści, a następnie w poniedziałek po dobrym weekendzie sprzedażowym następuje przerwa.

Już pisałem jak wybrać typ bazy danych. To kolejna warstwa: kod SQL, który faktycznie piszę, gdy pytanie o produkt brzmi: „ostatnie zamówienie na klienta”, „przychody w tym miesiącu w porównaniu z ostatnim” lub „wstaw tę rezerwację, chyba że to miejsce jest zajęte”. Postgres może odpowiedzieć na te pytania podczas jednej podróży w obie strony. Python też może – po pobraniu tabeli. Dodatkowym elementem jest sposób, w jaki to zapytanie powinno opuścić Python bez ponownego wciągania sterty.

1. Pętla pobierająca tabelę

Typowy skrypt: WYBIERZ * Z zamówień, następnie zgrupuj w dyktacie, a następnie wybierz najnowszy utworzony_at według identyfikatora klienta. Na 800 rzędach jest dobrze. Na 800 000 jest to bilet wsparcia. Baza danych ma już do tego indeks, sortowanie i pamięć. Zapłaciłeś za to. Użycie Python jako drugiego silnika zapytań powoduje ukazanie kosztów w czasie opóźnienia, a nie na zaplanowanej fakturze.

• Jeśli wynik jest mniejszy niż źródło, a reguła ma nazwę – ostatnie, suma, ranga, istnieje – jest to zapytanie.
• Jeśli regułą jest esej biznesowy – zwroty z wyjątkiem ludzkim, akapit dotyczący polityki – może pozostać w Python.

2. Ostatni wiersz na grupę – DISTINCT ON i ROW_NUMBER

Bieżąca subskrypcja na obszar roboczy. Najnowszy status faktury dla każdego zamówienia. Kierowca, który ostatni pingował. To pierwsza pętla, którą usuwam. W Postgres piszę DISTINCT ON (workspace_id) i ORDER BY workspace_id, create_at DESC. Przenośny kuzyn to ROW_NUMBER() OVER (PARTITION BY workspace_id ORDER BY create_at DESC), następnie zachowaj rn = 1.

DISTINCT ON jest krótszy i używam go, gdy posiadam Postgres. ROW_NUMBER piszę, gdy to samo zapytanie musi przetrwać ruch lub gdy potrzebuję rangi 1 i rangi 2 – poprzedni plan, drugie miejsce. Nie pobieraj każdej subskrypcji i zmniejszaj o Python. Otrzymasz błędne powiązania i pobierzesz kolumny, których nigdy nie renderowałeś.

3. Sumy bieżące i „od ostatniego miesiąca” – SUMA OVER i LAG

Miesiąc po miesiącu nie jest łączeniem tabeli ze sobą w pętli. LAG(przychody) OVER (PARTITION BY workspace_id ORDER BY miesiąc) umieszcza ostatni miesiąc w tym samym wierszu. SUM(kwota) OVER (PARTITION BY client_id ORDER BY create_at) to saldo bieżące. Funkcje okna zachowują ziarnistość wierszy i nadal widzą otoczenie. GROUP BY zwija się. Pętla Python odbudowuje tę okolicę w dyktacie i zapomina strefy czasowe.

Jeśli potrzebujesz zarówno wiersza szczegółów, jak i sumy grupy, SUM(kwota) OVER (PARTITION BY Order_id) pokonuje drugie zapytanie i pokonuje N+1 z ORM. Na tym polega cała sztuczka: zachowaj ziarno, które wyrenderujesz, oblicz sąsiedztwo w silniku.

4. Liczenie warunkowe – FILTR zamiast if

COUNT(*) FILTER (GDZIE status = zapłacony) obok COUNT(*) FILTER (GDZIE status = zwrócony) to jeden skan. Wersja Python to jedno przejście z dwoma licznikami – do czasu, aż ktoś w drugiej pętli doda trzeci status lub eksport się zwiększy i skanujesz dwukrotnie. FILTR jest czytelny. CASE wewnątrz SUM działa w silnikach, które nigdy nie otrzymały FILTER. Wolę FILTR w Postgresie, ponieważ intencja znajduje się na stronie: ta liczba ma predykat.

5. Wstaw zamiast wybierać i wstawiać

Wyścig: przeczytaj „czy ten e-mail jest bezpłatny”, a następnie wstaw. Dwie zakładki, dwóch pracowników, jedno unikalne ograniczenie, które odkrywasz w produkcji. INSERT... ON CONFLICT (e-mail) AKTUALIZUJ – lub NIE RÓB NIC – to transakcja, którą baza danych już wie, jak przeprowadzić. Ta sama rodzina: UPDATE ... WHERE wersja = $wersja dla blokady optymistycznej. Jeśli sprawdzisz i napiszesz Python, piszesz gorszą bazę danych.

• Rezerwacja slotów, klucze idempotencji, „utwórz klienta, jeśli go brakuje” – to są upserts, a nie if-blocki.
• Częścią produktu jest unikalny indeks. Bez tego ON CONFLICT jest teatrem.

6. Jak sprawdzam, czy nie wymyśliłem wolniejszego zapytania

WYJAŚNIJ (ANALIZUJ, BUFORY) na wolnej ścieżce. Jeśli widzisz sekwencyjne skanowanie tabeli, która się powiększyła, potrzebny był indeks pasujący do partycji i kolejności – (workspace_id, create_at DESC) dla DISTINCT ON. Funkcja okna nie jest darmowa. Jest to nadal tańsze niż wysyłanie sterty do aplikacji. Jeśli plan sortuje dwa miliony wierszy dla pulpitu nawigacyjnego, który pokazuje dwadzieścia, dodaj powiązanie z datą. SQL, który zastępuje pętlę, może nadal być złą pętlą w silniku.

Wniosek: nadaj nazwę zestawowi wyników, napisz zapytanie

Python to dobre miejsce na protokół HTTP, zasady i zdanie wyświetlane użytkownikowi. To kiepskie miejsce na ponowne odkrycie GROUP BY. Poznaj pięć kształtów – ostatnia grupa, opóźnienie, suma bieżąca, filtrowane agregaty, upsert – a większość „sprytnych” skryptów, które przeglądam, staje się widokiem lub pojedynczym zapytaniem w funkcji repozytorium. Drugi artykuł to Python, który nie pozwala cofnąć tej pracy.