添加链接 注册    登录
link管理
链接快照平台
  • 输入网页链接，自动生成快照
  • 标签化管理网页链接
相关文章推荐
大力的牛肉面  ·  SG Com Unity 软件包脚本·  1 月前    · 
没人理的大熊猫  ·  600岁昆曲为何魅力不减（薪火相传）--知识 ...·  2 月前    · 
没人理的骆驼  ·  具体并行集合类 | ...·  2 月前    · 
性感的野马  ·  C++基础——非类型模板参数_c++非类型实 ...·  3 月前    · 
温文尔雅的竹笋  ·  “世界预防自杀日”：世卫组织呼吁全社会协作防 ...·  4 月前    · 
link管理  ›  Hive函数全解——思维导图 + 七种函数类型-阿里云开发者社区
select函数 date函数 hive函数 hive
https://developer.aliyun.com/article/1500331
想出国的烤地瓜
2 周前
AI 助理
备案 控制台
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议 》和 《 阿里云开发者社区知识产权保护指引 》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单 进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。 
func(F|func|expr) over(
    [partition by F1[,F2,...]                        确定分区的边界(范围)
    [order by Fa [asc|desc][,Fb [asc|desc]]]        确定分区内行的排列顺序(只有指定了ORDER BY才可以使用Window_Clause)
    rows|range between ... and ...                    rows：物理行号(无重复) range：排序号(可能重复，会将重复行的数据视为一个整体)
        unbounded preceding
        N preceding
        current row
        N following
        unbounded following
)[as] ALIA(别名)

** 注意：
对窗口函数的结果筛选必须在外层。
 

ROW_NUMBER()     行号                
RANK()             排名(并列跳号)
DENSE_RANK()     排名(并列不跳号)✔    
NTILE(N)        桶号(将数据[在每个分组内]按ORDER BY排序后的顺序分成N个连续区间)
    作用：抽样 | 倾斜处理
PERCENT_RANK()    百分比排名 (当前排名-1)/(窗口总行数-1)
                (排名越高越接近0，排名越低越接近1)

** 数据倾斜现象
group_id data
 1 ...
 1 ...
 2 ...
 2 ...
 2 ...
 2 ...
 2 ...
 3 ...
 3 ...
 4 ...
 4 ...
 

定义
 键值对(通常是键)分布不均匀
影响
 计算资源分配不均 影响整体性能
 

如何处理数据倾斜现象？
1.识别数据倾斜
 

1 3 4 数据量为2
2 数据量为5
5 >> 2
 

2.计算分割因子
split_factor = floor(skewed_data_count/AVG(non-skewed_data_count))
 

3.应用分割逻辑
image.png
对于倾斜的数据，对每行数据添加一个额外的字段f1，f1的值从0到split_factor-1
对于非倾斜的数据，f1可以保持为一个固定值，如0
 
 
  • 调整分组键
    f与f1结合，形成新的分组键。根据新的分组键进行数据处理。
    在处理完毕后，将f+f1还原为原始的分组键f
    •  

    NTILE 如何自动化该数据倾斜处理过程？
     
 
  • 获取数据倾斜的group_id(假设为2)
    •  

    SELECT group_id,COUNT(*) AS cnt
FROM example_table
GROUP BY group_id
ORDER BY cnt DESC;
 
  • 计算分割因子
    •  

    SELECT FLOOR(COUNT(*)/t1.avg_cnt) AS split_factor
FROM example_table
CROSS JOIN(
    SELECT AVG(cnt) AS avg_cnt
    FROM(
        SELECT COUNT(*) AS cnt
        FROM example_table
        WHERE group_id <> 2
        GROUP BY group_id
WHERE group_id = 2;
 
  • 形成新分组键
    •  

    SELECT CONCAT_WS('_', group_id, bucket_id) as new_group_key, data
FROM (
    SELECT group_id, data, 
               WHEN group_id = 2 THEN NTILE(split_factor) OVER (PARTITION BY group_id ORDER BY data) 
               ELSE 0 
           END as bucket_id
    FROM example_table, 
         (SELECT FLOOR(COUNT(*) / AVG_COUNT) as split_factor
          FROM example_table
          CROSS JOIN (
              SELECT AVG(cnt) as AVG_COUNT
              FROM (
                  SELECT COUNT(*) as cnt
                  FROM example_table
                  WHERE group_id != 2
                  GROUP BY group_id
          WHERE group_id = 2) t3
) result;

    分析：不支持ROWS|RANGE BETWEEN，需要考虑F是否为NULL(IF(F IS NULL,NULL,...) AS ...)
     

    LAG(F,N)                                            当前窗口内当前行的上N行的字段F值
LEAD(F,N)                                            当前窗口内当前行的下N行的字段F值
FIRST_VALUE(F)                                        当前窗口内第一行的字段F值
LAST_VALUE(F)                                        当前窗口内最后一行的字段F值                                                    
CUME_DIST()                                            `<=`当前行值的所有行占窗口总行数的比例
PERCENTILE(F,V)                                        
F：BIGINT V：DECIMAL|ARRAY<DECIMAL>                    
    PERCENTILE(F,0.5)                                获取中位数
    PERCENTILE(F,array(0.25,0.56,0.9))                获取四分位数
CORR(F1,F2)                                            获取皮尔逊相关系数(-1~+1，两变量相关的强度和方向)                                        
COVAR_POP(F1,F2)                                    获取总体协方差(+|-，两变量是否同方向变化)
VAR_POP(F)                                            获取方差(衡量数据稳定性)
STDDEV_POP(F)                                        标准差

    2. 数学函数
     

    -----------------------正负-------------------------
    abs(N) 绝对值
    positive(N) 正数
    negative(N) 负数
    sign(N) 符号，正数返回+1，负数返回-1
    -----------------------度数-------------------------
    degrees(pi()/2) 弧转角
    radians(90) 角转弧
    sin(pi()/2) 求sin值
    cos(pi()/2) 求cos值
    tan(pi()/4) 求tan值
    asin(N) 求arcsin值
    acos(N) 求arccos值
    atan(N) 求arctan值
    -----------------------精度-------------------------
    round(N,M) 四舍五入
    bround(N,M) 四舍六入五凑偶(偶舍奇入)
    ceil(N) 向上取整
    floor(N) 向下取整
    trunc(N,M) 截断操作
     trunc(12345.678,2) => 12345.670000...
     trunc(12345.678,-2) => 12300.000000...
    format_number(N,FORMAT)
     FORMAT = N => 等同于round
     FORMAT = '###,###.#' =>
     如果#的数量>或<数字的数量，都显示原数字，再根据.和,分隔数字。
    -----------------------计算-------------------------
    pow(N,M) 求幂
    log(N,M) 求对数
    factorial(N) 求N的阶乘
    mod(N,M) N%M
    shiftleft(N,M) 十进制数N对应的二进制数左移M位
     shiftleft(cast(conv(1001,2,10) as int),1)
    shiftright(N,M) 十进制数N对应的二进制数右移M位
    shiftrightunsigned(N,M) 无符号右移
    greatest(N1,N2,...) 求多列的最大值
    least(N1,N2,...) 求多列的最小值
    width_bucket(77,0,100,5) 区间(P2、P3)分桶(P4)定值(P1)的桶号
    percentile_approx(expr,pc,[nb]) [超大]数据近似百分位数(n bins from histogram)
     expr 将计算百分位数的列或表达式
     pc 要计算的百分位数(0~1 0.5表示中位数，也可以用数组的形式表示)
     nb 近似算法的桶的数量，通常在超大数据的时候进行使用
    percentile_approx(salary,0.5,100) 计算salary列的近似中位数，并且在计算过程中使用了100个桶进行近似计算。
    percentile_approx(salary,array(0.25,0.5,0.75),100)
    histogram_numeric(F,N) 获取数据区间分布，将其分布为N个区。
     [{"x":277797.38999999996,"y":3.0},{"x":313823.77111111116,"y":9.0},{"x":334791.79142857145,"y":7.0},{"x":352004.46400000004,"y":5.0},{"x":364576.2504545455,"y":22.0},{"x":383282.47500000003,"y":6.0},{"x":397107.64571428567,"y":7.0},{"x":417563.0433333332,"y":15.0},{"x":438436.93000000005,"y":3.0},{"x":457320.16000000003,"y":3.0},{"x":475004.04,"y":3.0},{"x":501651.47,"y":1.0}]
     x表示数值边界，y表示前一个边界到当前边界出现的数据频次
    ----------------------进制转换----------------------
    conv(N,FROM_BINARY,TO_BINARY) 返回字符串类型
    ------------------------常量------------------------
    pi() 获取pi
    e() 获取E
    hash(N) 获取哈希值(数组 字符串 ✔ )
    rand() 无参数为0~1，有参数为伪随机(固定参数的rand值相同)
     日期函数
     

    SELECT year(`current_date`());                            -- 年
SELECT quarter(`current_date`());                        -- 季
SELECT month(`current_date`());                            -- 月
SELECT day(`current_date`());                            -- 日
SELECT hour(`current_timestamp`());                        -- 时
SELECT minute(`current_timestamp`());                    -- 分
SELECT second(`current_timestamp`());                    -- 秒
SELECT dayofweek(`2023-11-11`)                            -- 周日~周六 1~7
SELECT weekofyear(`current_date`());                    -- 年周
SELECT date_format(`current_date`(),'yyyy');            -- 日期格式化 ✔(yyyy-MM-dd HH:mm:ss.SSS 部分或全部)
2021-05-13 11:22:33.545
SELECT floor_second(`current_timestamp`());                -- 向下取整到零毫秒
SELECT floor_minute(`current_timestamp`());                -- 向下取整到零秒
SELECT floor_hour(`current_timestamp`());                -- 向下取整到零分
SELECT floor_day(`current_timestamp`());                -- 向下取整到零时：年月日 <=> current_date()
SELECT floor_week(`current_timestamp`());                -- 向下取整到当周第一天
SELECT floor_month(`current_timestamp`());                -- 向下取整到当月第一天 <=> trunc(`current_timestamp`(),'MM')
SELECT floor_quarter(`current_timestamp`());            -- 向下取整到当季第一天 <=> trunc(`current_timestamp`(),'Q')
SELECT floor_year(`current_timestamp`());                -- 向下取整到当年第一天 <=> trunc(`current_timestamp`(),'YYYY')
SELECT last_day(`current_date`());                        -- 向下取整到当月最后一天

    取整日期函数的应用场景：
     将其作为分组字段，统计不同粒度下的结果。
     

    SELECT date_add(`current_date`(),-2);                    -- 日期计算：天±
SELECT add_months(`current_date`(),-2);                    -- 日期计算：月±
SELECT datediff(`current_date`(),'2021-10-18');            -- 日期计算：两个日期天数差(前-后)
SELECT months_between(date1,date2);                        -- 日期计算：两个日期月数差(浮点数：表示日期之间的完整月数加上剩余天数的小数部分)
                                                        -- date1在date2之后，为正数；反之则为负数。
SELECT next_day(`current_date`(),'MON');                -- 下一个星期几（未至返回本周，已过返回下周）
SELECT `current_date`();                                -- 获取当前日期：年月日
SELECT `current_timestamp`();                            -- 获取当前时间：年月日时分秒
SELECT unix_timestamp();                                -- 获取当前日期时间戳
SELECT unix_timestamp('2021-10-18 11:12:13','yyyy-MM');    -- 获取指定日期指定格式的时间戳 <=> to_unix_timestamp，格式参数可选
SELECT from_unixtime(1634515200);                        -- 将时间戳转化为日期
SELECT to_utc_timestamp('2021-10-18 11:12:13','GMT+8');    -- 按指定时区转化日期格式：UTC
    `GMT+8`表示当地时间比格林威治时间早了8个小时，结果为'2021-02-18 11:12:13'
    将每条交易记录的时间戳从当地失去转换为UTC时区，并进行统一的数据分析。
SELECT to_date(`current_timestamp`());                    -- 将日期时间值转化为日期(年月日)        
    SELECT to_date('2021-01-15 15:32:08'); => 2021-01-15

    3. 字符函数
     

    SELECT encode('hello你好','UTF-8');                        -- 编码
SELECT decode(encode('hello你好','GBK'),'GBK');            -- 解码
SELECT base64(binary('abc'));                            -- 简单对称加密(二进制编码：将二进制数据转换为ASCII字符集)
    base64进行对称加密前，需要将数据转化为二进制形式
    SELECT base64(USER_NAME) FROM TABLE_NAME;    对识别信息(例如名字)进行匿名化处理
SELECT unbase64('YWJj');                                -- 简单对称解密
SELECT base64(aes_encrypt('henry','1234567812345678'))    -- AES对称加密：16 24 32
    AES加密之后的结果是二进制数据，AES加密需要一个密钥，该密钥的长度通常为16，24，32字符长度。
    base64(aes_encrypt(data,'AES_KEY'))                    -- 在不安全的网络中传输敏感数据
SELECT md5('abcdef');                                    -- 非对称加密：返回长度位32位的16进制值
    SELECT md5(USER_PASSWORD) FROM TABLE_NAME;    对敏感数据(例如用户密码)进行非对称加密
SELECT sha('abc');                                        -- 非对称加密 <=> sha1
SELECT sha2('abc',224);                                    -- 非对称加密：224，256，384，512
    SHA系列函数是一种加密哈希函数，用于生成固定位数的哈希值。

    安全性：非对称>对称
    性能：对称>非对称
     

    SELECT reflect("class","method",arg1[,arg2,...,argn])    使用反射函数在Hive中调用Java类方法
    如何实现对网页加密部分进行解码？
    可以使用`reflect`函数调用Java的`URLDecoder`类来解决这个问题
    SELECT reflect("java.net.URLDecoder", "decode", parse_url('https://search.jd.com/Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|&wq=%E5%8D%8E%E4%B8%BA&pvid=65c357d9dfb44555a9eb8708ca539b8b','QUERY','keyword'), 'UTF-8') AS decoded_keyword;
SELECT mask('x2你z');                                    -- 字母显示为x，数字显示为n，汉字保留。
SELECT mask_hash('123abc我');                            -- 返回长度为64位的16进制数
SELECT mask_first_n('123abc我',3);                        -- mask前n个字符
SELECT mask_last_n('123abc我',3);                        -- mask后n个字符
SELECT mask_show_first_n('123abcdef',3);                -- 保留原始字符串的前n个字符，其余字符被掩码替换。
    => 123xxxxxx
SELECT mask_show_last_n('123abcdef',3);                    -- 保留原始字符串的后n个字符，其余字符被掩码替换。
    => nnnxxxdef
mask 通常用于数据脱敏，如果需要自定义脱敏字符：replace(mask(...),'n','x');
     如果要对汉字进行脱敏：
-- 张**
select substr('张三丰',0,1)；--str,fromIndex,Length
select concat(substr('张三丰'，0,1)，repeat("*",Length('张三丰')-1))；
SELECT repeat('abc',2);                                    -- 将字符串重复指定的次数
SELECT replace('abc123abc','abc','XYZ')                    -- 替换全部相应的字符
SELECT regexp_replace('abc123def456xyz','\\d+','***')    -- 替换全部符合正则的字符
SELECT translate('abc123ae12f','a1','xx');                -- 按照字符进行替换
    SELECT translate('hello','el','12') => h122o
    SELECT translate('hello','e','12') => h1llo
    SELECT translate('hello','elo','12') => h122        -- 在原字符串中删除多出来的字符
SELECT reverse('abc');                                    -- 反转字符串
SELECT initcap('henry');                                -- 首字母大写
SELECT lcase('HenRy');                                    -- 全部小写 <=> lower(str)
SELECT ucase('henRy');                                    -- 全部大写 <=> upper(str)
SELECT lpad('aa',5,0);     => 000aa                        -- 左填充
SELECT rpad('aa',5,0);     => aa000                        -- 右填充
SELECT space(5);                                        -- 生成N个空格
SELECT trim(' abc   ');                                    -- 两边裁剪
SELECT ltrim(' abc   ');                                -- 左边裁剪
SELECT rtrim(' abc   ');                                -- 右边裁剪
SELECT length('abc我');      => 4                            -- 返回字符串长度
SELECT octet_length('abc我');  => 6                        -- 返回字符串字节长度(汉字3字节)
                                                        -- 了解字节长度有利于评估存储需求和网络带宽使用
SELECT index(`array`(22,11,33),2);                        -- 返回数组中指定下标位置的元素值 <=> array(22,11,33)[2];
SELECT elt(2,"aa","bb","cc");                            -- 提取字符串列表中的第N个值
SELECT field('aa','bb','cc','aa');                        -- 返回参数一在后面字符串列表中的位置，从1开始
SELECT find_in_set('aa','bb,cc,aa');                    -- 返回参数一在后面字符串中的位置，找不到返回负数。
SELECT locate('aa','bbaacc',1);                            -- 获取参数一在参数二中的从参数三(从1开始)开始向后第一次出现的位置，找不到返回负数。
SELECT instr('bbccaa','aa');                            -- 获取参数二在参数一中第一次出现的位置，从1开始
SELECT printf('%s,%d,%b,%.2f','aa',12,false,12.345);    -- 格式化输出(占位符同Java)
SELECT concat('abc','-','def');                            -- 拼接字符串
SELECT concat_ws('-','1','2','3'); => 1-2-3                -- 指定分隔符拼接字符串，支持【字符串数组拼接】
    SELECT concat_ws('-',`array`('apple','banana','city'));
SELECT uuid();                                            -- 随机36位的16进制字符串
SELECT split('1,2,3',',');     =>["1","2","3"]            -- 按指定分隔符将字符串分割为字符串数组
SELECT sentences('hello how are you? I am fine. Thank you!');     -- 英文句子按标点拆分成二维数组，处理大型文本
    =>[["hello","how","are","you"],["I","am","fine"],["Thank","you"]]
SELECT substring('[email protected]',2);                        -- 截取字符串：提取由指定位置开始的指定长度的字符串
SELECT substr('[email protected]',2,3);                        
SELECT substring_index('[email protected]',',',-2);            -- 提取参数二指定分隔符分隔的前N个元素，若为负值则代表后N个元素。
    SELECT substring_index('apple,can,you',',',2);  => apple can
    SELECT substring_index('apple,can,you',',',-2);    => can you
    SELECT substring_index(substring_index('henry,qq,com',',',2),',',-1); => qq <=> 提取第2个
SELECT 'abc' LIKE 'ab_';                                -- 模糊匹配(可以作为匹配模式的字符有：%<任意个字符>,_(单个字符))
SELECT '123' rlike '\\d{3}';                            -- 正则匹配
SELECT levenshtein('xyz','abcd');                        -- 相似性，0为相同，值越大相似性越差
SELECT soundex('Abcef');  => A120                        -- 旨在识别拼写不同但发音相似的单次
    人名搜索，例如在搜索形式"Smith"，可能希望同时找到"Smyth"或"Smithe",，避免因为拼写不当造成检索遗漏
    SELECT * FROM people WHERE soundex(name) = soundex('Smith');

    -- 【词频统计】ngrams()和context_ngrams()都要与sentences()函数一起使用
    SELECT ngrams(sentences('hello how are you? fine , thank you and you?'),2,3);
     -- 第一个参数：单词二维数组
     -- 第二个参数：连续N个单词
     -- 第三个参数：top-k
    SELECT context_ngrams(sentences('hello how are you? fine , thank you and you?'),array('how',null),3);
     -- 第一个参数：单词二维数组
     -- 第二个参数：和how右搭配的单词词频统计
     -- 第三个参数：top-k
    统计分词结果中与数组指定单词一起出现的频率最高的TOP-K结果。
    ** 其他搭配方式：
    左侧搭配：array(null,'how');
    特定位置的搭配：array('how',null,null); 寻找和'how'隔了一个单词搭配的单词
    精确序列：array('how', 'are', 'you') 
     

    -- 【json】解析：解析后的内容都是字符串({"province":"江苏","city":"南京"})
    SELECT get_json_object(json_string,json_path) 解析json的字符串json_string，返回path指定的内容;如果输入的json字符串无效，那么返回NULL。
     假设json_string为：
     

    { "store": {
    "book": [
      { "category": "reference",
        "author": "Nigel Rees",
        "title": "Sayings of the Century",
        "price": 8.95
      { "category": "fiction",
        "author": "Evelyn Waugh",
        "title": "Sword of Honour",
        "price": 12.99

    提取第一本书的作者：
     

        SELECT get_json_object(json_string,'$.store.book[0].author')
    FROM TABLE_NAME;
SELECT json_tuple(json_string,'FIELD1','FIELD2') AS (col1,col2)      提取json字符串中的特定字段并作为独立的列返回
with tmp as (
    SELECT json_tuple('{"name":"张三","hobbies":["beauty","money","power"],"address":{"province":"江苏","city":"南京"}}',
                      'name', 'hobbies', 'address') as (name, hobbies, address)

    如果提取的字段不可以直接作为独立的列，则可先作为临时表。 
     

    SELECT name,
       get_json_object(address,'`$`.province') as province,
       get_json_object(address,'`$`.city') as city,
       hobby
from tmp
lateral view explode(split(regexp_replace(hobbies,'\\[|]|"',''),','))V as hobby;

    使用 lateral view explode(split(...)) 处理 hobbies 字段：
     hobbies字段是一个JSON数组，首先通过regexp_replace函数去除方括号和引号，将其转换为普通的以逗号分隔的字符串。
     再通过split按逗号分割这个字符串，得到一个包含各个爱好的数组。
     lateral view + explode : 将数组的每个元素转成一个独立的行之后，与其他查询结果合并在最后的表中。
     

    name province city hobbies
    张三 江苏 南京 beauty
    张三 江苏 南京 money
    张三 江苏 南京 power
     

    with tmp as (
    SELECT json_tuple('{"name":"张三","hobbies":["beauty","money","power"],"address":{"province":"江苏","city":"南京"}}',
                      'name', 'hobbies', 'address') as (name, hobbies, address)
), tmp2 as (
    SELECT name,
        -- get_json_object(address,'`$`.province') as province,
        -- get_json_object(address,'`$`.city') as city,
        regexp_extract(address,'\\{"province":"(.*?)","city":"(.*?)"}',1) as province,
        regexp_extract(address,'\\{"province":"(.*?)","city":"(.*?)"}',2) as city
        regexp_replace(hobbies,'\\[|]|"','') as hobbies
    FROM tmp

    name province city hobbies
    张三 江苏 南京 beauty,money,power
     

    如果没有将hobbies由一行转多列，如何实现类似"查询所有爱好为beauty的用户"的查询？
     

    SELECT * FROM tmp2
-- WHERE find_in_set('beauty',hobbies)>0;
-- WHERE locate('beauty',hobbies)>0;
-- WHERE hobbies RLIKE '.*beauty.*';

    -- 【url】解析：PROTOCOL 协议 HOST 域名 PATH 检索 QUERY 查询 
     

    SELECT parse_url('https://search.jd.com/Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|&wq=%E5%8D%8E%E4%B8%BA&pvid=65c357d9dfb44555a9eb8708ca539b8b', 'PROTOCOL');

     协议
    -- https
     

    SELECT parse_url('https://search.jd.com/Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|&wq=%E5%8D%8E%E4%B8%BA&pvid=65c357d9dfb44555a9eb8708ca539b8b', 'HOST');

    域名
    -- search.jd.com
     

    SELECT parse_url('https://search.jd.com/Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|&wq=%E5%8D%8E%E4%B8%BA&pvid=65c357d9dfb44555a9eb8708ca539b8b', 'PATH');

    路径
    -- /Search
     

    SELECT parse_url('https://search.jd.com/Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|&wq=%E5%8D%8E%E4%B8%BA&pvid=65c357d9dfb44555a9eb8708ca539b8b', 'QUERY', 'KEY_NAME');

    检索(?后的所有内容)，检索时可以加上KEY_NAME便于查看键对应的值
    -- %E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60
     

    动态解析网页加密信息：
     

    WITH tmp AS (
    SELECT parse_url_tuple('https://search.jd.com/Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|&wq=%E5%8D%8E%E4%B8%BA&pvid=65c357d9dfb44555a9eb8708ca539b8b','QUERY','keyword') AS keyword,
        parse_url_tuple('https://search.jd.com/Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|&wq=%E5%8D%8E%E4%B8%BA&pvid=65c357d9dfb44555a9eb8708ca539b8b','QUERY','keyword') AS enc
SELECT reflect('java.net.URLDecoder','decode',keyword, if(enc is null,'UTF-8',enc)) AS keyword FROM tmp;
SELECT parse_url_tuple('https://search.jd.com/Search?keyword=%E5%8D%8E%E4%B8%BA%E6%89%8B%E6%9C%BAmate60&enc=utf-8&suggest=1.def.0.SAK7|MIXTAG_SAK7R,SAK7_M_AM_L5366,SAK7_M_GUD_R,SAK7_S_AM_R,SAK7_D_HSP_L30657,SAK7_SC_PD_R,SAK7_SM_PB_R,SAK7_SM_PRK_R,SAK7_SM_PRC_R,SAK7_SM_PRR_LC,SAK7_SS_PM_R|&wq=%E5%8D%8E%E4%B8%BA&pvid=65c357d9dfb44555a9eb8708ca539b8b','PROTOCOL','QUERY')

    从URL中提取多个值
    注意：parse_url_tuple()如果需要同时进行多种解析，此时若有QUERY解析，该解析后不可以加键名称。
     

    parse_url的具体应用场景：
    获取引流的来源或兴趣点，便于构建用户画像。(例如通过解析HOST获取引流来源，解析KEYWORD获取热点关键词。)
    不同平台的URL的格式不同，首先要对不同平台的URL格式作一定的了解和分析。
     

    ** URL的基础规则
    / 分隔域名与路径
    ? 表示查询字符串的开始
     ?query=keyword 表示查询参数(Google使用q,百度使用wd...)，后面是搜索关键词
    & 分隔多个参数
     ?query=keyword&page=2
    = 分隔键和值
     

    -- 【正则】分组提取，0表示整个字符串，1~N表示分组编号
    SELECT regexp_extract('https://www.baidu.com/s?wd=hive%20noop&rsv_spt=1&rsv_iqid=0xb9477d43000000e563&issp=1&f=8&rsv_bp=1',
    '(.?)://(.?)/(.?)?(\w+)=(.?)&(\w+)=(.?)&.',7)
     

    -- 【xml】路径提取
    -- xpath 字符串返回数组
    text() 用于选取XML元素的文本内容
    @PROPERTY_NAME 用于选取XML元素的属性值
    / 从根节点开始查找
    // 查找任意位置的元素
     

    SELECT xpath('<student stuId="1"><name>henry</name><age>22</age><gender>male</gender></student>','student/*/text()');    返回所有二级节点的值
SELECT xpath('<student stuId="1"><name>henry</name><age>22</age><gender>male</gender></student>','student/@stuId');        返回字符串中所有名为stuId的属性值
-- 返回第一个匹配结点的指定类型内容    xpath_boolean|double|int|float|long|number|short|string
SELECT xpath_string('<a><b id="foo">b1</b><b id="bar">b2</b></a>','//@id') // 返回xml字符中第一个名为id的属性值
=> "foo"

    4. 集合函数
     

    SELECT collect_list(),collect_set(),array(),split('','')                    -- 一维数组的构建
SELECT array(array('henry','jack'),array('pola','rose'))                    -- 二维数组的创建
SELECT size(array(1,2,3));                                                    -- array或map的大小
SELECT sort_array(array(22,11,33,3));                                        -- 数组排序(升序)
    -- 如何实现降序？
    SELECT reverse(sort_array(array(22,11,33,3)));
SELECT struct("henry",22,true);
{"col1":"henry","col2":22,"col3":true}                                        -- 匿名结构体
SELECT named_struct('name','henry','age',22,'is_Member',true);
{"name":"henry","age":22,"is_member":true}                                    -- 命名结构体
注意：is_Member => is_member 的原因是在结构化数据的过程中遵循JSON`首字母小写`的规约。
-- sort_array_by(array(structObj1,...),[f1,[f2...]],'ASC'|'DESC')
SELECT sort_array_by(array(                                                    -- 对结构体数组按照一定排序列进行排序
    named_struct('name','henry','age',22,'is_member',true),                
    named_struct('name','pola','age',20,'is_member',true),
    named_struct('name','ariel','age',16,'is_member',true)
),'name','ASC');
SELECT array_contains(array('henry','pola','ariel'),'pola')                    -- 判定数组中是否包含指定元素
SELECT split('henry','pola','ariel',',')[0];                                -- 获取数组元素
SELECT str_to_map('java:88,hadoop:96,hive:56',',',':');                        -- 
                                                                            -- str_to_map()的输入字符串必须符合映射的格式。
SELECT `map`("java",88,"hadoop",96,"hive",56);
SELECT explode(`map`("java",88,"hadoop",96,"hive",56)) AS (subject,score);    -- 列转行
SELECT map_keys(`map`("java",88,"hadoop",96,"hive",56)) AS key_set;             -- 键set
SELECT map_values(`map`("java",88,"hadoop",96,"hive",56)) AS value_set;         -- 值set
SELECT stack(N,f1,...,fn);                                                     -- 将n个数据分为N等份，每份占一行。若n%N!=0，报错。

    5. 条件函数
     

    SELECT if(true,1,0);
SELECT in_file('ariel','/root/hive/data/course/hive_func_in_file.data');    -- 判断 某个表中某个字段的值|指定内容 是否出现在指定文件中
SELECT isfalse();
    SELECT isfalse(0); => true
SELECT istrue();
SELECT isnull();
SELECT not();                                                                -- 取相反的情况
SELECT nullif(2,3);                                                            -- 如果两个参数相等，返回NULL;如果不相等，返回第一个参数。
    避免除零错误：
        SELECT col1, col2, col1/NULLIF(col2, 0) AS result(任何涉及NULL的结果都是NULL)
        FROM TABLE_NAME;
SELECT nvl(null,3);                                                            -- 返回第一个非NULL参数的值(只能有两个参数)
    替换NULL值为默认值：
        SELECT NVL(col,'默认值') AS new_col
        FROM TABLE_NAME;
    聚合函数中处理NULL值：
        SELECT SUM(NVL(col,0)) AS total
        FROM TABLE_NAME;
SELECT coalesce(null,null,...,5);                                            -- 返回第一个非NULL参数的值(可以有多个参数)
SELECT case f1 when V1 then ... when v2 then ... else vn end;
SELECT case when f1>=v1 then ... when f1>=v2 then ... else ... end;  
WITH tmp AS(
    SELECT named_struct('name','me','age',18,'is_member',true) as self,
           array(
                named_struct('name','henry','age',22,'is_member',true),
                named_struct('name','pola','age',20,'is_member',true),
                named_struct('name','ariel','age',16,'is_member',false)
           ) AS array_struct 
SELECT self.name,self.age,self.is_member,name,age,is_member
FROM tmp
LATERAL VIEW inline(array_struct)V AS name,age,is_member;

    self.name self.age self.is_member name age is_member
    me 18 true henry 22 true
    me 18 true pola 20 true
    me 18 true ariel 16 false
     

    LATERAL VIEW + inline() 用于将结构体数组的每个数组元素转化为一行，并且合并到最终结果中。
     

    ** explode和inline的区别:
    explode适用于单个字段的简单数组
    inline适用于复杂的结构体数组
     

    6. 其他函数
     

    SELECT version(); -- 检查Hive的版本
     
    推荐文章
    大力的牛肉面  ·  SG Com Unity 软件包脚本
    1 月前
    没人理的大熊猫  ·  600岁昆曲为何魅力不减（薪火相传）--知识产权--人民网
    2 月前
    没人理的骆驼  ·  具体并行集合类 | Parallel Collections | Scala Documentation
    2 月前
    性感的野马  ·  C++基础——非类型模板参数_c++非类型实体-CSDN博客
    3 月前
    温文尔雅的竹笋  ·  “世界预防自杀日”：世卫组织呼吁全社会协作防止自杀行为 | | 1联合国新闻
    4 月前
    Link管理   ·   51好读   ·   Sov5搜索   ·   小百科
    link管理 - 链接快照平台