首页 注册 登录
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 · 4329 次点击
    这是一个创建于 1675 天前的主题，其中的信息可能已经有所发展或是发生改变。

    现在有个功能需要统计每个用户的首单。自己写的 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 表才导致的。而且另外建张表存储，后期要改的话也会更加方便
  • user_id
  • SQL
  • select
  • 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
    https://blog.csdn.net/Fire_Sky_Ho/article/details/90345692

    希望这篇文章可以帮到你
    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   ·   实用小工具   ·   1088 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 27ms · UTC 23:33 · PVG 07:33 · LAX 15:33 · JFK 18:33
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.