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

MYSQL 订单查询求助

  •  
  •   marine2c · 2020-12-30 15:15:32 +08:00 · 3392 次点击
    这是一个创建于 696 天前的主题,其中的信息可能已经有所发展或是发生改变。

    假设有一张订单表 A,有下单时间和 IP,要求查出任意 30 分钟内同一 IP 交易超过 20 次的记录,该怎么写?自己想的是 group by ip,但是怎么控制任意 30 分钟以内呢

    37 条回复    2020-12-31 10:25:22 +08:00
    Latin
        1
    Latin  
       2020-12-30 15:17:07 +08:00
    这个任意很模糊,时间不能关联查询了吗?
    marine2c
        2
    marine2c  
    OP
       2020-12-30 15:20:10 +08:00
    @Latin 不能,时间和其他没有任何关系啊,就是只要你在任意 30 分钟间隔内下单超过 20 次就查询出来
    ingxx
        3
    ingxx  
       2020-12-30 15:21:21 +08:00
    可以考虑一下 Prometheus
    marine2c
        4
    marine2c  
    OP
       2020-12-30 15:23:05 +08:00
    @ingxx 用的是 MySQL,要求用 sql 语句或存储过程
    msaionyc
        5
    msaionyc  
       2020-12-30 15:43:47 +08:00
    每条订单记录,往前取三十分钟内的所有订单( ip 过滤),count,然后该分组就分组
    lpts007
        6
    lpts007  
       2020-12-30 15:51:33 +08:00
    @marine2c 谁要求的啊,是面试题吗

    1. 一秒一秒往前推,查 3600 次能把时间往前推 1h,后台查完另存起来,以后查这个新表。
    2. distinct ip,循环处理,同一个 ip 一条记录查一次半小内的数据 count, > 20 处理下一个 ip, 否则 下一条不同于上条时间的记录。后台查完另存起来,以后查这个新表。
    marine2c
        7
    marine2c  
    OP
       2020-12-30 15:59:31 +08:00
    @lpts007 工作内容,方式 2 效率高点吧,一秒一秒推太多循环了吧
    Jeyfang
        8
    Jeyfang  
       2020-12-30 16:11:29 +08:00
    任意 30 分钟怎么理解,比如现在 15:00,那要查出 13:00 的,这 30 分钟是指 12:30-13:00 ?
    marine2c
        9
    marine2c  
    OP
       2020-12-30 16:14:49 +08:00
    @Jeyfang 是的,就是任意时间点往后推 30 分钟嘛。
    Jeyfang
        10
    Jeyfang  
       2020-12-30 16:23:04 +08:00
    那就 6 楼的第 2 个方法吧。如果数据量比较少,直接全部取出来,然后自己计算。如果数据量大的话,是否可以以 1 分钟为单位,每次下单的时候,做一个统计,相当于在一个线性的轴上面记录,然后直接从轴上面统计
    gogo789
        11
    gogo789  
       2020-12-30 16:25:17 +08:00
    where 下单时间 between 开始时间 and 结束时间 group by ip having count > 20 ?
    fx050622
        12
    fx050622  
       2020-12-30 16:30:05 +08:00   ❤️ 1
    是不是可以理解为 下单之后连续 30 分钟内下单超过 20 次的用户?
    select a.ip,a.下单时间,b.下单时间 from a,b where a.会员=b.会员 and b.下单时间<=a.下单时间+30

    group by a.ip,a.下单时间 count(b.下单时间)
    marine2c
        13
    marine2c  
    OP
       2020-12-30 16:36:21 +08:00
    @fx050622 你的 a,b 指的是同一张表吗?好像有点意思
    fx050622
        14
    fx050622  
       2020-12-30 16:37:00 +08:00
    @marine2c 是的,自关联一下
    fish267
        15
    fish267  
       2020-12-30 16:39:44 +08:00
    起个调度,一直查呗,group by ip
    caola
        16
    caola  
       2020-12-30 16:49:09 +08:00
    按时间排序,每次拿 20 条记录( 1-20 ),下次拿 20 条( 2-21 ),
    比较一下这 20 条中最早的一条和最后的一条的间隔时间,是否在 30 分钟内的
    xiaoxinshiwo
        17
    xiaoxinshiwo  
       2020-12-30 17:24:23 +08:00
    ES
    dobelee
        18
    dobelee  
       2020-12-30 17:26:39 +08:00 via iPhone
    把订单异步索引到 es,想怎么查怎么查。
    rrfeng
        19
    rrfeng  
       2020-12-30 17:35:22 +08:00 via Android
    写 SQL 难,做个 20 长的队列,遍历一遍就行。
    LEFT
        20
    LEFT  
       2020-12-30 19:15:53 +08:00 via iPhone
    有一个方法:核心是两次 row_num 加自关联

    传送门
    https://muguayuan.com/2020/16111.html
    drrrtt
        21
    drrrtt  
       2020-12-30 19:19:38 +08:00
    自关联,group by ip,off set = 19
    LEFT
        22
    LEFT  
       2020-12-30 19:20:31 +08:00 via iPhone
    仅供参考
    mmdsun
        23
    mmdsun  
       2020-12-30 19:45:04 +08:00 via Android
    先 mark 一下。我写过类似的 SQL 。现在忘记了。。

    按时间分组 ,having 过滤 再关联?

    这是按 1 天分组的 group by,查出来是 1 天下单超过 20 单的,所有 ip:

    GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d')
    having count(*)> 20

    这是按 5 秒分组的

    SELECT id,time,count(1),second(time),floor(second(time)/5) FROM `personcount` GROUP BY DATE_FORMAT( concat(date(time),' ',hour(time),':',minute(time),':',floor(second(time)/5)) ,'%Y-%m-%d %H:%i:%s');
    leeg810312
        24
    leeg810312  
       2020-12-30 21:39:38 +08:00 via Android
    很难吗? where 下单时间 group by IP having count(1)>20 不就可以了吗?下单时间和 IP 要加索引。
    PopRain
        25
    PopRain  
       2020-12-30 23:55:42 +08:00 via Android
    你这个描述不够清晰,你是需要那种:
    1.用户指定一个 30 分钟的时间段,查出超过 20 条交易记录的 IP
    2.对历史数据进行统计,找出每 30 分钟发生过 20 比以上交易的 IP

    1:比较简单,时间过滤,group by+having
    2:可以以记录顺序倒序,做个子查询汇总当前记录前面 30 分钟的相同 ip 的数据,效率可能低一点,但是肯定可行。
    ttys001
        26
    ttys001  
       2020-12-31 00:09:00 +08:00
    select ip, sum(t4.cnt) from (select 1 cnt, * from ((select * from table t1) inner join (select * from table t2) on (t1.time-t2.time <= 30*60)) t3)t4 from t4 group by ip having sum(t4.cnt) >= 20;
    好久没写 mysql 了,语法估计有问题。但是真的不难,自关联+groupby 。
    dzdh
        27
    dzdh  
       2020-12-31 00:16:15 +08:00
    这个任意三十分钟是不是指的,当前已有数据和未来数据中,以每一单的时间(或分)往前(或后)推 30 分钟,有超过 20 的?

    流计算?
    dzdh
        28
    dzdh  
       2020-12-31 00:16:51 +08:00
    @dzdh 像令牌桶一样?
    dusu
        29
    dusu  
       2020-12-31 02:51:48 +08:00 via iPhone   ❤️ 1
    这种需求单靠 sql 压根就是自讨苦吃

    窗口按时间移动的需求

    显然用 redis 写个 key+ttl 就好解决的问题

    例如每 5 分钟内 每个用户使用固定 key

    用户交易成功 incr 记录次数

    每次都往前推 4 个 key 去计算和是否满足大于 20 次总量 ,如果超过,记录到异常 list 当中备查即可

    如果精度要求高那就每 1 分钟一个固定 key

    这实现不管是 30 分钟 1 个小时 6 个小时 12 个小时都可以做

    sql 去做这个事…想想都蛋疼
    ashong
        30
    ashong  
       2020-12-31 09:09:30 +08:00 via iPhone
    @leeg810312 他这个是非固定时间,要查的是任意时间段内频繁下单,估计是避免恶意下单吧
    marine2c
        31
    marine2c  
    OP
       2020-12-31 09:18:58 +08:00
    @dzdh 是的,理解没错
    marine2c
        32
    marine2c  
    OP
       2020-12-31 09:19:26 +08:00
    @LEFT 谢谢,我看看
    marine2c
        33
    marine2c  
    OP
       2020-12-31 09:21:08 +08:00
    @dusu 只用了 MySQL,蛋疼
    Habyss
        34
    Habyss  
       2020-12-31 10:08:01 +08:00
    查符合的 ip
    ```
    select distinct a.ip
    from (
    select a.ip, a.时间, count(b.时间) num
    from table a,
    table b
    where a.ip = b.ip and b.时间 between date_sub(a.时间, interval 30 minute) and a.时间
    group by a.ip, a.时间
    having num > 20) a;
    ```
    查符合的单号
    ```
    select a.ip, a.时间, count(b.时间) num, group_concat(b.单号) ids
    from myt_sales_order a,
    myt_sales_order b
    where a.ip = b.ip and b.时间 between date_sub(a.时间, interval 30 minute) and a.时间
    group by a.ip, a.时间
    having num > 4;
    ```
    AntoniotheFuture
        35
    AntoniotheFuture  
       2020-12-31 10:10:46 +08:00
    假设你的 ordertime 是秒时间戳:

    SELECT iprdertime, c
    FROM (
    SELECT CONCAT(o1.ip, o1.ordertime) AS iprdertime, count(*) AS c
    FROM orders o1
    JOIN (
    SELECT ip, ordertime
    FROM orders
    ) o2
    ON o1.ip = o2.ip
    AND o1.ordertime >= o2.ordertime
    AND o1.ordertime < o2.ordertime + 1800
    GROUP BY CONCAT(o1.ip, o1.ordertime)
    ORDER BY o1.ordertime
    ) oc
    WHERE c >= 20

    只测试了一小部分数据,性能可能比较低,但满足你的需求,可以通过前置筛选的方法减少查询量,如果是用来监控的,可以每天运行一次(查询前 24.5 小时内新增的数据)
    AntoniotheFuture
        36
    AntoniotheFuture  
       2020-12-31 10:11:47 +08:00
    @AntoniotheFuture 鉴于 ORDER BY 对性能影响较大,这里可以去掉
    marine2c
        37
    marine2c  
    OP
       2020-12-31 10:25:22 +08:00
    @ttys001
    @Habyss
    @AntoniotheFuture
    感谢各位大佬提供的思路,自关联查询可以满足要求了
    关于   ·   帮助文档   ·   API   ·   FAQ   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   1386 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 52ms · UTC 19:10 · PVG 03:10 · LAX 11:10 · JFK 14:10
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.