výpočet běžících součtů pomocí SQL Říjen 20, 2015 / Andy Granowitz
kolik uživatelů se připojilo za posledních 5 měsíců? Jaké byly celkové tržby ve 2. čtvrtletí? Kolik příjmů pocházelo z kohorty March sign up?
ačkoli tyto otázky mohou být zodpovězeny jedním číslem, může být užitečné vidět průběžný součet v průběhu času: kolik jedinečných uživatelů se připojilo nebo kolik kumulativních příjmů bylo během dne přijato za určité období.
obvykle se data ukládají postupně. Zde je například tabulka prodejů za den:
| Date | Sales |
|---|---|
| 10/1/2015 | 5 |
| 10/2/2015 | 3 |
| 10/3/2015 | 7 |
| 10/4/2015 | 8 |
| 10/5/2015 | 2 |
| 10/6/2015 | 3 |
| 10/7/2015 | 6 |
How do we generate the following table of cumulative sales over time? In SQL, there are two typical approaches: a self join or a window function.
| Date | Running Total of Sales |
|---|---|
| 10/1/2015 | 5 |
| 10/2/2015 | 8 |
| 10/3/2015 | 15 |
| 10/4/2015 | 23 |
| 10/5/2015 | 25 |
| 10/6/2015 | 28 |
| 10/7/2015 | 34 |
A self join is a query that compares a table to itself. V tomto případě, porovnáváme každé datum s jakýmkoli datem menším nebo rovným, abychom vypočítali průběžný součet. Konkrétně vezmeme součet sales ve druhé tabulce nad každým řádkem, který má datum menší nebo rovno datu pocházejícímu z první tabulky. Jedná se o syntaxi Postgres/Redshift, ale jiné SQL dialekty jsou velmi podobné.
To není špatný přístup; to je pěkné ukázky, jak extensible SQL může být pouze pomocí selectfromjoingroup by prohlášení.
ale je to hodně kódu pro jednoduchý úkol. Zkusme funkci okna. Jsou určeny k výpočtu metriky nad sadou řádků. V našem případě chceme shrnout každý řádek, kde je datum menší nebo rovno datu v aktuálním řádku.
funkce okna může filtrovat a uspořádat sadu řádků pro spuštění funkce. Zde order by date rows unbounded preceding omezuje funkci sum pouze na sales před datem aktuálního řádku. Funkce okna jsou neuvěřitelně užitečné pro analytické dotazy založené na čase; Chcete-li se dozvědět více, dokumenty Postgres jsou skvělým místem pro začátek.
poslední krok vytvoření grafu a jeho triumfálního sdílení se svými spoluhráči lze snadno provést pomocí vozu. Funkce okna pro výhru!
Wagon je moderní SQL editor pro analytiky a inženýry: psát dotazy, vizualizovat data a sdílet grafy se svým týmem. Registrace zdarma: