{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "## Analytic Functions – Aggregations\n", "\n", "Let us see how we can perform aggregations with in a partition or group using Windowing/Analytics Functions." ] }, { "cell_type": "code", "execution_count": 93, "metadata": { "tags": [ "remove-cell" ] }, "outputs": [ { "data": { "text/html": [ "\n" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%%HTML\n", "" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "* For simple aggregations where we have to get grouping key and aggregated results we can use **GROUP BY**.\n", "* If we want to get the raw data along with aggregated results, then using **GROUP BY** is not possible or overly complicated.\n", "* Using aggregate functions with **OVER** Clause not only simplifies the process of writing query, but also better with respect to performance.\n", "* Let us take an example of getting employee salary percentage when compared to department salary expense." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```{warning}\n", "If you are using Jupyter based environment make sure to restart the kernel, as the session might have been already connected with retail database.\n", "```" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "%load_ext sql" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "env: DATABASE_URL=postgresql://itversity_hr_user:hr_password@localhost:5432/itversity_hr_db\n" ] } ], "source": [ "%env DATABASE_URL=postgresql://itversity_hr_user:hr_password@localhost:5432/itversity_hr_db" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "10 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_iddepartment_idsalary
200104400.00
202206000.00
2012013000.00
119302500.00
118302600.00
117302800.00
116302900.00
115303100.00
1143011000.00
203406500.00
" ], "text/plain": [ "[(200, 10, Decimal('4400.00')),\n", " (202, 20, Decimal('6000.00')),\n", " (201, 20, Decimal('13000.00')),\n", " (119, 30, Decimal('2500.00')),\n", " (118, 30, Decimal('2600.00')),\n", " (117, 30, Decimal('2800.00')),\n", " (116, 30, Decimal('2900.00')),\n", " (115, 30, Decimal('3100.00')),\n", " (114, 30, Decimal('11000.00')),\n", " (203, 40, Decimal('6500.00'))]" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT employee_id, department_id, salary \n", "FROM employees \n", "ORDER BY department_id, salary\n", "LIMIT 10" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```{note}\n", "Let us write the query using `GROUP BY` approach.\n", "```" ] }, { "cell_type": "code", "execution_count": 4, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_hr_user:***@localhost:5432/itversity_hr_db\n", "12 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
department_iddepartment_salary_expense
104400.00
2019000.00
3024900.00
406500.00
50156400.00
6028800.00
7010000.00
80304500.00
9058000.00
10051600.00
11020300.00
None7000.00
" ], "text/plain": [ "[(10, Decimal('4400.00')),\n", " (20, Decimal('19000.00')),\n", " (30, Decimal('24900.00')),\n", " (40, Decimal('6500.00')),\n", " (50, Decimal('156400.00')),\n", " (60, Decimal('28800.00')),\n", " (70, Decimal('10000.00')),\n", " (80, Decimal('304500.00')),\n", " (90, Decimal('58000.00')),\n", " (100, Decimal('51600.00')),\n", " (110, Decimal('20300.00')),\n", " (None, Decimal('7000.00'))]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT department_id,\n", " sum(salary) AS department_salary_expense\n", "FROM employees\n", "GROUP BY department_id\n", "ORDER BY department_id" ] }, { "cell_type": "code", "execution_count": 5, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_hr_user:***@localhost:5432/itversity_hr_db\n", "10 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_iddepartment_idsalarydepartment_salary_expenseavg_salary_expense
200104400.004400.004400.00
202206000.0019000.009500.00
2012013000.0019000.009500.00
119302500.0024900.004150.00
118302600.0024900.004150.00
117302800.0024900.004150.00
116302900.0024900.004150.00
115303100.0024900.004150.00
1143011000.0024900.004150.00
203406500.006500.006500.00
" ], "text/plain": [ "[(200, 10, Decimal('4400.00'), Decimal('4400.00'), Decimal('4400.00')),\n", " (202, 20, Decimal('6000.00'), Decimal('19000.00'), Decimal('9500.00')),\n", " (201, 20, Decimal('13000.00'), Decimal('19000.00'), Decimal('9500.00')),\n", " (119, 30, Decimal('2500.00'), Decimal('24900.00'), Decimal('4150.00')),\n", " (118, 30, Decimal('2600.00'), Decimal('24900.00'), Decimal('4150.00')),\n", " (117, 30, Decimal('2800.00'), Decimal('24900.00'), Decimal('4150.00')),\n", " (116, 30, Decimal('2900.00'), Decimal('24900.00'), Decimal('4150.00')),\n", " (115, 30, Decimal('3100.00'), Decimal('24900.00'), Decimal('4150.00')),\n", " (114, 30, Decimal('11000.00'), Decimal('24900.00'), Decimal('4150.00')),\n", " (203, 40, Decimal('6500.00'), Decimal('6500.00'), Decimal('6500.00'))]" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT e.employee_id, e.department_id, e.salary,\n", " ae.department_salary_expense,\n", " ae.avg_salary_expense\n", "FROM employees e JOIN (\n", " SELECT department_id, \n", " sum(salary) AS department_salary_expense,\n", " round(avg(salary)::numeric, 2) AS avg_salary_expense\n", " FROM employees\n", " GROUP BY department_id\n", ") ae\n", "ON e.department_id = ae.department_id\n", "ORDER BY department_id, salary\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 6, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_hr_user:***@localhost:5432/itversity_hr_db\n", "10 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_iddepartment_idsalarydepartment_salary_expenseavg_salary_expensepct_salary
200104400.004400.004400.00100.00
202206000.0019000.009500.0031.58
2012013000.0019000.009500.0068.42
119302500.0024900.004150.0010.04
118302600.0024900.004150.0010.44
117302800.0024900.004150.0011.24
116302900.0024900.004150.0011.65
115303100.0024900.004150.0012.45
1143011000.0024900.004150.0044.18
203406500.006500.006500.00100.00
" ], "text/plain": [ "[(200, 10, Decimal('4400.00'), Decimal('4400.00'), Decimal('4400.00'), Decimal('100.00')),\n", " (202, 20, Decimal('6000.00'), Decimal('19000.00'), Decimal('9500.00'), Decimal('31.58')),\n", " (201, 20, Decimal('13000.00'), Decimal('19000.00'), Decimal('9500.00'), Decimal('68.42')),\n", " (119, 30, Decimal('2500.00'), Decimal('24900.00'), Decimal('4150.00'), Decimal('10.04')),\n", " (118, 30, Decimal('2600.00'), Decimal('24900.00'), Decimal('4150.00'), Decimal('10.44')),\n", " (117, 30, Decimal('2800.00'), Decimal('24900.00'), Decimal('4150.00'), Decimal('11.24')),\n", " (116, 30, Decimal('2900.00'), Decimal('24900.00'), Decimal('4150.00'), Decimal('11.65')),\n", " (115, 30, Decimal('3100.00'), Decimal('24900.00'), Decimal('4150.00'), Decimal('12.45')),\n", " (114, 30, Decimal('11000.00'), Decimal('24900.00'), Decimal('4150.00'), Decimal('44.18')),\n", " (203, 40, Decimal('6500.00'), Decimal('6500.00'), Decimal('6500.00'), Decimal('100.00'))]" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT e.employee_id, e.department_id, e.salary,\n", " ae.department_salary_expense,\n", " ae.avg_salary_expense,\n", " round(e.salary/ae.department_salary_expense * 100, 2) pct_salary\n", "FROM employees e JOIN (\n", " SELECT department_id, \n", " sum(salary) AS department_salary_expense,\n", " round(avg(salary)::numeric, 2) AS avg_salary_expense\n", " FROM employees\n", " GROUP BY department_id\n", ") ae\n", "ON e.department_id = ae.department_id\n", "ORDER BY department_id, salary\n", "LIMIT 10" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```{note}\n", "Let us see how we can get it using Analytics/Windowing Functions. \n", "```\n", "\n", "* We can use all standard aggregate functions such as `count`, `sum`, `min`, `max`, `avg` etc." ] }, { "cell_type": "code", "execution_count": 7, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_hr_user:***@localhost:5432/itversity_hr_db\n", "10 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_iddepartment_idsalarydepartment_salary_expense
200104400.004400.00
2012013000.0019000.00
202206000.0019000.00
1143011000.0024900.00
115303100.0024900.00
116302900.0024900.00
117302800.0024900.00
118302600.0024900.00
119302500.0024900.00
203406500.006500.00
" ], "text/plain": [ "[(200, 10, Decimal('4400.00'), Decimal('4400.00')),\n", " (201, 20, Decimal('13000.00'), Decimal('19000.00')),\n", " (202, 20, Decimal('6000.00'), Decimal('19000.00')),\n", " (114, 30, Decimal('11000.00'), Decimal('24900.00')),\n", " (115, 30, Decimal('3100.00'), Decimal('24900.00')),\n", " (116, 30, Decimal('2900.00'), Decimal('24900.00')),\n", " (117, 30, Decimal('2800.00'), Decimal('24900.00')),\n", " (118, 30, Decimal('2600.00'), Decimal('24900.00')),\n", " (119, 30, Decimal('2500.00'), Decimal('24900.00')),\n", " (203, 40, Decimal('6500.00'), Decimal('6500.00'))]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT e.employee_id, e.department_id, e.salary,\n", " sum(e.salary) OVER (\n", " PARTITION BY e.department_id\n", " ) AS department_salary_expense\n", "FROM employees e\n", "ORDER BY e.department_id\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_hr_user:***@localhost:5432/itversity_hr_db\n", "10 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_iddepartment_idsalarydepartment_salary_expensepct_salary
200104400.004400.00100.00
202206000.0019000.0031.58
2012013000.0019000.0068.42
119302500.0024900.0010.04
118302600.0024900.0010.44
117302800.0024900.0011.24
116302900.0024900.0011.65
115303100.0024900.0012.45
1143011000.0024900.0044.18
203406500.006500.00100.00
" ], "text/plain": [ "[(200, 10, Decimal('4400.00'), Decimal('4400.00'), Decimal('100.00')),\n", " (202, 20, Decimal('6000.00'), Decimal('19000.00'), Decimal('31.58')),\n", " (201, 20, Decimal('13000.00'), Decimal('19000.00'), Decimal('68.42')),\n", " (119, 30, Decimal('2500.00'), Decimal('24900.00'), Decimal('10.04')),\n", " (118, 30, Decimal('2600.00'), Decimal('24900.00'), Decimal('10.44')),\n", " (117, 30, Decimal('2800.00'), Decimal('24900.00'), Decimal('11.24')),\n", " (116, 30, Decimal('2900.00'), Decimal('24900.00'), Decimal('11.65')),\n", " (115, 30, Decimal('3100.00'), Decimal('24900.00'), Decimal('12.45')),\n", " (114, 30, Decimal('11000.00'), Decimal('24900.00'), Decimal('44.18')),\n", " (203, 40, Decimal('6500.00'), Decimal('6500.00'), Decimal('100.00'))]" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT e.employee_id, e.department_id, e.salary,\n", " sum(e.salary) OVER (\n", " PARTITION BY e.department_id\n", " ) AS department_salary_expense,\n", " round(e.salary / sum(e.salary) OVER (\n", " PARTITION BY e.department_id\n", " ) * 100, 2) AS pct_salary\n", "FROM employees e\n", "ORDER BY e.department_id,\n", " e.salary\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 9, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_hr_user:***@localhost:5432/itversity_hr_db\n", "10 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
employee_iddepartment_idsalarysum_sal_expenseavg_sal_expensemin_sal_expensemax_sal_expensecnt_sal_expense
200104400.004400.004400.004400.004400.001
202206000.0019000.009500.006000.0013000.002
2012013000.0019000.009500.006000.0013000.002
119302500.0024900.004150.002500.0011000.006
118302600.0024900.004150.002500.0011000.006
117302800.0024900.004150.002500.0011000.006
116302900.0024900.004150.002500.0011000.006
115303100.0024900.004150.002500.0011000.006
1143011000.0024900.004150.002500.0011000.006
203406500.006500.006500.006500.006500.001
" ], "text/plain": [ "[(200, 10, Decimal('4400.00'), Decimal('4400.00'), Decimal('4400.00'), Decimal('4400.00'), Decimal('4400.00'), 1),\n", " (202, 20, Decimal('6000.00'), Decimal('19000.00'), Decimal('9500.00'), Decimal('6000.00'), Decimal('13000.00'), 2),\n", " (201, 20, Decimal('13000.00'), Decimal('19000.00'), Decimal('9500.00'), Decimal('6000.00'), Decimal('13000.00'), 2),\n", " (119, 30, Decimal('2500.00'), Decimal('24900.00'), Decimal('4150.00'), Decimal('2500.00'), Decimal('11000.00'), 6),\n", " (118, 30, Decimal('2600.00'), Decimal('24900.00'), Decimal('4150.00'), Decimal('2500.00'), Decimal('11000.00'), 6),\n", " (117, 30, Decimal('2800.00'), Decimal('24900.00'), Decimal('4150.00'), Decimal('2500.00'), Decimal('11000.00'), 6),\n", " (116, 30, Decimal('2900.00'), Decimal('24900.00'), Decimal('4150.00'), Decimal('2500.00'), Decimal('11000.00'), 6),\n", " (115, 30, Decimal('3100.00'), Decimal('24900.00'), Decimal('4150.00'), Decimal('2500.00'), Decimal('11000.00'), 6),\n", " (114, 30, Decimal('11000.00'), Decimal('24900.00'), Decimal('4150.00'), Decimal('2500.00'), Decimal('11000.00'), 6),\n", " (203, 40, Decimal('6500.00'), Decimal('6500.00'), Decimal('6500.00'), Decimal('6500.00'), Decimal('6500.00'), 1)]" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT e.employee_id, e.department_id, e.salary,\n", " sum(e.salary) OVER (\n", " PARTITION BY e.department_id\n", " ) AS sum_sal_expense,\n", " round(avg(e.salary) OVER (\n", " PARTITION BY e.department_id\n", " ), 2) AS avg_sal_expense,\n", " min(e.salary) OVER (\n", " PARTITION BY e.department_id\n", " ) AS min_sal_expense,\n", " max(e.salary) OVER (\n", " PARTITION BY e.department_id\n", " ) AS max_sal_expense,\n", " count(e.salary) OVER (\n", " PARTITION BY e.department_id\n", " ) AS cnt_sal_expense\n", "FROM employees e\n", "ORDER BY e.department_id,\n", " e.salary\n", "LIMIT 10" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```{warning}\n", "If you are using Jupyter based environment make sure to restart the kernel, as the session might have been already connected with hr database.\n", "```" ] }, { "cell_type": "code", "execution_count": 1, "metadata": {}, "outputs": [], "source": [ "%load_ext sql" ] }, { "cell_type": "code", "execution_count": 2, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db\n" ] } ], "source": [ "%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db" ] }, { "cell_type": "code", "execution_count": 3, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "10 rows affected.\n" ] }, { "data": { "text/html": [ "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
order_dateorder_item_product_idrevenuesum_revenuemin_revenuemax_revenue
2013-07-25 00:00:0010045599.7231547.2349.985599.72
2013-07-25 00:00:001915099.4931547.2349.985599.72
2013-07-25 00:00:009574499.7031547.2349.985599.72
2013-07-25 00:00:003653359.4431547.2349.985599.72
2013-07-25 00:00:0010732999.8531547.2349.985599.72
2013-07-25 00:00:0010142798.8831547.2349.985599.72
2013-07-25 00:00:004031949.8531547.2349.985599.72
2013-07-25 00:00:005021650.0031547.2349.985599.72
2013-07-25 00:00:006271079.7331547.2349.985599.72
2013-07-25 00:00:00226599.9931547.2349.985599.72
" ], "text/plain": [ "[(datetime.datetime(2013, 7, 25, 0, 0), 1004, Decimal('5599.72'), Decimal('31547.23'), Decimal('49.98'), Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 191, Decimal('5099.49'), Decimal('31547.23'), Decimal('49.98'), Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 957, Decimal('4499.70'), Decimal('31547.23'), Decimal('49.98'), Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 365, Decimal('3359.44'), Decimal('31547.23'), Decimal('49.98'), Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1073, Decimal('2999.85'), Decimal('31547.23'), Decimal('49.98'), Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1014, Decimal('2798.88'), Decimal('31547.23'), Decimal('49.98'), Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 403, Decimal('1949.85'), Decimal('31547.23'), Decimal('49.98'), Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 502, Decimal('1650.00'), Decimal('31547.23'), Decimal('49.98'), Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 627, Decimal('1079.73'), Decimal('31547.23'), Decimal('49.98'), Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 226, Decimal('599.99'), Decimal('31547.23'), Decimal('49.98'), Decimal('5599.72'))]" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT\n", " order_date,\n", " order_item_product_id,\n", " revenue,\n", " sum(revenue) OVER (PARTITION BY order_date) AS sum_revenue,\n", " min(revenue) OVER (PARTITION BY order_date) AS min_revenue,\n", " max(revenue) OVER (PARTITION BY order_date) AS max_revenue\n", "FROM daily_product_revenue\n", "ORDER BY order_date,\n", " revenue DESC\n", "LIMIT 10" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.6.12" } }, "nbformat": 4, "nbformat_minor": 4 }