V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
natsukage
V2EX  ›  MySQL

新手求问一个简单的查询优化问题。

  •  
  •   natsukage · 2022-01-29 12:33:47 +08:00 · 2017 次点击
    这是一个创建于 789 天前的主题,其中的信息可能已经有所发展或是发生改变。

    一张表里存储了用户的 ID 和物品的种类、细节、时间,
    可以类比为这样的场景
    一个表里储存了所有玩家的出货记录,记录了玩家的 ID 、出货的具体物品、物品的品级。
    然后我现在想要查询所有玩家的欧皇榜,就是查询每个玩家在一段时间内的总共抽奖次数、5 星物品出货次数与出货率。

    因为我完全是 SQL 新手…想了半天最后是用 2 条语句依次进行了 2 次查询。一次是只查询 rType=5 并 count()取得所有人的 5 星出货次数,一次是查询全部并 count()取得全部出货次数,然后两者相除。

    select ID,rType,rCount,
    TotalCount, temp.rCount/temp2.TotalCount as rRate from(
        select ID,rType,count(*) rCount from item_list
    	where rType=5 and CreateDate>=@StartDate and CreateDate<@EndDate
    	group by ID,rType
    ) temp,(
        select ID ID2 ,count(*) TotalCount from item_list
    	where CreateDate>=@StartDate and CreateDate<@EndDate
    	group by ID
    ) temp2 where temp.ID = temp2.ID2
    order by temp.rType desc,rareRate desc
    

    目前这条语句可以正常工作,但是显然,两次查询查的都是同一个表,而且第一次查询的 temp 其实就只是 temp2 的查询加上了一个 rType=5 的限制条件。

    所以想问,这种情况的查询,是不是应该有更好的办法,只对原表一次查询,就获取到我需要的全部信息呢?还是说这种情况本身就应该需要对原表 2 次不同的查询(我感觉应该不会吧…)
    多谢指导!

    表的内容大概是这样:

    ID itemName rType CreateDate
    1 item1 5 2022 年 1 月 29 日
    1 item2 4 2022 年 1 月 29 日
    2 item1 5 2022 年 1 月 29 日
    1 item2 4 2022 年 1 月 29 日
    1 item3 3 2022 年 1 月 29 日
    2 item4 3 2022 年 1 月 29 日
    3 item5 4 2022 年 1 月 29 日
    10 条回复    2022-01-30 12:14:10 +08:00
    512357301
        1
    512357301  
       2022-01-29 12:53:39 +08:00 via Android   ❤️ 1
    逻辑不复杂,可以用一条语句实现的,核心就是用 sum 函数搭配 if 函数。
    思路如下(主要看个思路哈,sql 可能没法直接执行)

    select ID
    ,sum(if(rType=5,1,0) as fiveStarCnt
    ,count(*) TotalCount
    ,sum(if(rType=5,1,0)/count(*) as rRate
    from item_list
    where CreateDate>=@StartDate
    and CreateDate<@EndDate
    group by ID
    512357301
        2
    512357301  
       2022-01-29 12:59:19 +08:00 via Android
    emmm ,刚才 sql 少了两个括号,尴尬。。。
    修改如下:
    select ID
    ,sum(if(rType=5,1,0)) as fiveStarCnt
    ,count(*) TotalCount
    ,sum(if(rType=5,1,0))/count(*) as rRate
    from item_list
    where CreateDate>=@StartDate
    and CreateDate<@EndDate
    group by ID
    rabbbit
        3
    rabbbit  
       2022-01-29 14:17:17 +08:00   ❤️ 1
    SELECT
      ID, TotalCount, rCount, t.rCount/t.TotalCount AS rRate
    FROM (
      SELECT
       ID, rType,
       COUNT(ID) AS TotalCount,
       SUM(CASE rType WHEN 5 THEN 1 ELSE 0 END) AS rCount
       FROM ` item_list` GROUP BY ID
    ) t
    natsukage
        4
    natsukage  
    OP
       2022-01-29 23:28:39 +08:00
    @512357301 多谢解答!很有启发,因为我对 SQL 只是粗浅了解,现在才第一次知道原来 Sum()是这么使用的。感谢指导!
    不过这个方法我实践了一下,还是有个问题就是它和前面我自己折腾的例子不一样,最后查询出的表,会把 rCount=0 也就是完全没有出过货的角色也统计进去,所以最后我还是在外面套了一层 select where rCount>0 。不过除此以外的内容都非常详细,让我受益匪浅。
    natsukage
        5
    natsukage  
    OP
       2022-01-29 23:33:55 +08:00
    @rabbbit 感谢回答!这条回复也非常有启发,一开始我想的是有没有可能只用一条 select 完成全部,但是发现 2 层 select 的查询确实读起来清晰得多。而且如同前面回复的,因为我原本的查询中会过滤掉所有完全没有出货( rCount=0 )的结果,所以如果只是一次 select 的话查询出来的临时表里面还是带有不需要的信息。看到您的这条回复意识到外面再套一层 select where rCount>0 就可以解决这个问题了。
    rabbbit
        6
    rabbbit  
       2022-01-29 23:39:36 +08:00
    也可以把过滤写里头, 例如:

    SELECT
      ID, TotalCount, rCount, t.rCount/t.TotalCount AS rRate
    FROM (
      SELECT
        ID, rType,
        COUNT(ID) AS TotalCount,
        SUM(CASE rType WHEN 5 THEN 1 ELSE 0 END) AS rCount
      FROM ` item_list`
      GROUP BY ID
      HAVING rCount>0
    ) t
    natsukage
        7
    natsukage  
    OP
       2022-01-29 23:53:27 +08:00
    @rabbbit …原来 HAVING 是这么用的 233 。这确实完全是我知识盲区了,之前从来没用过…
    多谢解答
    leetcode2020
        8
    leetcode2020  
       2022-01-30 10:32:34 +08:00
    SELECT
    tt.ID,
    count( tt.ID ) AS ttCountt,
    CONCAT(
    ROUND( SUM( CASE tt.rType WHEN 5 THEN 1 ELSE 0 END ) / COUNT( tt.ID ), 2 ) * 100,
    '%'
    ) rRate
    FROM
    item_list tt
    GROUP BY
    tt.ID;
    512357301
        9
    512357301  
       2022-01-30 12:11:55 +08:00 via Android
    @natsukage 你原来的查询方法其实本质上是内连接,而 5 星的货品估计出货次数都不为 0 在内连接的时候就无形中过滤掉了。
    如果把内连接换成右连接或者全连接,那等于 0 那部分还是能查出来的
    512357301
        10
    512357301  
       2022-01-30 12:14:10 +08:00 via Android
    @natsukage 你的那个两步查询你可以复核下,看下是不是还缺少“完全没有 5 星货品的玩家”,这部分可能也被无形中过滤了
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   1340 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 23:37 · PVG 07:37 · LAX 16:37 · JFK 19:37
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.