NTILE() SQL function groups data into roughly equal groups like the SQL “GROUP BY” clause. However
NTILE() is applicable to window functions. A number can be specified with the function which will display the data organized roughly across a specified number of groups.
... used below is meant to indicate code before or after the
NTILE() clause below. It’s not part of the actual syntax.
A number is required between the parenthesis.
ORDER BY and a
PARTITION BY clause is a good idea to include. It makes the data more organized. The
ORDER BY SQL clause can show query results in ascending or descending order. They can be specified with
... NTILE(number_of_groups_to_split_data_between) OVER ( PARTITION BY column1_name ORDER BY column2_name DESC ) ...
Let’s say there is a “basketball_points” table where the first 10 rows look like this:
Then running an NTILE query so the different players and their points can be seen across weeks.
SELECTNTILE(4) OVER (PARTITION BY weekORDER BY total_points DESC) AS 'quartile',player,week,total_pointsFROM basketball_pointsLIMIT 10;
Running the following query will show data from the “basketball_points” table organized roughly into 4 groups. They’re going to be partitioned by week. That means that the results will display each week as a contiguous group. Each week is also ordered by total_points in descending order. Then the result of the “ntile” operation is set as “quartile”. So, now this new column highlights where players place in one of four quartiles of total points scored for that specific week.
Finally, the “player”, “week” and “total_points” columns are selected. The last line of the query adds a “LIMIT” condition, so that only the first 10 rows are displayed.
That will yield the following output: