Lecture 4 Aggregating data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Lecture 4 Aggregating data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Aggregate opertions\n",
    "\n",
    "**Concept**: \n",
    "*Aggregation* is the act of taking multiple values and reducing them down to a single value. Two of the simplest forms of aggregation are counting and adding.\n",
    "\n",
    "- Counting \n",
    "- Adding\n",
    "- Computing the average (mean)\n",
    "- Finding the minimum (lowest value)\n",
    "- Finding the maximum (highest value)\n",
    "\n",
    "Aggregate expressions: combine values from multiple rows <br>\n",
    "Non-Aggregate or scalar expressions: return one value per row\n",
    "\n",
    "Be **careful** when combining aggregate expressions and scalar expressions!!!\n",
    "\n",
    "\n",
    "\n",
    "#### Common aggregate functions\n",
    "\n",
    "Function Name|Function Description|Example Invocation|\n",
    "-|-|-\n",
    "`COUNT`|Count all rows|`COUNT(*)`\n",
    "`SUM`|Add all supplied values and return result|`SUM(salary)`\n",
    "`AVG`|Return the average of all supplied values|`AVG(salary)`\n",
    "`MIN`|Return the lowest value|`MIN(salary)`\n",
    "`MAX`|Return the highest value|`MAX(salary)`\n",
    "\n",
    "Some data analysts use the expression `SUM(1)` instead of `COUNT(*)`. \n",
    "\n",
    "These two aggregate expressions do the same thing: they count the number of rows in a table. This is because when you use a scalar argument to an aggregate function, the the aggregate function aggregates that same value over all the rows. \n",
    "\n",
    "\n",
    "#### Using aggregrate functions in the `SELECT` statement\n",
    "\n",
    "Uisng aggregrate functions in select list\n",
    "```MySQL\n",
    "SELECT COUNT(*) AS num_rows FROM employees;\n",
    "SELECT SUM(salary) AS total_salary FROM employees;\n",
    "SELECT MIN(salary), MAX(salary) FROM employees;\n",
    "SELECT round(SUM(salary) * 0.062) AS total_tax FROM employees;\n",
    "```\n",
    "\n",
    "- Valid mixing of aggregate and scalar operations ✅\n",
    "```SQL\n",
    "round(AVG(list_price))\n",
    "SUM(salary * 0.062)         \n",
    "```    \n",
    "- INVALID queries ❌\n",
    "```SQL \n",
    "SELECT salary - AVG(salary) FROM employees;\n",
    "SELECT first_name, SUM(salary) FROM employees;\n",
    "```\n",
    "\n",
    "**Conditional aggregation**\n",
    "\n",
    "```SQL\n",
    "SELECT COUNT(*) FROM employees WHERE salary > 30000;\n",
    "```\n",
    "\n",
    "Notice that you **CANNOT** use aggregate functions in `WHERE` clause.\n",
    "\n",
    "因为 `WHERE` caluse 后面接的布尔表达式将对每一行进行判断。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The `GROUP BY` clause\n",
    "\n",
    "上面的 aggregation 返回的都是一行，利用 `GROUP BY` 可以对数据分组并对每组进行 aggregation, 然后返回多行。\n",
    "\n",
    "Some examples\n",
    "```SQL\n",
    "-- How many employees are there in total?\n",
    "SELECT COUNT(*) FROM employees;\n",
    "\n",
    "-- How many employees are there in each office?\n",
    "-- Return one row per group\n",
    "SELECT office_id, COUNT(*) FROM employees GROUP BY office_id;\n",
    "\n",
    "-- How may employees are there in each office who have salary larger than 35000\n",
    "-- Note: WHERE clause comes before GROUP BY\n",
    "-- i.e. Filtering first, and then aggregation\n",
    "SELECT office_id, COUNT(*) FROM employees WHERE salary > 35000 GROUP BY office_id;\n",
    "```\n",
    "\n",
    "#### Ways to specify a `GROUP BY` clause\n",
    "\n",
    "- Column reference\n",
    "- Grouping expressions\n",
    "- Column alias (with some SQL engines)\n",
    "- Two or more of the above: `GROUP BY` list\n",
    "\n",
    "#### Choosing an aggregate function and grouping column\n",
    "\n",
    "```MySQL\n",
    "DESCRIBE inventory;\n",
    "```\n",
    "|name|type|\n",
    "|-|-|\n",
    "|shop|string|\n",
    "|game|string|\n",
    "|qty|int|\n",
    "|aisle|tinyint|\n",
    "|price|decimal(5, 2)|\n",
    "\n",
    "---\n",
    "\n",
    "```MySQL\n",
    "-- How many different games are in stock at each shop?\n",
    "SELECT shop, COUNT(*) FROM inventory GROUP BY shop;\n",
    "\n",
    "-- How many total games are in stock at each shop?\n",
    "SELECT shop, SUM(qty) FROM inventory GROUP BY shop;\n",
    "\n",
    "-- How many total copies of each game are in stock?\n",
    "SELECT game, SUM(qty) FROM inventory GROUP BY game;\n",
    "\n",
    "-- How many total copies of each game are in stock at each shop?\n",
    "-- The rows of the table already represent the grouping we are looking for.\n",
    "SELECT shop, game, qty FROM inventory;\n",
    "``` \n",
    "\n",
    "#### Grouping expressions and column alias\n",
    "\n",
    "Besicdes using a column reference, you can use an expression in `GROUP BY` clause.\n",
    "\n",
    "    GROPY BY boolean_expression\n",
    "    GROUP BY if(boolean_expression, true_result, false_result)\n",
    "    GROUP BY CASE\n",
    "        WHEN ... THEN ...\n",
    "        WHEN ... THEN ...\n",
    "        ELSE          ...\n",
    "    END\n",
    "    \n",
    "Note: use grouping expression in both `GROUP BY` clause and select list. This repeating can be cumbersome, so some SQL engines support *column alias*.\n",
    "\n",
    "```SQL\n",
    "-- Impala, MySQL, PostgreSQL \n",
    "-- but NOT supported in Hive\n",
    "SELECT list_price < 10 AS low_price, count(*) FROM games GROUP BY low_price;\n",
    "```\n",
    "\n",
    "#### `GROUP BY` list\n",
    "```SQL\n",
    "SELECT min_age, max_players, COUNT(*) FROM games GROUP BY min_age, players;\n",
    "SELECT min_age, list_price < 10 AS low_price, COUNT(*) FROM games GROUP BY min_age, low_price;\n",
    "```\n",
    "\n",
    "#### Grouping and aggregation, together and separately\n",
    "\n",
    "*Aggregation without grouping* <br>\n",
    "In effect, when you use an aggregate expression with no `GROPY BY` clause, there is an implict one that creates one group for the whole table.\n",
    "```SQL\n",
    "SELECT COUNT(*) FROM games;\n",
    "```\n",
    "\n",
    "*Grouping without aggregation*<br>\n",
    "❗❗❗ When using `GROUP BY`, the `SELECT` list can only have:\n",
    "- Aggregate expressions\n",
    "- Expressions used in `GROUP BY`\n",
    "- Literal values <br>\n",
    "    \n",
    "Some examples\n",
    "```SQL\n",
    "-- return unique values of min_ages\n",
    "SELECT min_age FROM games GROUP BY min_ages;\n",
    "-- better to use DISTINCT\n",
    "SELECT DISTINCT min_age;\n",
    "```\n",
    "\n",
    "*Grouping with aggregation*\n",
    "```SQL\n",
    "SELECT min_age,\n",
    "       round(AVG(list_price), 2) AS avg_list_price,\n",
    "       0.21 AS tax_price,\n",
    "       round(AVG(list_price) * 1.21, 2) AS avg_w_tax\n",
    "  FROM games\n",
    "  GROUP BY min_age;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### More about grouping and aggregation\n",
    "\n",
    "#### `NULL` values in aggregation\n",
    "\n",
    "```MySQL\n",
    "-- In SQL, aggregate functions ignore NULL values\n",
    "SELECT AVG(price) FROM inventory;\n",
    "```\n",
    "\n",
    "#### `NULL` values in grouping\n",
    "\n",
    "```MySQL\n",
    "-- The NULL group will be creted\n",
    "SELECT aisle, COUNT(*) FROM inventory GROUP BY aisle;\n",
    "```\n",
    "\n",
    "#### Count Non-NULL values\n",
    "\n",
    "```MySQL\n",
    "/*\n",
    "The COUNT function ignores NULL just like other aggregation functions\n",
    "Except in the case where you use COUNT(*)\n",
    "*/\n",
    "\n",
    "-- count non-NULL values in the price column\n",
    "SELECT COUNT(price) FROM inventory;\n",
    "\n",
    "-- count non-NULL price records for each shop\n",
    "SELECT shop, COUNT(price) FROM inventory GROUP BY shop;\n",
    "```\n",
    "---\n",
    "\n",
    "**QUIZ**: Which statements will return the same result as this one?\n",
    "\n",
    "```SQL\n",
    "SELECT AVG(price) AS avg_price FROM fun.inventory;\n",
    "```\n",
    " \n",
    "```SQL\n",
    "❌ A. SELECT SUM(price) / SUM(1) AS avg_price FRIM fun.inventory;\n",
    "❌ B. SELECT SUM(price) / COUNT(*) AS avg_price FRIM fun.inventory;\n",
    "✅ C. SELECT SUM(price) / COUNT(price) AS avg_price FRIM fun.inventory;\n",
    "```\n",
    "---\n",
    "\n",
    "#### Count distinct values\n",
    "\n",
    "```SQL\n",
    "-- Still, NULL values are ignored\n",
    "SELECT COUNT(DISTINCT aisle) FROM inventory;\n",
    "\n",
    "-- Count distinct combinations\n",
    "-- Works in Impala, Hive, MySQL; not PostgreSQL\n",
    "SELECT COUNT(DISTINCT red, green, blue) FROM wax.crayons;\n",
    "\n",
    "-- Mulitple distinct counts\n",
    "-- Works in Hive, MySQL, PostgreSQL; not Impala\n",
    "SELECT COUNT(DISTINCT red), COUNT(DISTINCT green), COUNT(DISTINCT blue) FROM wax.crayons;\n",
    "```\n",
    "\n",
    "---\n",
    "\n",
    "**QUIZ**: Which ones will return the same as\n",
    "```SQL\n",
    "SELECT COUNT(tz) AS time_zones FROM fly.airports;\n",
    "```\n",
    "\n",
    "```SQL\n",
    "✅ A. SELECT COUNT(*) AS time_zones FROM fly.airports WHERE tz IS NOT NULL;\n",
    "❌ B. SELECT COUNT(*) AS time_zones FROM fly.airports WHERE tz is NULL;\n",
    "✅ C. SELECT COUNT(ALL tz) AS time_zones FROM fly.airports;\n",
    "❌ D. SELECT COUNT(*) AS time_zones FROM fly.airports;\n",
    "❌ E. SELECT COUNT(DISTINCT tz) AS time_zones FROM fly.airports;\n",
    "```\n",
    "---\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "You can use `DISTINCT` with all aggregate functions\n",
    "- This is rarely helpful except with `COUNT`\n",
    "- With `MIN` or `MAX`, `DISTINCT` would have no effect"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Tips for applying grouping and aggregation\n",
    "\n",
    "1. *Pushdown* calclution to database instead of BI software. The unique values of grouping columns must be limitted (categorical).\n",
    "```SQL\n",
    "SELECT year, AVG(dep_delay) FROM flights GROUP BY year;\n",
    "```\n",
    "\n",
    "2. Filtering data before grouping\n",
    "```SQL\n",
    "SELECT dep_time, arr_time, COUNT(*) as num_flights\n",
    "     FROM flights\n",
    "     WHERE year=2009 AND month=1 AND day=15\n",
    "     GROUP BY dep_time, arr_time;\n",
    "```\n",
    "\n",
    "3. Bining\n",
    "```MySQL\n",
    "-- Bining\n",
    "SELECT MIN(dep_time), MAX(dep_time), COUNT(*)\n",
    "     FROM flights\n",
    "     GROUP BY CASE WHEN dep_time IS NULL THEN 'missing'\n",
    "                   WHEN dep_time < 500 THEN 'night'\n",
    "                   WHEN dep_time < 1200 THEN 'morning'\n",
    "                   WHEN dep_time < 1700 THEN 'afternoon'\n",
    "                   WHEN dep_time < 2200 THEN 'evening'\n",
    "                   ELSE 'night'\n",
    "              END;\n",
    "```\n",
    "```MySQL\n",
    "-- using column alias\n",
    "SELECT CASE WHEN dep_time IS NULL then 'missing'\n",
    "            WHEN dep_time < 500 THEN 'night'\n",
    "            WHEN dep_time < 1200 THEN 'morning'\n",
    "            WHEN dep_time < 1700 THEN 'afternoon'\n",
    "            WHEN dep_time < 2200 THEN 'evening'\n",
    "            ELSE 'night'\n",
    "       END AS dep_time_category,\n",
    "       COUNT(*)\n",
    "   FROM flights\n",
    "   GROUP BY dep_time_category;\n",
    "```\n",
    "```MySQL\n",
    "-- using positional references\n",
    "SELECT CASE WHEN dep_time IS NULL then 'missing'\n",
    "            WHEN dep_time < 500 THEN 'night'\n",
    "            WHEN dep_time < 1200 THEN 'morning'\n",
    "            WHEN dep_time < 1700 THEN 'afternoon'\n",
    "            WHEN dep_time < 2200 THEN 'evening'\n",
    "            ELSE 'night'\n",
    "       END AS dep_time_category,\n",
    "       COUNT(*)\n",
    "   FROM flights\n",
    "   GROUP BY 1;\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### The `HAVING` clause\n",
    "\n",
    "#### Filtering on aggregates\n",
    "\n",
    "❌Invalid query\n",
    "```MySQL\n",
    "SELECT shop, SUM(price * qty) FROM inventory\n",
    "  GROUP BY shop\n",
    "  WHERE❌ SUM(price * qty) > 300;\n",
    "```\n",
    "The `WHERE` clause can only filter individual rows of data. Also `WHERE` clause is executed before `GROUP BY` clause.\n",
    "\n",
    "Instead, using `HAVING` to filter aggregates ✅\n",
    "```MySQL\n",
    "SELECT shop, SUM(price * qty) FROM inventory\n",
    "  GROUP BY shop\n",
    "  HAVING SUM(price * qty) > 300;\n",
    "```\n",
    "\n",
    "Q: Which shops have at least two different games in stock that cost less than 20 dollars?\n",
    "```MySQL\n",
    "-- For each shop,                    -> use GROUP BY shop\n",
    "-- Constraint 1: at least 2 games.   -> group constraint, use HAVING\n",
    "-- Constraint 2: price < 20          -> all-row constraint, use WHERE\n",
    "SELECT shop, COUNT(*) AS game_cnt\n",
    "  FROM inventory\n",
    "  WHERE price < 20\n",
    "  GROUP BY shop\n",
    "  HAVING COUNT(*) >= 2\n",
    "```\n",
    "\n",
    "Q: The `fly.planes` table contains data about plane, including the columns `manufacturer` and `seats`. Write a query will prodvide the average number of seats in all planes built by a manufacturer, but only for manufacturers who have at least one plane with more than 100 seats?\n",
    "\n",
    "```MySQL\n",
    "SELECT manufacturer, AVG(seats) FROM fly.planes GROUP BY manufacturer HAVING MAX(seats) > 100;\n",
    "```\n",
    "\n",
    "Q: The `fly.flights` table has enough information to calculate the flight speed for a flight, but it's a little long and you probably don't want to repeat it any more than you have to. The calculation for a single flight, in miles per hour, is `distance / (nullif(air_time, 0) / 60)`.\n",
    "\n",
    "Which of the following queries for Impala is the most succinct (and correct) way to find the origin airport, destination airport, average flight speed in miles per hour, and number of flights for origin-destination pairs for which the average flight speed was over 575 miles per hour?\n",
    "\n",
    "wrong answers omitted\n",
    "\n",
    "```MySQL\n",
    "SELECT origin, dest,\n",
    "       AVG(distance / (nullif(air_time, 0) / 60)) AS avg_flight_speed,\n",
    "       COUNT(*) AS num_flights\n",
    "  FROM flights GROUP BY origin, dest\n",
    "  HAVING avg_flight_speed > 575;\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.7.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}