mysql怎么查中位数,mysql获取中位数
用MySQL计算中位数问题,怎么解决
中位数:也就是选取中间的数。一种衡量集中趋势的方法。
站在用户的角度思考问题,与客户深入沟通,找到阜新网站设计与阜新网站推广的解决方案,凭借多年的经验,让设计与互联网技术结合,创造个性化、用户体验好的作品,建站类型包括:网站设计、成都网站建设、企业官网、英文网站、手机端网站、网站推广、空间域名、虚拟主机、企业邮箱。业务覆盖阜新地区。
要找中位数,首先需要从小到大排序,例如这组数据:23、29、20、32、23、21、33、25;
我们将数据排序20、21、23、23、25、29、32、33;排序后发现有8个数怎么办?
若有n个数,n为奇数,则选择第(n+1)/2个为中位数,若n为偶数,则中位数是(n/2以及n+1/2)的平均数
此例中选择24为中位数
在MySQL中,如何同时计算多组数据的中位数
有点复杂,在你基础上加了条有奇数的数据
创建表,插入数据:
create table test
(cat_id int,
price int);
insert into test values (101,90);
insert into test values (101,99);
insert into test values (102,98);
insert into test values (103,96);
insert into test values (102,95);
insert into test values (102,94);
insert into test values (102,93);
insert into test values (103,99);
insert into test values (103,98);
insert into test values (103,97);
insert into test values (104,96);
insert into test values (104,95);
insert into test values (105,97);
insert into test values (105,96);
insert into test values (105,95);
执行:
SELECT
t1.cat_id,
round(avg(t1.price), 1) price
FROM
(
SELECT
*
FROM
(
SELECT
t.cat_id,
t.price,
count(*) AS rank
FROM
test t
LEFT OUTER JOIN test r ON t.cat_id = r.cat_id
AND t.price = r.price
GROUP BY
t.cat_id,
t.price
ORDER BY
t.cat_id,
t.price DESC
) s
) t1,
(
SELECT DISTINCT
a.cat_id,
round(a.maxrank / 2) rank
FROM
(
SELECT
cat_id,
max(rank) maxrank,
MOD (max(rank), 2) modrank
FROM
(
SELECT
*
FROM
(
SELECT
t.cat_id,
t.price,
count(*) AS rank
FROM
test t
LEFT OUTER JOIN test r ON t.cat_id = r.cat_id
AND t.price = r.price
GROUP BY
t.cat_id,
t.price
ORDER BY
t.cat_id,
t.price DESC
) s
) t1
GROUP BY
cat_id
) a,
(
SELECT
*
FROM
(
SELECT
t.cat_id,
t.price,
count(*) AS rank
FROM
test t
LEFT OUTER JOIN test r ON t.cat_id = r.cat_id
AND t.price = r.price
GROUP BY
t.cat_id,
t.price
ORDER BY
t.cat_id,
t.price DESC
) s
) b
WHERE
a.cat_id = b.cat_id
AND a.modrank = 0
UNION ALL
SELECT DISTINCT
a.cat_id,
round(a.maxrank / 2) + 1 rank
FROM
(
SELECT
cat_id,
max(rank) maxrank,
MOD (max(rank), 2) modrank
FROM
(
SELECT
*
FROM
(
SELECT
t.cat_id,
t.price,
count(*) AS rank
FROM
test t
LEFT OUTER JOIN test r ON t.cat_id = r.cat_id
AND t.price = r.price
GROUP BY
t.cat_id,
t.price
ORDER BY
t.cat_id,
t.price DESC
) s
) t1
GROUP BY
cat_id
) a,
(
SELECT
*
FROM
(
SELECT
t.cat_id,
t.price,
count(*) AS rank
FROM
test t
LEFT OUTER JOIN test r ON t.cat_id = r.cat_id
AND t.price = r.price
GROUP BY
t.cat_id,
t.price
ORDER BY
t.cat_id,
t.price DESC
) s
) b
WHERE
a.cat_id = b.cat_id
AND a.modrank = 0
UNION ALL
SELECT DISTINCT
a.cat_id,
round(a.maxrank / 2) rank
FROM
(
SELECT
cat_id,
max(rank) maxrank,
MOD (max(rank), 2) modrank
FROM
(
SELECT
*
FROM
(
SELECT
t.cat_id,
t.price,
count(*) AS rank
FROM
test t
LEFT OUTER JOIN test r ON t.cat_id = r.cat_id
AND t.price = r.price
GROUP BY
t.cat_id,
t.price
ORDER BY
t.cat_id,
t.price DESC
) s
) t1
GROUP BY
cat_id
) a,
(
SELECT
*
FROM
(
SELECT
t.cat_id,
t.price,
count(*) AS rank
FROM
test t
LEFT OUTER JOIN test r ON t.cat_id = r.cat_id
AND t.price = r.price
GROUP BY
t.cat_id,
t.price
ORDER BY
t.cat_id,
t.price DESC
) s
) b
WHERE
a.cat_id = b.cat_id
AND a.modrank = 1
) t2
WHERE
t1.cat_id = t2.cat_id
AND t1.rank = t2.rank
GROUP BY
t1.cat_id
结果:
其中:
select * from (
select t.cat_id,t.price,count(*) as rank from test t
LEFT OUTER JOIN test r
on t.cat_id = r.cat_id
and t.price=r.price
group by t.cat_id,t.price
order by t.cat_id, t.price desc
) s
这条是主语句,主要是按照大小给出一个排名,然后根据中位数的公式,偶数的话,取最中间两个的平均数,奇数取最中间的数。自己研究一下吧。
如何使用简单的SQL查询在MySQL中计算中位数
第五十九回 许褚裸衣斗马超 曹操抹书间韩遂第六十回 张永年反难杨修 庞士元议取西蜀
网页名称:mysql怎么查中位数,mysql获取中位数
地址分享:http://scyanting.com/article/phioph.html