iT邦幫忙

0

How to ANOVA using MySQL

請教
如何在 MySQL 中
將一組數字由大到小排序之後
分成10組,並求出各組的「平均值」
謝謝
(結果應該類似行政院主計處5等分位組之平均)
SQL Fiddle

CREATE TABLE tableS (`Salary` int);
    
INSERT INTO tableS (`Salary`) VALUES
    (67322),
    (5612),
    (23678),
    (94687),
    (11945),
    (6153),
    (57842),
    (91660),
    (96368),
    (27579),
    (71503),
    (60411),
    (60346),
    (41173),
    (88388),
    (24596),
    (75496),
    (25501),
    (48201),
    (70783),
    (68766),
    (95185),
    (25359),
    (43906),
    (18076),
    (668),
    (22503),
    (14986),
    (80919),
    (83624),
    (13254),
    (24132),
    (13005),
    (95837),
    (65813),
    (36425),
    (38642),
    (39496),
    (45553),
    (56835),
    (25620),
    (90242),
    (11498),
    (61870),
    (86328),
    (40939),
    (61451),
    (2765),
    (62186),
    (16746),
    (36844),
    (69861),
    (64139),
    (68237),
    (88853),
    (65373),
    (6551),
    (83666),
    (91468),
    (67808),
    (36639),
    (85002),
    (45626),
    (35400),
    (20990),
    (35918),
    (17922),
    (37017),
    (47520),
    (49336),
    (67002),
    (53325),
    (90218),
    (51121),
    (96653),
    (80949),
    (71545),
    (58449),
    (13329),
    (11922),
    (51731),
    (66977),
    (66589),
    (68381),
    (63102),
    (13790),
    (57568),
    (7811),
    (72755),
    (63242),
    (72880),
    (49969),
    (11891),
    (1902),
    (11296),
    (62430),
    (7935)
;

2 個回答

6
一級屠豬士
iT邦大師 1 級 ‧ 2019-06-20 11:46:10
最佳解答
-- 遵照指示使用 MySQL
select version();
+-----------+
| version() |
+-----------+
| 8.0.15    |
+-----------+

-- 不知這樣是否符合大大的需要.

CREATE TABLE ithelp190620 (
  id int unsigned not null auto_increment primary key
, salary int not null
);

INSERT INTO ithelp190620 (Salary) VALUES
(67322),(5612),(23678),(94687),(11945),
(6153),(57842),(91660),(96368),(27579),
(71503),(60411),(60346),(41173),(88388),
(24596),(75496),(25501),(48201),(70783),
(68766),(95185),(25359),(43906),(18076),
(668),(22503),(14986),(80919),(83624),
(13254),(24132),(13005),(95837),(65813),
(36425),(38642),(39496),(45553),(56835),
(25620),(90242),(11498),(61870),(86328),
(40939),(61451),(2765),(62186),(16746),
(36844),(69861),(64139),(68237),(88853),
(65373),(6551),(83666),(91468),(67808),
(36639),(85002),(45626),(35400),(20990),
(35918),(17922),(37017),(47520),(49336),
(67002),(53325),(90218),(51121),(96653),
(80949),(71545),(58449),(13329),(11922),
(51731),(66977),(66589),(68381),(63102),
(13790),(57568),(7811),(72755),(63242),
(72880),(49969),(11891),(1902),(11296),
(62430),(7935);

--

with t1 as (
select a.id, a.salary
     , ntile(10) over (order by a.rnk) as grp
  from (select id, salary
             , rank() over (order by salary desc) as rnk
          from ithelp190620) a
)
select distinct grp
     , avg(salary) over (partition by grp)
  from t1;

+------+-------------------------------------+
| grp  | avg(salary) over (partition by grp) |
+------+-------------------------------------+
|    1 |                          93117.1000 |
|    2 |                          81000.7000 |
|    3 |                          69120.8000 |
|    4 |                          64172.1000 |
|    5 |                          56907.9000 |
|    6 |                          45171.9000 |
|    7 |                          33558.5000 |
|    8 |                          21555.7778 |
|    9 |                          12846.6667 |
|   10 |                           5632.5556 |
+------+-------------------------------------+
10 rows in set (0.01 sec)

看更多先前的回應...收起先前的回應...

謝謝,大師出手一定是OK的
/images/emoticon/emoticon12.gif
只是我得用 PHP 去執行這段 SQL
不知道 PHP 可不可以建立 temptable
/images/emoticon/emoticon06.gif

還有個 trouble
SELECT version();看看現用的MySQL版本
結果答案是
5.7.25-log
/images/emoticon/emoticon05.gif

MySQL 5.7 沒有 Window Functions...
起碼要到8才有.

https://ithelp.ithome.com.tw/articles/10139659

MySQL 分組排名的方法,小弟以前寫的,你參考一下.
接著再用餘數方式分割,然後再平均....不過會比較繁瑣.

/images/emoticon/emoticon41.gif

小魚 iT邦大師 1 級 ‧ 2019-06-20 14:01:50 檢舉

我也是5.7

0
ckp6250
iT邦研究生 5 級 ‧ 2019-09-02 17:58:10

拾人牙慧啦

select 
	Groups AS `組別`,
	round(AVG(Salary),0) AS `平均值`
FROM (
	SELECT
		ntile(10) over (ORDER BY Salary) AS Groups,
		Salary
	FROM
		`tableS`) a
GROUP BY 1

我要發表回答

立即登入回答