SQL NTILE( ) Function
NTILE () is a window function that divides the result set generated by partitioning the rows from the table. This function divides the result set specified by an argument to the function. Each row is then assigned the group number to which it belongs.
- The syntax for NTILE –
NTILE (number) OVER ([PARTITION BY column_name] ORDER BY column_name ASC|DESC);
NTILE() function returns the result set in groups of two sizes with one difference. The larger group is ahead of the smaller group within the order specified by the ORDER BY within the OVER() clause.
Example – Let’s create a table named Student_name.
CREATE TABLE Student_name (ID INT NOT NULL , Name varchar(50));
Let’s insert some dummy values into the Student_name Table,
INSERT INTO Student_name(id,Name) VALUES(1, 'Simon');
INSERT INTO Student_name(id,Name) VALUES(2, 'Alvin');
INSERT INTO Student_name(id,Name) VALUES(3, 'Jasmine');
INSERT INTO Student_name(id,Name) VALUES(4, 'Ronald');
INSERT INTO Student_name(id,Name) VALUES(5, 'Govind');
INSERT INTO Student_name(id,Name) VALUES(6, 'Ashish');
INSERT INTOStudent_name(id,Name) VALUES(7, 'Indu');
INSERT INTO Student_name(id,Name) VALUES(8, 'Aarti');
INSERT INTO Student_name(id,Name) VALUES(9, 'Sam');
INSERT INTO Student_name(id,Name) VALUES(10, 'Ishwar');
Now,
SELECT * FROM Student_name
| ID | Student_Name |
| 1 | Simon |
| 2 | Alvin |
| 3 | Jasmine |
| 4 | Ronald |
| 5 | Govind |
| 6 | Ashish |
| 7 | Indu |
| 8 | Aarti |
| 9 | Sam |
| 10 | Ishwar |
Let’s use NTILE () Function to divide the above rows into 3 groups –
SELECT id,Name NTILE(3) OVER (ORDER BY ID) AS To_Group FROM Student_name;
This is the output by executing the above query –
| id | Name | To_Group |
| 1 | Simon | 1 |
| 2 | Alvin | 1 |
| 3 | Jasmine | 1 |
| 4 | Ronald | 1 |
| 5 | Govind | 2 |
| 6 | Ashish | 2 |
| 7 | Indu | 2 |
| 8 | Aarti | 3 |
| 9 | Sam | 3 |
| 10 | Ishwar | 3 |
Other SQL topics to check out:
- What is RDBMS (Relational Database Management System)?
- SQL SELECT Statement
- SQL INSERT INTO SELECT Statement
- SQL ALL Operator
- SQL WHERE Clause
- SQL AND Operator
- SQL OR Operator
Follow us on Twitter, Facebook, Linkedin, and Tableau Public to stay updated with our latest blog and what’s new in Tableau.
If you are looking forward to getting your data pipeline built and setting up the dashboard for business intelligence, book a call now from here.
#analytics #data #business #artificialintelligence #machinelearning #startup #deeplearning #deeplearning #datascience #ai #growth #dataanalytics #india #datascientist #powerbi #dataanalysis #tableau #SQL #businessanalytics #businessanalyst #businessandmanagement #dataanalyst #businessanalysis #analyst #analysis #powerbideveloper #powerbidesktop #letsviz
