StarRocks中sql常用的一些函数

最新推荐文章于 2024-07-09 10:02:34 发布

ZHC_1015

最新推荐文章于 2024-07-09 10:02:34 发布

阅读量1.8k

点赞数 15

分类专栏： StarRocks 文章标签： sql 数据库

本文链接：https://blog.csdn.net/ZHC_1015/article/details/136060880

版权

StarRocks 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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

结果如下：
在这里插入图片描述