|
Execute the following SQL Server T-SQL scripts in SSMS Query Editor to carry out statistical analysis of a randomly generated set of numbers. Calculate: Average, Median, Minimum, Maximum and Standard Deviation.
-- SQL statistical analysis - SQL median - SQL average
-- SQL standard deviation - SQL calculate median
USE AdventureWorks;
-- SQL local temporary table
CREATE TABLE #NumberList (
NumberListID INT IDENTITY ( 1 , 1 ),
X BIGINT NOT NULL)
GO
-- Populate temporary table with 20 rows of random numbers between 1 and 99
SET nocount ON
DECLARE @i INT
SET @i = 1
-- T-SQL while loop
WHILE (@i <= 21)
BEGIN
INSERT INTO #NumberList
(X)
VALUES (Cast(Rand() * 100 AS BIGINT))
SET @i = @i + 1
END
GO
SELECT * FROM #NumberList;
/*
NumberListID X
1 68
2 82
3 19
4 70
5 39
6 66
7 51
8 91
9 95
10 7
11 88
12 51
13 63
14 36
15 92
16 43
17 48
18 27
19 66
20 94
21 1
*/
-- SQL common table expression - CTE
-- SQL cross join
-- SQL row_number over order by
WITH cteAvg
AS (SELECT [avg] = Avg(X),
[min] = Min(X),
[max] = Max(X),
[stddev] = Stdev(X)
FROM #NumberList),
cteMedian
AS (SELECT X,
Row_number()
OVER(ORDER BY X ASC) AS high,
Row_number()
OVER(ORDER BY X DESC) AS low
FROM #NumberList)
SELECT [avg] AS average,
X AS median,
[min] AS minimum,
[max] AS maximum,
[stddev] AS 'standard deviation'
FROM cteMedian
CROSS JOIN cteAvg
WHERE high IN (low,low + 1,low - 1)
GO
/* Results
average median minimum maximum standard deviation
57 63 1 95 28.6024474477273
*/
-- Cleanup
drop table #NumberList
|
