class: center, middle, inverse, title-slide # Introduction to SQL ## Subqueries ### Prof. Dr. Jan Kirenz ### HdM Stuttgart ### 2019/09/01 (updated: 2019-09-03) --- layout: true <div class="my-header"></div> <div class="my-footer"><span> SQL > Inspecting and modifying data &emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp; &emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp;&emsp; Prof. Dr. Jan Kirenz </span></div> --- # Setup ``` pw = "your_password" ``` ```r library(DBI) library(RPostgreSQL) drv <- dbDriver("PostgreSQL") con <- dbConnect(drv, dbname = "postgres", host = "localhost", port = 5433, user = "postgres", password = pw) ``` * Note: the examples used in this presentation are based on the excellent book "A Beginner's Guide to Storytelling with Data" from Anthony DeBarros (2018). --- #### CREATE TABLE * This is only a part of the table (review the source code): ```sql CREATE TABLE us_counties_2010 ( geo_name varchar(90), -- Name of the geography state_us_abbreviation varchar(2), -- State/U.S. abbreviation summary_level varchar(3), -- Summary Level region smallint, -- Region division smallint, -- Division state_fips varchar(2), -- State FIPS code county_fips varchar(3), -- County code area_land bigint, -- Area (Land) in square meters area_water bigint, -- Area (Water) in square meters population_count_100_percent integer, -- Population count (100%) housing_unit_count_100_percent integer, -- Housing Unit count (100%) internal_point_lat numeric(10,7), -- Internal point (latitude) internal_point_lon numeric(10,7), -- Internal point (longitude) -- This section is referred to as P1. Race: p0010001 integer, -- Total population p0010002 integer, -- Population of one race: p0010003 integer, -- White Alone p0010004 integer, -- Black or African American alone p0010005 integer, -- American Indian and Alaska Native alone p0010006 integer, -- Asian alone p0010007 integer, -- Native Hawaiian and Other Pacific Islander alone p0010008 integer, -- Some Other Race alone p0010009 integer, -- Population of two or more races p0010010 integer, -- Population of two races: p0010011 integer, -- White; Black or African American p0010012 integer, -- White; American Indian and Alaska Native p0010013 integer, -- White; Asian p0010014 integer, -- White; Native Hawaiian and Other Pacific Islander p0010015 integer, -- White; Some Other Race p0010016 integer, -- Black or African American; American Indian and Alaska Native p0010017 integer, -- Black or African American; Asian p0010018 integer, -- Black or African American; Native Hawaiian and Other Pacific Islander p0010019 integer, -- Black or African American; Some Other Race p0010020 integer, -- American Indian and Alaska Native; Asian p0010021 integer, -- American Indian and Alaska Native; Native Hawaiian and Other Pacific Islander p0010022 integer, -- American Indian and Alaska Native; Some Other Race p0010023 integer, -- Asian; Native Hawaiian and Other Pacific Islander p0010024 integer, -- Asian; Some Other Race p0010025 integer, -- Native Hawaiian and Other Pacific Islander; Some Other Race p0010026 integer, -- Population of three races p0010047 integer, -- Population of four races p0010063 integer, -- Population of five races p0010070 integer, -- Population of six races -- This section is referred to as P2. HISPANIC OR LATINO, AND NOT HISPANIC OR LATINO BY RACE p0020001 integer, -- Total p0020002 integer, -- Hispanic or Latino p0020003 integer, -- Not Hispanic or Latino: p0020004 integer, -- Population of one race: p0020005 integer, -- White Alone p0020006 integer, -- Black or African American alone p0020007 integer, -- American Indian and Alaska Native alone p0020008 integer, -- Asian alone p0020009 integer, -- Native Hawaiian and Other Pacific Islander alone p0020010 integer, -- Some Other Race alone p0020011 integer, -- Two or More Races p0020012 integer, -- Population of two races p0020028 integer, -- Population of three races p0020049 integer, -- Population of four races p0020065 integer, -- Population of five races p0020072 integer, -- Population of six races -- This section is referred to as P3. RACE FOR THE POPULATION 18 YEARS AND OVER p0030001 integer, -- Total p0030002 integer, -- Population of one race: p0030003 integer, -- White alone p0030004 integer, -- Black or African American alone p0030005 integer, -- American Indian and Alaska Native alone p0030006 integer, -- Asian alone p0030007 integer, -- Native Hawaiian and Other Pacific Islander alone p0030008 integer, -- Some Other Race alone p0030009 integer, -- Two or More Races p0030010 integer, -- Population of two races p0030026 integer, -- Population of three races p0030047 integer, -- Population of four races p0030063 integer, -- Population of five races p0030070 integer, -- Population of six races -- This section is referred to as P4. HISPANIC OR LATINO, AND NOT HISPANIC OR LATINO BY RACE -- FOR THE POPULATION 18 YEARS AND OVER p0040001 integer, -- Total p0040002 integer, -- Hispanic or Latino p0040003 integer, -- Not Hispanic or Latino: p0040004 integer, -- Population of one race: p0040005 integer, -- White alone p0040006 integer, -- Black or African American alone p0040007 integer, -- American Indian and Alaska Native alone p0040008 integer, -- Asian alone p0040009 integer, -- Native Hawaiian and Other Pacific Islander alone p0040010 integer, -- Some Other Race alone p0040011 integer, -- Two or More Races p0040012 integer, -- Population of two races p0040028 integer, -- Population of three races p0040049 integer, -- Population of four races p0040065 integer, -- Population of five races p0040072 integer, -- Population of six races -- This section is referred to as H1. OCCUPANCY STATUS h0010001 integer, -- Total housing units h0010002 integer, -- Occupied h0010003 integer -- Vacant ); ``` --- ## COPY * Data: [us_counties_2010.csv](https://github.com/kirenz/datasets/blob/master/us_counties_2010.csv) ```sql COPY us_counties_2010 FROM '/Users/jankirenz/Documents/HdM/Vorlesungen/DataScience/ProgrammingLanguages/SQL/sql-sub/us_counties_2010.csv' WITH (FORMAT CSV, HEADER); ``` -- ```sql SELECT * FROM us_counties_2010; ``` --- class: center, middle <div id="htmlwidget-1134af1797a58e296015" style="width:100%;height:auto;" class="datatables html-widget"></div> <script type="application/json" data-for="htmlwidget-1134af1797a58e296015">{"x":{"filter":"none","fillContainer":false,"data":[["1","2","3","4","5","6"],["Autauga County","Baldwin County","Barbour County","Bibb County","Blount County","Bullock County"],["AL","AL","AL","AL","AL","AL"],["050","050","050","050","050","050"],[3,3,3,3,3,3],[6,6,6,6,6,6],["01","01","01","01","01","01"],["001","003","005","007","009","011"],[1539582278,4117521611,2291818968,1612480789,1669961855,1613056905],[25775735,1133190229,50864716,9289057,15157440,6056528],[54571,182265,27457,22915,57322,10914],[22135,104061,11829,8981,23887,4493],[32.5363818,30.6592183,31.8706701,33.0158929,33.9774479,32.1017589],[-86.6444901,-87.7460666,-85.4054562,-87.1271475,-86.5672464,-85.7172613],[54571,182265,27457,22915,57322,10914],[53702,179542,27199,22712,56638,10828],[42855,156153,13180,17381,53068,2507],[9643,17105,12875,5047,761,7666],[232,1216,114,64,307,23],[474,1348,107,22,117,20],[32,89,29,13,38,43],[466,3631,894,185,2347,569],[869,2723,258,203,684,86],[814,2583,254,195,662,82],[219,658,76,50,112,12],[262,1035,49,77,330,28],[177,336,7,16,74,3],[11,35,2,3,8,0],[50,311,34,16,102,3],[32,63,54,9,12,8],[19,34,2,13,2,5],[9,7,4,5,0,2],[16,38,7,4,6,9],[0,12,0,0,3,0],[0,0,1,0,0,0],[5,15,2,2,2,3],[5,11,1,0,4,0],[8,14,5,0,7,0],[1,14,10,0,0,9],[49,128,3,8,21,4],[6,11,0,0,1,0],[0,1,0,0,0,0],[0,0,1,0,0,0],[54571,182265,27457,22915,57322,10914],[1310,7992,1387,406,4626,777],[53261,174273,26070,22509,52696,10137],[52500,171976,25861,22328,52129,10078],[42154,152200,12837,17191,50952,2392],[9595,16966,12820,5024,724,7637],[217,1146,60,64,285,20],[467,1340,107,22,115,20],[22,79,24,7,18,4],[45,245,13,20,35,5],[761,2297,209,181,567,59],[719,2205,206,175,547,56],[36,87,2,6,19,3],[6,5,0,0,1,0],[0,0,0,0,0,0],[0,0,1,0,0,0],[39958,140367,21442,17714,43216,8484],[39530,138905,21275,17584,42810,8432],[31910,122238,10855,13403,40515,2164],[6767,12272,9647,3975,549,5838],[180,923,86,47,227,21],[346,1002,80,14,91,17],[23,70,24,11,23,31],[304,2400,583,134,1405,361],[428,1462,167,130,406,52],[404,1398,163,128,394,49],[22,60,3,2,12,3],[2,3,0,0,0,0],[0,1,0,0,0,0],[0,0,1,0,0,0],[39958,140367,21442,17714,43216,8484],[828,5186,925,310,2724,485],[39130,135181,20517,17404,40492,7999],[38746,133937,20382,17284,40141,7963],[31461,119671,10624,13247,39285,2102],[6738,12193,9605,3963,524,5821],[169,876,47,47,212,18],[341,994,80,14,89,17],[15,62,21,5,14,3],[22,141,5,8,17,2],[384,1244,135,120,351,36],[363,1198,132,119,341,33],[19,46,2,1,10,3],[2,0,0,0,0,0],[0,0,0,0,0,0],[0,0,1,0,0,0],[22135,104061,11829,8981,23887,4493],[20221,73180,9820,7953,21578,3745],[1914,30881,2009,1028,2309,748]],"container":"<table class=\"display\">\n <thead>\n <tr>\n <th> <\/th>\n <th>geo_name<\/th>\n <th>state_us_abbreviation<\/th>\n <th>summary_level<\/th>\n <th>region<\/th>\n <th>division<\/th>\n <th>state_fips<\/th>\n <th>county_fips<\/th>\n <th>area_land<\/th>\n <th>area_water<\/th>\n <th>population_count_100_percent<\/th>\n <th>housing_unit_count_100_percent<\/th>\n <th>internal_point_lat<\/th>\n <th>internal_point_lon<\/th>\n <th>p0010001<\/th>\n <th>p0010002<\/th>\n <th>p0010003<\/th>\n <th>p0010004<\/th>\n <th>p0010005<\/th>\n <th>p0010006<\/th>\n <th>p0010007<\/th>\n <th>p0010008<\/th>\n <th>p0010009<\/th>\n <th>p0010010<\/th>\n <th>p0010011<\/th>\n <th>p0010012<\/th>\n <th>p0010013<\/th>\n <th>p0010014<\/th>\n <th>p0010015<\/th>\n <th>p0010016<\/th>\n <th>p0010017<\/th>\n <th>p0010018<\/th>\n <th>p0010019<\/th>\n <th>p0010020<\/th>\n <th>p0010021<\/th>\n <th>p0010022<\/th>\n <th>p0010023<\/th>\n <th>p0010024<\/th>\n <th>p0010025<\/th>\n <th>p0010026<\/th>\n <th>p0010047<\/th>\n <th>p0010063<\/th>\n <th>p0010070<\/th>\n <th>p0020001<\/th>\n <th>p0020002<\/th>\n <th>p0020003<\/th>\n <th>p0020004<\/th>\n <th>p0020005<\/th>\n <th>p0020006<\/th>\n <th>p0020007<\/th>\n <th>p0020008<\/th>\n <th>p0020009<\/th>\n <th>p0020010<\/th>\n <th>p0020011<\/th>\n <th>p0020012<\/th>\n <th>p0020028<\/th>\n <th>p0020049<\/th>\n <th>p0020065<\/th>\n <th>p0020072<\/th>\n <th>p0030001<\/th>\n <th>p0030002<\/th>\n <th>p0030003<\/th>\n <th>p0030004<\/th>\n <th>p0030005<\/th>\n <th>p0030006<\/th>\n <th>p0030007<\/th>\n <th>p0030008<\/th>\n <th>p0030009<\/th>\n <th>p0030010<\/th>\n <th>p0030026<\/th>\n <th>p0030047<\/th>\n <th>p0030063<\/th>\n <th>p0030070<\/th>\n <th>p0040001<\/th>\n <th>p0040002<\/th>\n <th>p0040003<\/th>\n <th>p0040004<\/th>\n <th>p0040005<\/th>\n <th>p0040006<\/th>\n <th>p0040007<\/th>\n <th>p0040008<\/th>\n <th>p0040009<\/th>\n <th>p0040010<\/th>\n <th>p0040011<\/th>\n <th>p0040012<\/th>\n <th>p0040028<\/th>\n <th>p0040049<\/th>\n <th>p0040065<\/th>\n <th>p0040072<\/th>\n <th>h0010001<\/th>\n <th>h0010002<\/th>\n <th>h0010003<\/th>\n <\/tr>\n <\/thead>\n<\/table>","options":{"pageLength":8,"columnDefs":[{"className":"dt-right","targets":[4,5,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91]},{"orderable":false,"targets":0}],"order":[],"autoWidth":false,"orderClasses":false,"lengthMenu":[8,10,25,50,100]}},"evals":[],"jsHooks":[]}</script> --- # Using Subqueries - A subquery is nested inside another query. - It's used for a calculation or logical test that provides a value or set of data to be passed onto the main portion of the query. --- ## Filtering with Subqueries in a WHERE clause - A `WHERE` clause lets you filter query results based on criteria you provide, using an expression such as "`WHERE` quantity > 100" - But this requires that you already know the value to use for comparison. - A subquery lets you write a query that generates one or more values to use as part of an expression in a `WHERE` clause. --- ### Generating Values for a Query Expression - We want to write a query that shows which U.S. counties are at or above the 90th percentile, or top 10 percent, for population. - Rather than writing two seperate queries (one to calculate the 90th percentile and the other to filter by counties) we can do both at once using a subquery in a `WHERE` clause ```sql SELECT geo_name, state_us_abbreviation, p0010001 FROM us_counties_2010 WHERE p0010001 >= (SELECT percentile_cont(0.9) WITHIN GROUP (ORDER BY p0010001) FROM us_counties_2010) ORDER BY p0010001 DESC; ``` --- class: center, middle <div id="htmlwidget-c42e15140e805ba7e4f2" style="width:100%;height:auto;" class="datatables html-widget"></div> <script type="application/json" data-for="htmlwidget-c42e15140e805ba7e4f2">{"x":{"filter":"none","fillContainer":false,"data":[["1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","31","32","33","34","35","36","37","38","39","40","41","42","43","44","45","46","47","48","49","50","51","52","53","54","55","56","57","58","59","60","61","62","63","64","65","66","67","68","69","70","71","72","73","74","75","76","77","78","79","80","81","82","83","84","85","86","87","88","89","90","91","92","93","94","95","96","97","98","99","100"],["Los Angeles County","Cook County","Harris County","Maricopa County","San Diego County","Orange County","Kings County","Miami-Dade County","Dallas County","Queens County","Riverside County","San Bernardino County","Clark County","King County","Wayne County","Tarrant County","Santa Clara County","Broward County","Bexar County","New York County","Philadelphia County","Alameda County","Middlesex County","Suffolk County","Sacramento County","Bronx County","Nassau County","Palm Beach County","Cuyahoga County","Hillsborough County","Allegheny County","Oakland County","Franklin County","Hennepin County","Orange County","Fairfax County","Contra Costa County","Salt Lake County","Travis County","St. Louis County","Pima County","Montgomery County","Honolulu County","Westchester County","Milwaukee County","Fresno County","Shelby County","Fulton County","Mecklenburg County","Erie County","DuPage County","Fairfield County","Pinellas County","Bergen County","Marion County","Wake County","Hartford County","Duval County","Prince George's County","New Haven County","Macomb County","Kern County","Ventura County","Middlesex County","Gwinnett County","San Francisco County","Baltimore County","Hamilton County","El Paso County","Montgomery County","Worcester County","Pierce County","Essex County","Collin County","Hidalgo County","Monroe County","Essex County","Jefferson County","Multnomah County","Suffolk County","Oklahoma County","San Mateo County","Snohomish County","Lake County","DeKalb County","Cobb County","San Joaquin County","Will County","Jackson County","Norfolk County","Denton County","Bernalillo County","Jefferson County","Hudson County","Monmouth County","Davidson County","Providence County","Bucks County","El Paso County","Baltimore city"],["CA","IL","TX","AZ","CA","CA","NY","FL","TX","NY","CA","CA","NV","WA","MI","TX","CA","FL","TX","NY","PA","CA","MA","NY","CA","NY","NY","FL","OH","FL","PA","MI","OH","MN","FL","VA","CA","UT","TX","MO","AZ","MD","HI","NY","WI","CA","TN","GA","NC","NY","IL","CT","FL","NJ","IN","NC","CT","FL","MD","CT","MI","CA","CA","NJ","GA","CA","MD","OH","TX","PA","MA","WA","NJ","TX","TX","NY","MA","KY","OR","MA","OK","CA","WA","IL","GA","GA","CA","IL","MO","MA","TX","NM","AL","NJ","NJ","TN","RI","PA","CO","MD"],[9818605,5194675,4092459,3817117,3095313,3010232,2504700,2496435,2368139,2230722,2189641,2035210,1951269,1931249,1820584,1809034,1781642,1748066,1714773,1585873,1526006,1510271,1503085,1493350,1418788,1385108,1339532,1320134,1280122,1229226,1223348,1202362,1163414,1152425,1145956,1081726,1049025,1029655,1024266,998954,980263,971777,953207,949113,947735,930450,927644,920581,919628,919040,916924,916829,916542,905116,903393,900993,894014,864263,863420,862477,840978,839631,823318,809858,805321,805235,805029,802374,800647,799874,798552,795225,783969,782341,774769,744344,743159,741096,735334,722023,718633,718451,713335,703462,691893,688078,685306,677560,674158,670850,662614,662564,658466,634266,630380,626681,626667,625249,622263,620961]],"container":"<table class=\"display\">\n <thead>\n <tr>\n <th> <\/th>\n <th>geo_name<\/th>\n <th>state_us_abbreviation<\/th>\n <th>p0010001<\/th>\n <\/tr>\n <\/thead>\n<\/table>","options":{"pageLength":8,"columnDefs":[{"className":"dt-right","targets":3},{"orderable":false,"targets":0}],"order":[],"autoWidth":false,"orderClasses":false,"lengthMenu":[8,10,25,50,100]}},"evals":[],"jsHooks":[]}</script> --- ```sql SELECT percentile_cont(0.9) WITHIN GROUP (ORDER BY p0010001) FROM us_counties_2010; ``` -- <table> <thead> <tr> <th style="text-align:right;"> percentile_cont </th> </tr> </thead> <tbody> <tr> <td style="text-align:right;"> 197444.6 </td> </tr> </tbody> </table> --- ### Using a Subquery to Identify Rows to Delete - We can use a subquery to specify what to **remove** from a table. - Imagine you have a table with 100 million rows that, because of its size, takes a long time to query. - If you just want to work on a subset of the data (such as a particular state), you can make a copy of the table and delete what you don't need. - We'll make a copy of the census table and than delete everything from that backup except the 315 counties in the top 10 percent population. --- ### CREATE TABLE ```sql CREATE TABLE us_counties_2010_top10 AS (SELECT * FROM us_counties_2010); ``` -- ### DELETE ```sql DELETE FROM us_counties_2010_top10 WHERE p0010001 < (SELECT percentile_cont(0.9) WITHIN GROUP (ORDER BY p0010001) FROM us_counties_2010_top10); ``` --- ```sql SELECT COUNT(*) FROM us_counties_2010_top10; ``` -- <table> <thead> <tr> <th style="text-align:right;"> count </th> </tr> </thead> <tbody> <tr> <td style="text-align:right;"> 315 </td> </tr> </tbody> </table> --- ```sql SELECT COUNT(*) FROM us_counties_2010; ``` -- <table> <thead> <tr> <th style="text-align:right;"> count </th> </tr> </thead> <tbody> <tr> <td style="text-align:right;"> 3143 </td> </tr> </tbody> </table> --- ### Subquery as a derived table in a FROM clause - In this query we want to find the **average** and **median** population of U.S. counties as well as the **difference** between them. - We need to calculate the average and the median, and then we need to subtract the two. - We use the `AVG` and `PERCENTILE` functions to find th average and median of the census table's `p0010001` total polulation column and name each column with an alias (`AS`). - Then we name the subquery `calcs` (`AS` calcs), so we can reference it as a table in the main query. - Subtracting the median from the average (both are returned by the subquery), is done in the main query (first SELECT clause). - Then the main query rounds the result and labels it as `median_average_diff`. --- ### Median & average ```sql SELECT ROUND(calcs.average, 0) as average, calcs.median, ROUND(calcs.average - calcs.median, 0) AS median_average_diff FROM (SELECT AVG(p0010001) AS average, percentile_cont(.5) WITHIN GROUP (ORDER BY p0010001)::numeric(10,1) AS median FROM us_counties_2010) AS calcs; ``` -- <table> <thead> <tr> <th style="text-align:right;"> average </th> <th style="text-align:right;"> median </th> <th style="text-align:right;"> median_average_diff </th> </tr> </thead> <tbody> <tr> <td style="text-align:right;"> 98233 </td> <td style="text-align:right;"> 25857 </td> <td style="text-align:right;"> 72376 </td> </tr> </tbody> </table> --- ### Median & average - The difference between median and average is nearly three times the size of the median. - That shows that a relatively small number of high-population counties push the average county size over 98000. --- ## Adding a subquery to column list - You can generate new columns of data with subqueries by placing a subquery in the column list after `SELECT`. - For example: the query below selects `geo_name` and total population column `p0010001` from `us_counties_2010`, and then adds a subquery to add the median of all counties to each row in the new column `us_median`: ```sql SELECT geo_name, state_us_abbreviation AS st, p0010001 AS total_pop, (SELECT percentile_cont(.5) WITHIN GROUP (ORDER BY p0010001) FROM us_counties_2010) AS us_median FROM us_counties_2010; ``` --- class: center, middle <table> <thead> <tr> <th style="text-align:left;"> geo_name </th> <th style="text-align:left;"> st </th> <th style="text-align:right;"> total_pop </th> <th style="text-align:right;"> us_median </th> </tr> </thead> <tbody> <tr> <td style="text-align:left;"> Autauga County </td> <td style="text-align:left;"> AL </td> <td style="text-align:right;"> 54571 </td> <td style="text-align:right;"> 25857 </td> </tr> <tr> <td style="text-align:left;"> Baldwin County </td> <td style="text-align:left;"> AL </td> <td style="text-align:right;"> 182265 </td> <td style="text-align:right;"> 25857 </td> </tr> <tr> <td style="text-align:left;"> Barbour County </td> <td style="text-align:left;"> AL </td> <td style="text-align:right;"> 27457 </td> <td style="text-align:right;"> 25857 </td> </tr> <tr> <td style="text-align:left;"> Bibb County </td> <td style="text-align:left;"> AL </td> <td style="text-align:right;"> 22915 </td> <td style="text-align:right;"> 25857 </td> </tr> <tr> <td style="text-align:left;"> Blount County </td> <td style="text-align:left;"> AL </td> <td style="text-align:right;"> 57322 </td> <td style="text-align:right;"> 25857 </td> </tr> <tr> <td style="text-align:left;"> Bullock County </td> <td style="text-align:left;"> AL </td> <td style="text-align:right;"> 10914 </td> <td style="text-align:right;"> 25857 </td> </tr> </tbody> </table> --- ### Using a subquery expression in a calculation - It can also be useful to generate values that indicate how much each country's population deviates from the median value: ```sql SELECT geo_name, state_us_abbreviation AS st, p0010001 AS total_pop, (SELECT percentile_cont(.5) WITHIN GROUP (ORDER BY p0010001) FROM us_counties_2010) AS us_median, p0010001 - (SELECT percentile_cont(.5) WITHIN GROUP (ORDER BY p0010001) FROM us_counties_2010) AS diff_from_median FROM us_counties_2010 WHERE (p0010001 - (SELECT percentile_cont(.5) WITHIN GROUP (ORDER BY p0010001) FROM us_counties_2010)) BETWEEN -1000 AND 1000; ``` --- ### Using a subquery expression in a calculation - The added subquery subtracts the first subquery's result (us_median) from `p0010001`, the total population and creates a new column called `diff_from_median`. - By repeating the subquery expression in the `WHERE` clause and filter results using `BETWEEN`, we narrow the results further to show only counties which population falls within 1000 of the median (counties close to the median population) --- class: center, middle <div id="htmlwidget-cc28d01f23df12e7af9c" style="width:100%;height:auto;" class="datatables html-widget"></div> <script type="application/json" data-for="htmlwidget-cc28d01f23df12e7af9c">{"x":{"filter":"none","fillContainer":false,"data":[["1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","31","32","33","34","35","36","37","38","39","40","41","42","43","44","45","46","47","48","49","50","51","52","53","54","55","56","57","58","59","60","61","62","63","64","65","66","67","68","69","70","71"],["Cherokee County","Clarke County","Geneva County","Cleburne County","Johnson County","Ouachita County","Montezuma County","Washington County","Chattooga County","Grady County","Hart County","Monroe County","Stephens County","Tattnall County","Jefferson County","Saline County","Decatur County","Posey County","Randolph County","Benton County","Boone County","Plymouth County","Franklin County","Grayson County","Logan County","Montgomery County","Woodford County","Allen Parish","De Soto Parish","Somerset County","Charlevoix County","Cheboygan County","Dickinson County","Gladwin County","Iosco County","Oceana County","Brown County","Lyon County","Mille Lacs County","Todd County","Coahoma County","Prentiss County","Adair County","Audrain County","Randolph County","Texas County","Washington County","Cass County","Churchill County","Luna County","Yates County","Jackson County","Seminole County","Tillamook County","Union County","Wasco County","Abbeville County","Meade County","Hardin County","McNairy County","White County","Grimes County","San Jacinto County","Shelby County","Uvalde County","Fluvanna County","Lee County","Winchester city","Mingo County","Nicholas County","Juneau County"],["AL","AL","AL","AR","AR","AR","CO","FL","GA","GA","GA","GA","GA","GA","ID","IL","IN","IN","IN","IA","IA","IA","KS","KY","KY","KY","KY","LA","LA","MD","MI","MI","MI","MI","MI","MI","MN","MN","MN","MN","MS","MS","MO","MO","MO","MO","MO","NE","NV","NM","NY","OK","OK","OR","OR","OR","SC","SD","TN","TN","TN","TX","TX","TX","TX","VA","VA","VA","WV","WV","WI"],[25989,25833,26790,25970,25540,26120,25535,24896,26015,25011,25213,26424,26175,25520,26140,24913,25740,25910,26171,26076,26306,24986,25992,25746,26835,26499,24939,25764,26656,26470,25949,26152,26168,25692,25887,26570,25893,25857,26097,24895,26151,25276,25607,25529,25414,26008,25195,25241,24877,25095,25348,26446,25482,25250,25748,25213,25417,25434,26026,26075,25841,26604,26384,25448,26405,25691,25587,26203,26839,26233,26664],[25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857,25857],[132,-24,933,113,-317,263,-322,-961,158,-846,-644,567,318,-337,283,-944,-117,53,314,219,449,-871,135,-111,978,642,-918,-93,799,613,92,295,311,-165,30,713,36,0,240,-962,294,-581,-250,-328,-443,151,-662,-616,-980,-762,-509,589,-375,-607,-109,-644,-440,-423,169,218,-16,747,527,-409,548,-166,-270,346,982,376,807]],"container":"<table class=\"display\">\n <thead>\n <tr>\n <th> <\/th>\n <th>geo_name<\/th>\n <th>st<\/th>\n <th>total_pop<\/th>\n <th>us_median<\/th>\n <th>diff_from_median<\/th>\n <\/tr>\n <\/thead>\n<\/table>","options":{"pageLength":8,"columnDefs":[{"className":"dt-right","targets":[3,4,5]},{"orderable":false,"targets":0}],"order":[],"autoWidth":false,"orderClasses":false,"lengthMenu":[8,10,25,50,100]}},"evals":[],"jsHooks":[]}</script> --- ## Subquery Expressions - We can use subqueries to **filter rows** by evaluating whether a **condition** evaluates as TRUE or FALSE. ### Creating tables ```sql CREATE TABLE retirees ( id int, first_name varchar(50), last_name varchar(50) ); ``` ```sql INSERT INTO retirees VALUES (2, 'Lee', 'Smith'), (4, 'Janet', 'King'); ``` --- ### Creating tables ```sql CREATE TABLE employees ( emp_id bigserial, first_name varchar(100), last_name varchar(100), salary integer, dept_id integer REFERENCES departments (dept_id), CONSTRAINT emp_key PRIMARY KEY (emp_id), CONSTRAINT emp_dept_unique UNIQUE (emp_id, dept_id) ); ``` ```sql INSERT INTO employees (first_name, last_name, salary, dept_id) VALUES ('Nancy', 'Jones', 62500, 1), ('Lee', 'Smith', 59300, 1), ('Soo', 'Nguyen', 83000, 2), ('Janet', 'King', 95000, 2); ``` --- ```sql SELECT * FROM employees; ``` -- <table> <thead> <tr> <th style="text-align:right;"> emp_id </th> <th style="text-align:left;"> first_name </th> <th style="text-align:left;"> last_name </th> <th style="text-align:right;"> salary </th> <th style="text-align:right;"> dept_id </th> </tr> </thead> <tbody> <tr> <td style="text-align:right;"> 1 </td> <td style="text-align:left;"> Nancy </td> <td style="text-align:left;"> Jones </td> <td style="text-align:right;"> 62500 </td> <td style="text-align:right;"> 1 </td> </tr> <tr> <td style="text-align:right;"> 2 </td> <td style="text-align:left;"> Lee </td> <td style="text-align:left;"> Smith </td> <td style="text-align:right;"> 59300 </td> <td style="text-align:right;"> 1 </td> </tr> <tr> <td style="text-align:right;"> 3 </td> <td style="text-align:left;"> Soo </td> <td style="text-align:left;"> Nguyen </td> <td style="text-align:right;"> 83000 </td> <td style="text-align:right;"> 2 </td> </tr> <tr> <td style="text-align:right;"> 4 </td> <td style="text-align:left;"> Janet </td> <td style="text-align:left;"> King </td> <td style="text-align:right;"> 95000 </td> <td style="text-align:right;"> 2 </td> </tr> </tbody> </table> --- ```sql SELECT * FROM retirees; ``` -- <table> <thead> <tr> <th style="text-align:right;"> id </th> <th style="text-align:left;"> first_name </th> <th style="text-align:left;"> last_name </th> </tr> </thead> <tbody> <tr> <td style="text-align:right;"> 2 </td> <td style="text-align:left;"> Lee </td> <td style="text-align:left;"> Smith </td> </tr> <tr> <td style="text-align:right;"> 4 </td> <td style="text-align:left;"> Janet </td> <td style="text-align:left;"> King </td> </tr> </tbody> </table> --- ## Generating Values for the IN Operator - The following query uses a subquery to generate id values from a retirees table, and then uses that list for the `IN` operator in the `WHERE` clause. - The `NOT IN` expression does the opposite to find employees whose id value does not appear in retirees. ```sql SELECT first_name, last_name FROM employees WHERE emp_id IN ( SELECT id FROM retirees); ``` -- <table> <thead> <tr> <th style="text-align:left;"> first_name </th> <th style="text-align:left;"> last_name </th> </tr> </thead> <tbody> <tr> <td style="text-align:left;"> Lee </td> <td style="text-align:left;"> Smith </td> </tr> <tr> <td style="text-align:left;"> Janet </td> <td style="text-align:left;"> King </td> </tr> </tbody> </table> --- ## Generating Values for the IN Operator - The output shows the names of employees who have id values that match those in retirees. - Note: The presence of `NULL` values in a subquery result set will cause a query with a `NOT IN` expression to return no rows. If your data contains NULL values, use the `WHERE NOT EXISTS` expression described in the next query. --- ## Checking Whether Values Exist - `EXIST` is a TRUE/FALSE test - it returns TRUE if the subquery in parentheses returns at least one row - if it returns no rows, `EXISTS` evaluates to FALSE ```sql SELECT first_name, last_name FROM employees WHERE EXISTS ( SELECT id FROM retirees); ``` -- <table> <thead> <tr> <th style="text-align:left;"> first_name </th> <th style="text-align:left;"> last_name </th> </tr> </thead> <tbody> <tr> <td style="text-align:left;"> Nancy </td> <td style="text-align:left;"> Jones </td> </tr> <tr> <td style="text-align:left;"> Lee </td> <td style="text-align:left;"> Smith </td> </tr> <tr> <td style="text-align:left;"> Soo </td> <td style="text-align:left;"> Nguyen </td> </tr> <tr> <td style="text-align:left;"> Janet </td> <td style="text-align:left;"> King </td> </tr> </tbody> </table> --- - To mimic the behavior of `IN` and limit names to where the subquery after `EXISTS` finds at least one corresponding id value in retirees. - Using a correlated subquery to find matching values from employees in retirees: -- ```sql SELECT first_name, last_name FROM employees WHERE EXISTS ( SELECT id FROM retirees WHERE id = employees.emp_id); ``` -- <table> <thead> <tr> <th style="text-align:left;"> first_name </th> <th style="text-align:left;"> last_name </th> </tr> </thead> <tbody> <tr> <td style="text-align:left;"> Lee </td> <td style="text-align:left;"> Smith </td> </tr> <tr> <td style="text-align:left;"> Janet </td> <td style="text-align:left;"> King </td> </tr> </tbody> </table> --- - This approach is helpful if you need to join on more than one column, which you can't do with the `IN` expression. - We can also use `NOT` with `EXISTS`, for example to find employees with no corresponding record in retirees: ```sql SELECT first_name, last_name FROM employees WHERE NOT EXISTS ( SELECT id FROM retirees WHERE id = employees.emp_id); ``` - This technique is helpful for assessing whether a data set is complete. -- <table> <thead> <tr> <th style="text-align:left;"> first_name </th> <th style="text-align:left;"> last_name </th> </tr> </thead> <tbody> <tr> <td style="text-align:left;"> Nancy </td> <td style="text-align:left;"> Jones </td> </tr> <tr> <td style="text-align:left;"> Soo </td> <td style="text-align:left;"> Nguyen </td> </tr> </tbody> </table> --- # Common Table Expressions - A "CTE" (Common Table Expression) is temporary result set that you can reference within another SELECT, INSERT, UPDATE, or DELETE statement. - A CTE always returns a result set. - They are used to simplify queries. - Informally called `WITH` clause. - Using a CTE, you can define one or more tables up front with subqueries. Then you can query the table results as often as needed in a main query that follows. --- ### Common Table Expressions * Using a simple CTE to find large counties: ```sql WITH large_counties (geo_name, st, p0010001) AS ( SELECT geo_name, state_us_abbreviation, p0010001 FROM us_counties_2010 WHERE p0010001 >= 100000 ) SELECT st, count(*) FROM large_counties GROUP BY st ORDER BY count(*) DESC; ``` --- ### Common Table Expressions - The `WITH` ... AS block defines the CTE's temporary table large_counties - After `WITH`, we name the table and list its column names in parentheses - Unlike column definitions in a `CREATE TABLE` statement, we don't need to provide data types, because the temporary table inherits those from the subquery, which is enclosed in parentheses after `AS`. - The subquery must return the same number of columns as defined in the temporary table, but the column names don't need to match. - The column list is optional if you're not renaming columns, although it is still good for clarity even if you don't rename columns. - The main query (last `SELECT` statement) counts and groups the rows in `large_counties` by `st`, and then orders by the count in descending order. --- class: center, middle <table> <thead> <tr> <th style="text-align:left;"> st </th> <th style="text-align:right;"> count </th> </tr> </thead> <tbody> <tr> <td style="text-align:left;"> TX </td> <td style="text-align:right;"> 39 </td> </tr> <tr> <td style="text-align:left;"> CA </td> <td style="text-align:right;"> 35 </td> </tr> <tr> <td style="text-align:left;"> FL </td> <td style="text-align:right;"> 33 </td> </tr> <tr> <td style="text-align:left;"> PA </td> <td style="text-align:right;"> 31 </td> </tr> <tr> <td style="text-align:left;"> OH </td> <td style="text-align:right;"> 28 </td> </tr> <tr> <td style="text-align:left;"> NY </td> <td style="text-align:right;"> 28 </td> </tr> </tbody> </table> --- - You can get the same result using a `SELECT` query instead of a CTE: ```sql SELECT state_us_abbreviation, COUNT(*) FROM us_counties_2010 WHERE p0010001 >= 100000 GROUP BY state_us_abbreviation ORDER BY COUNT(*) DESC; ``` -- <table> <thead> <tr> <th style="text-align:left;"> state_us_abbreviation </th> <th style="text-align:right;"> count </th> </tr> </thead> <tbody> <tr> <td style="text-align:left;"> TX </td> <td style="text-align:right;"> 39 </td> </tr> <tr> <td style="text-align:left;"> CA </td> <td style="text-align:right;"> 35 </td> </tr> <tr> <td style="text-align:left;"> FL </td> <td style="text-align:right;"> 33 </td> </tr> <tr> <td style="text-align:left;"> PA </td> <td style="text-align:right;"> 31 </td> </tr> <tr> <td style="text-align:left;"> NY </td> <td style="text-align:right;"> 28 </td> </tr> <tr> <td style="text-align:left;"> OH </td> <td style="text-align:right;"> 28 </td> </tr> </tbody> </table> --- ### Why use CTE? - By using a CTE, you can pre-stage subsets of data to feed into larger query for more complex analysis. - Also, you can reuse each table defined in a CTE in multiple places in the main query, which means you don't have to repeat the SELECT query each time. - Sometimes the code is more readable --- # Cross Tabulations - Cross Tabulations provide a simple way to summerize and compare variables by displaying them in a table layout, or matrix. - In a matrix, rows represent another variable, columns represent another variable, and each cell where a row and column intersects holds a value, such as a count or percentage. - Cross Tabulations are also called __pivot tables__ or __crosstabs__. - They are often used to report summeries of survey results or to compare sets of variables. --- ## Installing the crosstab Function ```sql CREATE EXTENSION tablefunc; ``` * Creating and filling the ice_cream_survey table ```sql CREATE TABLE ice_cream_survey ( response_id integer PRIMARY KEY, office varchar(20), flavor varchar(20) ); ``` --- * Data: [ice_cream_survey.csv](https://github.com/kirenz/datasets/blob/master/ice_cream_survey.csv) ```sql COPY ice_cream_survey FROM '/Users/jankirenz/Documents/HdM/Vorlesungen/DataScience/ProgrammingLanguages/SQL/sql-sub/ice_cream_survey.csv' WITH (FORMAT CSV, HEADER); ``` --- * Inspecting the data ```sql SELECT * FROM ice_cream_survey LIMIT 5; ``` -- <table> <thead> <tr> <th style="text-align:right;"> response_id </th> <th style="text-align:left;"> office </th> <th style="text-align:left;"> flavor </th> </tr> </thead> <tbody> <tr> <td style="text-align:right;"> 1 </td> <td style="text-align:left;"> Uptown </td> <td style="text-align:left;"> Chocolate </td> </tr> <tr> <td style="text-align:right;"> 2 </td> <td style="text-align:left;"> Midtown </td> <td style="text-align:left;"> Chocolate </td> </tr> <tr> <td style="text-align:right;"> 3 </td> <td style="text-align:left;"> Downtown </td> <td style="text-align:left;"> Strawberry </td> </tr> <tr> <td style="text-align:right;"> 4 </td> <td style="text-align:left;"> Uptown </td> <td style="text-align:left;"> Chocolate </td> </tr> <tr> <td style="text-align:right;"> 5 </td> <td style="text-align:left;"> Midtown </td> <td style="text-align:left;"> Chocolate </td> </tr> </tbody> </table> --- ### Generating the ice cream survey tab ```sql SELECT * FROM crosstab('SELECT office, flavor, count(*) FROM ice_cream_survey GROUP BY office, flavor ORDER BY office', 'SELECT flavor FROM ice_cream_survey GROUP BY flavor ORDER BY flavor') AS (office varchar(20), chocolate bigint, strawberry bigint, vanilla bigint); ``` --- ### Generating the ice cream survey tab - The first statement selects everything from the contents of the crosstab function. - We placed two subqueries inside the crosstab function. - The first subquery generates the data for the crosstab and has three required columns. - The first column, office, supplies the row names for the crosstab. - The second column, flavor, supplies the category columns. - The third column supplies the values for each cell where row and column intersect in the table, here we want the intersecting cells to show a `COUNT(*)` of each flavor selected at each office - This first subquery creates a simple aggregated list (below) --- class: center, middle <table> <thead> <tr> <th style="text-align:left;"> office </th> <th style="text-align:right;"> chocolate </th> <th style="text-align:right;"> strawberry </th> <th style="text-align:right;"> vanilla </th> </tr> </thead> <tbody> <tr> <td style="text-align:left;"> Downtown </td> <td style="text-align:right;"> 23 </td> <td style="text-align:right;"> 32 </td> <td style="text-align:right;"> 19 </td> </tr> <tr> <td style="text-align:left;"> Midtown </td> <td style="text-align:right;"> 41 </td> <td style="text-align:right;"> NA </td> <td style="text-align:right;"> 23 </td> </tr> <tr> <td style="text-align:left;"> Uptown </td> <td style="text-align:right;"> 22 </td> <td style="text-align:right;"> 17 </td> <td style="text-align:right;"> 23 </td> </tr> </tbody> </table> --- ### Generating the ice cream survey tab #### Step by step ```sql SELECT office, flavor, count(*) FROM ice_cream_survey GROUP BY office, flavor ORDER BY office ``` -- <table> <thead> <tr> <th style="text-align:left;"> office </th> <th style="text-align:left;"> flavor </th> <th style="text-align:right;"> count </th> </tr> </thead> <tbody> <tr> <td style="text-align:left;"> Downtown </td> <td style="text-align:left;"> Chocolate </td> <td style="text-align:right;"> 23 </td> </tr> <tr> <td style="text-align:left;"> Downtown </td> <td style="text-align:left;"> Vanilla </td> <td style="text-align:right;"> 19 </td> </tr> <tr> <td style="text-align:left;"> Downtown </td> <td style="text-align:left;"> Strawberry </td> <td style="text-align:right;"> 32 </td> </tr> <tr> <td style="text-align:left;"> Midtown </td> <td style="text-align:left;"> Vanilla </td> <td style="text-align:right;"> 23 </td> </tr> <tr> <td style="text-align:left;"> Midtown </td> <td style="text-align:left;"> Chocolate </td> <td style="text-align:right;"> 41 </td> </tr> <tr> <td style="text-align:left;"> Uptown </td> <td style="text-align:left;"> Strawberry </td> <td style="text-align:right;"> 17 </td> </tr> </tbody> </table> --- ### Generating the ice cream survey tab #### Step by step ```sql SELECT flavor FROM ice_cream_survey GROUP BY flavor ORDER BY flavor; ``` - The second subquery produces the set category names for the columns. - The crosstab function requires that the second subquery return only one column, so here we use SELECT to retrieve the flavor column, and we use GROUP BY to return that column's unique values -- <table> <thead> <tr> <th style="text-align:left;"> flavor </th> </tr> </thead> <tbody> <tr> <td style="text-align:left;"> Chocolate </td> </tr> <tr> <td style="text-align:left;"> Strawberry </td> </tr> <tr> <td style="text-align:left;"> Vanilla </td> </tr> </tbody> </table> --- ### Generating the ice cream survey tab #### Step by step - Then we specify the names and data types of the crosstab's output columns following the AS keyword - The list must match the row and column names in the order the subqueries generate them --- ## Tabulating City Temperature Readings ### Creating and filling a temperature_readings table ```sql CREATE TABLE temperature_readings ( reading_id bigserial PRIMARY KEY, station_name varchar(50), observation_date date, max_temp integer, min_temp integer ); ``` --- ## Tabulating City Temperature Readings * Data: [temperature_readings.csv](https://github.com/kirenz/datasets/blob/master/ice_cream_survey.csv) ```sql COPY temperature_readings (station_name, observation_date, max_temp, min_temp) FROM '/Users/jankirenz/Documents/HdM/Vorlesungen/DataScience/ProgrammingLanguages/SQL/sql-sub/temperature_readings.csv' WITH (FORMAT CSV, HEADER); ``` --- ### Inspecting Data ```sql SELECT * FROM temperature_readings LIMIT 5; ``` -- <table> <thead> <tr> <th style="text-align:right;"> reading_id </th> <th style="text-align:left;"> station_name </th> <th style="text-align:left;"> observation_date </th> <th style="text-align:right;"> max_temp </th> <th style="text-align:right;"> min_temp </th> </tr> </thead> <tbody> <tr> <td style="text-align:right;"> 1 </td> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:left;"> 2016-01-01 </td> <td style="text-align:right;"> 31 </td> <td style="text-align:right;"> 20 </td> </tr> <tr> <td style="text-align:right;"> 2 </td> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:left;"> 2016-01-02 </td> <td style="text-align:right;"> 34 </td> <td style="text-align:right;"> 23 </td> </tr> <tr> <td style="text-align:right;"> 3 </td> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:left;"> 2016-01-03 </td> <td style="text-align:right;"> 32 </td> <td style="text-align:right;"> 26 </td> </tr> <tr> <td style="text-align:right;"> 4 </td> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:left;"> 2016-01-04 </td> <td style="text-align:right;"> 32 </td> <td style="text-align:right;"> 27 </td> </tr> </tbody> </table> --- ### Inspecting Data ```sql SELECT COUNT(*) FROM temperature_readings; ``` -- <table> <thead> <tr> <th style="text-align:right;"> count </th> </tr> </thead> <tbody> <tr> <td style="text-align:right;"> 1077 </td> </tr> </tbody> </table> --- * Generating the temperature readings crosstab ```sql SELECT * FROM crosstab('SELECT station_name, date_part(''month'', observation_date), percentile_cont(.5) WITHIN GROUP (ORDER BY max_temp) FROM temperature_readings GROUP BY station_name, date_part(''month'', observation_date) ORDER BY station_name', 'SELECT month FROM generate_series(1,12) month') AS (station varchar(50), jan numeric(3,0), feb numeric(3,0), mar numeric(3,0), apr numeric(3,0), may numeric(3,0), jun numeric(3,0), jul numeric(3,0), aug numeric(3,0), sep numeric(3,0), oct numeric(3,0), nov numeric(3,0), dec numeric(3,0) ); ``` --- class: center, middle <table> <thead> <tr> <th style="text-align:left;"> station </th> <th style="text-align:right;"> jan </th> <th style="text-align:right;"> feb </th> <th style="text-align:right;"> mar </th> <th style="text-align:right;"> apr </th> <th style="text-align:right;"> may </th> <th style="text-align:right;"> jun </th> <th style="text-align:right;"> jul </th> <th style="text-align:right;"> aug </th> <th style="text-align:right;"> sep </th> <th style="text-align:right;"> oct </th> <th style="text-align:right;"> nov </th> <th style="text-align:right;"> dec </th> </tr> </thead> <tbody> <tr> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:right;"> 34 </td> <td style="text-align:right;"> 36 </td> <td style="text-align:right;"> 46 </td> <td style="text-align:right;"> 50 </td> <td style="text-align:right;"> 66 </td> <td style="text-align:right;"> 77 </td> <td style="text-align:right;"> 81 </td> <td style="text-align:right;"> 80 </td> <td style="text-align:right;"> 77 </td> <td style="text-align:right;"> 65 </td> <td style="text-align:right;"> 57 </td> <td style="text-align:right;"> 35 </td> </tr> <tr> <td style="text-align:left;"> SEATTLE BOEING FIELD WA US </td> <td style="text-align:right;"> 50 </td> <td style="text-align:right;"> 54 </td> <td style="text-align:right;"> 56 </td> <td style="text-align:right;"> 64 </td> <td style="text-align:right;"> 66 </td> <td style="text-align:right;"> 71 </td> <td style="text-align:right;"> 76 </td> <td style="text-align:right;"> 77 </td> <td style="text-align:right;"> 69 </td> <td style="text-align:right;"> 62 </td> <td style="text-align:right;"> 55 </td> <td style="text-align:right;"> 42 </td> </tr> <tr> <td style="text-align:left;"> WAIKIKI 717.2 HI US </td> <td style="text-align:right;"> 83 </td> <td style="text-align:right;"> 84 </td> <td style="text-align:right;"> 84 </td> <td style="text-align:right;"> 86 </td> <td style="text-align:right;"> 87 </td> <td style="text-align:right;"> 87 </td> <td style="text-align:right;"> 88 </td> <td style="text-align:right;"> 87 </td> <td style="text-align:right;"> 87 </td> <td style="text-align:right;"> 86 </td> <td style="text-align:right;"> 84 </td> <td style="text-align:right;"> 82 </td> </tr> </tbody> </table> --- ### Generating the temperature readings crosstab #### Step by step - First subquery: generates the data for the crosstab, calculating the median maximum temperature for each month. - It supplies the three required columns. - The first column, station_name, names the rows. - The second column uses the date_part function to extract the month from observation _date, which provides the crosstab columns. - Then we use percentile_cont(.5) to find the 50th percentile, or the median, of the max_temp. - We group by station and month so we have a median max_temp for each month at each station. --- ### Generating the temperature readings crosstab #### Step by step ```sql SELECT station_name, date_part('month', observation_date), percentile_cont(.5) WITHIN GROUP (ORDER BY max_temp) FROM temperature_readings GROUP BY station_name, date_part('month', observation_date) ORDER BY station_name; ``` -- <table> <thead> <tr> <th style="text-align:left;"> station_name </th> <th style="text-align:right;"> date_part </th> <th style="text-align:right;"> percentile_cont </th> </tr> </thead> <tbody> <tr> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:right;"> 1 </td> <td style="text-align:right;"> 34 </td> </tr> <tr> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:right;"> 2 </td> <td style="text-align:right;"> 36 </td> </tr> <tr> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:right;"> 3 </td> <td style="text-align:right;"> 46 </td> </tr> <tr> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:right;"> 4 </td> <td style="text-align:right;"> 50 </td> </tr> </tbody> </table> --- ### Generating the temperature readings crosstab #### Step by step - Second subquery: produces the set of category names for the columns. - The `generate_series` function creates a list of numbers from 1 to 12 that match the month numbers `date_part` extracts from observation_date: -- ```sql SELECT month FROM generate_series(1,12) month; ``` --- class:center, middle <table> <thead> <tr> <th style="text-align:right;"> month </th> </tr> </thead> <tbody> <tr> <td style="text-align:right;"> 1 </td> </tr> <tr> <td style="text-align:right;"> 2 </td> </tr> <tr> <td style="text-align:right;"> 3 </td> </tr> <tr> <td style="text-align:right;"> 4 </td> </tr> <tr> <td style="text-align:right;"> 5 </td> </tr> <tr> <td style="text-align:right;"> 6 </td> </tr> <tr> <td style="text-align:right;"> 7 </td> </tr> <tr> <td style="text-align:right;"> 8 </td> </tr> <tr> <td style="text-align:right;"> 9 </td> </tr> <tr> <td style="text-align:right;"> 10 </td> </tr> <tr> <td style="text-align:right;"> 11 </td> </tr> <tr> <td style="text-align:right;"> 12 </td> </tr> </tbody> </table> --- - Following `AS`, we provide the names and data types for the crosstab's output columns: -- ```sql SELECT * FROM crosstab('SELECT station_name, date_part(''month'', observation_date), percentile_cont(.5) WITHIN GROUP (ORDER BY max_temp) FROM temperature_readings GROUP BY station_name, date_part(''month'', observation_date) ORDER BY station_name', 'SELECT month FROM generate_series(1,12) month') AS (station varchar(50), jan numeric(3,0), feb numeric(3,0), mar numeric(3,0), apr numeric(3,0), may numeric(3,0), jun numeric(3,0), jul numeric(3,0), aug numeric(3,0), sep numeric(3,0), oct numeric(3,0), nov numeric(3,0), dec numeric(3,0) ); ``` --- class: center, middle <table> <thead> <tr> <th style="text-align:left;"> station </th> <th style="text-align:right;"> jan </th> <th style="text-align:right;"> feb </th> <th style="text-align:right;"> mar </th> <th style="text-align:right;"> apr </th> <th style="text-align:right;"> may </th> <th style="text-align:right;"> jun </th> <th style="text-align:right;"> jul </th> <th style="text-align:right;"> aug </th> <th style="text-align:right;"> sep </th> <th style="text-align:right;"> oct </th> <th style="text-align:right;"> nov </th> <th style="text-align:right;"> dec </th> </tr> </thead> <tbody> <tr> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:right;"> 34 </td> <td style="text-align:right;"> 36 </td> <td style="text-align:right;"> 46 </td> <td style="text-align:right;"> 50 </td> <td style="text-align:right;"> 66 </td> <td style="text-align:right;"> 77 </td> <td style="text-align:right;"> 81 </td> <td style="text-align:right;"> 80 </td> <td style="text-align:right;"> 77 </td> <td style="text-align:right;"> 65 </td> <td style="text-align:right;"> 57 </td> <td style="text-align:right;"> 35 </td> </tr> <tr> <td style="text-align:left;"> SEATTLE BOEING FIELD WA US </td> <td style="text-align:right;"> 50 </td> <td style="text-align:right;"> 54 </td> <td style="text-align:right;"> 56 </td> <td style="text-align:right;"> 64 </td> <td style="text-align:right;"> 66 </td> <td style="text-align:right;"> 71 </td> <td style="text-align:right;"> 76 </td> <td style="text-align:right;"> 77 </td> <td style="text-align:right;"> 69 </td> <td style="text-align:right;"> 62 </td> <td style="text-align:right;"> 55 </td> <td style="text-align:right;"> 42 </td> </tr> <tr> <td style="text-align:left;"> WAIKIKI 717.2 HI US </td> <td style="text-align:right;"> 83 </td> <td style="text-align:right;"> 84 </td> <td style="text-align:right;"> 84 </td> <td style="text-align:right;"> 86 </td> <td style="text-align:right;"> 87 </td> <td style="text-align:right;"> 87 </td> <td style="text-align:right;"> 88 </td> <td style="text-align:right;"> 87 </td> <td style="text-align:right;"> 87 </td> <td style="text-align:right;"> 86 </td> <td style="text-align:right;"> 84 </td> <td style="text-align:right;"> 82 </td> </tr> </tbody> </table> - The table shows the median maximum temperature each month for each station --- # Reclassifying Values with CASE - `CASE` is a conditional expression, meaning it let's you add some "if this, then ..." logic to your query. - The `CASE` syntax follows this pattern: ```sql CASE WHEN condition THEN result WHEN another_condition THEN result ELSE result END ``` --- # CASE - Conditions can be any expression that the database can evaluate as TRUE or FALSE. - If the condition is TRUE, the case statement returns the result and stops checking any further conditions. - If the condition is FALSE, the database moves on to evaluate the next condition. - We can also provide an optional **ELSE** statement to return a result in case no condition evaluates as TRUE. - Without an **ELSE** clause, the statement would return a **NULL** when no conditions are TRUE --- ## Reclassifying temperature data with CASE - We are creating five ranges for the max_temp column in temperature_reading, which we define using comparison operators. - The `CASE` statement evaluates each value to find whether any of the five expressions are TRUE. - If TRUE, the statement outputs the appropriate text. - If none of the statements is TRUE, the ELSE clause assigns the value to the category Inhumane. --- ## Reclassifying temperature data with CASE ```sql SELECT max_temp, CASE WHEN max_temp >= 90 THEN 'Hot' WHEN max_temp BETWEEN 70 AND 89 THEN 'Warm' WHEN max_temp BETWEEN 50 AND 69 THEN 'Pleasant' WHEN max_temp BETWEEN 33 AND 49 THEN 'Cold' WHEN max_temp BETWEEN 20 AND 32 THEN 'Freezing' ELSE 'Inhumane' END AS temperature_group FROM temperature_readings; ``` -- <table> <thead> <tr> <th style="text-align:right;"> max_temp </th> <th style="text-align:left;"> temperature_group </th> </tr> </thead> <tbody> <tr> <td style="text-align:right;"> 31 </td> <td style="text-align:left;"> Freezing </td> </tr> <tr> <td style="text-align:right;"> 34 </td> <td style="text-align:left;"> Cold </td> </tr> <tr> <td style="text-align:right;"> 32 </td> <td style="text-align:left;"> Freezing </td> </tr> <tr> <td style="text-align:right;"> 32 </td> <td style="text-align:left;"> Freezing </td> </tr> <tr> <td style="text-align:right;"> 34 </td> <td style="text-align:left;"> Cold </td> </tr> <tr> <td style="text-align:right;"> 38 </td> <td style="text-align:left;"> Cold </td> </tr> </tbody> </table> --- ## Using CASE in a Common Table Expression (CTE) - The query before is a good example of a preprocessing step you would use in a CTE. - Now that we've grouped the temperatures in categories, let's count the groups by city in a CTE to see how many days of the year fall into each temperature category. --- #### Using CASE in a CTE - This code reclassifies the temperatures, and then counts and groups by station name to find general climate classifications of each city. - `WITH` defines the CTE of temps_collapsed, which has two columns: `station_name` and `max_temperature_group` - We then run a `SELECT` query on the CTE, performing straightforward `COUNT(*)` and `GROUP BY` operations on both columns. ```sql WITH temps_collapsed (station_name, max_temperature_group) AS (SELECT station_name, CASE WHEN max_temp >= 90 THEN 'Hot' WHEN max_temp BETWEEN 70 AND 89 THEN 'Warm' WHEN max_temp BETWEEN 50 AND 69 THEN 'Pleasant' WHEN max_temp BETWEEN 33 AND 49 THEN 'Cold' WHEN max_temp BETWEEN 20 AND 32 THEN 'Freezing' ELSE 'Inhumane' END FROM temperature_readings) SELECT station_name, max_temperature_group, count(*) FROM temps_collapsed GROUP BY station_name, max_temperature_group ORDER BY station_name, count(*) DESC; ``` --- <table> <thead> <tr> <th style="text-align:left;"> station_name </th> <th style="text-align:left;"> max_temperature_group </th> <th style="text-align:right;"> count </th> </tr> </thead> <tbody> <tr> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:left;"> Warm </td> <td style="text-align:right;"> 133 </td> </tr> <tr> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:left;"> Cold </td> <td style="text-align:right;"> 92 </td> </tr> <tr> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:left;"> Pleasant </td> <td style="text-align:right;"> 91 </td> </tr> <tr> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:left;"> Freezing </td> <td style="text-align:right;"> 30 </td> </tr> <tr> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:left;"> Inhumane </td> <td style="text-align:right;"> 8 </td> </tr> <tr> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:left;"> Hot </td> <td style="text-align:right;"> 8 </td> </tr> <tr> <td style="text-align:left;"> SEATTLE BOEING FIELD WA US </td> <td style="text-align:left;"> Pleasant </td> <td style="text-align:right;"> 198 </td> </tr> <tr> <td style="text-align:left;"> SEATTLE BOEING FIELD WA US </td> <td style="text-align:left;"> Warm </td> <td style="text-align:right;"> 98 </td> </tr> <tr> <td style="text-align:left;"> SEATTLE BOEING FIELD WA US </td> <td style="text-align:left;"> Cold </td> <td style="text-align:right;"> 50 </td> </tr> <tr> <td style="text-align:left;"> SEATTLE BOEING FIELD WA US </td> <td style="text-align:left;"> Hot </td> <td style="text-align:right;"> 3 </td> </tr> <tr> <td style="text-align:left;"> WAIKIKI 717.2 HI US </td> <td style="text-align:left;"> Warm </td> <td style="text-align:right;"> 361 </td> </tr> <tr> <td style="text-align:left;"> WAIKIKI 717.2 HI US </td> <td style="text-align:left;"> Hot </td> <td style="text-align:right;"> 5 </td> </tr> </tbody> </table> --- #### Using CASE in a CTE / Step by step ```sql SELECT station_name, CASE WHEN max_temp >= 90 THEN 'Hot' WHEN max_temp BETWEEN 70 AND 89 THEN 'Warm' WHEN max_temp BETWEEN 50 AND 69 THEN 'Pleasant' WHEN max_temp BETWEEN 33 AND 49 THEN 'Cold' WHEN max_temp BETWEEN 20 AND 32 THEN 'Freezing' ELSE 'Inhumane' END FROM temperature_readings ``` -- <table> <thead> <tr> <th style="text-align:left;"> station_name </th> <th style="text-align:left;"> case </th> </tr> </thead> <tbody> <tr> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:left;"> Freezing </td> </tr> <tr> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:left;"> Cold </td> </tr> <tr> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:left;"> Freezing </td> </tr> <tr> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:left;"> Freezing </td> </tr> <tr> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:left;"> Cold </td> </tr> <tr> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:left;"> Cold </td> </tr> <tr> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:left;"> Cold </td> </tr> </tbody> </table> --- #### Using CASE in a CTE / Step by step ```sql WITH temps_collapsed (station_name, max_temperature_group) AS (SELECT station_name, CASE WHEN max_temp >= 90 THEN 'Hot' WHEN max_temp BETWEEN 70 AND 89 THEN 'Warm' WHEN max_temp BETWEEN 50 AND 69 THEN 'Pleasant' WHEN max_temp BETWEEN 33 AND 49 THEN 'Cold' WHEN max_temp BETWEEN 20 AND 32 THEN 'Freezing' ELSE 'Inhumane' END FROM temperature_readings) SELECT station_name, max_temperature_group, count(*) FROM temps_collapsed GROUP BY station_name, max_temperature_group ORDER BY station_name, count(*) DESC; ``` -- <table> <thead> <tr> <th style="text-align:left;"> station_name </th> <th style="text-align:left;"> max_temperature_group </th> <th style="text-align:right;"> count </th> </tr> </thead> <tbody> <tr> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:left;"> Warm </td> <td style="text-align:right;"> 133 </td> </tr> <tr> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:left;"> Cold </td> <td style="text-align:right;"> 92 </td> </tr> <tr> <td style="text-align:left;"> CHICAGO NORTHERLY ISLAND IL US </td> <td style="text-align:left;"> Pleasant </td> <td style="text-align:right;"> 91 </td> </tr> </tbody> </table> --- class: center, middle ## Thank you! [Prof. Dr. Jan Kirenz](https://www.kirenz.com) HdM Stuttgart Nobelstraße 10 70569 Stuttgart