mysql - 查找一支球队最近 X 场比赛的胜/平/负百分比

Question

我有一个表(test_matches)，其中记录了几场比赛的结果，按日期排序。

GHFT = 目标主队全职。GAFT = 全职客场进球队。

CREATE TABLE `test_matches` (
 `id` int(10) NOT NULL AUTO_INCREMENT,
  `match_date` date NOT NULL,
 `home_team` varchar(250) DEFAULT NULL,
 `away_team` varchar(250) DEFAULT NULL,
  GHFT` int(11) NOT NULL DEFAULT '0',
 `GAFT` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
  );
INSERT INTO test_matches (match_date, home_team, away_team, GHFT, GAFT ) 
VALUES ('2019-01-01', 'Real Madrid', 'Zaragoza', 2,0), 
('2019-01-03', 'Barcelona', 'Lugo', 1,1), 
('2019-01-04', 'Real Madrid', 'Lugo', 2,1), 
('2019-01-05', 'Barcelona', 'Compostela', 4,1),
('2019-01-06', 'Real Madrid', 'Barcelona', 0,2),
('2019-01-07', 'Barcelona', 'Zaragoza', 0,0);

http://sqlfiddle.com/#!9/c0f16a/1

我试过这个查询:

SELECT home_team,
   ROUND(SUM(CASE WHEN ghft > gaft = 1 THEN 1 ELSE 0 END) /COUNT(*) *100) AS W_Home_Team,
   ROUND(SUM(CASE WHEN ghft = gaft = 1 THEN 1 ELSE 0 END) /COUNT(*) *100) AS D_Home_Team, 
   ROUND(SUM(CASE WHEN ghft < gaft = 1 THEN 1 ELSE 0 END) /COUNT(*) *100) AS L_Home_Team 
FROM ( SELECT home_team, ghft, gaft FROM test_matches ORDER BY id DESC LIMIT 2) average 
GROUP BY home_team;

但是，我得到的结果是不正确的，因为它考虑的是表格的最后两条记录，而不是每个团队的最后两条记录。

正确的结果是:巴塞罗那 50-50-0 和皇家马德里 50-0-50。

如何计算每支球队最近 2 场比赛的百分比？

Answer 1

使用这个 SELECT 语句

SELECT
  home_team
  , ROUND(SUM(IF(ghft > gaft,1,0)) / COUNT(*),2) * 100 W_Home_Team
  , ROUND(SUM(IF(ghft = gaft,1,0)) / COUNT(*),2) * 100 D_Home_Team
  , ROUND(SUM(IF(ghft < gaft,1,0)) / COUNT(*),2) * 100 L_Home_Team
FROM
  (SELECT 
    home_team
    ,match_date
    , ghft
    , gaft
    , IF(@team <> home_team,@rank := 0, @rank := @rank) dec1
    ,@rank := @rank+1 rnk
    ,@team := home_team
  FROM test_matches,(SELECT @rank := 0) r1,(SELECT @team := '') r2
  ORDER BY home_team,match_date) t1
 WHERE rnk < 3
 GROUP BY home_team
;

你得到这个结果

home_team       W_Home_Team     D_Home_Team     L_Home_Team
Barcelona       50              50              0
Real Madrid     100             0               0

这会将除法四舍五入为两位数，这样您将得到 25,25 Procent。您必须根据自己的需要来适应这一点。

内部 SELECT 仅用于获取每个俱乐部的正确比赛数。

与 WHERE rnk < 3你可以改变你需要的游戏数量

SQL Fiddle example