NTILE analytic function in SQL Server - #12 #NTILE #SQLServer #AnalyticFunctions #SQLTips

#NTILE #SQLServer #AnalyticFunctions #SQLTips #DataAnalysis #DatabaseManagement #SQLFunctions #SQLAnalytics #SQLLearning #DataScience #BigData #DBMS #DataEngineering #MicrosoftSQLServer #TechEducation #Coding #Programming #DataAnalytics #SQLQuery

The NTILE function in SQL Server is an analytic function that distributes the rows in an ordered partition into a specified number of approximately equal groups, or tiles. This is particularly useful for performing calculations such as percentile or quartile analysis or when dividing data into equal parts for further detailed analysis.

Syntax of NTILE:
NTILE(number_of_tiles) OVER (
[PARTITION BY column_name, ...]
ORDER BY column_name, ...
)

number_of_tiles: This is an integer that specifies how many groups the result set should be divided into. It's important to choose this number based on the analysis needs.

PARTITION BY: This clause is optional. It divides the result set into partitions the function is applied on. If omitted, the function treats all rows of the query result set as a single group.

ORDER BY: This clause specifies the order in which rows will be assigned to the tiles. This is necessary as it determines how the groups are formed.

SQL Server Create Table Script:
-- Create the table for Employee data
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Department VARCHAR(50),
Salary DECIMAL(10, 2) -- Salary can have decimals and needs precision
);

-- Insert sample data into the table
INSERT INTO Employees (EmployeeID, Department, Salary)
VALUES
(1, 'Sales', 55000.00),
(2, 'Sales', 48000.00),
(3, 'HR', 45000.00),
(4, 'HR', 47000.00),
(5, 'IT', 62000.00),
(6, 'IT', 60000.00),
(7, 'IT', 58000.00),
(8, 'IT', 59000.00);

-- Verify the inserted data
SELECT * FROM Employees;

Explanation of the Script:
EmployeeID: This column uses an INT data type and is defined as the primary key, meaning each value must be unique and not null. This identifies each employee uniquely.

Department: This is a VARCHAR(50) data type, allowing variable-length characters up to 50, enough to store the names of the departments.

Salary: The DECIMAL(10, 2) data type is used here to ensure that salary amounts are precise with two decimal points. This format supports values up to 10 digits in total.

Example Scenario
Let's assume we have a Employees table with the following data:
EmployeeID Department Salary
1 Sales 55000
2 Sales 48000
3 HR 45000
4 HR 47000
5 IT 62000
6 IT 60000
7 IT 58000
8 IT 59000
You want to divide the employees in each department into 2 groups (tiles) based on their salary.

SQL Query using NTILE
SELECT
EmployeeID,
Department,
Salary,
NTILE(2) OVER (PARTITION BY Department ORDER BY Salary) AS SalaryTile
FROM
Employees;

Expected Output
EmployeeID Department Salary SalaryTile
3 HR 45000 1
4 HR 47000 2
2 Sales 48000 1
1 Sales 55000 2
7 IT 58000 1
8 IT 59000 1
6 IT 60000 2
5 IT 62000 2

Explanation
HR Department: The NTILE function divides the 2 employees into 2 groups; since there are only 2 employees, each employee falls into their own group.
Sales Department: Similar to HR, each sales employee falls into their own tile group due to having only two employees.

IT Department: The 4 IT employees are divided into 2 tiles. The first half (lower salaries) are in tile 1, and the second half (higher salaries) are in tile 2.

Summery
This example demonstrates how NTILE can be used to split data into defined groups for comparative analysis across or within categories. It's especially useful in percentile-based analysis or load balancing tasks where equitable distribution is needed.

Chapter :
00:00 Introduction to NTILE () Analytic Function
00:35 Syntax of NTILE()
01:23 Example: Create Scripts | Query
01:52 Expected Output
02:17 Summery

Thank you for watching this video
EVERYDAY BE CODING