概念
中间表:是在业务逻辑上定义为中间表,只是这样命名,其实它与普通表是一样的,在业务逻辑计算时起到间接桥梁的作用
内表与外表
ALTER TABLE login DROP IF EXISTS PARTITION (dt=‘2008-08-08’);
Tips
where里面不能放split
distinct与group by
- https://zhuanlan.zhihu.com/p/78881618
- https://www.cnblogs.com/rrttp/p/9026359.html
基本数据类型
-- 类型转换
-- 将把字符串'1' 转换成整数 1
CAST('1' AS INT)
-- 如果强制类型转换失败,如执行下列语句,表达式返回空值 NULL
CAST('X' AS INT)
字符串处理
-- 字符串编码解码
url_decode(orig_str, 'utf-8')
-- 字符串拼接函数
concat_ws()
-- 字符串查找函数
regexp_extract(orig_str, 'pattern', 'index')
-- 字符串替换函数
regexp_replace(orig_str, 'pattern', 'replacement')
时间处理函数
DATEDIFF(${end_time}, date_)
day(to_date('{date}', 'YYYYMMDD'))
基础运算与逻辑
AVG()
COUNT()
SUM()
MAX()
MIN()
STD()
-- hql 实现自增id
row_number() over (order by tbl_stg.id)
ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn
ROW_NUMBER() OVER ( [query_partition_clause] order_by_clause )
返回每个分区内按照某些表达式排序后的行号,从1开始,为每条分组记录返回一个数字。NULL值被视为最小值。
逻辑语句
-- 如果bool为True,则取expr1,否则取expr2
if(bool , expr1, expr2)
-- case when语句
case when sex = '1' then '男'
when sex = '2' then '女'
else '其他' end
NVL(expr1, expr2):
1、空值转换函数;
2、类似于mysql-ifnull(expr1, expr2),sqlserver-ifnull(expr1, expr2)。
备注:
1、如果expr1为NULL,返回值为 expr2,否则返回expr1。
2、适用于数字型、字符型和日期型,但是 expr1和expr2的数据类型必须为同类型。
添加行号
select *, row_number() over(partition by uin order by cast(score as double) desc) as num
from XXX::XXXXXXXXXXXXXXXXXXXXXXXXXX
where statis_date='20210710'
列转行
collect_set的作用:
(1)去重,对group by后面的user_id进行去重
(2)对group by以后属于同一user_id的形成一个集合,结合concat_ws对集合中元素使用,进行分隔形成字符串
collect_set()
wm_concat()功能:把聚合数据拼接成一个字符串,一般搭配group by 使用。
参数:
col1 : columnName,合并字段
splitstr : string,分隔符
asc | desc : string,升降序
col2 : columnName,排序字段
select user_id,
wm_concat(item_id || '=' || round(score, 5), '&', 'desc', score) as score_list
from t
group by user_id
行转列
explode()
其中参数为list
lateral view
SELECT pageid, adid
FROM pageAds LATERAL VIEW explode(adid_list) adTable AS adid;
repeat()函数
-- 用户活跃记录
select
-- 每月1号
case when day(to_date('{date}', 'YYYYMMDD')) == 1 then CAST(nvl(t1.hy_flag, 0) as STRING)
-- 不是1号,且第一次出现
when day(to_date('{date}', 'YYYYMMDD')) <> 1 and (t2.hytag_str is null or t2.hytag_str='') then concat_ws('', repeat('0', day(date_sub(to_date('%{date}', 'YYYYMMDD'), 1))), nvl(CAST(t1.hy_flag as STRING), '0'))
-- 之前出现过
else concat_ws('', t2.hytag_str, nvl(CAST(t1.hycnt as STRING), '0')) end as hytag_str