事情是这样的，最近领导给了一个新的需求，要求在一张订单表中统计每个人第一次和第二次购买的时间间隔，最后还需要按照间隔统计计数，求出中位数等数据。
- 由于MySQL不想Oracle那般支持行号、中位数等，所以怎么在表中统计处数据成为了关键
订单表结构，主要包含字段如下
- id、订单号、购买人id、下单时间、商品信息字段、购买人信息字段等
1.为了方便后续统计，我的想法是构建了一张中间表，只存储一些关键字段，如购买人id，下单时间，订单号，以及购买的第几次，结构如下图：
字段解释：fans_id：购买人id、order_time:下单时间、tid：订单号、series：商品系列、shop：店铺、times:第几次购买、sync_time：同步时间、effective：是否有效、failure_time：失效时间
2.写了一段代码，处理历史订单，把所有数据按照表中格式添加进去，方便统计，每次新订单进来时，更新一下这个表即可。
3.统计：
-- 统计购买次数最大和最小 select max(times) from 统计表 where effective = '有效' -- 统计最大购买次数间隔、最小间隔以及平均间隔（中位数的话，由于MySQL没有中位数函数，所以可以利用子查询的SQL通过程序代码计算） SELECT max(date) as max, min(date) as min, sum( date * mans ) / count( mans ) as avg FROM ( SELECT ifnull(datediff( a.order_time, ( SELECT order_time FROM 统计表 WHERE times = 次数1 AND effective = '有效' AND a.fans_id = fans_id ) ),0) AS date, a.fans_id, 1 AS mans FROM 统计表 a WHERE a.times = 次数2 AND effective = '有效' ) t
4.由于接收订单后，可能状态会变，无法确保次数准确，更新统计表中每个人的次数SQL如下：
UPDATE (SELECT @rownum:=@rownum+1 as rn,id,fans_id,order_time from (SELECT id,fans_id,order_time from 统计表 where fans_id = 购买人 and effective = '有效' ORDER BY order_time asc) h, (SELECT @rownum:=0) t) t1, statistics_repurchase t2 set t2.times=t1.rn where t2.id=t1.id;
5.由于需求还需要支持按照商品系列查询，所以需要在该表基础之上建立临时表以作统计，满足MySQL在按照某个字段分组、排序加序号
第一版SQL如下：
SELECT a.fans_id, a.order_time, a.sync_time, count( * ) AS times FROM 统计表 AS a, 统计表 AS b WHERE a.fans_id = b.fans_id AND a.order_time >= b.order_time AND a.effective = '有效' AND b.effective = '有效' AND a.series LIKE concat('%','系列','%') AND b.series LIKE concat('%','系列','%') GROUP BY a.fans_id, a.id -- 按照购买人id，按照购买时间进行排序，并标记序号，加上创建表语句如下（建表时需加索引，方便后续查找）： CREATE TABLE 临时表名 ( id INT PRIMARY KEY AUTO_INCREMENT, fans_id VARCHAR ( 32 ), order_time datetime, sync_time date, times INT ( 6 ), PRIMARY KEY ( id ), INDEX mid_fans_id ( fans_id ) USING BTREE, INDEX mid_order_time ( order_time ) USING BTREE, INDEX mid_times ( times ) USING BTREE, INDEX mid_sync_time ( sync_time ) USING BTREE ) AS ( SELECT a.fans_id, a.order_time, a.sync_time, count( * ) AS times FROM 统计表 AS a, 统计表 AS b WHERE a.fans_id = b.fans_id AND a.order_time >= b.order_time AND a.effective = '有效' AND b.effective = '有效' AND a.series LIKE concat('%','系列','%') AND b.series LIKE concat('%','系列','%') GROUP BY a.fans_id, a.id ); -- 由于数据库版本为5.4，所以建完临时表不支持一条sql多次查询，没办法，只能直接创建表
结果如图：
满足了排序，但是后来我发现有一些人是同时间下单的，以至于某些人的times是重复的，于是更新为下面的SQL
SELECT a.fans_id, a.order_time, a.sync_time, ( @i := CASE WHEN @pre_keyword = fans_id THEN @i + 1 ELSE 1 END ) AS times, @pre_keyword:=fans_id FROM ( SELECT fans_id, order_time, sync_time FROM 统计表 WHERE effective = '有效' AND series LIKE concat('%','系列','%') ORDER BY fans_id,order_time ) a, ( SELECT @i := 0, @pre_keyword := '' ) AS b