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
CosWind
V2EX  ›  MySQL

这样的 SQL 语句该怎么写

  •  
  •   CosWind ·
    coswind · 2014-10-17 10:50:39 +08:00 · 3842 次点击
    这是一个创建于 3451 天前的主题,其中的信息可能已经有所发展或是发生改变。
    一张表Test,3个字段:a ,b ,score

    a ,b字段都有值,score由a / b的值按区段打分, 如大于0.3是5分,大于0.18是4分,大于0.12是3分等

    那么问题来了:如何用一条SQL语句达到目的
    第 1 条附言  ·  2014-10-17 11:39:02 +08:00
    实际的情况是一共有两张表TEST_A, TEST_B

    TEST_A: account_id, profit_rate, score
    TEST_B: account_id, profit, init_funds

    TEST_B表的数据都有

    account_id 是关联主键

    TEST_A为空
    profit_rate = profit / init_funds
    score = profit_rate > 0.3 ? 5 : profit_rate > 0.18 ? 4 : profit_rate > 0.12 ? 3 : 0

    问题是:
    如何使用一条SQL生成TEST_A表的数据
    第 2 条附言  ·  2014-10-17 11:41:27 +08:00
    考虑 init_funds为0的情况

    profit / init_funds只计算一次
    22 条回复    2014-10-17 13:31:12 +08:00
    lichao
        1
    lichao  
       2014-10-17 10:57:14 +08:00   ❤️ 1
    select a, b, case when a/b > 0.5 then 5 when a/b > 0.3 then 4 when a/b > 0.12 then 3 else 0 end as score
    CosWind
        2
    CosWind  
    OP
       2014-10-17 11:11:32 +08:00
    @lichao 诶,这样 a/b会算几次?
    lichao
        3
    lichao  
       2014-10-17 11:13:31 +08:00
    @CosWind 最影响数据库性能的是磁盘 IO,数学计算与之相比可忽略不计
    tobyzw
        4
    tobyzw  
       2014-10-17 11:17:33 +08:00   ❤️ 1
    select a, b, case when a/b > 0.5 then 5 when a/b > 0.3 then 4 when a/b > 0.12 then 3 else 0 end as score
    -------------------------
    可能会有问题,a/b可能会出异常,b=0的情况需要考虑进去
    CosWind
        5
    CosWind  
    OP
       2014-10-17 11:19:22 +08:00
    @lichao 诶。我测试的结果是80w的表,这样写和a/b设置成常量1计算出来的结果所需要的时间分别是1.01s 和 0.85s,差别还是有的
    xudshen
        6
    xudshen  
       2014-10-17 11:19:32 +08:00   ❤️ 1
    用a > 0.5*b 会不会效率高一点?
    CosWind
        7
    CosWind  
    OP
       2014-10-17 11:19:41 +08:00
    @tobyzw 是的。
    CosWind
        8
    CosWind  
    OP
       2014-10-17 11:20:03 +08:00
    @xudshen 条件有多个。
    CosWind
        9
    CosWind  
    OP
       2014-10-17 11:21:51 +08:00
    @lichao 额。。我这测试不科学。
    xudshen
        10
    xudshen  
       2014-10-17 11:24:21 +08:00
    @CosWind 你可以把a/b的值存起来,像这样的sql肯定也不是仅仅run一次的,与其每次都计算,不如一次先搞定
    CosWind
        11
    CosWind  
    OP
       2014-10-17 11:26:41 +08:00
    @xudshen 对,我想要的就是这个效果,a/b的计算只计算一次,但是我想只用一条SQL达成目的
    xudshen
        12
    xudshen  
       2014-10-17 11:28:46 +08:00
    @CosWind 在insert的时候就包括a/b,或者设置个insert,update的trigger
    frye
        13
    frye  
       2014-10-17 11:43:43 +08:00
    SELECT
    a,
    b,
    IF(
    a / b > 0.12,
    IF(
    a / b > 0.18,
    IF(
    a / b > 0.3,
    5,
    4
    ),
    3
    ),
    0
    ) AS score
    FROM
    table_name
    cye3s
        14
    cye3s  
       2014-10-17 11:50:06 +08:00 via Android   ❤️ 1
    子查询查一次a/b,外面套case
    frye
        15
    frye  
       2014-10-17 11:52:20 +08:00   ❤️ 1
    INSERT INTO TEST_A (account_id, profit_rate, score) SELECT
    account_id,

    IF (
    init_funds > 0,
    profit / init_funds,
    0
    ) AS profit_rate,

    IF (

    IF (
    init_funds > 0,
    profit / init_funds,
    0
    ) > 0.12,

    IF (

    IF (
    init_funds > 0,
    profit / init_funds,
    0
    ) > 0.18,

    IF (

    IF (
    init_funds > 0,
    profit / init_funds,
    0
    ) > 0.3,
    5,
    4
    ),
    3
    ),
    0
    ) AS score
    FROM
    TEST_B
    CosWind
        16
    CosWind  
    OP
       2014-10-17 11:55:03 +08:00
    @cye3s 子查询性能会不会差一点
    CosWind
        17
    CosWind  
    OP
       2014-10-17 11:55:20 +08:00
    @frye a/b 能否只算一次呢
    frye
        18
    frye  
       2014-10-17 11:57:14 +08:00   ❤️ 1
    INSERT INTO TEST_A (account_id, profit_rate, score) SELECT
    TEST_B.account_id,
    TEST_B.profit_rate,

    IF (
    TEST_B.profit_rate > 0.12,

    IF (
    TEST_B.profit_rate > 0.18,

    IF (TEST_B.profit_rate > 0.3, 5, 4),
    3
    ),
    0
    ) AS score
    FROM
    (
    SELECT

    IF (
    init_funds > 0,
    profit / init_funds,
    0
    ) AS profit_rate,
    account_id
    FROM
    TEST_B
    ) AS TEST_B
    CosWind
        19
    CosWind  
    OP
       2014-10-17 12:03:47 +08:00
    @frye 这样用子查询,感觉有点得不偿失。
    viquuu
        20
    viquuu  
       2014-10-17 12:05:15 +08:00   ❤️ 1
    select a,b,
    case when c > 0.5 then 5 when c > 0.3 then 4 when c > 0.12 then 3 else 0 end as score
    from (
    select a,b, a/isnull(b,1) as c from test
    )
    frye
        21
    frye  
       2014-10-17 12:06:27 +08:00
    @CosWind 本就没有必要去纠结 [profit / init_funds只计算一次] 这个问题
    CosWind
        22
    CosWind  
    OP
       2014-10-17 13:31:12 +08:00
    @frye 恩。我只是想钻个牛角尖,想看看有没有利用@变量能实现,或者其它方式实现的方法 >_<`
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5335 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 08:05 · PVG 16:05 · LAX 01:05 · JFK 04:05
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.