A cosa servono le funzioni finestra in SQL?

Le funzioni di finestra in SQL eseguono aggregazioni di dati in un insieme di righe correlate alla riga corrente, ma senza raggruppare l'intero set di risultati. Questo permette di eseguire calcoli come somme cumulative, medie mobili o ranking, mantenendo tutte le righe della query originale.

Gli obiettivi principali dell'uso delle funzioni di finestra:

• Classificazione: Assegnare un rango a ogni riga in una finestra determinata.

  -- Ottenere il rango dei dipendenti per salario all'interno di ogni dipartimento
  SELECT
      employee_name,
      salary,
      department,
      RANK() OVER (PARTITION BY department ORDER BY salary DESC) as department_rank
  FROM
      employees;
  

• Calcolo di statistiche per gruppo di righe senza raggruppamento: Aggregazione di dati all'interno di una "finestra" di righe, mantenendo la granularità a livello di riga.

  -- Calcolare il salario medio per tutto il dipartimento per ogni riga
  SELECT
      employee_name,
      salary,
      department,
      AVG(salary) OVER (PARTITION BY department) as avg_department_salary
  FROM
      employees;
  

• Confronto della riga corrente con altre righe nella finestra: Accesso ai dati di righe precedenti o successive nella finestra.

  -- Ottenere il salario del dipendente precedente nel dipartimento in ordine decrescente di salario
  SELECT
      employee_name,
      salary,
      department,
      LAG(salary, 1, 0) OVER (PARTITION BY department ORDER BY salary DESC) as previous_salary
  FROM
      employees;
  

• Calcoli con somma cumulativa: Calcolo di somme, medie e altre aggregazioni in un accumulo crescente.

  -- Calcolare la somma cumulativa delle vendite per data
  SELECT
      sale_date,
      amount,
      SUM(amount) OVER (ORDER BY sale_date) as cumulative_sales
  FROM
      sales;
  

A differenza delle funzioni di aggregazione normali (GROUP BY), le funzioni di finestra non fondono le righe, rendendole uno strumento potente per query analitiche e analisi dettagliate dei dati.