使用多个selectas列优化查询

khbbv19g  于 2021-07-24  发布在  Java
关注(0)|答案(2)|浏览(386)

我提出了一个问题:

SELECT DISTINCT player_1 AS player, 
                (SELECT COUNT(*) FROM results WHERE player_1=player OR player_2=player) AS since_start_matches, 
                (SELECT COUNT(*) FROM results WHERE (player_1=player OR player_2=player) AND ht_total_goals=0) AS since_start_ht_0,
                (SELECT COUNT(*) FROM results WHERE (player_1=player OR player_2=player) AND ht_total_goals=1) AS since_start_ht_1,
                (SELECT COUNT(*) FROM results WHERE (player_1=player OR player_2=player) AND ht_total_goals=2) AS since_start_ht_2,
                (SELECT COUNT(*) FROM results WHERE (player_1=player OR player_2=player) AND ht_total_goals=3) AS since_start_ht_3,
                (SELECT COUNT(*) FROM results WHERE (player_1=player OR player_2=player) AND ht_total_goals=4) AS since_start_ht_4,
                (SELECT COUNT(*) FROM results WHERE (player_1=player OR player_2=player) AND ht_total_goals>=5) AS since_start_ht_5_plus
FROM results ORDER BY player

这个 results 表有25000个条目,执行此查询大约需要7秒,这太长了。这个查询效率非常低,因为我创建的每一列都在同一个表上用不同的条件再次搜索。
我试着为where子句中感兴趣的列建立索引。几秒钟就搞定了。但还是太慢了。
处理此类查询的最佳方法是什么?
我用的是mariadb 10.2

91zkwejq

91zkwejq1#

取消激活数据,然后进行聚合:

SELECT player, 
        COUNT(*) AS since_start_matches, 
        SUM(ht_total_goals=0) AS since_start_ht_0,
        SUM(ht_total_goals=1) AS since_start_ht_1,
        SUM(ht_total_goals=2) AS since_start_ht_2,
        SUM(ht_total_goals=3) AS since_start_ht_3,
        SUM(ht_total_goals=4) AS since_start_ht_4,
        SUM( ht_total_goals>=5) AS since_start_ht_5_plus
FROM ((SELECT player_1 as player, ht_total_goals
       FROM results
      ) UNION ALL
      (SELECT player_2 as player, ht_total_goals
       FROM results
      )
     ) p
GROUP BY player
ctzwtxfj

ctzwtxfj2#

你可以用 LEFT JOIN 以及 conditional aggregation 具体如下:

SELECT  player_1 AS player, 
                COUNT(T2.player_1) AS since_start_matches, 
                SUM(CASE WHEN T2.ht_total_goals=0 THEN 1 END) AS since_start_ht_0,
                SUM(CASE WHEN T2.ht_total_goals=1 THEN 1 END) AS since_start_ht_1,
                SUM(CASE WHEN T2.ht_total_goals=2 THEN 1 END) AS since_start_ht_2,
                SUM(CASE WHEN T2.ht_total_goals=3 THEN 1 END) AS since_start_ht_3,
                SUM(CASE WHEN T2.ht_total_goals=4 THEN 1 END) AS since_start_ht_4,
                SUM(CASE WHEN T2.ht_total_goals>=5 THEN 1 END) AS since_start_ht_5_PLUS
FROM results T1 LEFT JOIN results T2 
ON (T2.player_1=T.player OR T2.player_2=T.player)
GROUP BY T1.PLA

按玩家排序

相关问题