【数分】4. 分析工具拓展篇

SQL

Hive SQL

1. 窗口函数

   1. 排序函数
   	row_number() over() 
   		-- 普通连续排序，eg:5, 6, 6, 7, 7；返回：12345
   	rank() over()
   		-- 断点密集排序，eg:5, 6, 6, 7, 7；返回：12244
   	dense_rank() over()
   		-- 连续密集排序，eg:5, 6, 6, 7, 7；返回：12233
   2. 分位数/累积分布/分组
   	percentile_approx( string1, array(0.3, 0.6, 0.9) )
   	percent_rank() over()
   		-- (当前行的rank值-1)/(分组内的总行数-1)
   		-- 可搭配format(str，'P')转化为百分比使用
   	cume_dist() over()
   		-- 小于等于(desc时,大于等于)当前值x的行数 / 分组内总行数
   		-- 区别下percent_rank，两者在等值处返回的结果正好「相反」
   	ntile(n) over()
   		-- 划分为大小尽可能相等的带排名的组，返回组的排名
   3. 错位函数
   	lead(col,n[,default]) over()
   		-- 目标数据整体上移 n 个单位，空值若不指定则默认取null
   	lag(col,n[,default]) over()
   		-- 目标数据整体下移 n 个单位，，空值若不指定则默认取null
   4. 头部值函数
   	first_value() over()
   	last_value() over()
   

2. 切片&截取

   substr(str, pos, len) 
   	-- 从pos开始的位置，截取len个字符，不填此参数默认截到最后；从1开始计数
   	-- eg: substr(string ,1,3) -> str，substr(string ,4) -> ing
   instr(str, substr)
   	-- 从 str 中查询 substr 这一字符串，返回0表示未找到，返回x表示在第x位
   split(attrs['scm_id'], '_')[4]    
   	--从0开始计数
   

3. 解json格式:

   get_json_object(attrs['param'], '$.algo_info')
   

4. 字符串拼接：

   concat_ws('.', year, month, day)    
   	--将单个字符串拼接，拼接符号自定义
   concat_ws(',', collect_set(goods_ids))    
   	--将字符串集合拼接，拼接符号自定义
   

5. 行列互转：

   -- 列转行
   	collect_list -- 不去重
   	collect_set  -- 去重
   -- 行转列
   	lateral view explode( split(goods_ids, ',') ) t0 as goods_id
   		-- 先执行from到行转列这一步，故另起个表名t0，再执行select和where后边的语句
   	lateral view posexplode(after_resort_goods) a as goods_idx, goods_id
   	lateral view posexplode(after_resort_scores) b as scores_idx, scores_id
   	where a.goods_idx = b.score_idx  
   		--行转列，并匹配上对应序号；可多个行转列并用；goods_idx和score_idx从0开始计数
   

6. 对二维数组排序：

   sort_array   
   	-- 升序排序+对字符排序
   

7. 正则表达式

   regexp_extract(subject, pattern, index)
   	-- pattern：需要匹配的正则表达式；
   	-- index：返回index指定的字符，0表示与之匹配的整个字符串，1表示第一个括号里面的；
   	-- 正则匹配字符解释：^ 表示开头，$ 表示结尾，. 表示任意字符，* 表示任意多个，| 表示或，+ 表示1次或多次。
   	-- eg: se4320  →  4320
   	regexp_extract(exp_tag, 'se([0-9]+)', 1)
   regexp_replace(source, pattern, replace_string)
   	-- eg：["CH313024"]  →  CH313024
   	regexp_replace(sorters,'\\"|\\\[|\\\]', '')
   

8. 取整方式：

   -- 向上取整
   	ceiling(1.6)=2
   -- 向下取整
   	floor(1.6)=1 
   -- 四舍五入
   	round(2.5, 0)=3 
   -- 高斯取整
   	--向最接近数字方向舍入的舍入模式，如果与两个相邻数字的距离相等，则向相邻的偶数舍入。
   	bround(2.5, 0)=2
   	bround(3.5, 0)=4
   

9. 时间差函数：

   date_sub(today, 2) --减日期，适用于dt格式日期     
   date_add(today, 2) --加日期
   datediff(startdate, enddate) / datediff(datepart, startdate, enddate)
   	-- 结果为负值，可以换成datediff(enddate, startdate)使用
   	-- 在hive sql里不支持使用datepart参数，在其他SQL里是支持的   
   months_between(startdate, enddate)  
   	-- 结果为负值
   

10. 时间戳与时间转换：

    --日期转为时间戳
    	unix_timestamp('2015-04-29', "yyyy-MM-dd HH:mm:ss")
    --时间戳转为日期
    	from_unixtime('1430236800', "yyyy-MM-dd HH:mm:ss")
    	-- ⚠️ 时间戳必须是 bigint 格式
    -- 注：10位时间戳和13位时间戳，10位时间戳直接相减得秒差
    

11. 空值处理：

    -- 为空返回replace_with
    	ifnull(string, replace_with) 
    	== nvl( string, replace_with) 
    -- 两值相等返回空，不等返回第二个值
    	nullif(string1, string2)
    -- 返回第一个非空值
    	coalesce(string1, string2, string3, …)
    -- 为空返回replace_with，否则返回else_value
    	nvl2(string, replace_with, else_value)
    	== if(string is null, replace_with, else_value)
    

12. 补足函数：

    lpad( string, padded_length, [ pad_string ] )  
    rpad( string, padded_length, [ pad_string ] )  
    	-- l从左边填充，r从右边填充
    	-- padded_length是返回的字符串的数量，若比原来的要短，pad函数将截取从左到右的n个字符（无论lpad或rpad）;
    	-- pad_string可选参数，若参数未写，将粘贴空格。
    

13. 转换数据类型

    cast(expression as data_type) 
    	-- 常见的数据类型有：string, int, bigint, 
    

14. 子查询

    in
    	-- in 子查询时，首先执行子查询，并将获得的结果列表存放在一个加了索引的临时表中;
    	-- in 只能返回一个字段值；外查询表大，子查询表小，选择in；
    	select id, name 
    	from table_a a 
    	where id in (select aid from table_b b)
    exists 和 not exists
    	-- exists 效率一般优于 in ，Oracle会首先检查主查询，然后运行子查询直到它找到第一个匹配项
    	-- 强调是否返回结果集，可以返回多个字段值，但返回的内容没有意义；外查询表小，子查询表大，选择exists；在子查询的条件一旦满足后，立即返回结果，自带去重。
    	select id,name 
    	from table_a a 
    	where exists(select * from table_b b WHERE a.id = b.aid)
    left semi join
    	-- 更高效的一种子查询方式
    	select id,name
    	from table_a a 
    	left semi join table_b b
    	on a.id=b.aid
    

15. 去重

    distinct
    	-- 放在首位，对后面所有字段进行去重处理
    group by
    

    两者区别：
    * distinct：这种方式会将全部内容存储在一个hash结构里，最后通过计算hash结构中key的个数即可得到结果，典型的以空间换取时间的方式。
    * group by：这种方式是先将字段排序（一般使用sort），然后进行计数，典型的以时间换取空间。
    * 数据越是离散，DISTINCT需要消耗的空间越大，效率也就越低，此时group by的空间优势就显现了；数据越是集中，distinct空间占用变小，时间优势就显现出来了。

16. 字符串转Map

    str_to_map(string [, delimiter1, delimiter2] )
    	-- 使用两个分隔符(delimiter)将文本拆分为键值对;
    	-- 分隔符1将文本分成K-V对，分隔符2分割每个K-V对，默认分隔符分别为','和'='。
    	-- eg:
        str_to_map(
            concat_ws(',', collect_set(concat(first_tag_name_en, '-', device_tag_play_rate) ) ),
            ',','-') kv		
    

Spark SQL

1. 将查询结果转存为parquet文件

   df = spark.sql(sql_test)
   path='储存路径'    ##路径可不存在
   df.write.format('parquet').mode('overwrite').save(path)
   

2. 将查询结果转存为其他文件：

   df = spark.sql(sql_test)
   data = df.toPandas()   ##要求文件不大
   data.to_excel('filename.xlsx')
   data.to_csv('filename.csv')		
   

3. 显示该字段全部

   df.show(truncate=False)
   

SQL优化

1. 尽量避免在 where 子句中使用 or 来连接条件， 否则将导致引擎放弃使用索引而进行全表扫描， 可用 union 合并查询；
2. 尽量使用 group by 来去重，使用 distinct 去重效率很低；
3. in 和 not in 慎用，否则会导致全表扫描，对于连续的数值，能用 between 就不用 in ；
4. 如果在 where 子句中使用参数，也会导致全表扫描；
5. 应尽量避免在 where 子句中对字段进行表达式和函数操作；
6. 很多时候用 exists 代替 in 是一个好的选择；
7. 在 in 后面值的列表中，将出现最频繁的值放在最前面，减少判断的次数；
8. 尽量使用 “>=” ，不要使用 “>” ；
9. 任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边；

Python

Excel