MYSQL 订单查询求助

marine2c 26天前 14

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

最新回复 (37)
  • Latin 22天前
    引用 2
    这个任意很模糊,时间不能关联查询了吗?
  • 楼主 marine2c 22天前
    引用 3
    @Latin 不能,时间和其他没有任何关系啊,就是只要你在任意 30 分钟间隔内下单超过 20 次就查询出来
  • ingxx 22天前
    引用 4
    可以考虑一下 Prometheus
  • 楼主 marine2c 22天前
    引用 5
    @ingxx 用的是 MySQL,要求用 sql 语句或存储过程
  • msaionyc 22天前
    引用 6
    每条订单记录,往前取三十分钟内的所有订单( ip 过滤),count,然后该分组就分组
  • lpts007 22天前
    引用 7
    @marine2c 谁要求的啊,是面试题吗

    1. 一秒一秒往前推,查 3600 次能把时间往前推 1h,后台查完另存起来,以后查这个新表。
    2. distinct ip,循环处理,同一个 ip 一条记录查一次半小内的数据 count, > 20 处理下一个 ip, 否则 下一条不同于上条时间的记录。后台查完另存起来,以后查这个新表。
  • 楼主 marine2c 22天前
    引用 8
    @lpts007 工作内容,方式 2 效率高点吧,一秒一秒推太多循环了吧
  • Jeyfang 22天前
    引用 9
    任意 30 分钟怎么理解,比如现在 15:00,那要查出 13:00 的,这 30 分钟是指 12:30-13:00 ?
  • 楼主 marine2c 22天前
    引用 10
    @Jeyfang 是的,就是任意时间点往后推 30 分钟嘛。
  • Jeyfang 22天前
    引用 11
    那就 6 楼的第 2 个方法吧。如果数据量比较少,直接全部取出来,然后自己计算。如果数据量大的话,是否可以以 1 分钟为单位,每次下单的时候,做一个统计,相当于在一个线性的轴上面记录,然后直接从轴上面统计
  • gogo789 22天前
    引用 12
    where 下单时间 between 开始时间 and 结束时间 group by ip having count > 20 ?
  • fx050622 22天前
    引用 13
    是不是可以理解为 下单之后连续 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 22天前
    引用 14
    @fx050622 你的 a,b 指的是同一张表吗?好像有点意思
  • fx050622 22天前
    引用 15
    @marine2c 是的,自关联一下
  • fish267 22天前
    引用 16
    起个调度,一直查呗,group by ip
  • caola 22天前
    引用 17
    按时间排序,每次拿 20 条记录( 1-20 ),下次拿 20 条( 2-21 ),
    比较一下这 20 条中最早的一条和最后的一条的间隔时间,是否在 30 分钟内的
  • xiaoxinshiwo 22天前
    引用 18
    ES
  • dobelee 22天前
    引用 19
    把订单异步索引到 es,想怎么查怎么查。
  • rrfeng 22天前
    引用 20
    写 SQL 难,做个 20 长的队列,遍历一遍就行。
  • LEFT 22天前
    引用 21
    有一个方法:核心是两次 row_num 加自关联

    传送门
    https://muguayuan.com/2020/16111.html
  • drrrtt 22天前
    引用 22
    自关联,group by ip,off set = 19
  • LEFT 22天前
    引用 23
    仅供参考
  • mmdsun 22天前
    引用 24
    先 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 22天前
    引用 25
    很难吗? where 下单时间 group by IP having count(1)>20 不就可以了吗?下单时间和 IP 要加索引。
  • PopRain 22天前
    引用 26
    你这个描述不够清晰,你是需要那种:
    1.用户指定一个 30 分钟的时间段,查出超过 20 条交易记录的 IP
    2.对历史数据进行统计,找出每 30 分钟发生过 20 比以上交易的 IP

    1:比较简单,时间过滤,group by+having
    2:可以以记录顺序倒序,做个子查询汇总当前记录前面 30 分钟的相同 ip 的数据,效率可能低一点,但是肯定可行。
  • ttys001 22天前
    引用 27
    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 22天前
    引用 28
    这个任意三十分钟是不是指的,当前已有数据和未来数据中,以每一单的时间(或分)往前(或后)推 30 分钟,有超过 20 的?

    流计算?
  • dzdh 22天前
    引用 29
    @dzdh 像令牌桶一样?
  • dusu 22天前
    引用 30
    这种需求单靠 sql 压根就是自讨苦吃

    窗口按时间移动的需求

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

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

    用户交易成功 incr 记录次数

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

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

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

    sql 去做这个事…想想都蛋疼
  • ashong 22天前
    引用 31
    @leeg810312 他这个是非固定时间,要查的是任意时间段内频繁下单,估计是避免恶意下单吧
  • 楼主 marine2c 22天前
    引用 32
    @dzdh 是的,理解没错
  • 楼主 marine2c 22天前
    引用 33
    @LEFT 谢谢,我看看
  • 楼主 marine2c 22天前
    引用 34
    @dusu 只用了 MySQL,蛋疼
  • Habyss 22天前
    引用 35
    查符合的 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 22天前
    引用 36
    假设你的 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 22天前
    引用 37
    @AntoniotheFuture 鉴于 ORDER BY 对性能影响较大,这里可以去掉
  • 楼主 marine2c 22天前
    引用 38
    @ttys001
    @Habyss
    @AntoniotheFuture
    感谢各位大佬提供的思路,自关联查询可以满足要求了
  • 游客
    39
返回