现有表结构 t_order,里面有2个字段
orderid 订单编号 int 自增长主键
userid 用户编号 int
createtime 创建时间 datetime
每天大概30万条订单。如何快速获取近10天,每天最后5笔订单,不想用游标,
列出最近5天内有订单的用户,并列出最后5笔订单信息
----获取近10天,每天最后5笔订单(createtime的长度为10,不是的需要转换)
--分组倒排订单
WITH _A AS (
SELECT ROW_NUMBER() OVER(PARTITION BY createtime ORDER BY createtime DESC) num,orderid
FROM t_order
WHERE datediff(d, createtime, getdate()) <= 10
)
--SELECT num,orderid FROM _A b WHERE b.num <= 5
SELECT * FROM t_order a
WHERE EXISTS(SELECT orderid FROM _A b
WHERE a.orderid = b.orderid AND b.num <= 5);
----最近5天内有订单的用户,及最后5笔订单信息
--连续几天下单用户
WITH _A AS (
SELECT CONVERT(VARCHAR(10),createtime,111) d,userid FROM t_order
WHERE DATEDIFF(d, createtime, GETDATE()) <=5
GROUP BY CONVERT(VARCHAR(10),createtime,111),userid
),
--根据连续下单用户,分组倒排订单
_B AS (
SELECT ROW_NUMBER() OVER(PARTITION BY createtime ORDER BY createtime DESC) num,orderid
FROM t_order a
WHERE datediff(d, createtime, getdate()) <=5
AND EXISTS ( SELECT userid FROM _A b
WHERE a.userid = b.userid
GROUP BY userid
having count(d)>=5 )
)
--WITH _A AS (
-- SELECT userid,createtime,LEAD(createtime,4) OVER (PARTITION BY userid ORDER BY createtime) lastime
-- FROM t_order
-- WHERE DATEDIFF(d, createtime, GETDATE()) <=5
--),
--_B AS (
-- SELECT ROW_NUMBER() OVER(PARTITION BY createtime ORDER BY createtime DESC) num,orderid
-- FROM t_order a
-- WHERE DATEDIFF(d, createtime, GETDATE()) <=5
-- AND EXISTS ( SELECT userid FROM _A b
-- WHERE a.userid = b.userid
-- AND DATEDIFF(d, b.lastime, b.createtime) <=5)
--)
SELECT * FROM t_order a
WHERE EXISTS(SELECT orderid FROM _B b
WHERE a.orderid = b.orderid AND b.num <= 5);
看看有没有问题,看着调整。
速度慢的话,用注释的试一试。再者可以尝试把WITH语句换成临时表或子查询,或者换种思路解决。