Section 07 Common Table Expressions/27 An Introduction to MySQL CTE.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "52ade747-9b17-478b-b201-9ba6b899d6ba",
   "metadata": {
    "tags": [],
    "toc-hr-collapsed": true
   },
   "source": [
    "## **[An Introduction to MySQL CTE](https://www.mysqltutorial.org/mysql-cte/)**"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1635d7e7-03dd-486f-b930-cbea3bda3eb2",
   "metadata": {},
   "source": [
    "Use MySQL CTE or common table expression to construct complex queries in a more readable manner."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "2a9dbbd7-1450-43c0-b776-0ebf52ab398f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import re\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.image as mpimg\n",
    "from IPython.display import Image, SVG\n",
    "\n",
    "from sqlalchemy_utils import database_exists, create_database\n",
    "from sqlalchemy import create_engine, inspect, MetaData, text\n",
    "from sqlalchemy_schemadisplay import create_schema_graph\n",
    "import pymysql\n",
    "\n",
    "pd.set_option(\n",
    "    'display.max_columns', None,\n",
    "    'expand_frame_repr', True,\n",
    "    'display.max_colwidth', None,\n",
    "    'display.max_rows', 10,\n",
    ")\n",
    "\n",
    "pd.set_option('display.width', 65)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "65b9c228-0686-4966-b98b-37f9b45fb45e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# connect to the classicmodels database\n",
    "connect_args={'ssl':{'fake_flag_to_enable_tls': True}}\n",
    "\n",
    "engine = create_engine('mysql+pymysql://namlq:abc123@localhost/classicmodels',\n",
    "                       connect_args=connect_args, echo=False\n",
    "                         )\n",
    "inspector = inspect(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "585761e9-c914-43d3-acc4-d617a8604f71",
   "metadata": {},
   "source": [
    "### What is a common table expression or CTE"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0aa65bc-e6f3-4ffc-a2bb-2f003a8e2fd4",
   "metadata": {
    "tags": []
   },
   "source": [
    "### MySQL CTE syntax"
   ]
  },
  {
   "cell_type": "raw",
   "id": "fda68c08-a97f-485c-9139-8ebc7f1a6bbe",
   "metadata": {},
   "source": [
    "WITH cte_name (column_list) AS (\n",
    "    query\n",
    ") \n",
    "SELECT * FROM cte_name;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab3f32ce-eedf-4bb7-a87a-aa3d4fdc49f4",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Simple MySQL CTE examples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "b9fda77c-e4d3-496d-b867-ad2db266dc86",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "WITH customers_in_usa AS (\n",
    "    SELECT customerName, state\n",
    "    FROM customers\n",
    "    WHERE country = 'USA'\n",
    ") \n",
    "SELECT customerName\n",
    "FROM customers_in_usa\n",
    "WHERE state = 'CA'\n",
    "ORDER BY customerName\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "c6be39ef-37c7-4060-b66d-812e698326ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "df2 = (\n",
    "    pd.read_sql_table(\n",
    "        'customers', engine, columns=['customerName', 'country', 'state'])\n",
    "    .query(\"(country == 'USA') and (state == 'CA')\")\n",
    "    [['customerName']]\n",
    "    .sort_values(by='customerName', key=lambda col: col.str.upper())\n",
    "    .reset_index(drop=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "bbe1e9cb-b8e8-4fd0-abe1-bfaea55c615c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "5261db40-9c4f-4e56-9d80-9cd2e820e3df",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customerName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Boards &amp; Toys Co.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Collectable Mini Designs Co.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Corporate Gift Ideas Co.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Men 'R' US Retailers, Ltd.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Mini Gifts Distributors Ltd.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   customerName\n",
       "0             Boards & Toys Co.\n",
       "1  Collectable Mini Designs Co.\n",
       "2      Corporate Gift Ideas Co.\n",
       "3    Men 'R' US Retailers, Ltd.\n",
       "4  Mini Gifts Distributors Ltd."
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "741e4c6e-5ca9-46c0-b3c9-2ade11e05414",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "WITH topsales2003 AS (\n",
    "    SELECT\n",
    "        salesRepEmployeeNumber employeeNumber,\n",
    "        SUM(quantityOrdered * priceEach) sales\n",
    "    FROM orders\n",
    "    INNER JOIN orderdetails USING (orderNumber)\n",
    "    INNER JOIN customers USING (customerNumber)\n",
    "    WHERE YEAR(shippedDate) = 2003 AND status = 'Shipped'\n",
    "    GROUP BY salesRepEmployeeNumber\n",
    "    ORDER BY sales DESC\n",
    "    LIMIT 5\n",
    ")\n",
    "SELECT employeeNumber, firstName, lastName, sales\n",
    "FROM employees\n",
    "JOIN topsales2003 USING (employeeNumber)\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "49e25e11-3f4b-43cd-abd0-27ac4fdf69ad",
   "metadata": {},
   "outputs": [],
   "source": [
    "topsales2003 = (\n",
    "    pd.read_sql_table('orders', engine)\n",
    "    .merge(pd.read_sql_table('orderdetails', engine),\n",
    "           on='orderNumber', how='inner')\n",
    "    .merge(pd.read_sql_table('customers', engine),\n",
    "           on='customerNumber', how='inner')\n",
    "    .query(\"(shippedDate.dt.year == 2003) and (status == 'Shipped')\")\n",
    "    .assign(sales = lambda df: df.quantityOrdered * df.priceEach)\n",
    "    .groupby('salesRepEmployeeNumber', as_index=False)\n",
    "    .agg({'sales': 'sum'})\n",
    "    .rename(columns={'salesRepEmployeeNumber': 'employeeNumber'})\n",
    "    [['employeeNumber', 'sales']]\n",
    "    .sort_values(by='sales', ascending=False, ignore_index=True)\n",
    "    .head(5)\n",
    ")\n",
    "\n",
    "df2 = (\n",
    "    pd.read_sql_table('employees', engine)\n",
    "    .merge(topsales2003, on='employeeNumber', how='inner', sort=True)\n",
    "    [['employeeNumber', 'firstName', 'lastName', 'sales']]\n",
    "    .sort_values(by='sales', ascending=False, ignore_index=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "a1050847-bdf2-4e09-8f9c-5ce46f6d0e25",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "308b3d1d-0a70-486d-982c-0fd7520c2f80",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>employeeNumber</th>\n",
       "      <th>firstName</th>\n",
       "      <th>lastName</th>\n",
       "      <th>sales</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1165</td>\n",
       "      <td>Leslie</td>\n",
       "      <td>Jennings</td>\n",
       "      <td>413219.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1370</td>\n",
       "      <td>Gerard</td>\n",
       "      <td>Hernandez</td>\n",
       "      <td>295246.44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1401</td>\n",
       "      <td>Pamela</td>\n",
       "      <td>Castillo</td>\n",
       "      <td>289982.88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1621</td>\n",
       "      <td>Mami</td>\n",
       "      <td>Nishi</td>\n",
       "      <td>267249.40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1501</td>\n",
       "      <td>Larry</td>\n",
       "      <td>Bott</td>\n",
       "      <td>261536.95</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   employeeNumber firstName   lastName      sales\n",
       "0            1165    Leslie   Jennings  413219.85\n",
       "1            1370    Gerard  Hernandez  295246.44\n",
       "2            1401    Pamela   Castillo  289982.88\n",
       "3            1621      Mami      Nishi  267249.40\n",
       "4            1501     Larry       Bott  261536.95"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7822a33-47f7-4a2e-bbe9-03ba42f2af64",
   "metadata": {
    "tags": []
   },
   "source": [
    "### A more advanced MySQL CTE example"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "05081969-a8ab-45b9-b586-a79381c1f640",
   "metadata": {},
   "outputs": [],
   "source": [
    "string = '''\n",
    "WITH salesrep AS (\n",
    "    SELECT\n",
    "        employeeNumber,\n",
    "        CONCAT(firstName, ' ', lastName) salesRepName\n",
    "    FROM employees\n",
    "    WHERE jobTitle = 'Sales Rep'\n",
    "),\n",
    "customer_salesrep AS (\n",
    "    SELECT customerName, salesRepName\n",
    "    FROM customers\n",
    "    INNER JOIN salesrep ON \n",
    "        employeeNumber = salesRepEmployeeNumber\n",
    ")\n",
    "SELECT *\n",
    "FROM customer_salesrep\n",
    "ORDER BY customerName\n",
    ";'''\n",
    "\n",
    "df1 = pd.read_sql(string, engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "08f04c81-7173-4dcb-ad89-a5153a4f637c",
   "metadata": {},
   "outputs": [],
   "source": [
    "salesrep = (\n",
    "    pd.read_sql_table('employees', engine)\n",
    "    .assign(salesRepName = lambda df: df.firstName.astype(str) +\n",
    "            ' ' + df.lastName.astype(str))\n",
    "    .query(\"jobTitle == 'Sales Rep'\")\n",
    "    [['employeeNumber', 'salesRepName']]\n",
    ")\n",
    "\n",
    "df2 = (\n",
    "    pd.read_sql_table('customers', engine)\n",
    "    .merge(salesrep, \n",
    "           left_on='salesRepEmployeeNumber',\n",
    "           right_on='employeeNumber',\n",
    "           how='inner')\n",
    "    [['customerName', 'salesRepName']]\n",
    "    .sort_values('customerName', \n",
    "                 key=lambda col: col.str.upper(),\n",
    "                 ignore_index=True)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "21043159-214e-4727-8db3-c277c10a521d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.equals(df2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "20f0031a-f8c1-4303-969c-c963b18d74ec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>customerName</th>\n",
       "      <th>salesRepName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alpha Cognac</td>\n",
       "      <td>Gerard Hernandez</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>American Souvenirs Inc</td>\n",
       "      <td>Foon Yue Tseng</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Amica Models &amp; Co.</td>\n",
       "      <td>Pamela Castillo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Anna's Decorations, Ltd</td>\n",
       "      <td>Andy Fixter</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Atelier graphique</td>\n",
       "      <td>Gerard Hernandez</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              customerName      salesRepName\n",
       "0             Alpha Cognac  Gerard Hernandez\n",
       "1   American Souvenirs Inc    Foon Yue Tseng\n",
       "2       Amica Models & Co.   Pamela Castillo\n",
       "3  Anna's Decorations, Ltd       Andy Fixter\n",
       "4        Atelier graphique  Gerard Hernandez"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b586973-1850-4bf7-8364-704ee24121cc",
   "metadata": {
    "tags": []
   },
   "source": [
    "### The WITH clause usages"
   ]
  },
  {
   "cell_type": "raw",
   "id": "28744adb-592c-4a9c-95be-ed9bca838212",
   "metadata": {},
   "source": [
    "WITH ... SELECT ...\n",
    "WITH ... UPDATE ...\n",
    "WITH ... DELETE ..."
   ]
  },
  {
   "cell_type": "raw",
   "id": "de176606-6d0c-4cbf-8f2f-f9e314a6324e",
   "metadata": {},
   "source": [
    "SELECT ... WHERE id IN (WITH ... SELECT ...);\n",
    "\n",
    "SELECT * FROM (WITH ... SELECT ...) AS derived_table;"
   ]
  },
  {
   "cell_type": "raw",
   "id": "a4714e4c-0a41-4b95-90e0-b587ec6b5cdf",
   "metadata": {},
   "source": [
    "CREATE TABLE ... WITH ... SELECT ...\n",
    "CREATE VIEW ... WITH ... SELECT ...\n",
    "INSERT ... WITH ... SELECT ...\n",
    "REPLACE ... WITH ... SELECT ...\n",
    "DECLARE CURSOR ... WITH ... SELECT ...\n",
    "EXPLAIN ... WITH ... SELECT ..."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.9.15"
  },
  "toc-autonumbering": true,
  "toc-showmarkdowntxt": false,
  "toc-showtags": false
 },
 "nbformat": 4,
 "nbformat_minor": 5
}