一波三折的mysql分组排序和联表更新

1.这几天一直在做怎么将一张表里的内容分组后取出更新时间最新的一条然后更新到另一个表内的某些字段上。
mysql不支持row_number()over()函数很令人头疼,最终各种曲线救国。找了好几个方法最终找到这个方法还是比较合适的。

select 
  lower(substring_index(table_name,'.',1)) table_db,
  lower(substring_index(table_name,'.',-1))  table_name,
  sql_query,
  start_time,
  finish_time,
  maps_total,
  reduces_total,
  consume_time
from
(
select 
  H.table_name,
  H.sql_query,
  H.start_time,
  H.finish_time,
  H.maps_total,
  H.reduces_total,
  H.consume_time,
  @rownum:=@rownum+1 rownum,
  if(@pdept=H.table_name,@rank:=@rank+1,@rank:=1) as rank,
  @pdept:=H.table_name
from (
select 
table_name,sql_query,start_time,finish_time,maps_total,reduces_total, consume_time
from 
(select  table_name,sql_query,min(start_time)start_time,max(finish_time)finish_time,sum(maps_total)maps_total,sum(reduces_total)reduces_total,sum(consume_time) consume_time
from t_yarn_jobs where
 start_time>='2019-11-13 00:00:00'
 and start_time<='2019-11-13 23:59:59'
and state='SUCCEEDED'
and table_name not like '%查询%' 
group by table_name,sql_query 
union all 
select  table_name,sql_query,min(start_time)start_time,max(finish_time)finish_time,null maps_total,null reduces_total, sum(consume_time) consume_time
from t_yarn_spark_applications where
 start_time>='2019-11-13 00:00:00'
 and start_time<='2019-11-13 23:59:59'
and table_name not like '%查询%'
group by table_name,sql_query 
)t0
ORDER BY table_name,start_time  DESC 
) H ,(select @rownum :=0 , @pdept := null ,@rank:=0) a
)result where rank=1

一开始使用拼接的方式来解决分组取一个的问题.

UPDATE t_hive_table_info tt
left JOIN (select
 table_db,
  table_name,
substring_index(infos,'@@22@@',1) start_time,
substring_index(substring_index(infos,'@@22@@',-5),'@@22@@',1) sql_query,
substring_index(substring_index(infos,'@@22@@',-4),'@@22@@',1) finish_time,
substring_index(substring_index(infos,'@@22@@',-3),'@@22@@',1) maps_total,
substring_index(substring_index(infos,'@@22@@',-2),'@@22@@',1) reduces_total,
substring_index(infos,'@@22@@',-1) consume_time
from
(
select lower(substring_index(table_name,'.',1)) table_db,
lower(substring_index(table_name,'.',-1))  table_name,max(CONCAT_WS('@@22@@',start_time,sql_query,finish_time,maps_total,reduces_total,consume_time)) infos from (
select  table_name,sql_query,min(start_time)start_time,max(finish_time)finish_time,sum(maps_total)maps_total,sum(reduces_total)reduces_total,sum(consume_time) consume_time
from t_yarn_jobs where
 start_time>='${dt_1} 00:00:00'
 and start_time<='${dt} 23:59:59'
and state='SUCCEEDED'
and table_name not like '%查询%'
group by table_name,sql_query ) as tt1
GROUP BY lower(substring_index(table_name,'.',1)) ,
lower(substring_index(table_name,'.',-1))
)tt3
union all
select
 table_db,
  table_name,
substring_index(infos,'@@22@@',1) start_time,
substring_index(substring_index(infos,'@@22@@',-3),'@@22@@',1) sql_query,
substring_index(substring_index(infos,'@@22@@',-2),'@@22@@',1) finish_time,
null maps_total,
null reduces_total,
substring_index(infos,'@@22@@',-1) consume_time
from
(
select lower(substring_index(table_name,'.',1)) table_db,
lower(substring_index(table_name,'.',-1))  table_name,max(CONCAT_WS('@@22@@',start_time,sql_query,finish_time,consume_time)) infos from (
select  table_name,sql_query,min(start_time)start_time,max(finish_time)finish_time,sum(consume_time) consume_time
from t_yarn_spark_applications where
 start_time>='${dt_1} 00:00:00'
 and start_time<='${dt} 23:59:59'
and table_name not like '%查询%'
group by table_name,sql_query ) as tt1
GROUP BY lower(substring_index(table_name,'.',1)) ,
lower(substring_index(table_name,'.',-1))
)tt3
)tt4
ON tt.table_db=tt4.table_db  and tt.table_name= tt4.table_name
SET tt.insert_sql = tt4.sql_query,
tt.map_nums=tt4.maps_total,
tt.reduce_nums=tt4.reduces_total,
tt.time_grading=tt4.consume_time,
tt.start_time=tt4.start_time,
tt.finish_time=tt4.finish_time

本来以为很好的解决了,最后发现 left join去联表更新的时候会把所有的表更新了。假如我tt表原先已经有1000个已经更跟过了,tt4表里需要更新的只有41条记录结果原来的1000个也被无辜的更新了。所以联表更新update还必须使用inner join 去解决。
使用inner join 后数据库一直报错,一开始以为是mysql版本的问题，后来通过一点点查找,发现tt4做出来的临时表内的sql_query字段即sql语句里的有中文的全部乱码了,导致更新的时候报错如下 Error : Can't find record in t_hive_table_info
。最终还是放弃了拼接的方式来解决分组的问题。

UPDATE t_hive_table_info tt
 JOIN (
select 
  lower(substring_index(table_name,'.',1)) table_db,
  lower(substring_index(table_name,'.',-1))  table_name,
  sql_query,
  start_time,
  finish_time,
  maps_total,
  reduces_total,
  consume_time
from
(
select 
  H.table_name,
  H.sql_query,
  H.start_time,
  H.finish_time,
  H.maps_total,
  H.reduces_total,
  H.consume_time,
  @rownum:=@rownum+1 rownum,
  if(@pdept=H.table_name,@rank:=@rank+1,@rank:=1) as rank,
  @pdept:=H.table_name
from (
select 
table_name,sql_query,start_time,finish_time,maps_total,reduces_total, consume_time
from 
(select  table_name,sql_query,min(start_time)start_time,max(finish_time)finish_time,sum(maps_total)maps_total,sum(reduces_total)reduces_total,sum(consume_time) consume_time
from t_yarn_jobs where
 start_time>='2019-11-13 00:00:00'
 and start_time<='2019-11-13 23:59:59'
and state='SUCCEEDED'
and table_name not like '%查询%' 
group by table_name,sql_query 
union all 
select  table_name,sql_query,min(start_time)start_time,max(finish_time)finish_time,0 maps_total,0 reduces_total, sum(consume_time) consume_time
from t_yarn_spark_applications where
 start_time>='2019-11-13 00:00:00'
 and start_time<='2019-11-13 23:59:59'
and table_name not like '%查询%'
group by table_name,sql_query 
)t0
ORDER BY table_name,start_time  DESC 
) H ,(select @rownum :=0 , @pdept := null ,@rank:=0) a
)result where rank=1  limit 1235-- and table_name like '%adm_activity_dau_account_day%'
)tt4
ON tt.table_db=tt4.table_db  and tt.table_name= tt4.table_name 
SET 
tt.insert_sql = tt4.sql_query,
tt.map_nums=tt4.maps_total,
tt.reduce_nums=tt4.reduces_total,
tt.time_grading=tt4.consume_time,
tt.start_time=tt4.start_time,
tt.finish_time=tt4.finish_time,

一点一滴

一波三折的mysql分组排序和联表更新

没有评论