Wat is de eenvoudigste (en hopelijk niet te langzame) manier om de mediaan te berekenen met MySQL? Ik heb AVG(x)gebruikt om het gemiddelde te vinden, maar ik vind het moeilijk om een eenvoudige manier te vinden om de mediaan te berekenen. Voor nu breng ik alle rijen terug naar PHP, doe ik een sortering en kies dan de middelste rij, maar er moet zeker een eenvoudige manier zijn om dit in een enkele MySQL-query te doen.
Voorbeeldgegevens:
id | val
--------
1 4
2 7
3 2
4 2
5 9
6 8
7 3
Sorteren op valgeeft 2 2 3 4 7 8 9, dus de mediaan moet 4zijn, versus SELECT AVG(val)die == 5.
Antwoord 1, autoriteit 100%
In MariaDB / MySQL:
SELECT AVG(dd.val) as median_val
FROM (
SELECT d.val, @rownum:=@rownum+1 as `row_number`, @total_rows:=@rownum
FROM data d, (SELECT @rownum:=0) r
WHERE d.val is NOT NULL
-- put some where clause here
ORDER BY d.val
) as dd
WHERE dd.row_number IN ( FLOOR((@total_rows+1)/2), FLOOR((@total_rows+2)/2) );
Steve Cohenwijst erop dat na de eerste pas @rownum het totale aantal rijen zal bevatten. Dit kan worden gebruikt om de mediaan te bepalen, dus er is geen tweede pass of join nodig.
Ook AVG(dd.val)en dd.row_number IN(...)worden gebruikt om correct een mediaan te produceren wanneer er een even aantal records is. Redenering:
SELECT FLOOR((3+1)/2),FLOOR((3+2)/2); -- when total_rows is 3, avg rows 2 and 2
SELECT FLOOR((4+1)/2),FLOOR((4+2)/2); -- when total_rows is 4, avg rows 2 and 3
Ten slotte bevat MariaDB 10.3.3+ een MEDIAN-functie
Antwoord 2, autoriteit 28%
Ik heb zojuist een ander antwoord online gevonden in de opmerkingen:
Voor medianen in bijna elke SQL:
SELECT x.val from data x, data y GROUP BY x.val HAVING SUM(SIGN(1-SIGN(y.val-x.val))) = (COUNT(*)+1)/2
Zorg ervoor dat uw kolommen goed zijn geïndexeerd en dat de index wordt gebruikt voor filteren en sorteren. Verifieer met de uit te leggen plannen.
select count(*) from table --find the number of rows
Bereken het “mediaan” rijnummer. Gebruik misschien: median_row = floor(count / 2).
Kies het dan uit de lijst:
select val from table order by val asc limit median_row,1
Dit zou u één rij moeten opleveren met alleen de gewenste waarde.
Jacob
Antwoord 3, autoriteit 15%
Ik ontdekte dat de geaccepteerde oplossing niet werkte op mijn MySQL-installatie, waarbij een lege set werd geretourneerd, maar deze query werkte voor mij in alle situaties waarop ik hem heb getest:
SELECT x.val from data x, data y
GROUP BY x.val
HAVING SUM(SIGN(1-SIGN(y.val-x.val)))/COUNT(*) > .5
LIMIT 1
Antwoord 4, autoriteit 11%
Helaas geven noch de antwoorden van TheJacobTaylor noch Velcrow nauwkeurige resultaten voor de huidige versies van MySQL.
Het antwoord van Velcro van bovenaf is dichtbij, maar het berekent niet correct voor resultatensets met een even aantal rijen. Medianen worden gedefinieerd als 1) het middelste getal in sets met oneven nummers, of 2) het gemiddelde van de twee middelste getallen bij sets met even nummers.
Dus, hier is de oplossing van klittenband die is gepatcht om zowel oneven als even getallenreeksen te verwerken:
SELECT AVG(middle_values) AS 'median' FROM (
SELECT t1.median_column AS 'middle_values' FROM
(
SELECT @row:=@row+1 as `row`, x.median_column
FROM median_table AS x, (SELECT @row:=0) AS r
WHERE 1
-- put some where clause here
ORDER BY x.median_column
) AS t1,
(
SELECT COUNT(*) as 'count'
FROM median_table x
WHERE 1
-- put same where clause here
) AS t2
-- the following condition will return 1 record for odd number sets, or 2 records for even number sets.
WHERE t1.row >= t2.count/2 and t1.row <= ((t2.count/2) +1)) AS t3;
Volg deze 3 eenvoudige stappen om dit te gebruiken:
- Vervang “median_table” (2 keer) in de bovenstaande code door de naam van uw tabel
- Vervang “mediaan_column” (3 keer) door de kolomnaam waarvoor u een mediaan wilt vinden
- Als u een WHERE-voorwaarde heeft, vervangt u “WHERE 1” (2 keer) door uw waar-voorwaarde
Antwoord 5, autoriteit 5%
Ik stel een snellere manier voor.
Het aantal rijen ophalen:
SELECT CEIL(COUNT(*)/2) FROM data;
Neem dan de middelste waarde in een gesorteerde subquery:
SELECT max(val) FROM (SELECT val FROM data ORDER BY val limit @middlevalue) x;
Ik heb dit getest met een 5x10e6-dataset van willekeurige getallen en het zal de mediaan in minder dan 10 seconden vinden.
Antwoord 6, autoriteit 4%
Installeer en gebruik deze statistische mysql-functies: http://www .xarg.org/2012/07/statistical-functions-in-mysql/
Daarna is het berekenen van de mediaan eenvoudig:
SELECT median(val) FROM data;
Antwoord 7, autoriteit 3%
Een opmerking over deze pagina in de MySQL-documentatieheeft de volgende suggestie:
-- (mostly) High Performance scaling MEDIAN function per group
-- Median defined in http://en.wikipedia.org/wiki/Median
--
-- by Peter Hlavac
-- 06.11.2008
--
-- Example Table:
DROP table if exists table_median;
CREATE TABLE table_median (id INTEGER(11),val INTEGER(11));
COMMIT;
INSERT INTO table_median (id, val) VALUES
(1, 7), (1, 4), (1, 5), (1, 1), (1, 8), (1, 3), (1, 6),
(2, 4),
(3, 5), (3, 2),
(4, 5), (4, 12), (4, 1), (4, 7);
-- Calculating the MEDIAN
SELECT @a := 0;
SELECT
id,
AVG(val) AS MEDIAN
FROM (
SELECT
id,
val
FROM (
SELECT
-- Create an index n for every id
@a := (@a + 1) mod o.c AS shifted_n,
IF(@a mod o.c=0, o.c, @a) AS n,
o.id,
o.val,
-- the number of elements for every id
o.c
FROM (
SELECT
t_o.id,
val,
c
FROM
table_median t_o INNER JOIN
(SELECT
id,
COUNT(1) AS c
FROM
table_median
GROUP BY
id
) t2
ON (t2.id = t_o.id)
ORDER BY
t_o.id,val
) o
) a
WHERE
IF(
-- if there is an even number of elements
-- take the lower and the upper median
-- and use AVG(lower,upper)
c MOD 2 = 0,
n = c DIV 2 OR n = (c DIV 2)+1,
-- if its an odd number of elements
-- take the first if its only one element
-- or take the one in the middle
IF(
c = 1,
n = 1,
n = c DIV 2 + 1
)
)
) a
GROUP BY
id;
-- Explanation:
-- The Statement creates a helper table like
--
-- n id val count
-- ----------------
-- 1, 1, 1, 7
-- 2, 1, 3, 7
-- 3, 1, 4, 7
-- 4, 1, 5, 7
-- 5, 1, 6, 7
-- 6, 1, 7, 7
-- 7, 1, 8, 7
--
-- 1, 2, 4, 1
-- 1, 3, 2, 2
-- 2, 3, 5, 2
--
-- 1, 4, 1, 4
-- 2, 4, 5, 4
-- 3, 4, 7, 4
-- 4, 4, 12, 4
-- from there we can select the n-th element on the position: count div 2 + 1
Antwoord 8, autoriteit 2%
De meeste van de bovenstaande oplossingen werken slechts voor één veld van de tabel. Mogelijk moet u de mediaan (50e percentiel) voor veel velden in de query ophalen.
Ik gebruik dit:
SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(
GROUP_CONCAT(field_name ORDER BY field_name SEPARATOR ','),
',', 50/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS `Median`
FROM table_name;
Je kunt de “50” in het bovenstaande voorbeeld vervangen door elk percentiel, dat is erg efficiënt.
Zorg er wel voor dat je genoeg geheugen hebt voor de GROUP_CONCAT, je kunt dit wijzigen met:
SET group_concat_max_len = 10485760; #10MB max length
Meer details: http://web.performancerasta.com/metrics-tips-calculating-95th-99th-or-any-percentile-with-single-mysql-query/
Antwoord 9, autoriteit 2%
Ik heb deze onderstaande code die ik op HackerRank heb gevonden en het is vrij eenvoudig en werkt in elk geval.
SELECT M.MEDIAN_COL FROM MEDIAN_TABLE M WHERE
(SELECT COUNT(MEDIAN_COL) FROM MEDIAN_TABLE WHERE MEDIAN_COL < M.MEDIAN_COL ) =
(SELECT COUNT(MEDIAN_COL) FROM MEDIAN_TABLE WHERE MEDIAN_COL > M.MEDIAN_COL );
Antwoord 10, autoriteit 2%
U kunt de door de gebruiker gedefinieerde functie gebruiken die hiervindt.
Antwoord 11, autoriteit 2%
Voortbouwend op het antwoord van klittenband, voor degenen onder u die een mediaan moeten maken van iets dat is gegroepeerd op een andere parameter:
SELECTEER grp_field, t1.val FROM (
SELECT grp_field, @rownum:=IF(@s = grp_field, @rownum + 1, 0) AS ROW_NUMBER,
@s:=IF(@s = grp_field, @s, grp_field) AS sec, d.val
VAN gegevens d, (SELECT @rownum:=0, @s:=0) r
BESTELLEN OP grp_field, d.val
) als t1 JOIN (
SELECT grp_field, count(*) as total_rows
VAN gegevens d
GROEP OP grp_field
) als t2
AAN t1.grp_field = t2.grp_field
WAAR t1.row_number=verdieping (total_rows/2)+1;
Antwoord 12
Zorgt voor een oneven waardetelling – geeft in dat geval het gemiddelde van de twee waarden in het midden.
SELECT AVG(val) FROM
( SELECT x.id, x.val from data x, data y
GROUP BY x.id, x.val
HAVING SUM(SIGN(1-SIGN(IF(y.val-x.val=0 AND x.id != y.id, SIGN(x.id-y.id), y.val-x.val)))) IN (ROUND((COUNT(*))/2), ROUND((COUNT(*)+1)/2))
) sq
Antwoord 13
Als MySQL ROW_NUMBER heeft, dan is de MEDIAN (laat u inspireren door deze SQL Server-query):
WITH Numbered AS
(
SELECT *, COUNT(*) OVER () AS Cnt,
ROW_NUMBER() OVER (ORDER BY val) AS RowNum
FROM yourtable
)
SELECT id, val
FROM Numbered
WHERE RowNum IN ((Cnt+1)/2, (Cnt+2)/2)
;
De IN wordt gebruikt als u een even aantal invoeren heeft.
Als je de mediaan per groep wilt vinden, gebruik dan gewoon PARTITION BY group in je OVER-clausules.
Rob
Antwoord 14
Mijn code, efficiënt zonder tabellen of extra variabelen:
SELECT
((SUBSTRING_INDEX(SUBSTRING_INDEX(group_concat(val order by val), ',', floor(1+((count(val)-1) / 2))), ',', -1))
+
(SUBSTRING_INDEX(SUBSTRING_INDEX(group_concat(val order by val), ',', ceiling(1+((count(val)-1) / 2))), ',', -1)))/2
as median
FROM table;
Antwoord 15
Optioneel kunt u dit ook doen in een opgeslagen procedure:
DROP PROCEDURE IF EXISTS median;
DELIMITER //
CREATE PROCEDURE median (table_name VARCHAR(255), column_name VARCHAR(255), where_clause VARCHAR(255))
BEGIN
-- Set default parameters
IF where_clause IS NULL OR where_clause = '' THEN
SET where_clause = 1;
END IF;
-- Prepare statement
SET @sql = CONCAT(
"SELECT AVG(middle_values) AS 'median' FROM (
SELECT t1.", column_name, " AS 'middle_values' FROM
(
SELECT @row:=@row+1 as `row`, x.", column_name, "
FROM ", table_name," AS x, (SELECT @row:=0) AS r
WHERE ", where_clause, " ORDER BY x.", column_name, "
) AS t1,
(
SELECT COUNT(*) as 'count'
FROM ", table_name, " x
WHERE ", where_clause, "
) AS t2
-- the following condition will return 1 record for odd number sets, or 2 records for even number sets.
WHERE t1.row >= t2.count/2
AND t1.row <= ((t2.count/2)+1)) AS t3
");
-- Execute statement
PREPARE stmt FROM @sql;
EXECUTE stmt;
END//
DELIMITER ;
-- Sample usage:
-- median(table_name, column_name, where_condition);
CALL median('products', 'price', NULL);
Antwoord 16
Mijn onderstaande oplossing werkt in slechts één query zonder dat er een tabel, variabele of zelfs subquery hoeft te worden gemaakt.
Bovendien kun je de mediaan voor elke groep krijgen in groepsgewijze zoekopdrachten (dit is wat ik nodig had!):
SELECT `columnA`,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(`columnB` ORDER BY `columnB`), ',', CEILING((COUNT(`columnB`)/2))), ',', -1) medianOfColumnB
FROM `tableC`
-- some where clause if you want
GROUP BY `columnA`;
Het werkt dankzij een slim gebruik van group_concat en substring_index.
Maar om big group_concat toe te staan, moet je group_concat_max_len op een hogere waarde zetten (standaard 1024 char).
Je kunt het zo instellen (voor huidige sql-sessie):
SET SESSION group_concat_max_len = 10000;
-- up to 4294967295 in 32-bits platform.
Meer informatie voor group_concat_max_len: https:/ /dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_group_concat_max_len
Antwoord 17
Nog een riff op het antwoord van Velcrow, maar gebruikt een enkele tussentabel en maakt gebruik van de variabele die wordt gebruikt voor rijnummering om de telling te krijgen, in plaats van een extra query uit te voeren om deze te berekenen. Start de telling ook zodat de eerste rij rij 0 is, zodat u eenvoudig Floor en Ceil kunt gebruiken om de mediaanrij(en) te selecteren.
SELECT Avg(tmp.val) as median_val
FROM (SELECT inTab.val, @rows := @rows + 1 as rowNum
FROM data as inTab, (SELECT @rows := -1) as init
-- Replace with better where clause or delete
WHERE 2 > 1
ORDER BY inTab.val) as tmp
WHERE tmp.rowNum in (Floor(@rows / 2), Ceil(@rows / 2));
Antwoord 18
SELECT
SUBSTRING_INDEX(
SUBSTRING_INDEX(
GROUP_CONCAT(field ORDER BY field),
',',
((
ROUND(
LENGTH(GROUP_CONCAT(field)) -
LENGTH(
REPLACE(
GROUP_CONCAT(field),
',',
''
)
)
) / 2) + 1
)),
',',
-1
)
FROM
table
Het bovenstaande lijkt voor mij te werken.
Antwoord 19
Eén zoekopdracht om de perfecte mediaan te archiveren:
SELECT
COUNT(*) as total_rows,
IF(count(*)%2 = 1, CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(val ORDER BY val SEPARATOR ','), ',', 50/100 * COUNT(*)), ',', -1) AS DECIMAL), ROUND((CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(val ORDER BY val SEPARATOR ','), ',', 50/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) + CAST(SUBSTRING_INDEX(SUBSTRING_INDEX( GROUP_CONCAT(val ORDER BY val SEPARATOR ','), ',', 50/100 * COUNT(*)), ',', -1) AS DECIMAL)) / 2)) as median,
AVG(val) as average
FROM
data
Antwoord 20
Ik heb een benadering met twee zoekopdrachten gebruikt:
- eerste die telling, min, max en avg krijgt
- tweede (voorbereide instructie) met een “LIMIT @count/2, 1” en “ORDER BY ..”-clausules om de mediaanwaarde te krijgen
Deze zijn verpakt in een functie defn, dus alle waarden kunnen worden geretourneerd vanuit één aanroep.
Als uw reeksen statisch zijn en uw gegevens niet vaak veranderen, kan het efficiënter zijn om deze waarden te verbeteren / op te slaan en de opgeslagen waarden te gebruiken in plaats van telkens opnieuw te vragen.
Antwoord 21
Zoals ik net een mediane en percentieloplossing nodig had, heb ik een eenvoudige en vrij flexibele functie gemaakt op basis van de bevindingen in deze thread. Ik weet dat ik me gelukkig ben als ik ‘Readymade’ -functies vind die gemakkelijk in mijn projecten zijn, dus ik besloot snel te delen:
function mysql_percentile($table, $column, $where, $percentile = 0.5) {
$sql = "
SELECT `t1`.`".$column."` as `percentile` FROM (
SELECT @rownum:=@rownum+1 as `row_number`, `d`.`".$column."`
FROM `".$table."` `d`, (SELECT @rownum:=0) `r`
".$where."
ORDER BY `d`.`".$column."`
) as `t1`,
(
SELECT count(*) as `total_rows`
FROM `".$table."` `d`
".$where."
) as `t2`
WHERE 1
AND `t1`.`row_number`=floor(`total_rows` * ".$percentile.")+1;
";
$result = sql($sql, 1);
if (!empty($result)) {
return $result['percentile'];
} else {
return 0;
}
}
Gebruiksgebruik is heel eenvoudig, bijvoorbeeld uit mijn huidige project:
...
$table = DBPRE."zip_".$slug;
$column = 'seconds';
$where = "WHERE `reached` = '1' AND `time` >= '".$start_time."'";
$reaching['median'] = mysql_percentile($table, $column, $where, 0.5);
$reaching['percentile25'] = mysql_percentile($table, $column, $where, 0.25);
$reaching['percentile75'] = mysql_percentile($table, $column, $where, 0.75);
...
Antwoord 22
Hier is mijn manier. Je zou het natuurlijk in een procedure kunnen zetten 🙂
SET @median_counter = (SELECT FLOOR(COUNT(*)/2) - 1 AS `median_counter` FROM `data`);
SET @median = CONCAT('SELECT `val` FROM `data` ORDER BY `val` LIMIT ', @median_counter, ', 1');
PREPARE median FROM @median;
EXECUTE median;
U kunt de variabele @median_countervermijden als u deze vervangt:
SET @median = CONCAT( 'SELECT `val` FROM `data` ORDER BY `val` LIMIT ',
(SELECT FLOOR(COUNT(*)/2) - 1 AS `median_counter` FROM `data`),
', 1'
);
PREPARE median FROM @median;
EXECUTE median;
Antwoord 23
Op deze manier lijkt zowel het even als het oneven aantal te bevatten zonder subquery.
SELECT AVG(t1.x)
FROM table t1, table t2
GROUP BY t1.x
HAVING SUM(SIGN(t1.x - t2.x)) = 0
Antwoord 24
Gebaseerd op het antwoord van @bob, generaliseert dit de zoekopdracht om de mogelijkheid te hebben om meerdere medianen te retourneren, gegroepeerd op enkele criteria.
Denk bijvoorbeeld aan de mediane verkoopprijs voor gebruikte auto’s in een autokavel, gegroepeerd per jaar-maand.
SELECT
period,
AVG(middle_values) AS 'median'
FROM (
SELECT t1.sale_price AS 'middle_values', t1.row_num, t1.period, t2.count
FROM (
SELECT
@last_period:=@period AS 'last_period',
@period:=DATE_FORMAT(sale_date, '%Y-%m') AS 'period',
IF (@period<>@last_period, @row:=1, @row:=@row+1) as `row_num`,
x.sale_price
FROM listings AS x, (SELECT @row:=0) AS r
WHERE 1
-- where criteria goes here
ORDER BY DATE_FORMAT(sale_date, '%Y%m'), x.sale_price
) AS t1
LEFT JOIN (
SELECT COUNT(*) as 'count', DATE_FORMAT(sale_date, '%Y-%m') AS 'period'
FROM listings x
WHERE 1
-- same where criteria goes here
GROUP BY DATE_FORMAT(sale_date, '%Y%m')
) AS t2
ON t1.period = t2.period
) AS t3
WHERE
row_num >= (count/2)
AND row_num <= ((count/2) + 1)
GROUP BY t3.period
ORDER BY t3.period;
Antwoord 25
Vaak moeten we de mediaan niet alleen voor de hele tabel berekenen, maar ook voor aggregaten met betrekking tot onze ID. Met andere woorden, bereken de mediaan voor elke ID in onze tabel, waarbij elke ID veel records heeft. (goede prestaties en werkt in veel SQL + lost het probleem van even en oneven op, meer over de prestaties van verschillende Median-methoden https://sqlperformance.com/2012/08/t-sql-queries/median)
SELECT our_id, AVG(1.0 * our_val) as Median
FROM
( SELECT our_id, our_val,
COUNT(*) OVER (PARTITION BY our_id) AS cnt,
ROW_NUMBER() OVER (PARTITION BY our_id ORDER BY our_val) AS rn
FROM our_table
) AS x
WHERE rn IN ((cnt + 1)/2, (cnt + 2)/2) GROUP BY our_id;
Hopelijk helpt het
Antwoord 26
MySQL ondersteunt vensterfuncties sinds versie 8.0, u kunt ROW_NUMBERof DENSE_RANKgebruiken (NIETgebruik RANKaangezien het dezelfde rangorde toekent aan dezelfde waarden, zoals in sportrangschikking):
SELECT AVG(t1.val) AS median_val
FROM (SELECT val,
ROW_NUMBER() OVER(ORDER BY val) AS rownum
FROM data) t1,
(SELECT COUNT(*) AS num_records FROM data) t2
WHERE t1.row_num IN
(FLOOR((t2.num_records + 1) / 2),
FLOOR((t2.num_records + 2) / 2));
Antwoord 27
Een eenvoudige manier om de mediaan te berekenen in MySQL
set @ct := (select count(1) from station);
set @row := 0;
select avg(a.val) as median from
(select * from table order by val) a
where (select @row := @row + 1)
between @ct/2.0 and @ct/2.0 +1;
Antwoord 28
Na het lezen van alle voorgaande kwamen ze niet overeen met mijn werkelijke vereiste, dus implementeerde ik mijn eigen die geen procedure of ingewikkelde verklaringen nodig heeft, alleen ik GROUP_CONCATalle waarden uit de kolom I wilde de MEDIAAN verkrijgen en een COUNT DIV BY 2 toepassen. Ik extraheer de waarde in het midden van de lijst zoals de volgende query doet:
(POS is de naam van de kolom waarvan ik de mediaan wil krijgen)
(query) SELECT
SUBSTRING_INDEX (
SUBSTRING_INDEX (
GROUP_CONCAT(pos ORDER BY CAST(pos AS SIGNED INTEGER) desc SEPARATOR ';')
, ';', COUNT(*)/2 )
, ';', -1 ) AS `pos_med`
FROM table_name
GROUP BY any_criterial
Ik hoop dat dit nuttig kan zijn voor iemand op de manier waarop veel andere opmerkingen voor mij van deze website waren.
Antwoord 29
Als u het exacte aantal rijen kent, kunt u deze zoekopdracht gebruiken:
SELECT <value> AS VAL FROM <table> ORDER BY VAL LIMIT 1 OFFSET <half>
Waar <half> = ceiling(<size> / 2.0) - 1
Antwoord 30
Ik heb een database met ongeveer 1 miljard rijen die we nodig hebben om de mediane leeftijd in de set te bepalen. Het sorteren van een miljard rijen is moeilijk, maar als je de verschillende waarden die kunnen worden gevonden (leeftijden variëren van 0 tot 100), samenvoegt, kun je DEZE lijst sorteren en wat rekenkundige magie gebruiken om elk gewenst percentiel als volgt te vinden:
with rawData(count_value) as
(
select p.YEAR_OF_BIRTH
from dbo.PERSON p
),
overallStats (avg_value, stdev_value, min_value, max_value, total) as
(
select avg(1.0 * count_value) as avg_value,
stdev(count_value) as stdev_value,
min(count_value) as min_value,
max(count_value) as max_value,
count(*) as total
from rawData
),
aggData (count_value, total, accumulated) as
(
select count_value,
count(*) as total,
SUM(count(*)) OVER (ORDER BY count_value ROWS UNBOUNDED PRECEDING) as accumulated
FROM rawData
group by count_value
)
select o.total as count_value,
o.min_value,
o.max_value,
o.avg_value,
o.stdev_value,
MIN(case when d.accumulated >= .50 * o.total then count_value else o.max_value end) as median_value,
MIN(case when d.accumulated >= .10 * o.total then count_value else o.max_value end) as p10_value,
MIN(case when d.accumulated >= .25 * o.total then count_value else o.max_value end) as p25_value,
MIN(case when d.accumulated >= .75 * o.total then count_value else o.max_value end) as p75_value,
MIN(case when d.accumulated >= .90 * o.total then count_value else o.max_value end) as p90_value
from aggData d
cross apply overallStats o
GROUP BY o.total, o.min_value, o.max_value, o.avg_value, o.stdev_value
;
Deze query is afhankelijk van uw DB-ondersteuningsvensterfuncties (inclusief rijen onbegrensd voorgaande), maar als u niet hebt dat het een eenvoudige zaak is om lid te worden van AGGDATA CTE met zichzelf en alle eerdere totalen in de ‘verzamelde’ kolom die wordt gebruikt, agggregate Om te bepalen welke waarde het opgegeven precentiel bevat. Het bovenstaande monster berekent P10, P25, P50 (mediaan), P75 en P90.
-CHRIS