hive-SQL学习笔记12

上一篇是初步（分组），本文逐渐进阶高级查询，比如正则，条件，group，且看如下分解，看懂别人的代码实际上也是读懂别人的想法，这也算是内心的交流。

For Recommendation in Deep learning QQ Group 277356808

For deep learning QQ Second Group 629530787

I'm here waiting for you

1-正则regexp_replace

其实就是个替换，如下，将A中的B替换为C，【注意B此时可以加中括号】

REGEXP_REPLACE(A, B, C)

例如去掉A中的前缀或者后缀，那么将B设置为前缀或者后缀即可，此时C为""(空字符串)。但这种没有体现正则，纯粹就是Replace，那么下面的就是正则了，

REGEXP_REPLACE(A, '[a-z]', '')#将小写字母全部替换为空,中括号必带
REGEXP_REPLACE(A, '[^a-z]', '')#替换所有非小写字母
REGEXP_REPLACE(A, '[0-9]', '')#替换所有数字
REGEXP_REPLACE(A, '\n|\t|\r', '')#替换掉换行,很有用

2- mapjoin关联，对数据倾斜有帮助

这里挖个坑，下次再填，因为我这里有没有这个都一样的结果，而且其他地方并没有再次出现这个new_tab,所以果断删掉，【后面通过diff test1.txt test2.txt比较两个文件并无差异】

SELECT /*+ mapjoin(new_tab)*/ 
user_id, item_id, max(time) 
FROM table

但这里要清楚max的含义，是因为后面有个group by（上面没有补上），因此对比上一篇，里面有个坑（就是rn忘了写了，直接在那个括号后面即可，就是个别名）

其实上个博文的第2个问题并没有用group by 来解决，如果是仅仅获取其中两列（即不要item列），那么可以直接得到结果，然而是需要那一行的数据，那怎么解决呢？如下，但是这个结果与上一篇博文结果不一样，数据少了很多行。【网上搜的】

select a.* from test a,
(select type,max(timestamp) timestamp from test group by type) b 
where a.type = b.type and a.timestamp = b.timestamp order by a.type 

群里大佬说lag函数，留个坑吧，现在继续向前推进。

3-like 和case when

like顾名思义，就是像，后面跟的就是模糊匹配的东西，比如以“000”开头的user_id,

select user_id,item_id,time from ClickLogTable
where datetime=20200101
and user_id like '000%'

当然也可以是带有“000”（举个例子）的字符串，那就（也可能是中间，也可能是两边是000）

and user_id like '%000%'

case when then其实就相当于if then

case when time>12 then length(user_id)>24
when time<=12 then length(user_id)<24
else user_id is not NULL
end

如果time满足条件，那么需要user_id满足啥条件，都不满足when的条件，那么user_id不能为空，然后end，后面可以继续加and条件，经过测试，就是这种含义。

4-concat_ws 和collect_set和struct

4.1先说concat，这个望文生义，就是拼接在一起，比如，concat(A,B,C,.....)，那么输出就是ABC...如果其中有一个为NULL，那么结果就是null

select user_id,item_id ,
concat(user_id,'-',item_id)
from CLickLogTable
where datetime=20220101

那么就是将user_id与item_id以"-"连接起来。这样也可以写成下面的表示：

select user_id,item_id ,
concat_ws('-',user_id,item_id)
from CLickLogTable
where datetime=20220101

其实就是concat_ws(sep，A,B),sep是分隔符，连接在AB之间的字符

4.2struct

【0305我遇到幺蛾子了，卧槽，在spark-hive就能跑的在hive-SQL就不能跑，卧槽，这啥几把代码，bug出错了】

FAILED: RuntimeException cannot find field sid(lowercase form: sid) in [col1, col2]

发现是spark-SQL的存储写错了，TXT及csv的存储方法（方式）只有下面两种

    #df.repartition(10).write.format("csv").save(path=save_path)
    df.select('user_id').write.text(path=save_path)

第二个存储文件太多了，没有必要，可能会消耗CPU，上面的那个是存储为10个csv文件，hadoop下拉也比较快，而后者比较慢。

【0308回归正题】

struct是不同类型的数据放在一起，比如字符串，浮点，数据构成字典类型（结构），如下所示：

SELECT struct(user_id,item_id,time)
FROM ClickLogTable
where datetime=20220101 and hour=01
and user_id is not null and time is not null

#results
{"col1":"0002","col2":"8CON","col3":"73547"}
{"col1":"0006","col2":"8C9n","col3":"72316"}
{"col1":"001d","col2":"8GsC","col3":"73128"}
{"col1":"001d","col2":"89rN","col3":"73000"}

此时也可排序吧，但不能以上述结构体的名字order by了，

SELECT struct(user_id,item_id,time) AS dict
FROM ClickLogTable
where datetime=20220101 and hour=01
and user_id is not null and time is not null
ORDER BY dict

#results
{"col1":"7793","col2":"8CPq","col3":"70305"}
{"col1":"7793","col2":"8CQ8","col3":"73273"}
{"col1":"7793","col2":"8CQR","col3":"70256"}
{"col1":"3272","col2":"8CLQ","col3":"70484"}
{"col1":"3272","col2":"8CNA","col3":"70454"}
{"col1":"3272","col2":"8CQK","col3":"70196"}
{"col1":"915c","col2":"8CLF","col3":"73547"}
{"col1":"915c","col2":"8COq","col3":"71086"}
{"col1":"915c","col2":"8CPz","col3":"73195"}
{"col1":"915c","col2":"8CQB","col3":"73392"}

上面的user_id是去掉了前缀的（我保证是按字符串的顺序的），上面的order by dict可见是by其中的前两列了，同一个用户时间上并不是顺序，如果要求按照其中user_id和time排序怎么实现呢？（不能另外再增加user_id和time），强行order by user_id, time 会报错如下：留个坑吧。下面解决

FAILED: SemanticException [Error 10004]: Line 1:150 Invalid table alias or column reference 'user_id': (possible column names are: dict)

4.3-collect_set与list,set与list正是py中的含义，set去重，list是转成列表

直接
ORDER BY dict.\`col1\`,dict.\`col3\`
报错

加上
collect_list(struct(user_id,item_id,time)) AS dict
如下
[{"col1":"000a5ecb8e60438d84bace1734a64aea","col2":"8COggsIoYSy","col3":"1640974713"},{"co    l1":"000a5ecb8e60438d84bace1734a64aea","col2":"8CN8ZSbcv0I","col3":"1640972582"},。。。

由col3时间信息发现并没有按照顺序排，连基本的的字符串的顺序都不是，无论是啥顺序，7都不可能在4前面。

emmm改成sort_array呢？还是按照前两列排列的，如下

sort_array(collect_set(struct(user_id,item_id,time))) AS dict
[{"col1":"7793","col2":"8CPq","col3":"70305"},{"col1":"7793","col2":"8CQ8","col3":"73273"},{"col1":"7793","col2":"8CQR","col3":"70256"}。。

肯定有这样的函数，sort_array_by

sort_array_by(collect_set(struct(uid,id,ctime)),'col1','col3')#
[{"col1":"7793","col2":"8CQR","col3":"70256"},{"col1":"7793","col2":"8CPq","col3":"70305"},{"col1":"7793","col2":"8CQ8","col3":"73273"},...

还可以加ASC，DESC放在后面，但要对应，但我尝试加user_id及time（倒排），也就是一个升序，一个降序不行，搜到的如下：

sort_array_by(array(obj1, obj2,...),'f1','f2',...,['ASC','DESC']) 

先这样吧，下期再见。拜拜

愿我们终有重逢之时，

而你还记得我们曾经讨论的话题。