{ "cells": [ { "cell_type": "markdown", "metadata": {}, "source": [ "## Named Queries - Using WITH Clause\n", "\n", "Let us understand how to use `WITH` clause to define a named query." ] }, { "cell_type": "code", "execution_count": 16, "metadata": { "tags": [ "remove-cell" ] }, "outputs": [ { "data": { "text/html": [ "\n" ], "text/plain": [ "" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "%%HTML\n", "" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "* At times we might have to develop a large query in which same complex logic need to be used multiple times. The query can become cumbersome if you just define the same logic multiple times.\n", "* One of the way to mitigate that issue is by providing the name to the logic using WITH clause.\n", "* We can only use the names provided to named queries as part of the main query which follows the WITH clause.\n", "\n", "```{note}\n", "In case of frequently used complex and large query, we use named queries while defining the views. We will then use view for reporting purposes.\n", "```" ] }, { "cell_type": "code", "execution_count": 17, "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": 18, "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": 19, "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \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_idorder_dateorder_customer_idorder_statusorder_item_idorder_item_order_idorder_item_product_idorder_item_quantityorder_item_subtotalorder_item_product_price
12013-07-25 00:00:0011599CLOSED119571299.98299.98
22013-07-25 00:00:00256PENDING_PAYMENT2210731199.99199.99
22013-07-25 00:00:00256PENDING_PAYMENT325025250.050.0
22013-07-25 00:00:00256PENDING_PAYMENT424031129.99129.99
42013-07-25 00:00:008827CLOSED54897249.9824.99
42013-07-25 00:00:008827CLOSED643655299.9559.99
42013-07-25 00:00:008827CLOSED745023150.050.0
42013-07-25 00:00:008827CLOSED8410144199.9249.98
52013-07-25 00:00:0011318COMPLETE959571299.98299.98
52013-07-25 00:00:0011318COMPLETE1053655299.9559.99
" ], "text/plain": [ "[(1, datetime.datetime(2013, 7, 25, 0, 0), 11599, 'CLOSED', 1, 1, 957, 1, 299.98, 299.98),\n", " (2, datetime.datetime(2013, 7, 25, 0, 0), 256, 'PENDING_PAYMENT', 2, 2, 1073, 1, 199.99, 199.99),\n", " (2, datetime.datetime(2013, 7, 25, 0, 0), 256, 'PENDING_PAYMENT', 3, 2, 502, 5, 250.0, 50.0),\n", " (2, datetime.datetime(2013, 7, 25, 0, 0), 256, 'PENDING_PAYMENT', 4, 2, 403, 1, 129.99, 129.99),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 8827, 'CLOSED', 5, 4, 897, 2, 49.98, 24.99),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 8827, 'CLOSED', 6, 4, 365, 5, 299.95, 59.99),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 8827, 'CLOSED', 7, 4, 502, 3, 150.0, 50.0),\n", " (4, datetime.datetime(2013, 7, 25, 0, 0), 8827, 'CLOSED', 8, 4, 1014, 4, 199.92, 49.98),\n", " (5, datetime.datetime(2013, 7, 25, 0, 0), 11318, 'COMPLETE', 9, 5, 957, 1, 299.98, 299.98),\n", " (5, datetime.datetime(2013, 7, 25, 0, 0), 11318, 'COMPLETE', 10, 5, 365, 5, 299.95, 59.99)]" ] }, "execution_count": 19, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "WITH order_details_nq AS (\n", " SELECT * FROM orders o\n", " JOIN order_items oi\n", " on o.order_id = oi.order_item_order_id\n", ") SELECT * FROM order_details_nq LIMIT 10" ] }, { "cell_type": "markdown", "metadata": {}, "source": [ "```{error}\n", "One cannot use the named queries apart from the query in which it is defined. Following query will fail.\n", "```" ] }, { "cell_type": "code", "execution_count": 20, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "(psycopg2.errors.UndefinedTable) relation \"order_details_nq\" does not exist\n", "LINE 1: SELECT * FROM order_details_nq LIMIT 10\n", " ^\n", "\n", "[SQL: SELECT * FROM order_details_nq LIMIT 10]\n", "(Background on this error at: http://sqlalche.me/e/13/f405)\n" ] } ], "source": [ "%%sql\n", "\n", "SELECT * FROM order_details_nq LIMIT 10" ] }, { "cell_type": "code", "execution_count": 21, "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:00100410799.46
2013-07-25 00:00:009579599.36
2013-07-25 00:00:001918499.15
2013-07-25 00:00:003657558.74
2013-07-25 00:00:0010736999.65
2013-07-25 00:00:0010146397.44
2013-07-25 00:00:004035589.57
2013-07-25 00:00:005025100.00
2013-07-25 00:00:006272879.28
2013-07-25 00:00:00226599.99
" ], "text/plain": [ "[(datetime.datetime(2013, 7, 25, 0, 0), 1004, Decimal('10799.46')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 957, Decimal('9599.36')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 191, Decimal('8499.15')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 365, Decimal('7558.74')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1073, Decimal('6999.65')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1014, Decimal('6397.44')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 403, Decimal('5589.57')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 502, Decimal('5100.00')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 627, Decimal('2879.28')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 226, Decimal('599.99'))]" ] }, "execution_count": 21, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "WITH order_details_nq AS (\n", " SELECT * FROM orders o\n", " JOIN order_items oi\n", " on o.order_id = oi.order_item_order_id\n", ") SELECT order_date,\n", " order_item_product_id,\n", " round(sum(order_item_subtotal)::numeric, 2) AS revenue\n", "FROM order_details_nq \n", "GROUP BY order_date,\n", " order_item_product_id\n", "ORDER BY order_date,\n", " revenue DESC\n", "LIMIT 10" ] }, { "cell_type": "code", "execution_count": 22, "metadata": {}, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ " * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db\n", "Done.\n" ] }, { "data": { "text/plain": [ "[]" ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "CREATE OR REPLACE VIEW daily_product_revenue_v\n", "AS\n", "WITH order_details_nq AS (\n", " SELECT * FROM orders o\n", " JOIN order_items oi\n", " on o.order_id = oi.order_item_order_id\n", ") SELECT order_date,\n", " order_item_product_id,\n", " round(sum(order_item_subtotal)::numeric, 2) AS revenue\n", "FROM order_details_nq \n", "GROUP BY order_date,\n", " order_item_product_id" ] }, { "cell_type": "code", "execution_count": 23, "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:00100410799.46
2013-07-25 00:00:009579599.36
2013-07-25 00:00:001918499.15
2013-07-25 00:00:003657558.74
2013-07-25 00:00:0010736999.65
2013-07-25 00:00:0010146397.44
2013-07-25 00:00:004035589.57
2013-07-25 00:00:005025100.00
2013-07-25 00:00:006272879.28
2013-07-25 00:00:00226599.99
" ], "text/plain": [ "[(datetime.datetime(2013, 7, 25, 0, 0), 1004, Decimal('10799.46')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 957, Decimal('9599.36')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 191, Decimal('8499.15')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 365, Decimal('7558.74')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1073, Decimal('6999.65')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 1014, Decimal('6397.44')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 403, Decimal('5589.57')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 502, Decimal('5100.00')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 627, Decimal('2879.28')),\n", " (datetime.datetime(2013, 7, 25, 0, 0), 226, Decimal('599.99'))]" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "%%sql\n", "\n", "SELECT * FROM daily_product_revenue_v\n", "ORDER BY order_date, 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 }