DocumentazioneNumeri in corsa
I numeri progressivi sono, in parole povere, quando i numeri vengono sommati l'uno all'altro. Ad esempio, si vogliono conoscere i valori anno per anno (YTD). La somma di numeri è forse un caso spesso utilizzato, ma le possibilità non sono limitate a questo. Si può anche fare una media mobile.
In questo caso lavoreremo con un esempio di YTD. Supponiamo di avere una tabella con le entrate di una certa categoria per mese.
ea_month id amount ea_year circle_id April 92570 1000 2014 1 April 92571 3000 2014 2 April 92572 2000 2014 3 March 92573 3000 2014 1 March 92574 2500 2014 2 March 92575 3750 2014 3 February 92576 2000 2014 1 February 92577 2500 2014 2 February 92578 1450 2014 3
Per ottenere il totale del mese fino a quel momento, si potrebbe essere tentati di utilizzare una subquery come get all the amount of the previous months of the current year of my product. In realtà funziona, ma per ogni riga recuperata viene eseguita una sottoquery. Il che può facilmente portare a migliaia di query e quindi a tempi lunghi, come in questo esempio:
select branch_id,
net_profit as store_profit,
(select sum(net_profit) from store_sales as s2 where s2.city = s1.city) as city_profit,
store_profit / city_profit * 100 as store_percentage_of_city_profit
from store_sales as s1
order by branch_id;
+-----------+--------------+-------------+---------------------------------+
| BRANCH_ID | STORE_PROFIT | CITY_PROFIT | STORE_PERCENTAGE_OF_CITY_PROFIT |
|-----------+--------------+-------------+---------------------------------|
| 1 | 10000.00 | 25000.00 | 40.00000000 |
| 2 | 15000.00 | 25000.00 | 60.00000000 |
| 3 | 10000.00 | 19000.00 | 52.63157900 |
| 4 | 9000.00 | 19000.00 | 47.36842100 |
+-----------+--------------+-------------+---------------------------------+
Postgres fornisce una cosiddetta funzione finestra chiamata OVER. Quindi, in un linguaggio meno naturale, si dice in SQL che si vuole avere una somma() su un set di dati PARZIALIZZATO per tempo e/o prodotto.
SELECT ea_month, id, amount, ea_year, circle_id
, sum(amount) OVER (PARTITION BY circle_id
ORDER BY ea_year, ea_month) AS cum_amt
FROM tbl
ORDER BY circle_id, month;
Per avere effettivamente un YTD l'anno deve far parte del PARTIZIONAMENTO, altrimenti sarà su tutto il tempo.
Il calcolo verrà eseguito per riga. Quindi, se la tabella ha dati giornalieri, ma l'output è desiderato su base mensile, i dati devono essere prima raggruppati e sommati per mese, prima che un ytd su base mensile possa funzionare.