CUME_DIST analytic function in SQL Server - #1

Опубликовано: 18 Октябрь 2024
на канале: Everyday Be Coding

9,262

104

#SQLServer #CUMEDIST #SQLAnalytics #DataAnalysis #TSQL #SQLFunctions #DataScience #Database #SQLQuery #SQLTips #DataEngineering #BigData #Analytics #SQLServer2019 #SQLServer2022 #TechTips #Programming #DataVisualization #BusinessIntelligence #SQLPerformance

Playlist -    • CUME_DIST analytic function in SQL Se...
------------------------------------------------------------------------------------------
CUME_DIST() -    • CUME_DIST analytic function in SQL Se...
PERCENT_RANK() -    • PERCENT_RANK analytic function in SQL...
FIRST_VALUE() -    • FIRST_VALUE Analytic Function in SQL ...
LAST_VALUE() -    • LAST_VALUE Analytic Functions in SQL ...
LEAD() -    • LED Analytic Function in SQL  - #5 #S...
LAG() -    • LAG Analytic Function in SQL Server -...
PERCENTILE_CONT() -    • PERCENTILE_CONT analytic function in ...
PERCENTILE_DISC() -    • PERCENTILE DISC analytic function in ...
------------------------------------------------------------------------------------------

QUERY SOLVE -
1. Analytic functions in SQL Server
2. CUME_DIST analytic functions in SQL Server
3. Syntax of CUME_DIST analytic functions
4. partition by clause in SQL Server
5. Order by clause in SQL Server
6. Default parameters of CUME_DIST analytic functions in SQL Server

CUME_DIST ()
As per SQL Server Official Documentation, the CUME_DIST function is used to find the cumulative distribution of value among the group of values. Simply speaking, the cumulative distribution value for a row can be calculated by dividing the row number (as defined by the ORDER BY clause) by the
total number of rows in the group.
The range of values returned by CUME_DIST is greater than 0.0 and less than or equal to 1.0

Syntax :
CUME_DIST( ) OVER ( [ partition_by_clause ] order_by_clause )

Formula :
CUME_DIST( ) = (RowNo / TotalRows)

QUERY :
CREATE TABLE SCRIPT -
----------------------------------------
CREATE TABLE [dbo].[Bikes](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](100) NULL,
[Make] [varchar](100) NULL,
[Model] [int] NULL,
[Price] [int] NULL,
[Type] [varchar](20) NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

-----CUME_DIST() - ANALYTIC FUNCTION QUERY
-----------------------------------------------------------------------------
SELECT [Name],
Make,
Model,
Price,
[Type],
CUME_DIST () over (PARTITION BY [TYPE] ORDER BY price) as CUME_DIST
FROM [Bikes]

Thanks for watching this video