Pivot table în SQL: când e util și când îl muți în Polars sau pandas

Un pivot table este o transformare prin care valorile dintr-o coloană devin ele însele coloane noi, iar celulele conțin o valoare agregată (sumă, număr, medie) calculată per grup. Altfel spus, rotești datele cu 90 de grade: rândurile de categorie devin anteturi de coloană. SQL permite această transformare, cu condiția că știi dinainte câte coloane va produce pivot-ul.

Cel mai frecvent loc unde apare termenul este raportarea: un tabel cu vânzări per lună și per produs devine un grid cu produsele pe rânduri și lunile pe coloane, fiecare celulă conținând totalul vânzărilor. SQL poate face asta, dar cu o limitare importantă pe care mulți o descoperă prea târziu: coloana trebuie definită la momentul scrierii query-ului, nu la runtime.

Ce face un pivot table mai exact?

Transformarea are trei ingrediente:

• Coloana sursă (sau pivot column): valorile distincte devin antete de coloană. De exemplu, luna cu valorile ian, feb, mar.
• Coloana de valori: furnizează datele agregate per celulă. De exemplu, vanzari.
• Coloana de grupare (sau row identifier): valorile care rămân pe rânduri. De exemplu, produs.

Inversa operației (coloanele înapoi în rânduri) se numește unpivot sau melt în Python. Operația apare frecvent în data warehouse-uri, unde datele brute în format lung trebuie transformate în format larg. Același principiu stă la baza tabelelor materialized view care pre-calculează agregările scumpe.

Cum se scrie un pivot în SQL (CASE WHEN, crosstab în Postgres)?

Există două abordări principale în SQL. Prima, universală, este CASE WHEN + GROUP BY:

SELECT
  produs,
  SUM(CASE WHEN luna = 'ian' THEN vanzari ELSE 0 END) AS ian,
  SUM(CASE WHEN luna = 'feb' THEN vanzari ELSE 0 END) AS feb,
  SUM(CASE WHEN luna = 'mar' THEN vanzari ELSE 0 END) AS mar
FROM vanzari_lunare
GROUP BY produs;

Funcționează pe orice motor SQL. Dezavantajul este verbozitatea: dacă lunile sunt 12 sau categoriile variabile, query-ul devine lung și rigid.

A doua abordare, disponibilă în Postgres prin extensia tablefunc, este crosstab():

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  'SELECT produs, luna, SUM(vanzari)
   FROM vanzari_lunare GROUP BY produs, luna ORDER BY 1, 2',
  'SELECT DISTINCT luna FROM vanzari_lunare ORDER BY 1'
) AS ct(produs TEXT, ian NUMERIC, feb NUMERIC, mar NUMERIC);

Sintaxa este mai scurtă, dar limitarea rămâne: coloanele din AS ct(…) trebuie enumerate la scriere. Alte engine-uri au soluții similare: SQL Server și Oracle au PIVOT / UNPIVOT nativ; BigQuery are PIVOT în StandardSQL; MySQL nu are clauză PIVOT nativă și rămâne la CASE WHEN.

Care sunt limitele coloanelor dinamice (pivot la runtime)?

Limitarea fundamentală a SQL-ului: schema unui result set trebuie cunoscută la parse time. Pivot-ul cu coloane dinamice (valorile din coloana sursă nu sunt știute dinainte) nu poate fi exprimat într-un singur query SQL standard.

Dacă vrei să pivotezi pe o coloană cu valori care cresc în timp, ai trei opțiuni în SQL:

• SQL generat dinamic. Prima interogare citește valorile distincte; a doua, construită programatic, execută pivot-ul. Funcționează, dar adaugă un round-trip și complică pipeline-ul.
• JSONB agregat. În Postgres poți folosi json_object_agg() pentru a returna coloanele pivot ca keys JSON. Nu e un pivot clasic, dar e util dacă downstream-ul consumă JSON.
• Coloane fixe cu NULL pentru absent. Dacă setul de valori este finit și stabil (12 luni, 4 trimestre), enumerezi toate coloanele și accepți NULL pentru combinațiile absente. Cea mai comună soluție pentru raportare.

Dacă niciuna nu e satisfăcătoare, problema aparține mai natural unui instrument de tip dataframe.

Când o muți în Polars sau pandas?

Există câteva semnale clare că pivotul ar trebui să trăiască în afara bazei de date:

• Coloanele pivot variază la runtime. Polars și pandas rezolvă asta nativ: df.pivot() sau pd.pivot_table() determină coloanele dinamic din date, fără SQL generat. Nu ai nevoie să știi dinainte lista valorilor distincte.
• Pivotul este un pas intermediar într-un pipeline Python mai larg. Dacă datele intră în continuare într-un notebook, un raport sau o vizualizare, are mai mult sens să faci pivotul direct acolo decât să exporți, să pivotezi în SQL și să re-importezi.
• Ai nevoie de filtre și agregări multiple în același pas. Polars rulează pe un motor columnar multithreaded și permite transformări compuse (filtrare, pivotare, calcule de coloane noi) într-un singur lanț de operații. SQL produce adesea un subquery în subquery, greu de întreținut.

Pivotul din baza de date rămâne preferat când rezultatul este consumat direct de altă interogare SQL, când devine o materialized view care cache-uiește un calcul scump, sau când schema coloanelor este stabilă. SQL știe cel mai bine ce date există; Python știe cel mai bine cum să le transforme pentru consum extern.

Această decizie se înscrie în aceeași logică descrisă la OLAP vs OLTP: Postgres se comportă bine ca motor analitic la volume medii, dar la transformări complexe sau cu coloane variabile, stratul de procesare a datelor câștigă prin flexibilitate.

Care sunt capcanele de performanță la pivot-uri SQL pe volume mari?

Un pivot SQL pe un tabel mare are câteva capcane comune care merită anticipate:

• Full table scan fără index util. Un CASE WHEN + GROUP BY cere de obicei parcurgerea întregii tabele pentru a colecta toate valorile de agregat per grup. Un index B-tree simplu pe coloana pivot nu ajută pentru o scanare completă; un index parțial sau un index pe coloana de grupare poate ajuta dacă filtrezi înainte de pivot.
• Cardinalitate explozivă la pivot multi-key. Dacă pivotezi simultan pe două coloane (de exemplu, produs + regiune ca coloane), numărul de coloane rezultate crește multiplicativ. Un tabel cu 100 de produse și 50 de regiuni produce 5.000 de coloane, ceea ce depășește limitele practice ale majorității engine-urilor SQL și devine imposibil de consumat vizual.
• Lipsa de partition pruning. La tabele partiționate (de exemplu, partiție pe lună), un pivot care agrega pe toate lunile forțează scanarea tuturor partițiilor. Dacă există un filtru temporal, asigură-te că îl aplici înainte de pivot, nu după, ca optimizer-ul să poată elimina partiții inutile.
• Re-calculare la fiecare query. Dacă același pivot este solicitat frecvent și datele sursă nu se schimbă des, un materialized view care pre-calculează pivotul poate reduce timpul de răspuns de la câteva secunde la sub 100ms. Prețul este spațiul de stocare și refreshing-ul periodic.
• Temporare implicite mari. Aggregările de pivot trec adesea prin un sort sau un hash aggregate intern care necesită memorie de lucru (work_mem în Postgres). Pe tabele mari, crește work_mem doar pentru sesiunea specifică, nu global, ca să nu afectezi alte conexiuni: SET LOCAL work_mem = '256MB' la începutul tranzacției.

Semnalul că pivotul tău a depășit limitele confortabile ale SQL este că query-ul durează mai mult de câteva secunde pe date care nu s-au schimbat. La acel punct, un materialized view sau mutarea transformării într-un pipeline de data warehouse separat sunt pașii naturali următori. În proiectele de data engineering pe care le construim la crawlerra (incluzând pipeline-uri cu peste 24 de milioane de înregistrări reîmprospătate noapte de noapte¹), această decizie apare constant: pivot-uri cu coloane fixe și volum mare rămân în Postgres ca materialized view; cele cu coloane variabile sau ca pas intermediar se mută în Python. Stack-ul de observabilitate, cu query logging activat, este cel mai simplu mod de a detecta pivot-urile costisitoare înainte să cauzeze probleme la producție.

1. Cifra de 24M+ înregistrări reîmprospătate noapte de noapte provine din pipeline-ul de date al produselor crawlerra. [services.s10]