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.....