sql中位数
① oracle数据库中中位数用sql的求法。菜鸟求高手解答
--1
select median (sales) from total_sales
--2
select avg(sales)
from (select row_number() over(order by sales) as rn, name, sales from total_sales) t,
(select count(*) / 2 as mid_c from total_sales) b
where rn = b.mid_c
or rn = trunc (b.mid_c+1)
② SQL语句分组求中位数。数据和所需结果如图。
select ppid,ycljh,avg(jp) as jp from table
group by ppid,ycljh
③ sql怎么计算中位数
众数：count()行数后取max值
平均数：avg()
中位数：(max()+min())/2
④ SQL 2008中位数怎么求
declare @str varchar(100)
set @str='1，2，3，4，5，6，7，8，9，0'
select len(@str)
⑤ 在MySQL中，如何同时计算多组数据的中位数
有点复杂，在你基础上加了条有奇数的数据
创建表，插入数据：
createtabletest
(cat_idint,
priceint);
insertintotestvalues(101,90);
insertintotestvalues(101,99);
insertintotestvalues(102,98);
insertintotestvalues(103,96);
insertintotestvalues(102,95);
insertintotestvalues(102,94);
insertintotestvalues(102,93);
insertintotestvalues(103,99);
insertintotestvalues(103,98);
insertintotestvalues(103,97);
insertintotestvalues(104,96);
insertintotestvalues(104,95);
insertintotestvalues(105,97);
insertintotestvalues(105,96);
insertintotestvalues(105,95);
执行：
SELECT
t1.cat_id,
round(avg(t1.price),1)price
FROM
(
SELECT
*
FROM
(
SELECT
t.cat_id,
t.price,
count(*)ASrank
FROM
testt
LEFTOUTERJOINtestrONt.cat_id=r.cat_id
ANDt.price<=r.price
GROUPBY
t.cat_id,
t.price
ORDERBY
t.cat_id,
t.priceDESC
)s
)t1,
(
SELECTDISTINCT
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(*)ASrank
FROM
testt
LEFTOUTERJOINtestrONt.cat_id=r.cat_id
ANDt.price<=r.price
GROUPBY
t.cat_id,
t.price
ORDERBY
t.cat_id,
t.priceDESC
)s
)t1
GROUPBY
cat_id
)a,
(
SELECT
*
FROM
(
SELECT
t.cat_id,
t.price,
count(*)ASrank
FROM
testt
LEFTOUTERJOINtestrONt.cat_id=r.cat_id
ANDt.price<=r.price
GROUPBY
t.cat_id,
t.price
ORDERBY
t.cat_id,
t.priceDESC
)s
)b
WHERE
a.cat_id=b.cat_id
ANDa.modrank=0
UNIONALL
SELECTDISTINCT
a.cat_id,
round(a.maxrank/2)+1rank
FROM
(
SELECT
cat_id,
max(rank)maxrank,
MOD(max(rank),2)modrank
FROM
(
SELECT
*
FROM
(
SELECT
t.cat_id,
t.price,
count(*)ASrank
FROM
testt
LEFTOUTERJOINtestrONt.cat_id=r.cat_id
ANDt.price<=r.price
GROUPBY
t.cat_id,
t.price
ORDERBY
t.cat_id,
t.priceDESC
)s
)t1
GROUPBY
cat_id
)a,
(
SELECT
*
FROM
(
SELECT
t.cat_id,
t.price,
count(*)ASrank
FROM
testt
LEFTOUTERJOINtestrONt.cat_id=r.cat_id
ANDt.price<=r.price
GROUPBY
t.cat_id,
t.price
ORDERBY
t.cat_id,
t.priceDESC
)s
)b
WHERE
a.cat_id=b.cat_id
ANDa.modrank=0
UNIONALL
SELECTDISTINCT
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(*)ASrank
FROM
testt
LEFTOUTERJOINtestrONt.cat_id=r.cat_id
ANDt.price<=r.price
GROUPBY
t.cat_id,
t.price
ORDERBY
t.cat_id,
t.priceDESC
)s
)t1
GROUPBY
cat_id
)a,
(
SELECT
*
FROM
(
SELECT
t.cat_id,
t.price,
count(*)ASrank
FROM
testt
LEFTOUTERJOINtestrONt.cat_id=r.cat_id
ANDt.price<=r.price
GROUPBY
t.cat_id,
t.price
ORDERBY
t.cat_id,
t.priceDESC
)s
)b
WHERE
a.cat_id=b.cat_id
ANDa.modrank=1
)t2
WHERE
t1.cat_id=t2.cat_id
ANDt1.rank=t2.rank
GROUPBY
t1.cat_id
结果：
select*from(
selectt.cat_id,t.price,count(*)asrankfromtestt
LEFTOUTERJOINtestr
ont.cat_id=r.cat_id
andt.price<=r.price
groupbyt.cat_id,t.price
orderbyt.cat_id,t.pricedesc
)s
这条是主语句，主要是按照大小给出一个排名，然后根据中位数的公式，偶数的话，取最中间两个的平均数，奇数取最中间的数。自己研究一下吧。
⑥ SQL + ORACLE + 中位数 在线等
create proc test1
as
declare @a int
select @a=count(*) from m
if @a%2=1
begin
select xm from (select xm,zkzh,count(*) k from(select i.xm,i.zkzh from m i ,m where i.xm>=m.xm)as l group by xm,zkzh )as q where k=((@a+1)/2)
end
if @a%2=0
begin
select avg(xm) xm from (select xm,zkzh,count(*) k from(select i.xm,i.zkzh from m i ,m where i.xm>=m.xm)as l group by xm,zkzh )as q where k in(@a/2,(@a+2)/2)
end
go
haiyoudian wenti.....