Mysql 分组聚合实现 over partition by 功能

太过爱你忘了你带给我的痛 2022-01-09 08:53 277阅读 0赞

本文转自 https://www.cnblogs.com/zhwbqd/p/4205821.html

mysql中没有类似oracle和postgreSQL的 OVER(PARTITION BY)功能. 那么如何在MYSQL中搞定分组聚合的查询呢

先说结论: 利用 group\_concat + substr等函数处理

例如: 订单表一张, 只保留关键字段

<table> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>user_id</td> 
   <td>money</td> 
   <td>create_time</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>1</td> 
   <td>50</td> 
   <td>1420520000</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>1</td> 
   <td>100</td> 
   <td>1420520010</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>2</td> 
   <td>100</td> 
   <td>1420520020</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>2</td> 
   <td>200</td> 
   <td>1420520030</td> 
  </tr> 
 </tbody> 
</table>

业务: 查找每个用户的最近一笔消费金额

单纯使用group by user\_id, 只能按user\_id 将money进行聚合, 是无法将最近一单的金额筛选出来的, 只能满足这些需求, 例如:　每个用户的总消费金额 sum(money), 最大消费金额 max(money), 消费次数count(1) 等

但是我们有一个group\_concat可以用, 思路如下:

1. 查找出符合条件的记录, 按user\_id asc, create\_time desc 排序;

select ord.user_id, ord.money, ord.create_time from orders ord where ord.user_id > 0 and create_time > 0 order by ord.user_id asc , ord.create_time desc

<table> 
 <tbody> 
  <tr> 
   <td>user_id</td> 
   <td>money</td> 
   <td>create_time</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>100</td> 
   <td>1420520010</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>50</td> 
   <td>1420520000</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>200</td> 
   <td>1420520030</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>100</td> 
   <td>1420520020</td> 
  </tr> 
 </tbody> 
</table>

2. 将(1)中记录按user\_id分组, group\_concat(money);

select t.user_id, group_concat( t.money order by t.create_time desc ) moneys from (select ord.user_id, ord.money, ord.create_time from orders ord where ord.user_id > 0 and ord.create_time > 0 order by ord.user_id asc , ord.create_time desc) t group by t.user_id

<table> 
 <tbody> 
  <tr> 
   <td>user_id</td> 
   <td>moneys</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>100,50</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>200,100</td> 
  </tr> 
 </tbody> 
</table>

3. 这时, 如果用户有多个消费记录, 就会按照时间顺序排列好, 再利用 subString\_index 函数进行切分即可

完整SQL, 注意group\_concat的内排序, 否则顺序不保证, 拿到的就不一定是第一个了

select t.user_id, substring_index(group_concat( t.money order by t.create_time desc ),',',1) lastest_money from (select ord.user_id, ord.money, ord.create_time from orders ord where ord.user_id > 0 and create_time > 0 order by user_id asc , create_time desc) t group by user_id ;

<table> 
 <tbody> 
  <tr> 
   <td>user_id</td> 
   <td>moneys</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>100</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>200</td> 
  </tr> 
 </tbody> 
</table>

利用这个方案, 以下类似业务需求都可以这么做, 如:

1. 查找每个用户过去10个的登陆IP

2. 查找每个班级中总分最高的两个人

补充: 如果是只找出一行记录, 则可以直接只用聚合函数来进行

select t.user_id, t.money  from (select ord.user_id, ord.money, ord.create_time from orders ord where ord.user_id > 0 and create_time > 0 order by user_id asc , create_time desc) t group by user_id ;

前提一定是(1) 只需要一行数据, (2) 子查询中已排好序, (3) mysql关闭 strict-mode

参考资料:

http://dev.mysql.com/doc/refman/5.0/en/sql-mode.html\#sql-mode-strict

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html\#function\_group-concat

转载于:https://www.cnblogs.com/bionexit/p/7823314.html