1.SR中unnest实现列转行
Hive中可以使用lateral view explode实现行转列,比如该sql:
select a.date_list,b.dt
from (
select split('20240201-20240202-20240203-20240204-20240205','-') as date_list
)a
lateral view explode(date_list) b as dt
limit 10
结果如下:
SR中不支持 lateral view 操作, 可使用unnest函数,sql转换如下:
select a.date_list
,unnest as dt
from (
select split('20240201-20240202-20240203-20240204-20240205','-') as date_list
)a,unnest(date_list)
结果如下:
2.SR中grouping+rollup
Mysql中支持group by 字段 with rollup操作,可以实现分组汇总及整体汇总;SR支持Mysql的常规语法操作,使用该聚合函数时可用 grouping + rollup实现。
参考代码如下:
select
case when grouping(province)=1 then '全部' else province end as province
,count(1) as city_cnt
from (
select 'Henan' as province,'Zhengzhou' as city
union all
select 'Henan' as province,'KaiFeng' as city
union all
select 'Henan' as province,'LuoYang' as city
union all
select 'Anhui' as province,'Hefei' as city
union all
select 'Anhui' as province,'Wuhu' as city
union all
select 'Anhui' as province,'Fuyang' as city
)t
group by ROLLUP(province)
结果如下:
3.SR中常用的日期函数
select
date_trunc('month','20240207') as month_first -- 月初 YYYY-MM-DD HH:MM:SS
,date(date_trunc('month','20240207')) as month_first_day -- 月初 YYYY-MM-DD
,DAYOFWEEK('20240207') as day_of_week-- 指定日期为当周第几天 -- 周日为第一天
,'20240207' + interval 1 hour as date_next1hour
,'20240207' + interval 1 day as date_next1day
,'20240207' + interval 1 week as date_next1week
,'20240207' + interval 1 month as date_nextmonth1 -- 指定日期的下月日期 YYYY-MM-DD HH:MM:SS
,date_format('20240207' + interval 1 month,'%Y%m%d') as date_nextmonth2 -- 指定日期的下月日期 YYYY-MM-DD
,date(DATE_sub('20240207', DAYOFWEEK('20240207')-2)) as Monday
,date(DATE_sub('20240207', 0-DAYOFWEEK('20240207'))) as Sunday
,datediff('20240207','20240205') as date_diff1 -- 日期差 int
,TIMESTAMPDIFF(day, '20240205', '20240207') as date_diff2
,TIMESTAMPDIFF(hour, '20240205', '20240207') as date_diff3
,TIMESTAMPDIFF(minute, '20240205', '20240207') as date_diff4
,TIMESTAMPDIFF(second, '20240205', '20240207') as date_diff5
,DATE_FORMAT(DATE_sub(concat(SUBSTR('202403',1,4),'0101'), INTERVAL DAYOFWEEK(CONCAT(SUBSTR('202403',1,4), '0101')) - 2 DAY)+ INTERVAL SUBSTR('202403',5,2)-1 WEEK, '%Y%m%d') as week_start -- 获取指定年度第几周的周一
limit 10
结果如下: