SQL Academy · Lezione

Pattern crosstab (crosstab() di PostgreSQL)

Generi vere tabelle pivot con la funzione crosstab() dell’estensione tablefunc

Pattern crosstab (crosstab() di PostgreSQL) è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 4 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.

Perché un vero crosstab?

I pivot con CASE richiedono di elencare ogni colonna di destinazione. Per pivot realmente larghi, ad esempio con una colonna per prodotto, lo strumento adatto è crosstab() dell'estensione tablefunc.

Abilitare l'estensione

tablefunc è inclusa nei moduli contrib di PostgreSQL:

CREATE EXTENSION IF NOT EXISTS tablefunc;

Firma di base di crosstab

crosstab accetta una stringa SQL con 3 colonne (chiave della riga, categoria, valore) e restituisce la chiave della riga più una colonna per ogni categoria:

SELECT * FROM crosstab(
  $$
    SELECT user_id, status, COUNT(*)::INT
    FROM orders
    GROUP BY user_id, status
    ORDER BY user_id, status
  $$
) AS ct (
  user_id BIGINT,
  paid    INT,
  pending INT,
  cancelled INT
);

Perché dichiarare le colonne di output

SQL è tipizzato staticamente: il pianificatore deve conoscere le colonne di output al momento dell'analisi. Per questo specifica lo schema nella clausola AS, inclusi i tipi di dati.

crosstab con due argomenti (con insieme di categorie)

Per dati sparsi, fornisca separatamente l'elenco delle categorie, così i valori mancanti diventano NULL invece di causare disallineamenti:

SELECT * FROM crosstab(
  $$
    SELECT user_id, status, COUNT(*)::INT
    FROM orders GROUP BY user_id, status
    ORDER BY user_id
  $$,
  $$ VALUES ('paid'), ('pending'), ('cancelled') $$
) AS ct (
  user_id BIGINT, paid INT, pending INT, cancelled INT
);

Quando CASE è preferibile a crosstab

Per un insieme noto e ridotto di categorie, CASE/FILTER è più semplice: non richiede estensioni né le insidie della variante a due argomenti. Usi crosstab quando:

Ha molte categorie
Le categorie vengono caricate dinamicamente
Sta generando dati per un sistema esterno che consumerà il pivot

Pivot dinamici

Per categorie sconosciute a runtime, generi SQL nell'applicazione oppure usi PL/pgSQL con format() + EXECUTE.

-- Build the SQL dynamically:
SELECT string_agg(format('SUM(CASE WHEN status = %L THEN 1 END) AS %I',
                          status, status), ', ')
FROM (SELECT DISTINCT status FROM orders) s;

Creare pivot larghi per i fogli di calcolo

I report destinati agli analisti spesso richiedono il formato wide. Lo generi in SQL oppure fornisca il formato long e lasci che sia lo strumento di BI a creare il pivot.

Unpivot: l'operazione inversa

Per passare da wide → long, usi UNION ALL o jsonb_each_text() di PostgreSQL:

SELECT id, key AS month, (value)::NUMERIC AS revenue
FROM monthly_wide,
     jsonb_each_text(to_jsonb(monthly_wide) - 'id');

Prestazioni

crosstab() esegue una sola volta la query interna e crea il pivot in memoria. Il collo di bottiglia è lo stesso di una normale query con GROUP BY.

Limitazioni di crosstab

PostgreSQL non dispone della parola chiave PIVOT nativa, a differenza di Oracle e SQL Server. crosstab() è la soluzione alternativa.

Riepilogo

Per la maggior parte dei pivot, CASE/FILTER è la soluzione più chiara. crosstab() è lo strumento da usare quando le categorie sono numerose o non sono note in anticipo.

Verifica rapida

Quale estensione fornisce la funzione crosstab() di PostgreSQL?

Domande Frequenti

La lezione «Pattern crosstab (crosstab() di PostgreSQL)» è gratuita?

Sì — il testo completo di «Pattern crosstab (crosstab() di PostgreSQL)» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Academy, passa a CoddyKit PRO. Il corso SQL Academy include 4 lezioni in totale.

Cosa imparerò in «Pattern crosstab (crosstab() di PostgreSQL)»?

Generi vere tabelle pivot con la funzione crosstab() dell’estensione tablefunc Eserciti SQL Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare SQL Academy?

Non è richiesta alcuna esperienza precedente. SQL Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 4 di 4.

Quanto tempo richiede la lezione «Pattern crosstab (crosstab() di PostgreSQL)»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione SQL Academy?

Sì. Ogni lezione SQL Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

UNION, INTERSECT, EXCEPT
UNION ALL rispetto a UNION (costo della deduplicazione)
Espressioni CASE e query pivot
Pattern crosstab (crosstab() di PostgreSQL)

← Torna a SQL Academy