Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
SQL Server 119
#SQLServer 88
Oracle 70
#SQL SERVER 35
BRH 31
SQL Server 2012 29
denali 23
#TSQL 19
TSQL 19
C# 15

Archive · View All
October 2011 31
November 2011 30
September 2011 30
August 2011 18
December 2011 15
July 2011 13
June 2011 8
May 2012 4
April 2012 3
January 2010 3

Cume_Dist Function in Denali (SQL Server 2012) CTP3

Sep 3 2011 3:12AM by Niladri Biswas   

Cume_Dist Function

Purpose: Within a group of rows, it calculates the cumulative distribution of a value in a group of values.

Syntax: Cume_Dist() Over ([partition_by_clause] order_by_clause)

Formula:

Cumulative_Distribution = (R)/(N)

Where,

R => The row whose rank needs to be found

N => Total number of rows or records.

e.g. We have a set with 10 elements. We need to find the cumulative distribution of the 7th row. Henceforth, applying the values to the above formula, we get

R = 7 , N = 10

So, Cumulative_Distribution = (7)/(10) = 0.7

The range of values return by this function is between 0 to 1 inclusive. The return data type is always a positive number.

Before going to look into some examples of First_Value we will first create the test environment

Run the below script for doing so

IF EXISTS (SELECT * FROM sys.objects WHERE name = N'PlayerTable' AND type = 'U')
    DROP TABLE PlayerTable
GO
SET ANSI_NULLS ON
GO
--Create the table
CREATE TABLE [dbo].[PlayerTable](
	PlayerID INT IDENTITY(1001,1),
	PlayerName VARCHAR(15),
	BelongsTo VARCHAR(15),
	MatchPlayed INT,
	RunsMade INT,
	WicketsTaken INT,
	FeePerMatch NUMERIC(16,2)
) ON [PRIMARY]
GO

--Insert records
INSERT INTO PlayerTable(
	PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) 
	VALUES('A. Won','India',10,440,10, 1000)
INSERT INTO PlayerTable(
	PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) 
	VALUES('A. Cricket','India',10,50,17, 4000)
INSERT INTO PlayerTable(
	PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) 
	VALUES('B. Dhanman','India',10,650,0,3600)
INSERT INTO PlayerTable(
	PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) 
	VALUES('C. Barsat','India',10,950,0,5000)
INSERT INTO PlayerTable(
	PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) 
	VALUES('A. Mirza','India',2,3,38, 3600)
INSERT INTO PlayerTable(
	PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) 
	VALUES('M. Karol','US',15,44,4, 2000)
INSERT INTO PlayerTable(
	PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) 
	VALUES('Z. Hamsa','US',3,580,0, 400)
INSERT INTO PlayerTable(
	PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) 
	VALUES('K. Loly','US',6,500,12,8000)
INSERT INTO PlayerTable(
	PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) 
	VALUES('S. Summer','US',87,50,8,1230)
INSERT INTO PlayerTable(
	PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) 
	VALUES('J.June','US',12,510,9, 4988)
INSERT INTO PlayerTable(
	PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) 
	VALUES('A.Namaki','Australia',1,4,180, 9999)
INSERT INTO PlayerTable(
	PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) 
	VALUES('Z. Samaki','Australia',2,6,147, 8888)
INSERT INTO PlayerTable(
	PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) 
	VALUES('MS. Kaki','Australia',40,66,0,1234)
INSERT INTO PlayerTable(
	PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) 
	VALUES('S. Boon','Australia',170,888,10,890)
INSERT INTO PlayerTable(
	PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) 
	VALUES('DC. Shane','Australia',28,39,338, 4444)
INSERT INTO PlayerTable(
	PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) 
	VALUES('S. Noami','Singapore',165,484,45, 5678)
INSERT INTO PlayerTable(
	PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) 
	VALUES('Z. Biswas','Singapore',73,51,50, 2222)
INSERT INTO PlayerTable(
	PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) 
	VALUES('K. Dolly','Singapore',65,59,1,9999)
INSERT INTO PlayerTable(
	PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) 
	VALUES('S. Winter','Singapore',7,50,8,128)
INSERT INTO PlayerTable(
	PlayerName, BelongsTo, MatchPlayed,RunsMade,WicketsTaken,FeePerMatch) 
	VALUES('J.August','Singapore',9,99,98, 890)

-- Project the records
Select * from PlayerTable

/*Result

PlayerID	PlayerName	BelongsTo	MatchPlayed	RunsMade	WicketsTaken	FeePerMatch
1001		A. Won		India		10		440		10		1000.00
1002		A. Cricket	India		10		50		17		4000.00
1003		B. Dhanman	India		10		650		0		3600.00
1004		C. Barsat	India		10		950		0		5000.00
1005		A. Mirza	India		2		3		38		3600.00
1006		M. Karol	US		15		44		4		2000.00
1007		Z. Hamsa	US		3		580		0		400.00
1008		K. Loly		US		6		500		12		8000.00
1009		S. Summer	US		87		50		8		1230.00
1010		J.June		US		12		510		9		4988.00
1011		A.Namaki	Australia	1		4		180		9999.00
1012		Z. Samaki	Australia	2		6		147		8888.00
1013		MS. Kaki	Australia	40		66		0		1234.00
1014		S. Boon		Australia	170		888		10		890.00
1015		DC. Shane	Australia	28		39		338		4444.00
1016		S. Noami	Singapore	165		484		45		5678.00
1017		Z. Biswas	Singapore	73		51		50		2222.00
1018		K. Dolly	Singapore	65		59		1		9999.00
1019		S. Winter	Singapore	7		50		8		128.00
1020		J.August	Singapore	9		99		98		890.00

*/

Example

Select 
	PlayerID
	,PlayerName
	,BelongsTo
	,FeePerMatch
	,Cume_Dist() Over(
		Partition By BelongsTo Order By BelongsTo,FeePerMatch
		) As CumilativeDistribution
From PlayerTable

/* Result (Partial)

PlayerID	PlayerName	BelongsTo	FeePerMatch	CumilativeDistribution
1014		S. Boon		Australia	890.00		0.2
1013		MS. Kaki	Australia	1234.00		0.4
1015		DC. Shane	Australia	4444.00		0.6
1012		Z. Samaki	Australia	8888.00		0.8
1011		A.Namaki	Australia	9999.00		1
1001		A. Won		India		1000.00		0.2
1003		B. Dhanman	India		3600.00		0.6
1005		A. Mirza	India		3600.00		0.6
1002		A. Cricket	India		4000.00		0.8
1004		C. Barsat	India		5000.00		1
*/

Now let us understand how the relative rank for the [Fee Per Match] column has been generated.

Take of case of the row where the Player ID is 1015 i.e. 3rd from the row.

There are altogether 5 rows for the [Belongs to] field “Australia”.

So , we have R = 3 and N = 5.

Placing the values in our formula yields.

Cumulative_Distribution = (3)/(5) = 0.6

Hope this helps

Tags: sql 11, #SQLServer, SQL Server, TSQL, #TSQL, Denali CTP3, SQL Server 2012,


Niladri Biswas
7 · 21% · 6720
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

"Cume_Dist Function in Denali (SQL Server 2012) CTP3" rated 5 out of 5 by 1 readers
Cume_Dist Function in Denali (SQL Server 2012) CTP3 , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]