June 5, 2015 - coding sql

# SQL - Part 5 - Common Table Expressions

Xavier Geerinck

CTE's (Common Table Expressions) are mostly used when creating recursive queries, creating temporary views (instead of subqueries).

## Calculating the dates of easter

**Use case And Example:** We want to calculate the dates of when it is easter for the next hundred years.

**Solution:**
Since we are explaining examples in SQL we are not working on the math of these problems, that is why the variables to calculate the date of easter are given:

j = year - 1900a = MOD(year - 1900, 19)b = FLOOR((7 * a + 1) / 19)c = MOD(11 * a + 4 - b, 29)d = 25 - c - MOD9j + FLOOR(j / 4) + 31 - c, 7)

if d > 0, then d returns the day number in april, if d<=0 then (31 + d) returns the day in march for easter.

Another thing that we know is that the following recursive query returns all the years starting from 1900 until 2014:

SELECT 1899 + levelFROM dualCONNECT BY level <= 2014 - 1899

Now we need to connect those 2 queries together.

The recursion part will come in a later article, so don't bother to much about it right now :D

**Query:**

WITH-- Get the years from 1900 - 2014p AS (SELECT 1899 + level yearFROM dualCONNECT BY level <= 2014 - 1899 ),-- Start filling in the formulas that were given, and connect them to each otherq AS (SELECT year, year - 1900 j, MOD(year - 1900, 19) a, FLOOR((7 * MOD(year - 1900, 19) + 1) / 19) bFROM p),r AS (SELECT year, j, a, b, MOD(11 * a + 4 - b, 29) cFROM q),s AS (SELECT year, 25 - c - MOD(j + FLOOR(j / 4) + 31 - c, 7) easterFROM r)-- Add them to the date and be sure to add the easter dateSELECT year, TO_CHAR(TO_TIMESTAMP('01-04' || CAST(year AS CHAR(4)), 'DD-MM-RRRR') + easter - 1, 'DD-MM-YYYY') easterFROM sORDER BY year;

## TOP N problem (with the use of the rank() function and CTE's)

**Use Case:** When we want to be able to return the top n results of a result set then we have several methods, now we can also do this with the rank function.

**Example:** We want to get the number of appearances for the elevations in the world, and then we want to get the top 10 most appearing elevations.

**Solution:** To solve this problem we will use a CTE and the rank function, the first CTE will get our result and the second will appoint a rank function to the first it's result. On the end it will then limit on the rank.

**Query:**

WITH x AS (SELECT elevation, COUNT(1) amountFROM citiesWHERE elevation IS NOT NULLGROUP BY elevationORDER BY amount DESC),y AS(SELECT elevation, amount, rank() over(order by amount desc) "rank"FROM x)SELECT elevation, amount FROM y WHERE "rank" <= 10

## Pivoting results, Removing NULL in between and only showing value for the first row.

**Use Case:** You want to display results horizontally instead of vertically, and you also want to remove NULL results in between.

**Example:** We need to select the results from a season and afterwards we need to make sure they are pivoted so that the top 3 are shown horizontally.

**Solution:** We write a CTE that selects the results and assigns a rank to every one based on gender and discipline. Once we have that we are able to pivot them in the next CTE by using the CASE function. Here comes the trick, when we just use the case it will show NULL results for the other rows and we don't see the top 3 next to each other.

Something like this:

result1 | result2 | result3 |
---|---|---|

a | (null) | (null) |

(null) | b | (null) |

(null) | (null) | c |

Now we can solve this by using the MAX function on the result set, which will merge them into one row for us.

The second thing we want to do is to only show the season (which is a year), in the first row, and the same for the gender. We do this by assigning a ROW_NUMBER for the season, and a ROW_NUMBER for the genders, and only showing it when this is 1.

**Query:**

WITH x AS (SELECT season, gender, discipline, name, rank() over(partition by gender, discipline order by points) seqFROM rankingWHERE season = 2007),y AS (SELECT season, gender, discipline, MAX(CASE WHEN seq = 1 THEN name END) "1", MAX(CASE WHEN seq = 2 THEN name END) "2", MAX(CASE WHEN seq = 3 THEN name END) "3", row_number() OVER(PARTITION BY season ORDER BY gender, discipline) row_season, row_number() OVER(PARTITION BY gender ORDER BY season, discipline) row_genderFROM xWHERE seq <= 3GROUP BY season, gender, disciplineORDER BY season, gender, discipline)SELECTCASE WHEN row_season = 1 THEN TO_CHAR(season) ELSE ' ' END "SEASON", CASE WHEN row_gender = 1 THEN TO_CHAR(gender) ELSE ' ' END "GEN", discipline, "1", "2", "3"FROM y

**Query2:**

WITH x AS (SELECT hasc1 hasc, hasc2 hasc2, lengthFROM grenzenWHERE hasc1 IN('BE', 'NL', 'FR', 'DE', 'LU')UNIONSELECT hasc2 hasc, hasc1 hasc2, lengthFROM grenzenWHERE hasc2 IN('BE', 'NL', 'FR', 'DE', 'LU')),y AS (SELECT x.hasc hasc, x.length length, re.name name, row_number() OVER(PARTITION BY x.hasc ORDER BY length DESC) "rw"FROM x xJOIN regios re ON x.hasc2 = re.hasc)SELECTMAX(CASE WHEN hasc = 'BE' THEN TO_CHAR(name) || '(' || length || ')' ELSE ' ' END) "BE", MAX(CASE WHEN hasc = 'NL' THEN TO_CHAR(name) || '(' || length || ')' ELSE ' ' END) "NL", MAX(CASE WHEN hasc = 'FR' THEN TO_CHAR(name) || '(' || length || ')' ELSE ' ' END) "FR", MAX(CASE WHEN hasc = 'DE' THEN TO_CHAR(name) || '(' || length || ')' ELSE ' ' END) "DE", MAX(CASE WHEN hasc = 'LU' THEN TO_CHAR(name) || '(' || length || ')' ELSE ' ' END) "LU"FROM yGROUP BY "rw"ORDER BY "rw"