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

请教大家一个关于订单统计 sql 的问题

  •  1
     
  •   liubx · 2020-04-23 10:06:06 +08:00 · 4307 次点击
    这是一个创建于 1657 天前的主题,其中的信息可能已经有所发展或是发生改变。

    现在有个功能需要统计每个用户的首单。自己写的 sql 不太满意,希望大家能指点下。

    这是我写的 sql

    SELECT * FROM order WHERE create_time IN (
    		SELECT min( create_time ) create_time FROM
    			order WHERE
    			order_status IN ( 'PRE_DELIVER', 'PRE_ACCEPT', 'AFTER_DELIVER' ) 
    			AND user_id in (1,2,3,4) AND deleted = 0 GROUP BY user_id 
    	) 
    GROUP BY user_id
    

    我是先查询出每个用户第一单的时间,在通过时间去查询对应的订单。

    但想不出怎么去优化,只能请教下大家,希望能给一些思路

    第 1 条附言  ·  2020-04-23 11:31:59 +08:00
    感谢大家的回复,最终决定用 @lifespy 这位老哥的方法,重新建一张表存储。

    决定不用 sql 去查是因为, 这个功能需要查询两张表 order 和 c_order 。如果纯用 sql 查会导致 sql 特别庞大,并且不够灵活。

    现在这个问题就是因为统计功能加入了 c_order 表才导致的。而且另外建张表存储,后期要改的话也会更加方便
    39 条回复    2020-04-23 16:37:15 +08:00
    linauror
        1
    linauror  
       2020-04-23 10:18:39 +08:00
    `
    SELECT *,MIN(create_time) FROM order WHERE order_status IN ( 'PRE_DELIVER', 'PRE_ACCEPT', 'AFTER_DELIVER' ) AND user_id in (1,2,3,4) AND deleted = 0 GROUP BY user_id
    `
    试试这个呢
    kamisama
        2
    kamisama  
       2020-04-23 10:21:17 +08:00
    这种最好把表索引也发出来
    dongisking
        3
    dongisking  
       2020-04-23 10:27:17 +08:00 via Android
    敢用子查询的都是菜鸡吧
    liubx
        4
    liubx  
    OP
       2020-04-23 10:35:40 +08:00
    @linauror 不行,这只能让 create_time 是第一单的时间,其他的列还是默认查询的列
    zhuzhibin
        5
    zhuzhibin  
       2020-04-23 10:36:30 +08:00 via iPhone
    @dongisking 有必要吗?人家是请教问题 ?你在干嘛?
    zhuzhibin
        6
    zhuzhibin  
       2020-04-23 10:36:30 +08:00 via iPhone
    @dongisking 有必要吗?人家是请教问题 ?你在干嘛?
    liubx
        7
    liubx  
    OP
       2020-04-23 10:37:45 +08:00
    @kamisama 索引是`user_id`,`create_time``sale_order_no``deleted`这四个字段
    liubx
        8
    liubx  
    OP
       2020-04-23 10:38:17 +08:00
    @dongisking 嗯,数据库确实不熟,大佬指点下啊
    newtype0092
        9
    newtype0092  
       2020-04-23 10:42:22 +08:00
    @dongisking 多干两年接触点复杂业务再来说话。
    CrazyMoon
        10
    CrazyMoon  
       2020-04-23 10:45:50 +08:00
    不太懂怎么优化,不过感觉这个 sql 的正确性可能有问题。。如果有多订单同时创建的话,按照 create time 取会取出多余的订单
    moonsola
        11
    moonsola  
       2020-04-23 11:06:20 +08:00
    要我就选择在用户表里加个字段:first_order_id
    lifespy
        12
    lifespy  
       2020-04-23 11:09:27 +08:00
    liubx
        13
    liubx  
    OP
       2020-04-23 11:10:57 +08:00
    @CrazyMoon 嗯,用户多了确实有这个问题。但是没有想到其他的 sql
    lifespy
        14
    lifespy  
       2020-04-23 11:11:48 +08:00
    如果数据量特别大的话,建议专门独立一个表出去存储
    mwiker
        15
    mwiker  
       2020-04-23 11:15:38 +08:00
    SELECT t.*
    FROM order t
    INNER JOIN (
    SELECT user_id, min(create_time) create_time
    FROM order
    WHERE order_status IN ( 'PRE_DELIVER', 'PRE_ACCEPT', 'AFTER_DELIVER' )
    AND user_id in (1,2,3,4)
    AND deleted = 0
    GROUP BY user_id ) t2
    ON t.user_id = t2.user_id
    AND t.create_time = t2.create_time
    int11
        16
    int11  
       2020-04-23 11:15:51 +08:00
    订单 id 是自增的话,通过 id 大小去判断要好一些
    mwiker
        17
    mwiker  
       2020-04-23 11:17:59 +08:00
    搞个 mysql8.0,有开窗函数做这些就很简单了
    Tomorrowxxy
        18
    Tomorrowxxy  
       2020-04-23 11:18:55 +08:00
    如果是 mysql5.7 版本以下的
    tinybaby365
        19
    tinybaby365  
       2020-04-23 11:19:05 +08:00
    order 表有多大,每个用户平均有几单?如果你是在线上 db 操作,我有点担心你这一次性把所有用户首单查出来的操作把会把你的 db 搞挂。如果是操作 OLAP 的 DB,那无所谓啦。
    Tomorrowxxy
        20
    Tomorrowxxy  
       2020-04-23 11:21:03 +08:00
    SELECT * FROM
    order WHERE
    order_status IN ( 'PRE_DELIVER', 'PRE_ACCEPT', 'AFTER_DELIVER' )
    AND user_id in (1,2,3,4) AND deleted = 0 GROUP BY user_id ORDER BY order_id
    )
    liubx
        21
    liubx  
    OP
       2020-04-23 11:21:29 +08:00
    @lifespy 谢谢,这个方法不错啊
    wuzhizuiguo
        22
    wuzhizuiguo  
       2020-04-23 11:24:16 +08:00
    如果 order 表的 id 是 bigint 然后是自增的, 根据 user_id 分组找到 id 最小的(时间应该也是最早的)
    select id from orders group by user_id order by id asc
    //找出这些订单
    select * from orders where id in( select o.id from orders o group by o.user_id order by o.id asc) and ...
    lifespy
        23
    lifespy  
       2020-04-23 11:25:49 +08:00
    @liubx #21 是的,因为根据你的描述以及业务。订单这个东西一旦创建时不会变的,所以可以单独存储
    DoUSeeMe
        24
    DoUSeeMe  
       2020-04-23 11:26:52 +08:00
    这个问题类似于获取分类前 N 条记录,你这里的 user 就是类,可以去查一查。

    如果你要具体的优化语句,可以把表的代码复制出来,我比较懒,懒得去新建一个
    levelworm
        25
    levelworm  
       2020-04-23 11:27:29 +08:00
    不能用窗口函数吗?不能的话这个不知道行不行

    select
    *
    from (
    select
    *
    from
    order
    order by
    user_id, time asc
    ) as x
    group by cid
    Nostalgiaaaa
        26
    Nostalgiaaaa  
       2020-04-23 11:27:52 +08:00
    试一下 partition by,解决这类首单,首次,最后一次的问题
    简单搜了一下应该是一个类似的问题
    https://stackoverflow.com/questions/18987727/sql-query-using-partition-by

    SELECT *
    FROM
    (SELECT * ,
    ROW_NUMBER() over( partition by user_id
    ORDER BY create_time DESC ) AS rank
    FROM order
    WHERE order_status IN ( 'PRE_DELIVER', 'PRE_ACCEPT', 'AFTER_DELIVER' )
    AND user_id IN (1,2,3,4)
    AND deleted = 0 )
    WHERE rank = 1

    另外,分析型 sql 建议做成定时任务定时刷新,比如半小时一次,实时查意义不大并且性能问题很严重
    levelworm
        27
    levelworm  
       2020-04-23 11:30:08 +08:00
    @Nostalgiaaaa 他这个估计版本比较老用不了窗口函数,我猜测
    iffi
        28
    iffi  
       2020-04-23 11:45:15 +08:00
    可用 join
    liubx
        29
    liubx  
    OP
       2020-04-23 11:45:20 +08:00
    @mwiker 感谢,之前没了解窗口函数,刚看了下。窗口函数确实可以解决
    liubx
        30
    liubx  
    OP
       2020-04-23 11:46:41 +08:00   ❤️ 1
    @Nostalgiaaaa 嗯,谢谢回复。现在决定做成定时任务了
    icchux
        31
    icchux  
       2020-04-23 11:47:02 +08:00
    @levelworm 这个可以 我上午刚用这个子查询的方法 统计一些东西 但这个是慢查询 数据量大的时候特别慢。。。。。。mysql 低版本对于这种统计要求真的烦心
    liubx
        32
    liubx  
    OP
       2020-04-23 11:48:20 +08:00
    @levelworm 嗯,窗口函数可以解决。但是涉及多张表了。还是决定重新建张表存储了。
    liubx
        33
    liubx  
    OP
       2020-04-23 11:49:49 +08:00
    @icchux 可以用定时任务,把统计结果存到另一张表里。
    icchux
        34
    icchux  
       2020-04-23 11:53:26 +08:00
    @iffi 大佬可否指点一二
    levelworm
        35
    levelworm  
       2020-04-23 11:54:44 +08:00 via Android
    @icchux 没有窗口函数真的要死了,我现在随便一个查询都有几个窗口函数
    ooyy
        36
    ooyy  
       2020-04-23 12:01:07 +08:00
    sql 查询改动小,性能要看表数据量
    with t as (
    select *, ROW_NUMBER() over (partition by user_id ORDER BY create_time DESC ) AS rn
    from order inner join c_order on order.id = c_order.id ...
    ) select * from t where rn = 1 and a.order_status IN ( 'PRE_DELIVER', 'PRE_ACCEPT', 'AFTER_DELIVER' )
    and ...
    seanseek
        37
    seanseek  
       2020-04-23 13:09:54 +08:00
    能不能按照 id 和时间排序,然后再去个重?
    jeff0819
        38
    jeff0819  
       2020-04-23 15:11:18 +08:00
    建议中间表,写个脚本跑出来再去查
    seanseek
        39
    seanseek  
       2020-04-23 16:37:15 +08:00
    group by user_id 可以
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1241 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 23:15 · PVG 07:15 · LAX 15:15 · JFK 18:15
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.