ws02_views-and-case-when.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "00000-19d4ddfa-9c1d-4a46-84db-cd543206483f",
    "deepnote_cell_type": "markdown",
    "tags": []
   },
   "source": [
    "# Workshop 2: CASE_WHEN, VIEWs und User Defined Functions (UDF)\n",
    "\n",
    "In diesem Workshop geht es um Techniken, um häufig benutzte Queries zu automatisieren und die zu optimieren. \n",
    "\n",
    "* Schwierigkeit: einfach\n",
    "* Prüfungsrelevant: ja (ausser UDF)\n",
    "* Zeitaufwand: 30min\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "cell_id": "00001-f1d9a1dd-1ed1-4738-86ff-baed987a62cc",
    "deepnote_cell_type": "code",
    "deepnote_to_be_reexecuted": false,
    "execution_millis": 26,
    "execution_start": 1631441521612,
    "source_hash": "c74526d2",
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Connection to SQLite DB 2.6.0 (./data/chinook.sqlite) successful\n"
     ]
    }
   ],
   "source": [
    "# Hilfsfunktionen laden und ...\n",
    "from util.sqlite_util import *\n",
    "\n",
    "# ... Test-Datenbank öffnen\n",
    "path = get_connection_path(\"chinook.sqlite\")\n",
    "connection = create_connection(path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "00002-8471aefe-5f74-4f58-83e4-de9ea1898bf3",
    "deepnote_cell_type": "text-cell-h2",
    "is_collapsed": false,
    "tags": []
   },
   "source": [
    "## 1. CASE WHEN\n",
    "\n",
    "* CASE WHEN ist vergleichbar mit if-then-else in Python\n",
    "* CASE WHEN kann an beliebigen Stellen in einer Query verwendet werden. \n",
    "* Üblich ist der SELECT-Part, um Daten zu formatieren (siehe Beispiel)\n",
    "* Aufgabe 1 zeigt eine Alternative in der ORDER BY Clause. \n",
    "\n",
    "``` sql\n",
    "CASE case_expression\n",
    "     WHEN when_expression_1 THEN result_1\n",
    "     WHEN when_expression_2 THEN result_2\n",
    "     ...\n",
    "     [ ELSE result_else ] \n",
    "END [field_name]\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "cell_id": "00003-21fbb041-55aa-4ad8-907c-3fcc3a77dc79",
    "deepnote_cell_type": "code",
    "deepnote_to_be_reexecuted": false,
    "execution_millis": 47,
    "execution_start": 1631441521644,
    "source_hash": "59489435",
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'For Those About To Rock (We Salute You)', 'long')\n",
      "(2, 'Balls to the Wall', 'long')\n",
      "(3, 'Fast As a Shark', 'medium')\n",
      "(4, 'Restless and Wild', 'medium')\n",
      "(5, 'Princess of the Dawn', 'long')\n",
      "(6, 'Put The Finger On You', 'medium')\n",
      "(7, \"Let's Get It Up\", 'medium')\n",
      "(8, 'Inject The Venom', 'medium')\n",
      "(9, 'Snowballed', 'medium')\n",
      "(10, 'Evil Walks', 'medium')\n"
     ]
    }
   ],
   "source": [
    "# Beispiel 1.1\n",
    "stmt = \"\"\"SELECT\n",
    "        trackid, name,\n",
    "    CASE\n",
    "        WHEN milliseconds <= 60000 THEN 'short'\n",
    "        WHEN milliseconds > 60000 AND \n",
    "            milliseconds < 300000 THEN 'medium'\n",
    "        ELSE\n",
    "            'long'\n",
    "        END category\n",
    "FROM\n",
    "    tracks\n",
    "LIMIT 10;\n",
    "\"\"\"\n",
    "cur = execute_query(connection, stmt)\n",
    "rows = cur.fetchall()\n",
    "for row in rows:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['GenreId', 'Name']\n",
      "(1, 'Rock')\n",
      "(2, 'Jazz')\n",
      "(3, 'Metal')\n",
      "(4, 'Alternative & Punk')\n",
      "(5, 'Rock And Roll')\n",
      "(6, 'Blues')\n",
      "(7, 'Latin')\n",
      "(8, 'Reggae')\n",
      "(9, 'Pop')\n",
      "(10, 'Soundtrack')\n"
     ]
    }
   ],
   "source": [
    "# Aufgabe 1.1: Sortiere (ORDER BY) die Genres so, dass zuerst die Begriffe ohne und dann mit Leerzeichen angezeigt werden (LIKE benutzen).\n",
    "# Tip: mit || können zwei Strings verbunden werden, '_' wird am Schluss des Alphabets einsortiert.\n",
    "\n",
    "stmt = \"\"\"\n",
    "SELECT *\n",
    "    FROM Genres\n",
    "    ;\n",
    "\"\"\"\n",
    "\n",
    "cur = execute_query(connection, stmt)\n",
    "print_results(cur)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "00002-6d9c5982-65c2-4f23-8b42-6240c1026962",
    "deepnote_cell_type": "text-cell-h2",
    "is_collapsed": false,
    "tags": []
   },
   "source": [
    "## 2. VIEW\n",
    "\n",
    "Eine VIEW enthält die Resultate einer gespeicherten Query, also eines SELECT-Statements. VIEWS werden benutzt,\n",
    "1. als Abstraktions-Layer über Tabellen. Views können nur bestimmte Spalten einer Tabelle anzeigen, ohne dass das Datenbankmodell verändert werden muss.\n",
    "2. als Methode, um komplexe Queries mit JOINS einfacher verfügbar zu machen. \n",
    "In `sqlite` können VIEWS nur gelesen werden (`SELECT`). `INSERT`, `UPDATE` und `DELETE` funktionieren nicht.\n",
    "\n",
    "### Syntax\n",
    "``` sql\n",
    "CREATE [TEMP] VIEW [IF NOT EXISTS] view_name[(column-name-list)]\n",
    "AS \n",
    "   select-statement;\n",
    "```\n",
    "\n",
    "* `TEMP` erzeugt eine temporäre View, die nach beenden der Session wieder glöscht wird. Ansonsten ist die View für immer Verfügbar.\n",
    "* `column-name-list` erlaubt es eigene Namen für die Spalten einer View zu definieren. Ein Beispiel findet sich hier: https://www.sqlitetutorial.net/sqlite-create-view/\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "cell_id": "00002-5b451693-c7fa-43e9-a967-f1884a57397e",
    "deepnote_cell_type": "code",
    "deepnote_to_be_reexecuted": false,
    "execution_millis": 50,
    "execution_start": 1631441521688,
    "source_hash": "3f4d946d",
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CREATE VIEW CustomerInvoices\n",
      "AS\n",
      "SELECT\n",
      "\tCustomerId,\n",
      "\tstrftime('%Y',InvoiceDate) Year,\n",
      "\tSUM( total ) Total\n",
      "FROM\n",
      "\tinvoices\n",
      "\tGROUP BY CustomerId, strftime('%Y',InvoiceDate)\n"
     ]
    }
   ],
   "source": [
    "# Beispiel 2.1: erstellen wir nun also eine sogenannte View. Diese kann dann wie eine Tabelle verwendet werden.\n",
    "# Views werden bei jedem Aufruf neu berechnet.\n",
    "stmt=\"\"\"\n",
    "CREATE VIEW IF NOT EXISTS CustomerInvoices\n",
    "AS\n",
    "SELECT\n",
    "    CustomerId,\n",
    "    strftime('%Y',InvoiceDate) Year,\n",
    "    SUM( total ) Total\n",
    "FROM\n",
    "    invoices\n",
    "    GROUP BY CustomerId, strftime('%Y',InvoiceDate);\n",
    "\"\"\"\n",
    "cur = execute_query(connection, stmt)\n",
    "print(desc_table(connection, \"CustomerInvoices\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Aufgabe 2.1: Wähle ein SELECT-Statement aus ws01 und erstelle eine View daraus. Erstelle einige Abfragen auf dieser View."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "cell_id": "00005-21648041-7513-450b-b963-d76319f6d0db",
    "deepnote_cell_type": "text-cell-h2",
    "is_collapsed": false,
    "tags": []
   },
   "source": [
    "## 3. User Defined Functions (UDF)\n",
    "UDFs sind in Python erstellte Funktionen, welche bei `sqlite` registriert werden und dann in einem SQL-Statement verwendet werden können.\n",
    "\n",
    "``` python\n",
    "def _myfunc(p1, p2):\n",
    "\tpass\n",
    "\n",
    "# syntax\n",
    "connection.create_function(name, num_params, func)\n",
    "\n",
    "# beispiel\n",
    "connection.create_function(‘myfunc‘, 2, _myfunc)\n",
    "```\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "cell_id": "00006-8df3ea89-1fd2-425e-a831-dc954d0ee0a8",
    "deepnote_cell_type": "code",
    "deepnote_to_be_reexecuted": false,
    "execution_millis": 18,
    "execution_start": 1631441521775,
    "source_hash": "8ff1aaf4",
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Name', 'title(name)', 'upper(name)']\n",
      "('Rock', 'Rock', 'ROCK')\n",
      "('Jazz', 'Jazz', 'JAZZ')\n",
      "('Metal', 'Metal', 'METAL')\n",
      "('Alternative & Punk', 'Alternative & Punk', 'ALTERNATIVE & PUNK')\n",
      "('Rock And Roll', 'Rock And Roll', 'ROCK AND ROLL')\n",
      "('Blues', 'Blues', 'BLUES')\n",
      "('Latin', 'Latin', 'LATIN')\n",
      "('Reggae', 'Reggae', 'REGGAE')\n",
      "('Pop', 'Pop', 'POP')\n",
      "('Soundtrack', 'Soundtrack', 'SOUNDTRACK')\n"
     ]
    }
   ],
   "source": [
    "# Beispiel 3.1: String Manipulation\n",
    "    \n",
    "import sqlite3\n",
    "\n",
    "def _toTitleCase(string):\n",
    "    return str(string).title()\n",
    "def _toUpperCase(string):\n",
    "    return str(string).upper()\n",
    "\n",
    "connection.create_function(\"title\", 1, _toTitleCase)\n",
    "connection.create_function(\"upper\", 1, _toUpperCase)\n",
    "\n",
    "stmt=\"\"\"SELECT name, title(name), upper(name) FROM genres;\"\"\"\n",
    "\n",
    "cur = execute_query(connection, stmt)\n",
    "print_results(cur)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "allow_embed": false,
    "cell_id": "00008-7b95afc8-3e2b-453a-b3b1-e3d634ee9d7a",
    "deepnote_cell_type": "code",
    "deepnote_to_be_reexecuted": false,
    "execution_millis": 17,
    "execution_start": 1631441521802,
    "source_hash": "8b0d351c",
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['Name', 'formatbytes(bytes)']\n",
      "('For Those About To Rock (We Salute You)', '10.65mb')\n",
      "('Balls to the Wall', '5.26mb')\n",
      "('Fast As a Shark', '3.81mb')\n",
      "('Restless and Wild', '4.13mb')\n",
      "('Princess of the Dawn', '6.0mb')\n",
      "('Put The Finger On You', '6.4mb')\n",
      "(\"Let's Get It Up\", '7.28mb')\n",
      "('Inject The Venom', '6.54mb')\n",
      "('Snowballed', '6.29mb')\n",
      "('Evil Walks', '8.21mb')\n"
     ]
    }
   ],
   "source": [
    "# Beispiel 3.2: String Formatierung\n",
    "def _formatBytes(bytes):\n",
    "    fmt = \"b\"\n",
    "    if bytes > 1024:\n",
    "        bytes = bytes / 1024\n",
    "        fmt = \"kb\"\n",
    "    if bytes > 1024:\n",
    "        bytes = bytes / 1024\n",
    "        fmt = \"mb\"\n",
    "    if bytes > 1024:\n",
    "        bytes = bytes / 1024\n",
    "        fmt = \"gb\"\n",
    "    if bytes > 1024:\n",
    "        bytes = bytes / 1024\n",
    "        fmt = \"tb\"\n",
    "    return \"%s%s\" % (round(bytes,2), fmt)\n",
    "\n",
    "connection.create_function(\"formatbytes\", 1, _formatBytes)\n",
    "\n",
    "stmt=\"\"\"SELECT name, formatbytes(bytes) FROM tracks LIMIT 10;\"\"\"\n",
    "cur = execute_query(connection, stmt)\n",
    "print_results(cur)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "cell_id": "00012-7fcae57d-e0b0-4538-8558-fba7e3c060d7",
    "deepnote_cell_type": "code",
    "deepnote_to_be_reexecuted": false,
    "execution_millis": 5,
    "execution_start": 1631441521827,
    "source_hash": "efbfe77c",
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['log(milliseconds)']\n",
      "(5.536203539606202,)\n",
      "(5.534739185524943,)\n",
      "(5.3628950846379295,)\n",
      "(5.401488424819678,)\n",
      "(5.574515091709049,)\n",
      "(5.313154054869936,)\n",
      "(5.36907849469925,)\n",
      "(5.323940648389206,)\n",
      "(5.307714200040391,)\n",
      "(5.420775674991212,)\n"
     ]
    }
   ],
   "source": [
    "# Beispiel 3.3: existierende Python-Funktionen registrieren.\n",
    "import math;\n",
    "connection.create_function(\"log\", 1, math.log10)\n",
    "stmt=\"\"\"SELECT log(milliseconds) FROM tracks LIMIT 10;\"\"\" # dieses Beispiel ist nur illustrativ und macht keinen wirklichen Sinn.\n",
    "cur = execute_query(connection, stmt)\n",
    "print_results(cur)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Aufgabe 3.1: Implementiere die 'short', 'medium' und 'long'-Funtionalität aus Beispiel 1.1 als UDF\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Lösungen"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['GenreId', 'Name']\n",
      "(23, 'Alternative')\n",
      "(6, 'Blues')\n",
      "(24, 'Classical')\n",
      "(22, 'Comedy')\n",
      "(21, 'Drama')\n",
      "(15, 'Electronica/Dance')\n",
      "(2, 'Jazz')\n",
      "(7, 'Latin')\n",
      "(3, 'Metal')\n",
      "(25, 'Opera')\n"
     ]
    }
   ],
   "source": [
    "# Aufgabe 1.1: Sortiere (ORDER BY) die Genres so, dass zuerst die Begriffe ohne und dann mit Leerzeichen angezeigt werden (LIKE benutzen).\n",
    "# Tip: mit || können zwei Strings verbunden werden, '_' wird am Schluss des Alphabets einsortiert.\n",
    "\n",
    "stmt = \"\"\"\n",
    "SELECT *\n",
    "    FROM Genres\n",
    "ORDER BY CASE \n",
    "    WHEN name LIKE '% %' THEN ('_' || name)\n",
    "    ELSE name\n",
    "    END\n",
    "    ;\n",
    "\"\"\"\n",
    "\n",
    "cur = execute_query(connection, stmt)\n",
    "print_results(cur)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Aufgabe 2.1: keine Lösung, da generisch. Bitte nachfragen bei Verständnisproblemen."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['TrackId', 'Name', 'size(milliseconds)']\n",
      "(1, 'For Those About To Rock (We Salute You)', 'long')\n",
      "(2, 'Balls to the Wall', 'long')\n",
      "(3, 'Fast As a Shark', 'medium')\n",
      "(4, 'Restless and Wild', 'medium')\n",
      "(5, 'Princess of the Dawn', 'long')\n",
      "(6, 'Put The Finger On You', 'medium')\n",
      "(7, \"Let's Get It Up\", 'medium')\n",
      "(8, 'Inject The Venom', 'medium')\n",
      "(9, 'Snowballed', 'medium')\n",
      "(10, 'Evil Walks', 'medium')\n"
     ]
    }
   ],
   "source": [
    "# Aufgabe 3.1: Implementiere die 'short', 'medium' und 'long'-Funtionalität aus Beispiel 1.1 als UDF\n",
    "def _track_size(milliseconds):\n",
    "    if milliseconds < 60000:\n",
    "        return 'short'\n",
    "    if milliseconds < 300000:\n",
    "        return 'medium'\n",
    "    return 'long'\n",
    "\n",
    "connection.create_function(\"size\", 1, _track_size)\n",
    "\n",
    "stmt = \"\"\"SELECT\n",
    "    trackid, name, size(milliseconds)\n",
    "FROM\n",
    "    tracks\n",
    "LIMIT 10;\n",
    "\"\"\"\n",
    "cur = execute_query(connection, stmt)\n",
    "print_results(cur)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "deepnote": {
   "is_reactive": false
  },
  "deepnote_execution_queue": [],
  "deepnote_notebook_id": "a0eb4905-3c56-4235-998d-e70218e3518c",
  "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.8.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}