复杂业务查询

一、计算出订单在哪个时间区间(以区间进行分组)

CEIL(TIMESTAMPDIFF(SECOND,'2019-05-10 00:00:00',creatTime)/20) num

1、查询下单频率过快  （一段时间内范围内，每间隔时间t秒，订单数量超出n单则为异常单）

（2）以时间区间分组

SELECT 
d.`no` FROM o_delivery d
INNER JOIN
(
    SELECT 
    CEIL(TIMESTAMPDIFF(SECOND,'2019-05-10 00:00:00',creatTime)/20) num
    FROM o_delivery d
    WHERE .creatTime>=#{beginTime} AND d.creatTime<#{endTime}
    GROUP BY num HAVING COUNT(*)>3
)a ON CEIL(TIMESTAMPDIFF(SECOND,'2019-05-10 00:00:00',d.creatTime)/20)=a.num
WHERE d.creatTime>=#{beginTime} AND d.creatTime<#{endTime}