MySQL在按照某个字段分组、排序加序号
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL在按照某个字段分组、排序加序号，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3612字，纯文字阅读大概需要6分钟。
内容图文
事情是这样的，最近领导给了一个新的需求，要求在一张订单表中统计每个人第一次和第二次购买的时间间隔，最后还需要按照间隔统计计数，求出中位数等数据。
- 由于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
这次的sql是按照时间排序后，判断当前购买人第几次出现，打上序号，由此满足需求
查询结果和上图相同，就不附图了哈
效率这，购买人id，下单时间需要创建索引，否则可能有些慢，测试库中数据大概七百万左右，总体查询可在四秒内完成
希望这篇文章能在开发中给予您一定的帮助，新人博客主，码龄一年，如有更好的方案，望指教！
内容总结
以上是互联网集市为您收集整理的MySQL在按照某个字段分组、排序加序号全部内容，希望文章能够帮你解决MySQL在按照某个字段分组、排序加序号所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】