mysql 与 spark sql 语法大致相通。但是会有一些坑存在。比如 group by
·
需求场景:
在视屏表中,取每个作者最新的一条数据。
即, 筛选出 所有 user_id对应的 最新的(通过create_time 倒序取)一条数据。
·
mysql中:
select user_id,site,create_time
from ( select user_id, site, create_time
from table_video
where dt =20210909 and user_id is not null
order by create_time desc ) a
group by user_id
·
mysql --> Spark 产生异常:
但是, 同样的代码 进入 spark 就会报异常:
org.apache.spark.sql.AnalysisException:
expression 'a.`site`' is neither present in the group by, nor is it an aggregate function.
Add to group by or wrap in first() (or first_value)
·
网上搜索后得知:
mySQL 语句中少了一个分组, spark 中的sql用的是美式标准的sql。
说是需要将 查询的字段,都加在 group by 后面:
select user_id,site,create_time
from ( select user_id, site, create_time
from table_video
where dt =20210909 and user_id is not null
order by create_time desc ) a
group by user_id,site, create_time
但最终查询的结果并不满足我们场景需要的内容:
`
最终分组是根据 三列属性来分组,如果他们的数据个数分别是:k、m、n个,那查询的行数是 k*m*n个;
不符合我们期待的k个user_id对应的最新记录。
`
正确解决:
在spark中,site等 几列在 group by user_id 时,会有多个查询结果:
-
如果都需要,就获取其余几列对应的属性值集合: collect_set(site), collect_list(create_time) -- 前者去重,后者不去重
-
如果不需要全部,只是取随机一行里的属性值:first(site) 【不同于 MySQL 默认的第一行】
·
Spark中:
select user_id, first(site), first(create_time)
from ( select user_id, site, create_time from table_video
where dt =20210909 and user_id is not null
order by create_time desc ) a
group by user_id
·
Spark中复杂嵌套:
如果外部有嵌套,最好给几列属性,内部另起别名/或者 外部也得使用 dtpv.first(site) 。否则在外面 直接用 dtpv.site 会报错
select dta.user_id, dta.user_name, dtpv.site, dtpv.create_time as update_time
from tv_author AS dta
LEFT JOIN (
select user_id, first(site) as site, first(create_time) as create_time
from ( select user_id,site,create_time from table_video
where dt =20210909 and user_id is not null
order by create_time desc ) a
group by user_id
) AS dtpv
on dtpv.user_id = dta.user_id
where dta.dt = 20210909