{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "## Analytic Functions – Windowing\n", "\n", "Let us go through the list of Windowing functions supported by Postgres." ] }, { "cell_type": "code", "execution_count": 7, "metadata": { "tags": [ "remove-cell" ] }, "outputs": [ { "data": { "text/html": [ "\n" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%%HTML\n", "" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "* `lead` and `lag`\n", "* `first_value` and `last_value`\n", "* We can either use `ORDER BY sort_column` or `PARTITION BY partition_column ORDER BY sort_column` while using Windowing Functions." ] }, { "cell_type": "code", "execution_count": 8, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "The sql extension is already loaded. To reload it, use:\n", " %reload_ext sql\n" ] } ], "source": [ "%load_ext sql" ] }, { "cell_type": "code", "execution_count": 9, "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": "markdown", "metadata": {}, "source": [ "### Getting LEAD and LAG values\n", "\n", "Let us understand LEAD and LAG functions to get column values from following or prior records." ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```{note}\n", "Here is the example to get values from either immediate prior or following record along with values from curent record. We will get values from prior or following record based on `ORDER BY` within `OVER` Clause.\n", "```" ] }, { "cell_type": "code", "execution_count": 10, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_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", "
order_daterevenueprior_dateprior_revenuelag_prior_datelag_prior_revenue
2014-07-24 00:00:0050885.192014-07-23 00:00:0038795.232014-07-23 00:00:0038795.23
2014-07-23 00:00:0038795.232014-07-22 00:00:0036717.242014-07-22 00:00:0036717.24
2014-07-22 00:00:0036717.242014-07-21 00:00:0051427.702014-07-21 00:00:0051427.70
2014-07-21 00:00:0051427.702014-07-20 00:00:0060047.452014-07-20 00:00:0060047.45
2014-07-20 00:00:0060047.452014-07-19 00:00:0038420.992014-07-19 00:00:0038420.99
2014-07-19 00:00:0038420.992014-07-18 00:00:0043856.602014-07-18 00:00:0043856.60
2014-07-18 00:00:0043856.602014-07-17 00:00:0036384.772014-07-17 00:00:0036384.77
2014-07-17 00:00:0036384.772014-07-16 00:00:0043011.922014-07-16 00:00:0043011.92
2014-07-16 00:00:0043011.922014-07-15 00:00:0053480.232014-07-15 00:00:0053480.23
2014-07-15 00:00:0053480.232014-07-14 00:00:0029937.522014-07-14 00:00:0029937.52
" ], "text/plain": [ "[(datetime.datetime(2014, 7, 24, 0, 0), Decimal('50885.19'), datetime.datetime(2014, 7, 23, 0, 0), Decimal('38795.23'), datetime.datetime(2014, 7, 23, 0, 0), Decimal('38795.23')),\n", " (datetime.datetime(2014, 7, 23, 0, 0), Decimal('38795.23'), datetime.datetime(2014, 7, 22, 0, 0), Decimal('36717.24'), datetime.datetime(2014, 7, 22, 0, 0), Decimal('36717.24')),\n", " (datetime.datetime(2014, 7, 22, 0, 0), Decimal('36717.24'), datetime.datetime(2014, 7, 21, 0, 0), Decimal('51427.70'), datetime.datetime(2014, 7, 21, 0, 0), Decimal('51427.70')),\n", " (datetime.datetime(2014, 7, 21, 0, 0), Decimal('51427.70'), datetime.datetime(2014, 7, 20, 0, 0), Decimal('60047.45'), datetime.datetime(2014, 7, 20, 0, 0), Decimal('60047.45')),\n", " (datetime.datetime(2014, 7, 20, 0, 0), Decimal('60047.45'), datetime.datetime(2014, 7, 19, 0, 0), Decimal('38420.99'), datetime.datetime(2014, 7, 19, 0, 0), Decimal('38420.99')),\n", " (datetime.datetime(2014, 7, 19, 0, 0), Decimal('38420.99'), datetime.datetime(2014, 7, 18, 0, 0), Decimal('43856.60'), datetime.datetime(2014, 7, 18, 0, 0), Decimal('43856.60')),\n", " (datetime.datetime(2014, 7, 18, 0, 0), Decimal('43856.60'), datetime.datetime(2014, 7, 17, 0, 0), Decimal('36384.77'), datetime.datetime(2014, 7, 17, 0, 0), Decimal('36384.77')),\n", " (datetime.datetime(2014, 7, 17, 0, 0), Decimal('36384.77'), datetime.datetime(2014, 7, 16, 0, 0), Decimal('43011.92'), datetime.datetime(2014, 7, 16, 0, 0), Decimal('43011.92')),\n", " (datetime.datetime(2014, 7, 16, 0, 0), Decimal('43011.92'), datetime.datetime(2014, 7, 15, 0, 0), Decimal('53480.23'), datetime.datetime(2014, 7, 15, 0, 0), Decimal('53480.23')),\n", " (datetime.datetime(2014, 7, 15, 0, 0), Decimal('53480.23'), datetime.datetime(2014, 7, 14, 0, 0), Decimal('29937.52'), datetime.datetime(2014, 7, 14, 0, 0), Decimal('29937.52'))]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT t.*,\n", " lead(order_date) OVER (ORDER BY order_date DESC) AS prior_date,\n", " lead(revenue) OVER (ORDER BY order_date DESC) AS prior_revenue,\n", " lag(order_date) OVER (ORDER BY order_date) AS lag_prior_date,\n", " lag(revenue) OVER (ORDER BY order_date) AS lag_prior_revenue\n", "FROM daily_revenue AS t\n", "ORDER BY order_date DESC\n", "LIMIT 10" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```{note}\n", "Here is the example to get values from either prior or following 7th record along with values from current record.\n", "```" ] }, { "cell_type": "code", "execution_count": 11, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_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", "
order_daterevenueprior_dateprior_revenue
2014-07-24 00:00:0050885.192014-07-17 00:00:0036384.77
2014-07-23 00:00:0038795.232014-07-16 00:00:0043011.92
2014-07-22 00:00:0036717.242014-07-15 00:00:0053480.23
2014-07-21 00:00:0051427.702014-07-14 00:00:0029937.52
2014-07-20 00:00:0060047.452014-07-13 00:00:0040410.99
2014-07-19 00:00:0038420.992014-07-12 00:00:0038449.77
2014-07-18 00:00:0043856.602014-07-11 00:00:0029596.32
2014-07-17 00:00:0036384.772014-07-10 00:00:0047826.02
2014-07-16 00:00:0043011.922014-07-09 00:00:0036929.91
2014-07-15 00:00:0053480.232014-07-08 00:00:0050434.81
" ], "text/plain": [ "[(datetime.datetime(2014, 7, 24, 0, 0), Decimal('50885.19'), datetime.datetime(2014, 7, 17, 0, 0), Decimal('36384.77')),\n", " (datetime.datetime(2014, 7, 23, 0, 0), Decimal('38795.23'), datetime.datetime(2014, 7, 16, 0, 0), Decimal('43011.92')),\n", " (datetime.datetime(2014, 7, 22, 0, 0), Decimal('36717.24'), datetime.datetime(2014, 7, 15, 0, 0), Decimal('53480.23')),\n", " (datetime.datetime(2014, 7, 21, 0, 0), Decimal('51427.70'), datetime.datetime(2014, 7, 14, 0, 0), Decimal('29937.52')),\n", " (datetime.datetime(2014, 7, 20, 0, 0), Decimal('60047.45'), datetime.datetime(2014, 7, 13, 0, 0), Decimal('40410.99')),\n", " (datetime.datetime(2014, 7, 19, 0, 0), Decimal('38420.99'), datetime.datetime(2014, 7, 12, 0, 0), Decimal('38449.77')),\n", " (datetime.datetime(2014, 7, 18, 0, 0), Decimal('43856.60'), datetime.datetime(2014, 7, 11, 0, 0), Decimal('29596.32')),\n", " (datetime.datetime(2014, 7, 17, 0, 0), Decimal('36384.77'), datetime.datetime(2014, 7, 10, 0, 0), Decimal('47826.02')),\n", " (datetime.datetime(2014, 7, 16, 0, 0), Decimal('43011.92'), datetime.datetime(2014, 7, 9, 0, 0), Decimal('36929.91')),\n", " (datetime.datetime(2014, 7, 15, 0, 0), Decimal('53480.23'), datetime.datetime(2014, 7, 8, 0, 0), Decimal('50434.81'))]" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT t.*,\n", " lead(order_date, 7) OVER (ORDER BY order_date DESC) AS prior_date,\n", " lead(revenue, 7) OVER (ORDER BY order_date DESC) AS prior_revenue\n", "FROM daily_revenue t\n", "ORDER BY order_date DESC\n", "LIMIT 10" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```{note}\n", "For values related to non existing prior or following record, we will get nulls.\n", "```" ] }, { "cell_type": "code", "execution_count": 12, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_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", "
order_daterevenueprior_dateprior_revenue
2013-07-25 00:00:0031547.23NoneNone
2013-07-26 00:00:0054713.23NoneNone
2013-07-27 00:00:0048411.48NoneNone
2013-07-28 00:00:0035672.03NoneNone
2013-07-29 00:00:0054579.70NoneNone
2013-07-30 00:00:0049329.29NoneNone
2013-07-31 00:00:0059212.49NoneNone
2013-08-01 00:00:0049160.082013-07-25 00:00:0031547.23
2013-08-02 00:00:0050688.582013-07-26 00:00:0054713.23
2013-08-03 00:00:0043416.742013-07-27 00:00:0048411.48
" ], "text/plain": [ "[(datetime.datetime(2013, 7, 25, 0, 0), Decimal('31547.23'), None, None),\n", " (datetime.datetime(2013, 7, 26, 0, 0), Decimal('54713.23'), None, None),\n", " (datetime.datetime(2013, 7, 27, 0, 0), Decimal('48411.48'), None, None),\n", " (datetime.datetime(2013, 7, 28, 0, 0), Decimal('35672.03'), None, None),\n", " (datetime.datetime(2013, 7, 29, 0, 0), Decimal('54579.70'), None, None),\n", " (datetime.datetime(2013, 7, 30, 0, 0), Decimal('49329.29'), None, None),\n", " (datetime.datetime(2013, 7, 31, 0, 0), Decimal('59212.49'), None, None),\n", " (datetime.datetime(2013, 8, 1, 0, 0), Decimal('49160.08'), datetime.datetime(2013, 7, 25, 0, 0), Decimal('31547.23')),\n", " (datetime.datetime(2013, 8, 2, 0, 0), Decimal('50688.58'), datetime.datetime(2013, 7, 26, 0, 0), Decimal('54713.23')),\n", " (datetime.datetime(2013, 8, 3, 0, 0), Decimal('43416.74'), datetime.datetime(2013, 7, 27, 0, 0), Decimal('48411.48'))]" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT t.*,\n", " lead(order_date, 7) OVER (ORDER BY order_date DESC) AS prior_date,\n", " lead(revenue, 7) OVER (ORDER BY order_date DESC) AS prior_revenue\n", "FROM daily_revenue t\n", "ORDER BY order_date\n", "LIMIT 10" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```{note}\n", "We can replace nulls by passing relevant values as 3rd argument. However, the data type of the values should be compatible with the columns on which `lead` or `lag` is applied.\n", "```" ] }, { "cell_type": "code", "execution_count": 13, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_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", "
order_daterevenueprior_dateprior_revenue
2013-07-25 00:00:0031547.23None0.0
2013-07-26 00:00:0054713.23None0.0
2013-07-27 00:00:0048411.48None0.0
2013-07-28 00:00:0035672.03None0.0
2013-07-29 00:00:0054579.70None0.0
2013-07-30 00:00:0049329.29None0.0
2013-07-31 00:00:0059212.49None0.0
2013-08-01 00:00:0049160.082013-07-25 00:00:0031547.23
2013-08-02 00:00:0050688.582013-07-26 00:00:0054713.23
2013-08-03 00:00:0043416.742013-07-27 00:00:0048411.48
" ], "text/plain": [ "[(datetime.datetime(2013, 7, 25, 0, 0), Decimal('31547.23'), None, Decimal('0.0')),\n", " (datetime.datetime(2013, 7, 26, 0, 0), Decimal('54713.23'), None, Decimal('0.0')),\n", " (datetime.datetime(2013, 7, 27, 0, 0), Decimal('48411.48'), None, Decimal('0.0')),\n", " (datetime.datetime(2013, 7, 28, 0, 0), Decimal('35672.03'), None, Decimal('0.0')),\n", " (datetime.datetime(2013, 7, 29, 0, 0), Decimal('54579.70'), None, Decimal('0.0')),\n", " (datetime.datetime(2013, 7, 30, 0, 0), Decimal('49329.29'), None, Decimal('0.0')),\n", " (datetime.datetime(2013, 7, 31, 0, 0), Decimal('59212.49'), None, Decimal('0.0')),\n", " (datetime.datetime(2013, 8, 1, 0, 0), Decimal('49160.08'), datetime.datetime(2013, 7, 25, 0, 0), Decimal('31547.23')),\n", " (datetime.datetime(2013, 8, 2, 0, 0), Decimal('50688.58'), datetime.datetime(2013, 7, 26, 0, 0), Decimal('54713.23')),\n", " (datetime.datetime(2013, 8, 3, 0, 0), Decimal('43416.74'), datetime.datetime(2013, 7, 27, 0, 0), Decimal('48411.48'))]" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT t.*,\n", " lead(order_date, 7) OVER (ORDER BY order_date DESC) AS prior_date,\n", " lead(revenue, 7, 0.0) OVER (ORDER BY order_date DESC) AS prior_revenue\n", "FROM daily_revenue t\n", "ORDER BY order_date\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 14, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_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", "
order_dateorder_item_product_idrevenue
2013-07-25 00:00:0010045599.72
2013-07-25 00:00:001915099.49
2013-07-25 00:00:009574499.70
2013-07-25 00:00:003653359.44
2013-07-25 00:00:0010732999.85
2013-07-25 00:00:0010142798.88
2013-07-25 00:00:004031949.85
2013-07-25 00:00:005021650.00
2013-07-25 00:00:006271079.73
2013-07-25 00:00:00226599.99
" ], "text/plain": [ "[(datetime.datetime(2013, 7, 25, 0, 0), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 191, Decimal('5099.49')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 957, Decimal('4499.70')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 365, Decimal('3359.44')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1073, Decimal('2999.85')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1014, Decimal('2798.88')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 403, Decimal('1949.85')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 502, Decimal('1650.00')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 627, Decimal('1079.73')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 226, Decimal('599.99'))]" ] }, "execution_count": 14, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM daily_product_revenue \n", "ORDER BY order_date, revenue DESC\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 15, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "30 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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \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_idrevenuenext_product_idnext_revenue
2013-07-25 00:00:0010045599.721915099.49
2013-07-25 00:00:001915099.499574499.70
2013-07-25 00:00:009574499.703653359.44
2013-07-25 00:00:003653359.4410732999.85
2013-07-25 00:00:0010732999.8510142798.88
2013-07-25 00:00:0010142798.884031949.85
2013-07-25 00:00:004031949.855021650.00
2013-07-25 00:00:005021650.006271079.73
2013-07-25 00:00:006271079.73226599.99
2013-07-25 00:00:00226599.9924319.96
2013-07-25 00:00:0024319.96821207.96
2013-07-25 00:00:00821207.96625199.99
2013-07-25 00:00:00625199.99705119.99
2013-07-25 00:00:00705119.99572119.97
2013-07-25 00:00:00572119.97666109.99
2013-07-25 00:00:00666109.99725108.00
2013-07-25 00:00:00725108.00134100.00
2013-07-25 00:00:00134100.0090699.96
2013-07-25 00:00:0090699.9682895.97
2013-07-25 00:00:0082895.9781079.96
2013-07-25 00:00:0081079.9692479.95
2013-07-25 00:00:0092479.9592679.95
2013-07-25 00:00:0092679.959374.97
2013-07-25 00:00:009374.9783563.98
2013-07-25 00:00:0083563.9889749.98
2013-07-25 00:00:0089749.98NoneNone
2013-07-26 00:00:00100410799.463657978.67
2013-07-26 00:00:003657978.679576899.54
2013-07-26 00:00:009576899.541916799.32
2013-07-26 00:00:001916799.3210144798.08
" ], "text/plain": [ "[(datetime.datetime(2013, 7, 25, 0, 0), 1004, Decimal('5599.72'), 191, Decimal('5099.49')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 191, Decimal('5099.49'), 957, Decimal('4499.70')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 957, Decimal('4499.70'), 365, Decimal('3359.44')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 365, Decimal('3359.44'), 1073, Decimal('2999.85')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1073, Decimal('2999.85'), 1014, Decimal('2798.88')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1014, Decimal('2798.88'), 403, Decimal('1949.85')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 403, Decimal('1949.85'), 502, Decimal('1650.00')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 502, Decimal('1650.00'), 627, Decimal('1079.73')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 627, Decimal('1079.73'), 226, Decimal('599.99')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 226, Decimal('599.99'), 24, Decimal('319.96')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 24, Decimal('319.96'), 821, Decimal('207.96')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 821, Decimal('207.96'), 625, Decimal('199.99')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 625, Decimal('199.99'), 705, Decimal('119.99')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 705, Decimal('119.99'), 572, Decimal('119.97')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 572, Decimal('119.97'), 666, Decimal('109.99')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 666, Decimal('109.99'), 725, Decimal('108.00')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 725, Decimal('108.00'), 134, Decimal('100.00')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 134, Decimal('100.00'), 906, Decimal('99.96')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 906, Decimal('99.96'), 828, Decimal('95.97')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 828, Decimal('95.97'), 810, Decimal('79.96')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 810, Decimal('79.96'), 924, Decimal('79.95')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 924, Decimal('79.95'), 926, Decimal('79.95')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 926, Decimal('79.95'), 93, Decimal('74.97')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 93, Decimal('74.97'), 835, Decimal('63.98')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 835, Decimal('63.98'), 897, Decimal('49.98')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 897, Decimal('49.98'), None, None),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 1004, Decimal('10799.46'), 365, Decimal('7978.67')),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 365, Decimal('7978.67'), 957, Decimal('6899.54')),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 957, Decimal('6899.54'), 191, Decimal('6799.32')),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 191, Decimal('6799.32'), 1014, Decimal('4798.08'))]" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT t.*,\n", " LEAD(order_item_product_id) OVER (\n", " PARTITION BY order_date \n", " ORDER BY revenue DESC\n", " ) next_product_id,\n", " LEAD(revenue) OVER (\n", " PARTITION BY order_date \n", " ORDER BY revenue DESC\n", " ) next_revenue\n", "FROM daily_product_revenue t\n", "ORDER BY order_date, revenue DESC\n", "LIMIT 30" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "### Getting first and last values\n", "\n", "Let us see how we can get first and last value based on the criteria. `min` or `max` can be used to get only the min or max of the metric we are interested in, however we cannot get other attributes of those records.\n", "\n", "Here is the example of using first_value." ] }, { "cell_type": "code", "execution_count": 16, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_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", "
order_dateorder_item_product_idrevenuefirst_product_idfirst_revenuemax_revenue
2013-07-25 00:00:0010045599.7210045599.725599.72
2013-07-25 00:00:001915099.4910045599.725599.72
2013-07-25 00:00:009574499.7010045599.725599.72
2013-07-25 00:00:003653359.4410045599.725599.72
2013-07-25 00:00:0010732999.8510045599.725599.72
2013-07-25 00:00:0010142798.8810045599.725599.72
2013-07-25 00:00:004031949.8510045599.725599.72
2013-07-25 00:00:005021650.0010045599.725599.72
2013-07-25 00:00:006271079.7310045599.725599.72
2013-07-25 00:00:00226599.9910045599.725599.72
" ], "text/plain": [ "[(datetime.datetime(2013, 7, 25, 0, 0), 1004, Decimal('5599.72'), 1004, Decimal('5599.72'), Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 191, Decimal('5099.49'), 1004, Decimal('5599.72'), Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 957, Decimal('4499.70'), 1004, Decimal('5599.72'), Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 365, Decimal('3359.44'), 1004, Decimal('5599.72'), Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1073, Decimal('2999.85'), 1004, Decimal('5599.72'), Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1014, Decimal('2798.88'), 1004, Decimal('5599.72'), Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 403, Decimal('1949.85'), 1004, Decimal('5599.72'), Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 502, Decimal('1650.00'), 1004, Decimal('5599.72'), Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 627, Decimal('1079.73'), 1004, Decimal('5599.72'), Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 226, Decimal('599.99'), 1004, Decimal('5599.72'), Decimal('5599.72'))]" ] }, "execution_count": 16, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT t.*,\n", " first_value(order_item_product_id) OVER (\n", " PARTITION BY order_date ORDER BY revenue DESC\n", " ) first_product_id,\n", " first_value(revenue) OVER (\n", " PARTITION BY order_date ORDER BY revenue DESC\n", " ) first_revenue,\n", " max(revenue) OVER (\n", " PARTITION BY order_date\n", " ) max_revenue\n", "FROM daily_product_revenue t\n", "ORDER BY order_date, revenue DESC\n", "LIMIT 10" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "Let us see an example with last_value. While using last_value we need to specify **ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING**.\n", "* By default it uses `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`.\n", "* The last value with in `UNBOUNDED PRECEDING AND CURRENT ROW` will be current record.\n", "* To get the right value, we have to change the windowing clause to `ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING`." ] }, { "cell_type": "code", "execution_count": 17, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "30 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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \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_idrevenuelast_product_idlast_revenue
2013-07-25 00:00:0010045599.7210045599.72
2013-07-25 00:00:001915099.4910045599.72
2013-07-25 00:00:009574499.7010045599.72
2013-07-25 00:00:003653359.4410045599.72
2013-07-25 00:00:0010732999.8510045599.72
2013-07-25 00:00:0010142798.8810045599.72
2013-07-25 00:00:004031949.8510045599.72
2013-07-25 00:00:005021650.0010045599.72
2013-07-25 00:00:006271079.7310045599.72
2013-07-25 00:00:00226599.9910045599.72
2013-07-25 00:00:0024319.9610045599.72
2013-07-25 00:00:00821207.9610045599.72
2013-07-25 00:00:00625199.9910045599.72
2013-07-25 00:00:00705119.9910045599.72
2013-07-25 00:00:00572119.9710045599.72
2013-07-25 00:00:00666109.9910045599.72
2013-07-25 00:00:00725108.0010045599.72
2013-07-25 00:00:00134100.0010045599.72
2013-07-25 00:00:0090699.9610045599.72
2013-07-25 00:00:0082895.9710045599.72
2013-07-25 00:00:0081079.9610045599.72
2013-07-25 00:00:0092479.9510045599.72
2013-07-25 00:00:0092679.9510045599.72
2013-07-25 00:00:009374.9710045599.72
2013-07-25 00:00:0083563.9810045599.72
2013-07-25 00:00:0089749.9810045599.72
2013-07-26 00:00:00100410799.46100410799.46
2013-07-26 00:00:003657978.67100410799.46
2013-07-26 00:00:009576899.54100410799.46
2013-07-26 00:00:001916799.32100410799.46
" ], "text/plain": [ "[(datetime.datetime(2013, 7, 25, 0, 0), 1004, Decimal('5599.72'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 191, Decimal('5099.49'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 957, Decimal('4499.70'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 365, Decimal('3359.44'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1073, Decimal('2999.85'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1014, Decimal('2798.88'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 403, Decimal('1949.85'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 502, Decimal('1650.00'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 627, Decimal('1079.73'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 226, Decimal('599.99'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 24, Decimal('319.96'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 821, Decimal('207.96'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 625, Decimal('199.99'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 705, Decimal('119.99'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 572, Decimal('119.97'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 666, Decimal('109.99'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 725, Decimal('108.00'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 134, Decimal('100.00'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 906, Decimal('99.96'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 828, Decimal('95.97'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 810, Decimal('79.96'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 924, Decimal('79.95'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 926, Decimal('79.95'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 93, Decimal('74.97'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 835, Decimal('63.98'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 897, Decimal('49.98'), 1004, Decimal('5599.72')),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 1004, Decimal('10799.46'), 1004, Decimal('10799.46')),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 365, Decimal('7978.67'), 1004, Decimal('10799.46')),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 957, Decimal('6899.54'), 1004, Decimal('10799.46')),\n", " (datetime.datetime(2013, 7, 26, 0, 0), 191, Decimal('6799.32'), 1004, Decimal('10799.46'))]" ] }, "execution_count": 17, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT t.*,\n", " last_value(order_item_product_id) OVER (\n", " PARTITION BY order_date ORDER BY revenue \n", " ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING\n", " ) last_product_id,\n", " max(revenue) OVER (\n", " PARTITION BY order_date\n", " ) last_revenue\n", "FROM daily_product_revenue AS t\n", "ORDER BY order_date, revenue DESC\n", "LIMIT 30" ] } ], "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 }