By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t(data DECIMAL(4,2), weights DECIMAL(4,2));
INSERT INTO t(data, weights) VALUES
(1, 0.25)
,(1.5, 0.55)
,(2, 0.65)
,(2.5, 0.34)
,(3, 0.15)
,(3.5, 0.10)
,(4, 0.05)
,(4.5, 0.75)
,(5, 0.40);
9 rows affected
SELECT * FROM t;
| data | weights |
|---|---|
| 1.00 | 0.25 |
| 1.50 | 0.55 |
| 2.00 | 0.65 |
| 2.50 | 0.34 |
| 3.00 | 0.15 |
| 3.50 | 0.10 |
| 4.00 | 0.05 |
| 4.50 | 0.75 |
| 5.00 | 0.40 |
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY data) OVER()
,PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY data) OVER()
FROM t
| (No column name) | (No column name) |
|---|---|
| 3.00 | 3.00 |
| 3.00 | 3.00 |
| 3.00 | 3.00 |
| 3.00 | 3.00 |
| 3.00 | 3.00 |
| 3.00 | 3.00 |
| 3.00 | 3.00 |
| 3.00 | 3.00 |
| 3.00 | 3.00 |
SELECT
(
(SELECT MAX(data) FROM
(SELECT TOP 50 PERCENT data FROM t ORDER BY data) AS BottomHalf)
+
(SELECT MIN(data) FROM
(SELECT TOP 50 PERCENT data FROM t ORDER BY data DESC) AS TopHalf)
) / 2 AS Median
| Median |
|---|
| 3.000000 |
WITH tally_table AS (
SELECT ROW_NUMBER() OVER(ORDER BY s) AS c
FROM
(select 0 AS s union all select 1 union all select 2 union all
select 3 union all select 4 union all select 5 union all
select 6 union all select 7 union all select 8 union all select 9) t
CROSS JOIN
(select 0 AS b union all select 1 union all select 2 union all
select 3 union all select 4 union all select 5 union all
select 6 union all select 7 union all select 8 union all select 9) t2
), weight_as_int AS (
SELECT data,CAST(weights * 100 AS INT) AS weights
FROM t
), multipied_values AS (
SELECT data, weights
FROM weight_as_int t
JOIN tally_table
ON t.weights <= tally_table.c
)
SELECT DISTINCT 0.01 * 100 AS percentile,
PERCENTILE_DISC(0.01) WITHIN GROUP (ORDER BY data)OVER () AS weightedPercentile
FROM multipied_values
UNION ALL
SELECT DISTINCT 0.02 * 100 AS percentile,
PERCENTILE_DISC(0.02) WITHIN GROUP (ORDER BY data)OVER () AS weightedPercentile
FROM multipied_values
UNION ALL
SELECT DISTINCT 0.03 * 100 AS percentile,
PERCENTILE_DISC(0.03) WITHIN GROUP (ORDER BY data)OVER () AS weightedPercentile
FROM multipied_values
UNION ALL
SELECT DISTINCT 0.04 * 100 AS percentile,
PERCENTILE_DISC(0.04) WITHIN GROUP (ORDER BY data)OVER () AS weightedPercentile
FROM multipied_values
UNION ALL
SELECT DISTINCT 0.49 * 100 AS percentile,
| percentile | weightedPercentile |
|---|---|
| 1.00 | 1.00 |
| 2.00 | 1.00 |
| 3.00 | 1.00 |
| 4.00 | 1.00 |
| 49.00 | 3.00 |
| 50.00 | 3.00 |
| 51.00 | 3.00 |
| 98.00 | 5.00 |
| 99.00 | 5.00 |