常用函数
coalesce(t1.item_without_tax_total_amt,0)
SELECT from_unixtime( unix_timestamp('20220101','yyyyMMdd'),'yyyy-MM-dd');
4.1 聚合函数
Hive支持count(),max(),min(),avg()等常用的聚合函数。
4.2 关系函数
支持 =, !=, <>, < , >, <= , >, >=
空值判断：is null， is not null.
relike， regexp操作。
4.3 数学运算
支持所有的数值类型： + , - , * , /， % ，& , | , ^, ~等。
4.4 逻辑运算
逻辑与： and
逻辑或： or
逻辑非： not
4.5 数值运算
取整函数: round()
round(double a) 返回值bigint，返回double类型的整数值部门(遵循四舍五入)
指定精度取整函数
round(double a, int d) 返回值double。返回指定指定精度d的double类型。
向下取整函数
floor(double a )返回等于或者大于该double变量的最小的整数。
向上取整函数
ceil(double a) 返回等于或者大于该double 变量的最小的整数；
取随机函数
rand(), rand(int seed):返回一个0到1范围内的随机数。
自然指数函数
exp(): 返回自然对数e的a次方。
幂运算函数
pow(double a, double p)： 返回a的p次幂。
开平方函数
sqrt(double a)： 返回a的平方根。
4.6 条件函数
if函数
if(boolean testCondition,T valueTrue, T valueFalseOrNull)
当条件testCondition为True时候，返回valueTrue,否则返回valueFalseOrNull
非空查找函数coalesce
coalesce(T v1, T v2,.....)
返回参数中第一个非空值，如果所有值都为null，那么返回Null。
条件判断函数case when
case when写法一：
case sex
when '1' then '男'
when '2' then '女'
else '其他' end
case when写法二：
case when sex = '1' then '男'
when sex = '2' then '女'
else '其他' end
4.7 日期函数
1.unix_timestamp函数的三种情况：
获取当前时间戳函数: unix_timestamp
语法：unix_timestamp();
返回值：bigint ,获得当前时区的unix时间戳
样例：select unix_timestamp() from table;
日期转unix时间戳函数:unit_timestamp
语法：unix_timestamp(String date)
返回值：bigint
说明：将格式为”yyyy-MM-dd HH:ss”的日期转换到unix时间戳，如果转换失败，则返回0.
将指定格式日期转unix时间戳函数:unix_timestamp()
语法：unix_timestamp(string date, string pattern)
返回值：bigint
说明：转换pattern格式日期到unix时间戳。如果转化失败，则返回0.
样例：select unix_timestamp('2021-03-08 14:21:11','yyyy-MM-dd HH:mm:ss') from table
2.unit时间戳转日期函数: from_unixtime
from_unixtime()
语法：from_unixtime(bigint unixtime)
返回值：string
说明：把具体的秒转化为时间日期。
3.获取当前的时间精确到毫秒
current_timestamp()
样例:select current_timestamp() -- 2011-09-02 10:11:09.234212000
4.日期时间转日期函数:to_date
年月日时分秒只取其中的年月日部分：to_date（）
语法：to_date(string timestamp)
返回值：string 返回日期时间部分的日期。
样例：select to_date('2021-09-02 12:09:09') from table
5.日期转年/月/日/小时/分钟/秒/周函数
日期转年函数year()
语法：year(String date)
返回值:int 返回日期中的年
样例：select year('2021-03-21 10:11:02') from table
日期转月函数mounth
返回日期中的月
日期转天函数day
返回日期中的天
日期转小时函数:hour
返回日期中小时函数
日期转分钟函数minute
返回日期中的分钟
日期转秒函数second
返回日期中的秒
日期转周函数 weekofyear
weekofyear(string date)
返回值为int，返回日期在当前的周数
6.日期操作函数
日期比较函数：datediff
语法:datediff(string enddate, string startdate)
返回值:int 返回结束日期减去开始日期
日期增加函数 ：date_add
语法：date_add(string startdate, int days)
返回值string，返回开始日期startdate增加days天的日期。
日期减少函数 date_sub
语法：date_sub(string startdate, int days)
返回值：string. 返回开始日期startdate减少days天后的日期。
4.8 字符串函数
字符串长度函数: length()
length(string a): 返回字符串a的长度
字符串反转函数：reverse
reverse(string a) ：返回字符串a的反转结果
字符串连接函数
不带分隔符的字符串连接函数concat()
语法:concat(string A, string B，........)
返回输入字符串连接后的结果，支持任意个输入字符串
带分隔符字符串连接函数 concat_ws()
concat_ws(String SEP, string a, string b)
返回输入字符串连接后的结果，sep表示各个字符串之间的分隔符。
4.9 字符串截取函数
substr(string a, int start, int len)
返回字符串a从start位置开始，长度为len的字符串。
substring(string a, int start, int len)
返回字符串a从start位置开始，长度为len的字符串。
字符串分割函数：split
语法：split(string str, string pat)
4.10 去空格函数
去空格函数trim
去掉字符串两边的空格
左边去空格ltrim
ltrim(string a) 去掉字符串左边的空格。
右边去空格函数
rtrim(string a)去掉字符串右边的空格。
4.11 正则表达式与解析函数
正则表达式替换函数regexp_replace(string a, string b, string c)
将字符串a中符合java正则表达式b的部分替换为c.注意在有些情况下需要使用转义字符.
样例：select regexp_replace('foobar', 'oo|ar' , '') from table_Name;
正则表达式解析函数：regexp_extract(string subject, string pattern, int index)
样例：select regexp_extract('foothebar', 'foo(.*?)(bar)', 1) from tableName;
将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符。
url解析函数: parse_url
样例：parse_url(string urlString, string partToExtract [, string keyToExtract] )
说明：返回url中指定的部分。
json解析：get_json_object()
语法：get_json_object(string json_string, string path)
样例：select get_json_object('{......}', '$.owner') from tableName;
4.12 explode函数
todo:结合百度需求进行总结
4.13 行转列与列转行
1.行转列
concat(string a/col, string b/col,.....):返回输入字符串连接后的结果，支持任意个输入字符串。
concat_ws(separator, str1, str2,....):它是一个特殊形式的concat
collect_set(col):将某字段的值进行去重汇总，产生array类型字段。
2.列转行
explode(col)
将hive一列中复杂的array或者map结构拆成多行。可以结合lateral view进行使用。
4.14 基础窗口函数与分析函数
4.14.1 窗口函数简介
在sql中有一类函数叫做聚合函数，例如sum(), avg(),max()等等，这类函数可以将多行数据按照规则聚集为一行，一般来讲，聚集后的行数是要少于聚集前的行数的。但是有时候我们既想要显示聚集前的数据，又要显示聚集后的函数，这个时候，我们便引入了窗口函数。窗口函数又叫OLAP函数/分析函数.
窗口函数最重要的关键字是partition by和 order by.具体语法是：over(partition by 用于分组的列名 order by 用于排序的列名)。
4.14.2 窗口的含义
1）如果不指定rows between,默认为从起点到当前行；
2）如果不指定order by,则将分组内所有值累加；
3）理解rows between的含义，也叫window子句：
preceding:往前；
following：往后；
current row: 当前行；
unbounded:起点；
unbounded preceding : 表示从前面的起点；
unbounded following : 表示到后面的终点；
4.14.3 窗口函数分类
1）专用窗口函数：包括dense_rank(),rank(),row_number()等。
2）聚合函数：例如sum(), avg(),max(),min(),count().
样例1：
select *,
sum(play_rate) over(order by user_id),
avg(play_rate) over(order by user_id),
max(play_rate) over(order by user_id),
min(play_rate) over(order by user_id),
count(play_rate) over(order by user_id)
from haokan_ads_test02;
样例1测试结果：
样例1分析：
聚合函数在窗口函数中，是对自身记录及位于自身记录以上的数据进行求和的结果。eg:user_id为4的时候，在使用sum()窗口函数后的结果，是对1，2，3，4号deplat_rate求和。
注意1：可以注意到专用窗口函数括号里面是空的，什么也没有写，但是聚合函数后面括号不能为空，需要指定聚合的列名。
注意2：因为窗口函数是对where或者group by子句处理后的结果进行的操作，所以窗口函数原则上只能写在select子句中。
注意3：聚合函数对应的窗口函数都是对自身记录，以及自身记录之上的所有数据进行计算。如果想看所有人的聚合情况，我们直接看输出结果的最后一行即可。
4.14.4 窗口函数的使用
1）为什么叫窗口函数：这是因为partition by分组以后的结果叫做窗口，这里的窗口是范围的意思。窗口函数同时具有分组和排序的功能；不减少原有表的行数
2）带partition by使用区别：
窗口函数中的partition by子句可以省去，但是此时就失去了分组的功能。
窗口函数一般用于排名问题和top-N问题。
3）dense_rank(),rank(),row_number()区别
DENSE_RANK() :排序相同时，会重复、总数会减少。1,1,2
Rank() :排序相同时，会重复，但是总数不会变。 1,1,3
ROW_NUMBER(): 会根据顺序计算。1，2,3
4.14.5 窗口函数与group by区别
group by分组汇总会改变行数（默认返回每组中的第一行）；而窗口函数不会减少原表中的行数。
4.16 高级窗口函数 （todo）
ntile;
lag:
lag(col,n,default)用于统计窗口内往上第n行值；第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为null时候，取默认值，如果不指定，则为null）
lead:
与lag相反。lead(col,n, default)用于统计窗口内往下第n行值。第一个参数为列名，第二个参数为往下第n行，第三个参数为默认值。
first_value
取分组内排序后，截止到当前行，第一个值。
last_value:
取分组内排序后，截止到当前行，最后一个值。
4.17集合操作函数(todo)
grouping sets
grouping_id
cube
rollup
4.18类型转换函数
cast (字段名 as 转换的类型)
数据验证
--数据验证
select 1 as c1,'cn_dim_dev_dl_tables.dim_item_sams_label_info_df' as c2,'数据量一致性' as c3, t1.cnt1 - t2.cnt1 as c4
from
(select count(1) as cnt1 from cn_dim_dev_dl_tables.dim_item_sams_label_info_df) t1
join (select count(1) as cnt1 from cn_dim_dl_tables.dim_item_sams_info_all_df ) t2
union all
select 2 as c1,'cn_dim_dev_dl_tables.dim_item_sams_label_info_df' as c2,'主键唯一性' as c3, count(1) as c4
from (
select 1 from cn_dim_dev_dl_tables.dim_item_sams_label_info_df group by ts,item_nbr having count(1) >1 ) t1
union all
select 3 as c1,'cn_dim_dev_dl_tables.dim_item_sams_label_info_df' as c2,'主键为空' as c3, t1.cnt1 as c4
from
(select count(1) as cnt1 from cn_dim_dev_dl_tables.dim_item_sams_label_info_df group by ts is null or item_nbr is null ) t1
复制单个分区到多个分区
create external table cn_dw_dev_dl_tables.dws_item_sams_cbec_sales_mid_di
like
cn_dw_dl_tables.dws_item_sams_cbec_sales_mid_di
stored as orc tblproperties('external.table.purge'='true');
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
INSERT OVERWRITE TABLE cn_dw_dev_dl_tmp.dwd_item_sams_cbec_master_info_df_tmp20230310 PARTITION(ts)
SELECT t1.year_date
,t1.month_date
,t1.item_nbr
,t1.channel
,t1.item_name
,t1.brand_name
,t1.category
,t1.eos
,t1.rate
,t1.is_direct_mail
,t1.caliber
,t1.unit_cost
,t1.etl_load_time
,r.ts --partition column is the last one
FROM cn_dw_dl_tables.dwd_item_sams_cbec_master_info_df t1
CROSS JOIN
(
select date_add ('2022-01-01',s.i) as ts,s.x
from ( select posexplode(split(space(datediff('2022-01-30','2022-01-01')),' ')) as (i,x) ) s
) r
WHERE t1.ts='2023-03-09'
DISTRIBUTE BY r.ts;
INSERT OVERWRITE TABLE cn_dw_dl_tables.dwd_item_sams_cbec_master_info_df PARTITION(ts)
SELECT t1.year_date
,t1.month_date
,t1.item_nbr
,t1.channel
,t1.item_name
,t1.brand_name
,t1.category
,t1.eos
,t1.rate
,t1.is_direct_mail
,t1.caliber
,t1.unit_cost
,t1.etl_load_time
,t1.ts --partition column is the last one
FROM cn_dw_dev_dl_tmp.dwd_item_sams_cbec_master_info_df_tmp20230310 t1
where t1.ts <'2022-07-01';
select t2.brand,t2.start_date,t2.end_date,t3.x,t3.y,date_add(t2.start_date,t3.x)
from (
select
t1.brand,t1.start_date,t1.end_date,datediff(t1.end_date,t1.start_date)as diff,split(space(datediff(t1.end_date,t1.start_date)),'') as a
from (
select 'hw' as brand,'2023-01-10'as start_date ,'2023-01-20' as end_date) t1
) t2
lateral view posexplode(a) t3 as x,y
where t3.x%2=1 ;
sql规范示例脚本
select t1a.store_nbr as store_nbr -- 注释
, case when t1b.closed_date is not null
and floor(months_between(trunc(t1b.closed_date,'MM'),trunc(t1a.visit_date,'MM'))) <= 0 then
'N'
when t1b.closed_date is not null
and floor(months_between(trunc(t1b.closed_date,'MM'),trunc(t1a.visit_date,'MM'))) > 0 then
'Y' --条件1：判断Closed Date是否为空，若不为空，判断其所在月份（年/月）是否小于等于业务时间所在月份（年/月），若小于等于，则该业务时间该门店为非可比店；
else
case when t1b.comp_date is not null
and floor(months_between(trunc(t1b.comp_date,'MM'),trunc(t1a.visit_date,'MM'))) <= 0 then
'Y'
when t1b.comp_date is not null
and floor(months_between(trunc(t1b.comp_date,'MM'),trunc(t1a.visit_date,'MM'))) > 0 then
'N' --条件2：再判断comp date是否为空，若不为空，判断其所在月份（年/月）是否小于等于业务时间所在月份（年/月），若小于等于，则该业务时间该门店为可比店；
else
case when t1b.g_o_date is not null
and floor(months_between(trunc(t1a.visit_date,'MM'),trunc(t1b.g_o_date,'MM'))) >= 13 then
'Y'
when t1b.g_o_date is not null
and floor(months_between(trunc(t1a.visit_date,'MM'),trunc(t1b.g_o_date,'MM'))) < 13 then
'N' --条件3：判断g.o date是否为空，若不为空，判断业务时间所在月份（年/月）是否大于等于g.o date所在月份（年/月）13个月，若大于等于，则该业务时间该门店为可比店；
else
'N' --以上都不满足则其他情况该门店为非可比店
end
end
end as comp -- 可比店
, case when t1b.store_type is null then 'other' else t1b.store_type end as store_type -- 注释
, case when t1b.region is null then 'other' else t1b.region end as region -- 注释
, case when t1b.dm is null then 'other' else t1b.dm end as dm -- 注释
, case when t1b.city is null then 'other' else t1b.city end as city -- 注释
, coalesce(t1b.city,'other') as city -- 注释
, t1a.visit_date as visit_date -- 注释
, t1a.hierarchy_nbr as hierarchy_nbr -- 注释
, case when t1a.sales_classification = 'offline' then 'Offline'
when t1a.sales_classification = 'online' then 'Online'
when t1a.sales_classification = 'Total' then 'Total'
else 'other'
end as sales_classification -- 注释
, t1a.netsales as netsales -- 注释
, t1a.target_netsales as target_netsales -- 注释
, first_value(t1a.netsales) over(partition by t1a.visit_date
, t1a.hierarchy_nbr
, coalesce(t1b.city,'other')
order by t1a.visit_date desc
) as first_netsales -- 注释
from (
-- 语句块注释
select -- 注释
, store_nbr as store_nbr -- 注释
, visit_date as visit_date -- 注释
, hierarchy_nbr as hierarchy_nbr -- 注释
, sales_classification as sales_classification -- 注释
, channel as channel -- 注释
, netsales as netsales -- 注释
, target_netsales as target_netsales -- 注释
, ly_netsales as ly_netsales -- 注释
, netsales_std as netsales_std -- 注释
, target_netsales_std as target_netsales_std -- 注释
, ly_netsales_std as ly_netsales_std -- 注释
from cn_fin_morningreport_dl_tables.tmp_countdown_d_level0 t2a -- 表注释
union all
-- 语句块注释
select t2b.store_nbr as store_nbr -- 注释
, t2b.visit_date as visit_date -- 注释
, t2b.hierarchy_nbr as hierarchy_nbr -- 注释
, t2b.sales_classification as sales_classification -- 注释
, t2b.channel as channel -- 注释
, sum(t2b.netsales) as netsales -- 注释
, sum(t2b.target_netsales) as target_netsales -- 注释
, sum(t2b.ly_netsales) as ly_netsales -- 注释
, sum(t2b.netsales_std) as netsales_std -- 注释
, sum(t2b.target_netsales_std) as target_netsales_std -- 注释
, sum(t2b.ly_netsales_std) as ly_netsales_std -- 注释
from database_name.tmp_countdown_d_level1 t2b -- 表注释
group by store_nbr
, visit_date
, hierarchy_nbr
, sales_classification
, channel
) as t1a
left join (
-- 注释，只有当t2c中的字段全部用到时才允许使用“* ”，否则只取用到的字段
select *
from cn_core_dim_dl_tables.dim_store_info as t2c -- 表注释
) t1b
on t1b.store_nbr = t1a.store_nbr
left join cn_core_dim_dl_tables.dim_item_info as t1c -- 表注释
on lower(t1a.hierarchy_nbr) = lower(t1c.hierarchy_nbr)
left join cn_core_dim_dl_tables.dim_countdown_date_info as t1d -- 表注释
on t1a.visit_date = t1d.countdown_date
left join cn_core_dim_dl_tables.dim_channel_info as t1e -- 表注释
on t1a.channel = t1e.channel_short_name
inner join cn_core_dim_dl_tables.dim_store_info as t1f -- 表注释
on t1f.store_nbr = t1a.store_nbr
where t1b.store_nbr is not null
and t1d.countdown_date is not null
group by t1a.visit_date
, t1a.hierarchy_nbr
order by t1a.visit_date asc
, t1a.hierarchy_nbr desc
having count(1) > 1
3569
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
