SQL/part3 FuctDistr.sql at master · Grue1ing/SQL · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
DECLARE @pct AS FLOAT = 0.5;

SELECT testid, score,
		PERCENTILE_DISC(@pct) WITHIN GROUP(ORDER BY score) OVER(PARTITION BY testid) AS percentiledisc,
		PERCENTILE_CONT(@pct) WITHIN GROUP(ORDER BY score) OVER(PARTITION BY testid) AS percentilecont
FROM Stats.Scores

DECLARE @pct AS FLOAT = 0.5;

SELECT testid,
		PERCENTILE_DISC(@pct) WITHIN GROUP(ORDER BY score) OVER(PARTITION BY testid) AS percentiledisc,
		PERCENTILE_CONT(@pct) WITHIN GROUP(ORDER BY score) OVER(PARTITION BY testid) AS percentilecont
FROM Stats.Scores
GROUP BY testid

DECLARE @pct AS FLOAT = 0.5;

SELECT DISTINCT testid,
		PERCENTILE_DISC(@pct) WITHIN GROUP(ORDER BY score) OVER(PARTITION BY testid) AS percentiledisc,
		PERCENTILE_CONT(@pct) WITHIN GROUP(ORDER BY score) OVER(PARTITION BY testid) AS percentilecont
FROM Stats.Scores

DECLARE @pct AS FLOAT = 0.5;
WITH C AS
(
	SELECT DISTINCT testid,
		PERCENTILE_DISC(@pct) WITHIN GROUP(ORDER BY score) OVER(PARTITION BY testid) AS percentiledisc,
		PERCENTILE_CONT(@pct) WITHIN GROUP(ORDER BY score) OVER(PARTITION BY testid) AS percentilecont,
		ROW_NUMBER() OVER(PARTITION BY testid ORDER BY (SELECT NULL)) AS rownum
	FROM Stats.Scores
)
SELECT testid, percentiledisc, percentilecont
FROM C
WHERE rownum = 1


DECLARE @pct AS FLOAT = 0.5;

SELECT TOP (1) WITH TIES testid,
		PERCENTILE_DISC(@pct) WITHIN GROUP(ORDER BY score) OVER(PARTITION BY testid) AS percentiledisc,
		PERCENTILE_CONT(@pct) WITHIN GROUP(ORDER BY score) OVER(PARTITION BY testid) AS percentilecont
FROM Stats.Scores
ORDER BY ROW_NUMBER() OVER(PARTITION BY testid ORDER BY (SELECT NULL))


DECLARE @pct AS FLOAT = 0.5;
WITH C AS
(
	SELECT testid, score,
		ROW_NUMBER() OVER(PARTITION BY testid ORDER BY score) AS np,
		COUNT(*) OVER(PARTITION BY testid) AS nr
	FROM Stats.Scores
)
SELECT testid, MIN(score) AS percentiledisc
FROM C
WHERE 1.0 * np / nr >= @pct
GROUP BY testid


DECLARE @pct AS FLOAT = 0.5;
WITH C1 AS
(
	SELECT testid, score,
		ROW_NUMBER() OVER(PARTITION BY testid ORDER BY score) - 1 AS rownum,
		@pct * (COUNT(*) OVER(PARTITION BY testid)- 1) AS a
	FROM Stats.Scores
),
C2 AS
(
	SELECT testid, score, a-FLOOR(a) AS factor
	FROM C1
	WHERE rownum IN (FLOOR(a), CEILING(a))
)
SELECT testid, MIN(score) + factor * (MAX(score) - MIN(score)) AS percentilecont
FROM C2
GROUP BY testid, factor