How to ANOVA using MySQL

mysql sql anova

海綿寶寶 2019-06-20 11:16:15 ‧ 1701 瀏覽

分享至

請教
如何在 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)
;

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

一級屠豬士

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)

回應 6
分享
檢舉

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

海綿寶寶 iT邦大神 1 級 ‧ 2019-06-20 11:59:59 檢舉

謝謝，大師出手一定是OK的

只是我得用 PHP 去執行這段 SQL
不知道 PHP 可不可以建立 temptable

海綿寶寶 iT邦大神 1 級 ‧ 2019-06-20 12:03:04 檢舉

還有個 trouble
我SELECT version();看看現用的MySQL版本
結果答案是
5.7.25-log

一級屠豬士 iT邦大師 1 級 ‧ 2019-06-20 12:13:29 檢舉

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

一級屠豬士 iT邦大師 1 級 ‧ 2019-06-20 12:17:18 檢舉

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

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

海綿寶寶 iT邦大神 1 級 ‧ 2019-06-20 12:57:20 檢舉

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

我也是5.7

登入發表回應

ckp6250

iT邦好手 1 級 ‧ 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