(1)建表、查看表、删除表、向某个分区插入数据
create table if not exists table_name;
desc table_name;
drop table if exists table_name;
insert into behavior_log_basic_100291674 partition(dt='20190705',ts='5') values ();
(2)查看分区
Show partitions table_name;
(3)拆分一行数据变成多行
trans_array(4, ',', cookie, ideaid, tag, device, videopref) as (cookie, ideaid, tag, device, videopref_split);
注:不变的key值有4个,(前四个, cookie, ideaid, tag, device,以‘,’为分隔符对剩下的字段内部进行分割,这里是videopref) 转换过后为:
cookie, ideaid, tag, device, videopref1
cookie, ideaid, tag, device, videopref2
cookie, ideaid, tag, device, videopref3
(4)去掉字符串的前导空格、后面空格
返回不带前导空格(LTrim)、后续空格 (RTrim) 或前导与后续空格 (Trim) 的字符串副本。
LTrim(string)
RTrim(string)
Trim(string)
(5)NVL(表达式1,表达式2)
如果表达式1为空值,NVL返回值为表达式2的值,否则返回表达式1的值。 该函数的目的是把一个空值(null)转换成一个实际的值
(6)分组排序取top n
select course, score from
(
select course, score, row_number() over(partition by cource order by score desc) as n
form lesson
) t1
where t1.n <= 2;
row_number() over(partition by cource order by score desc意思是以课程分组,按照成绩排序,并为每组中的数据打上行号标记,从1开始。
(7)替换某个特殊字符
regexp_replace(query,'\n','')
将query字段中的换行符替换成空格、
(8)排序
Order by 。。。默认升序,desc为降序
(9)模糊查询
Where column like '%word%'
(10)修改表名
alter table name rename to new_name;
(11)删除表的子分区
alter table DG_query_log_on_odps_transfer drop partition (dt=20190725);
(12)多条件语句
select prediction_score,
(CASE WHEN prediction_score>=0.6 THEN 2
WHEN prediction_score<=0.6 and prediction_score>0.5 THEN 1
ELSE 0 END) as level
from table;
(13)instr(title, "query")
在一个字符串中搜索指定的字符。返回发现指定的字符的位置,没找到则返回0。
(14)split_part(字段,分隔符,part)
(15)多行合并成一行
concat_ws(' ', str1,str2)连接两个字符,可以一次性指定分隔符
concat(str1,str2)如果有任何一个为null,则返回null