事情是这样的,最近领导给了一个新的需求,要求在一张订单表中统计每个人第一次和第二次购买的时间间隔,最后还需要按照间隔统计计数,求出中位数等数据。
- 由于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