MySQL 在按照某个字段分组、排序加序号

事情是这样的，最近领导给了一个新的需求，要求在一张订单表中统计每个人第一次和第二次购买的时间间隔，最后还需要按照间隔统计计数，求出中位数等数据。

• 由于 MySQL 不想 Oracle 那般支持行号、中位数等，所以怎么在表中统计处数据成为了关键

订单表结构，主要包含字段如下

• id、订单号、购买人 id、下单时间、商品信息字段、购买人信息字段等

1.为了方便后续统计，我的想法是构建了一张中间表，只存储一些关键字段，如购买人 id，下单时间，订单号，以及购买的第几次，结构如下图：

字段解释：fans_id：购买人 id、order_time:下单时间、tid：订单号、series：商品系列、shop：店铺、times:第几次购买、sync_time：同步时间、effective：是否有效、failure_time：失效时间

2.写了一段代码，处理历史订单，把所有数据按照表中格式添加进去，方便统计，每次新订单进来时，更新一下这个表即可。

3.统计：

-- 统计购买次数最大和最小selectmaxfromwhere'有效'-- 统计最大购买次数间隔、最小间隔以及平均间隔（中位数的话，由于MySQL没有中位数函数，所以可以利用子查询的SQL通过程序代码计算）SELECTmaxdateasmaxmindateasminsumdatecountasavgFROMSELECTifnulldatediffSELECTFROMWHERE1AND'有效'AND0ASdate1ASFROMWHERE2AND'有效'

4.由于接收订单后，可能状态会变，无法确保次数准确，更新统计表中每个人的次数 SQL 如下：

UPDATESELECTrownumrownum1asidfromSELECTidfromwhereand'有效'ORDERBYascSELECTrownum0setwhere

5.由于需求还需要支持按照商品系列查询，所以需要在该表基础之上建立临时表以作统计，满足 MySQL 在按照某个字段分组、排序加序号

第一版 SQL 如下：

SELECTcountASFROMASASWHEREANDAND'有效'AND'有效'ANDLIKEconcat'%''系列''%'ANDLIKEconcat'%''系列''%'GROUPBY-- 按照购买人id，按照购买时间进行排序，并标记序号，加上创建表语句如下（建表时需加索引，方便后续查找）：CREATETABLEidINTKEYVARCHAR32dateINT6KEYidINDEXUSINGINDEXUSINGINDEXUSINGINDEXUSINGASSELECTcountASFROMASASWHEREANDAND'有效'AND'有效'ANDLIKEconcat'%''系列''%'ANDLIKEconcat'%''系列''%'GROUPBY-- 由于数据库版本为5.4，所以建完临时表不支持一条sql多次查询，没办法，只能直接创建表

结果如图：

满足了排序，但是后来我发现有一些人是同时间下单的，以至于某些人的 times 是重复的，于是更新为下面的 SQL

SELECTCASEWHENTHEN1ELSE1ENDASFROMSELECTFROMWHERE'有效'ANDLIKEconcat'%''系列''%'ORDERBYSELECT0''AS

这次的 sql 是按照时间排序后，判断当前购买人第几次出现，打上序号，由此满足需求

查询结果和上图相同，就不附图了哈

效率这，购买人 id，下单时间需要创建索引，否则可能有些慢，测试库中数据大概七百万左右，总体查询可在四秒内完成

希望这篇文章能在开发中给予您一定的帮助，新人博客主，码龄一年，如有更好的方案，望指教！

看完三件事❤️

如果你觉得这篇内容对你还蛮有帮助，我想邀请你帮我三个小忙：

1. 点赞，转发，有你们的 『点赞和评论』，才是我创造的动力。

2. 关注公众号 『 java 烂猪皮 』，不定期分享原创知识。

3. 同时可以期待后续文章 ing🚀

4. .关注后回复【666】扫码即可获取学习资料包

作者：而已~

出处：https://www.cnblogs.com/fqliu/p/14254701.html?utm_source=tuicool&utm_medium=referral