MYSQL 订单查询求助

• 資深大佬 : Latin

  这个任意很模糊，时间不能关联查询了吗？

• 主 資深大佬 : marine2c

  @Latin 不能，时间和其他没有任何关系啊，就是只要你在任意 30 分钟间隔内下单超过 20 次就查询出来

• 資深大佬 : ingxx

  可以考虑一下 Prometheus

• 主 資深大佬 : marine2c

  @ingxx 用的是 MySQL，要求用 sql 语句或存储过程

• 資深大佬 : msaionyc

  每条订单记录，往前取三十分钟内的所有订单（ ip 过滤），count，然后该分组就分组

• 資深大佬 : lpts007

  @marine2c 谁要求的啊，是面试题吗

  1. 一秒一秒往前推，查 3600 次能把时间往前推 1h，后台查完另存起来，以后查这个新表。
  2. distinct ip，循环处理，同一个 ip 一条记录查一次半小内的数据 count， > 20 处理下一个 ip， 否则 下一条不同于上条时间的记录。后台查完另存起来，以后查这个新表。

• 主 資深大佬 : marine2c

  @lpts007 工作内容，方式 2 效率高点吧，一秒一秒推太多循环了吧

• 資深大佬 : Jeyfang

  任意 30 分钟怎么理解，比如现在 15:00，那要查出 13:00 的，这 30 分钟是指 12:30-13:00 ？

• 主 資深大佬 : marine2c

  @Jeyfang 是的，就是任意时间点往后推 30 分钟嘛。

• 資深大佬 : Jeyfang

  那就 6 的第 2 个方法吧。如果数据量比较少，直接全部取出来，然后自己计算。如果数据量大的话，是否可以以 1 分钟为单位，每次下单的时候，做一个统计，相当于在一个线性的轴上面记录，然后直接从轴上面统计

• 資深大佬 : gogo789

  where 下单时间 between 开始时间 and 结束时间 group by ip having count > 20 ?

• 資深大佬 : fx050622

  是不是可以理解为 下单之后连续 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

  @fx050622 你的 a，b 指的是同一张表吗？好像有点意思

• 資深大佬 : fx050622

  @marine2c 是的，自关联一下

• 資深大佬 : fish267

  起个调度，一直查呗，group by ip

• 資深大佬 : caola

  按时间排序，每次拿 20 条记录（ 1-20 ），下次拿 20 条（ 2-21 ），
  比较一下这 20 条中最早的一条和最后的一条的间隔时间，是否在 30 分钟内的

• 資深大佬 : xiaoxinshiwo

  ES

• 資深大佬 : dobelee

  把订单异步索引到 es，想怎么查怎么查。

• 資深大佬 : rrfeng

  写 SQL 难，做个 20 长的队列，遍历一遍就行。

• 資深大佬 : LEFT

  有一个方法：核心是两次 row_num 加自关联

  传送门
  https://muguayuan.com/2020/16111.html

• 資深大佬 : drrrtt

  自关联，group by ip，off set = 19

• 資深大佬 : LEFT

  仅供参考

• 資深大佬 : mmdsun

  先 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

  很难吗？ where 下单时间 group by IP having count(1)>20 不就可以了吗？下单时间和 IP 要加索引。

• 資深大佬 : PopRain

  你这个描述不够清晰，你是需要那种：
  1.用户指定一个 30 分钟的时间段，查出超过 20 条交易记录的 IP
  2.对历史数据进行统计，找出每 30 分钟发生过 20 比以上交易的 IP

  1：比较简单，时间过滤，group by+having
  2：可以以记录顺序倒序，做个子查询汇总当前记录前面 30 分钟的相同 ip 的数据，效率可能低一点，但是肯定可行。

• 資深大佬 : ttys001

  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

  这个任意三十分钟是不是指的，当前已有数据和未来数据中，以每一单的时间（或分）往前（或后）推 30 分钟，有超过 20 的？

  流计算？

• 資深大佬 : dzdh

  @dzdh 像令牌桶一样？

• 資深大佬 : dusu

  这种需求单靠 sql 压根就是自讨苦吃

  窗口按时间移动的需求

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

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

  用户交易成功 incr 记录次数

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

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

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

  sql 去做这个事…想想都蛋疼

• 資深大佬 : ashong

  @leeg810312 他这个是非固定时间，要查的是任意时间段内频繁下单，估计是避免恶意下单吧

• 主 資深大佬 : marine2c

  @dzdh 是的，理解没错

• 主 資深大佬 : marine2c

  @LEFT 谢谢，我看看

• 主 資深大佬 : marine2c

  @dusu 只用了 MySQL，蛋疼

• 資深大佬 : Habyss

  查符合的 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

  假设你的 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

  @AntoniotheFuture 鉴于 ORDER BY 对性能影响较大，这里可以去掉

• 主 資深大佬 : marine2c

  @ttys001
  @Habyss
  @AntoniotheFuture
  感谢各位大佬提供的思路，自关联查询可以满足要求了