Arquivo
Ranking Functions – Top 5
A friend of mine recently told me she’s very proud of her son, because he is consistently in the upper quarter of every class he takes. Right there she performed a calculation similar to the NTILE function. She didn’t know it, but she tiled the class into four pieces and then identified which piece her son belongs in.
Just like its three fellow ranking functions, NTILE takes data you’ve sorted and then marches straight down the list in order of row number. Instead of assigning a rank value to each record, NTILE counts records and assigns them into a group (a.k.a., “tile”, or “bucket” in geek speak) based on:
1) the number of tiles/pieces you have specified, and
2) the count of records contained in the query.
Like all other ranking functions, we want to first write a SELECT statement and an ORDER BY clause to determine the sort order.
Adding the NTILE function to the query is almost the same as with the first three ranking functions we saw from yesterdays blog post. The ORDER BY clause gets plugged into the parentheses of the OVER( ) clause. Just one new item to note with the NTILE: you must insert the number of tiles (or pieces) in the parentheses of the NTILE. In this case we want four pieces, just like my friend’s story about her son.
The upper quarter consists of Grants 007, 008, 009 and are the “1” group of records. The lowest quarter (the group “4” records) consists of Grants 001 and 010 as seen in the figure below
If you want just an upper and lower half ranking just change this query to show just two pieces. We see the grant records divided into an upper half (the “1” records) and a lower half (“2” records). The figure below shows the top 5 grants in group 1 and the lowest 5 grants in group 2. In this case the NTILE query now divides the grants into an upper half and lower half.
LEFT OUTER JOIN
August 5, 2011 by pinaldave
Finding un-matching Records
Often time we want to find records in one table that have no matching key in another table. This is common for things like finding products that have never sold, or students who did not re-enroll. Something we were expecting is missing. Records in one table were expecting some related activity in another table and did not find them. There are many ways to find these records.
Basic Subquery
We have probably all heard that subqueries should be avoided if there is a better solution. Often times basic subqueries are used where a simple “Unmatched Records Query” could have been used.
Let’s start off with the subquery example. Looking at the Location table in the figure below we see all the data. In fact, this table does not allow nulls for the LocationID field. Looking further, there are no nulls anywhere in the Location table. So does this mean that all locations have at least one employee? It’s possible that some of these locations might be new and have not employees working there yet.
How can we find the location with no employees. We can use a subquery. For example we can use the following query to find all locations of the JProCo database that have now employee working there.
SELECT *
FROM Location
WHERE LocationID
NOT IN (SELECT DISTINCT LocationID
FROM Employee
WHERE LocationID IS NOT NULL)
Unmatched Records Queries
If you wanted to find all locations with no employees, you could run an “Unmatched Records Query”. Let’s build this piece by piece. In this case, we have to join the Location table with the Employee table to determine the location that has no employees. What type of join will tell us this? Since nulls don’t map through a join, the INNER JOIN drops the record from the result set and we won’t see Chicago. The outer join will show both the matches and the unmatched records, so we see every location. In the figure below we get all location even if there are no employees.
Notice Seattle is listed many times but Chicago is listed once with no employees found. A NULL appears in the fields from the Employee table for Chicago. With the Location table on the left and the NULL on the right, we have part of an unmatched records query. To find just the records that don’t match, we look for null records on the table that the outer join does not favor. In this case, it’s the Employee table.
The outer join will show us the unmatched records with null location details if you set the WHERE clause to look for nulls on a field in the non-dominant table. Unmatched record queries use SQL to return a result set displaying only the unmatched records between the two tables.
When our query criterion specifies NULL, only Chicago shows up in our result set. By doing a LEFT OUTER JOIN and using a NULL value from the Employee table (or “RIGHT” table) as our search condition criteria, our unmatched records query shows us one record.
Note: If you want to setup the sample JProCo database on your system you can watch this video.
Road to BI 