PERCENTILE_CONT analytic function in SQL Server - #7 #SQLServer #PERCENTILECONT #Analytics

#SQLServer #PERCENTILECONT #Analytics #DataAnalysis #SQLExamples #AnalyticFunctions #SQLTips #DatabaseDevelopment #SQLQueries #DataScience #SQLLearning #BusinessIntelligence #TechEducation #DataEngineering #ProgrammingTips #Technology #BigData #DatabaseManagement #TechCommunity #DataInsights

------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------
QUERY SOLVE -
1. What is analytic functions in SQL Server?
2. What is PERCENTILE_CONT analytic functions in SQL Server?
4. Syntax of PERCENTILE_CONT analytic functions?
5. What is partition by clause in SQL Server?
6. What is Order by clause in SQL Server?
7. Default parameters of PERCENTILE_CONT analytic functions in SQL Server.
8. Get continuous distribution median value without use of PERCENTILE_CONT analytic function.
9. Get continuous distribution median value use of PERCENTILE_CONT analytic function.

PERCENTILE CONT()
Calculates a percentile based on a continuous distribution of the column value in SQL Server. The result is interpolated and might not be equal to any of the specific values in the column.

Syntax :
PERCENTILE_CONT ( numeric_literal )
WITHIN GROUP ( ORDER BY order_by_expression)
OVER ( [ partition_by_clause ])

Numeric_literal: The percentile to compute. The value must range between 0.0 and 1.0.

WITHIN GROUP ( ORDER BY order_by_expression ) Specifies a list of numeric values to sort and compute the percentile over. Only one order_by_expression is allowed. The expression must evaluate to an exact or approximate numeric type, with no other data types allowed. Exact numeric types are int, big int, small int, tiny int, numeric, bit, decimal, small money, and money. Approximate numeric types are float and real.

OVER ( partition_by_clause ): Divides the result set produced by the FROM clause into partitions to which the percentile function is applied.

Query:
Get median value without use of PERCENTILE_CONT Analytic function:

Declare @MaxValue int;
Declare @MinValue int;

Select @MaxValue = (select MAX(Price) from
(select TOP 60 percent Price from dbo.Bikes2 where Make='Yamaha' and Type='SuperBike' order by Price) as a)

Select @MinValue =(select MIN(Price) from
(select TOP 60 percent Price from dbo.Bikes2 where Make='Yamaha' and Type='SuperBike' order by Price desc) as a)

Use PERCENTILE_CONT Analytic functions:

SELECT [Name],
Price,
[Type],
PERCENTILE_CONT(0.1) WITHIN GROUP (ORDER BY Price) OVER (PARTITION BY [Type]) AS [0.1 PERCENTILE_CONT],
PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY Price) OVER (PARTITION BY [Type]) AS [0.2 PERCENTILE_CONT],
PERCENTILE_CONT(0.3) WITHIN GROUP (ORDER BY Price) OVER (PARTITION BY [Type]) AS [0.3 PERCENTILE_CONT],
PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY Price) OVER (PARTITION BY [Type]) AS [0.4 PERCENTILE_CONT],
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Price) OVER (PARTITION BY [Type]) AS [0.5 PERCENTILE_CONT],
PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY Price) OVER (PARTITION BY [Type]) AS [0.6 PERCENTILE_CONT],
PERCENTILE_CONT(0.7) WITHIN GROUP (ORDER BY Price) OVER (PARTITION BY [Type]) AS [0.7 PERCENTILE_CONT],
PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY Price) OVER (PARTITION BY [Type]) AS [0.7 PERCENTILE_CONT],
PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY Price) OVER (PARTITION BY [Type]) AS [0.7 PERCENTILE_CONT],
PERCENTILE_CONT(1.0) WITHIN GROUP (ORDER BY Price) OVER (PARTITION BY [Type]) AS [0.7 PERCENTILE_CONT]

FROM [DbSample].[dbo].[Bikes2] where Make = 'Yamaha'

Thanks for watching this video.