Mysql 刷题笔记 0104 求出NAME中每组累加/每组总数的比例大于0.6的ID和NAME

-- 0104 求出NAME中每组累加/每组总数的比例大于0.6的ID和NAME

CREATE TABLE T0104
(
	ID INT,
	NAME VARCHAR(10),
	NUM INT
);

INSERT INTO T0104 VALUES (1,'A',1);
INSERT INTO T0104 VALUES (2,'A',2);
INSERT INTO T0104 VALUES (3,'A',6);
INSERT INTO T0104 VALUES (4,'A',4);
INSERT INTO T0104 VALUES (5,'A',3);
INSERT INTO T0104 VALUES (6,'B',2);
INSERT INTO T0104 VALUES (7,'B',8);
INSERT INTO T0104 VALUES (8,'B',2);

T0104
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    1 | A    |    1 |
|    2 | A    |    2 |
|    3 | A    |    6 |
|    4 | A    |    4 |
|    5 | A    |    3 |
|    6 | B    |    2 |
|    7 | B    |    8 |
|    8 | B    |    2 |
+------+------+------+

结果
+------+------+------+
| ID   | NAME | NUM  |
+------+------+------+
|    4 | A    |    4 |
|    5 | A    |    3 |
|    7 | B    |    8 |
|    8 | B    |    2 |
+------+------+------+

解释：从题目意思可以看出A组的总数为16，从ID为1到5分别累加后的结果分别为1,3,9,13,16，
只有13和16除以总数16才大于0.6，所以返回的结果ID为4和5，同样B组为7和8

SELECT B.ID, B.NAME, B.NUM  
FROM
(
  SELECT A.*,
  SUM(A.NUM) OVER(PARTITION BY A.NAME ORDER BY A.ID) / 
  SUM(A.NUM) OVER(PARTITION BY A.NAME) AS Ratio
  FROM T0104 A
)B WHERE B.Ratio >= 0.6;

SELECT B.ID, B.NAME, B.NUM 
FROM
(
  SELECT A.*,
  (SELECT SUM(T1.NUM)FROM T0104 T1 WHERE T1.ID <= A.ID AND T1.NAME = A.NAME) / 
  (SELECT SUM(T2.NUM) FROM T0104 T2 WHERE T2.NAME = A.NAME) Ratio
  FROM T0104 A
)B WHERE B.Ratio >= 0.6;