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

淡淡的烟草味﹌ 2022-03-20 14:12 319阅读 0赞

###### 需求描述 ######

`主要是 利用 GROUP_CONCAT 和 SUBSTRING_INDEX实现`

例如: 订单表一张, 只保留关键字段 id user\_id money create\_time 1 1 50 1420520000 2 1 100 1420520010 3 2 100 1420520020 4 2 200 1420520030

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

修改思路如下 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

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

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

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 ;

##### 函数详解 #####

###### GROUP\_CONCAT ######

`语法:group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator ‘分隔符’])`

例子

查询出 语数外三门课的最低分，还有哪个学生考的
    select GROUP_CONCAT(stuName ORDER BY score ASC),min(score) as score,course from grade1 group by  course; --score分数 course学科名称 stuName 学生名字

###### SUBSTRING\_INDEX ######

`从左开始截取字符串`

left（str, length） 
    说明：left（被截取字段，截取长度） 
    例：select left（content,200） as abstract from my_content_t

`从右开始截取字符串`

right（str, length） 
    说明：right（被截取字段，截取长度） 
    例：select right（content,200） as abstract from my_content_t

`截取字符串`

substring（str, pos） 
    substring（str, pos, length） 
    说明：substring（被截取字段，从第几位开始截取） 
    substring（被截取字段，从第几位开始截取，截取长度） 
    例：select substring（content,5） as abstract from my_content_t 
    select substring（content,5,200） as abstract from my_content_t 
    （注：如果位数是负数 如-5 则是从后倒数位数，到字符串结束或截取的长度）

`按关键字截取字符串`

substring_index（str,delim,count） 
    说明：substring_index（被截取字段，关键字，关键字出现的次数） 
    例：select substring_index（"blog.jb51.net"，"。"，2） as abstract from my_content_t 
    结果：blog.jb51 
    （注：如果关键字出现的次数是负数 如-2 则是从后倒数，到字符串结束）