获得组表的百分比

Question

我正在为一个地方联赛做一场预测比赛。

匹配由它的SPIELTAG (播放日)和它的MATCH_NR定义。预测有匹配和结果(ERGA，ERGB)

我有一张桌子就像这把小提琴https://www.db-fiddle.com/f/o4NXPFfzod39LpMaTzqz3r/0

我得到输出来统计每一场比赛和每一场比赛的每一个结果。

| SPIELTAG | MATCH_NR | ERGA | ERGB | AMOUNT |
| -------- | -------- | ---- | ---- | ------ |
| 4        | 1        | 7    | 1    | 1      |
| 4        | 1        | 7    | 2    | 2      |
| 4        | 1        | 7    | 3    | 5      |
| 4        | 1        | 7    | 4    | 1      |
| 4        | 2        | 1    | 7    | 1      |
| 4        | 2        | 2    | 7    | 6      |
| 4        | 2        | 3    | 7    | 3      |

我想要实现的是，就在“金额”栏旁边

| SPIELTAG | MATCH_NR | ERGA | ERGB | AMOUNT | PERC | 
| -------- | -------- | ---- | ---- | ------ | ---- | 
| 4        | 1        | 7    | 1    | 1      | 11.1%| => 1 / 9
| 4        | 1        | 7    | 2    | 2      | 22.2%| => 2 / 9
| 4        | 1        | 7    | 3    | 5      | 55.5%| => 5 / 9
| 4        | 1        | 7    | 4    | 1      | 11.1%| => 1 / 9
| 4        | 2        | 1    | 7    | 1      | 11.1%| => 1 / 9
| 4        | 2        | 2    | 7    | 5      | 55.5%| => 5 / 9
| 4        | 2        | 3    | 7    | 3      | 33.3%| => 3 / 9

其中PERC是每个休息日SPIELTAG挑选的百分比。基本上结果是多久一次，元组(ERGA，ERGB)对每一场比赛都有预测，SPIELTAG，MATCH_NR。

我找到了一个帖子，我可以得到的百分比超过所有的选择，但不限制在游戏日，匹配元组。

一个例子: Match 1 (Spieltag 4，Match 1)有9个预测。

1x: 7-1

2x: 7-2

5x: 7-3

1x: 7-4

__

9x -> ALL_COUNTS_PER_MATCH

因此，PERC应该类似于'AMOUNT‘/ ALL_COUNTS_PER_MATCH。

Answer 1

我认为你需要另一个小组：

SELECT `t`.`SPIELTAG` AS `SPIELTAG`,
       `t`.`MATCH_NR` AS `MATCH_NR`,
       `t`.`TEAM_A`   AS `ERGA`,
       `t`.`TEAM_B`   AS `ERGB`,
       count(0)       AS `AMOUNT`,
       COUNT(0) / MAX(A.TOTAL_AMOUNT) -- MIN would also work
FROM `TIPPSPIEL_TIPP` `t`
         JOIN (
    -- Calculate the row count by each different spieltag & match_nr combination
    SELECT `t`.`SPIELTAG`,
           `t`.`MATCH_NR`,
           count(0) AS `TOTAL_AMOUNT`
    FROM `TIPPSPIEL_TIPP` `t`
    GROUP BY `t`.`SPIELTAG`, `t`.`MATCH_NR`
) A USING (SPIELTAG, MATCH_NR)
GROUP BY `t`.`SPIELTAG`, `t`.`MATCH_NR`, `t`.`TEAM_A`, `t`.`TEAM_B`
;

https://www.db-fiddle.com/f/o4NXPFfzod39LpMaTzqz3r/3

Answer 2

检查一下这个，但是将在Mysql 8中工作，而不是更低的版本。

https://www.db-fiddle.com/f/o4NXPFfzod39LpMaTzqz3r/4

SELECT DISTINCT  `t`.`SPIELTAG` AS `SPIELTAG`,
       `t`.`MATCH_NR` AS `MATCH_NR`,
       `t`.`TEAM_A` AS `ERGA`,
       `t`.`TEAM_B` AS `ERGB`,
       COUNT(0) OVER (PARTITION BY `t`.`SPIELTAG`,
                                   `t`.`MATCH_NR`,
                                   `t`.`TEAM_A`,
                                   `t`.`TEAM_B`) AS AMOUNT,
                     COUNT(0) OVER (PARTITION BY `t`.`SPIELTAG`,
                                                 `t`.`MATCH_NR`,
                                                 `t`.`TEAM_A`,
                                                 `t`.`TEAM_B`) / COUNT(CONCAT(SPIELTAG, MATCH_NR)) OVER (PARTITION BY `t`.`SPIELTAG`,
                                                                                                                      `t`.`MATCH_NR`) AS match_count
FROM `TIPPSPIEL_TIPP` `t`