SQL & HiveSQL 进阶函数、语法与使用技巧

字符串相关

REPEAT：重复

把字符串重复 x 次并返回

SELECT REPEAT('SQL', 3);
+---------------------------------------------------------+
| REPEAT('SQL', 3)                                      |
+---------------------------------------------------------+
| SQLSQLSQL                                         |
+---------------------------------------------------------+
1 row in set (0.00 sec)

split：分隔

注意：mysql 8.4 依然不支持 split 函数，hive 是支持的

select split('&&&','&');
return: ["","","",""]

concat_ws：字符串拼接

concat_ws 第一个字符为分隔符，只要有一个字符串不是 NULL，就不会返回 NULL

select concat_ws('-','a','b',null);
return: a-b

对比 concat

concat 只要有一个为 null ，直接返回 null

日期相关函数

前一天

date_sub(date,interval 1 day)

date_add(date,-1)

推荐用 add ，sub 可能存在兼容性问题

时间戳转日期

13位 Unix 时间戳转日期

from_unixtime(timestamp/1000)

10位 Unix 时间戳转日期

from_unixtime(timestamp)

可以指定格式

select format_datetime(current_timestamp,'yyyy-MM-dd');

区间字段打平：explode

举个例子：源表的数据是一个区间，比如物品 x 处在 1 ～ 10，表字段对应两个边界为 1 和 10，现在需要将这个区间打平到每一个点，即 1 2 3 4 5 6 7 8 9 10，一行变十行数据，此时就需要用到 explode 函数

select explode(array) from tmptable

explode 和 posexplode 的区别

• explode 只返回数组或MAP中的元素或值。
• posexplode 返回一个包含两个字段的行：第一个字段是元素在原始数组中的位置索引（从0开始），第二个字段是元素本身。

搭配 lateral view 使用

入参：一个数组，数组有x个元素，就把一行数据变成x行数据

select <字段>
from <原始表名称>
lateral view posexplode(<数组>) <表别名> as new_col1,new_col2... 

eg:
lateral view posexplode(split(repeat_num,'&')) t1 as row_index,data

搭配 sequence 使用（推荐）

这种方式比较简洁

eg: 日期区间打平
select explode(sequence(start,end, interval 1 day)) as sale_date from xxx

另一种实现方案：手写循环

declare @i int
set @i=1
while @i<30
begin
  insert into test (userid) values(@i)
set @i=@i+1
end

数组（列不支持）

MySQL 是不支持某一列存数组的，但是中间状态常常会有数组

取数组元素：[]

数组默认从1开始， 可通过 set array.subscript.start.at0=true; 参数设置令其从 0 开始

-- 取数组第一个元素
select arrayvalue[1] from testtable;

contains：是否存在

select contains(array[3,323], 3);

collect_set：去重数组

相对的，collect_list 就是不去重的版本

注意：这两个函数都不保证顺序性

这是个聚合函数，使用前提是 group by，在 group by 的维度基础上，把字段拼接成一个数组

select
  t.id,
  concat_ws(',', collect_set(t.id))
from
  temp t
group by
  t.id

sequence：补齐递增(减)序列

sequence 生成一个数组，其中包含从 start 到 stop（含）的元素，这些元素按 step 递增。

基本模版：sequence(start, stop [, step] )

• start：整数类型、DATE 或 TIMESTAMP 的表达式。
• stop：如果 start 是数字，则为整数，否则为 DATE 或 TIMESTAMP。
• step：如果 start 是 DATE 或 TIMESTAMP，则为 INTERVAL 表达式；否则为整数。

// 1. 基础递增
SELECT sequence(1, 5);
 [1,2,3,4,5]

// 2. 递减
SELECT sequence(5, 1);
 [5,4,3,2,1]

// 日期 日递增
sequence(date1,date2, interval 1 day)

sort_array：排序

sort_array(e: column, asc: boolean)将array中元素排序(自然排序)，默认asc。

其他

条件判断：CASE WHEN

CASE WHEN 条件1 THEN 1 （结果） 
 WHEN 条件2 THEN 2 
     ELSE 3
     END
if(条件,a,b) # 满足条件为a 否则为b 类似三目

为 null 定制排序规则：IF

ORDER BY  IF(ISNULL(title), 0, 1), money;

查询随机行

# 一行
select count(*) into @C from t;
set @Y = floor(@C * rand());
set @sql = concat("select * from t limit ", @Y, ",1");
prepare stmt from @sql;
execute stmt;
DEALLOCATE prepare stmt;

# 多行 无法保证不重复
mysql> select count(*) into @C from t;
set @Y1 = floor(@C * rand());
set @Y2 = floor(@C * rand());
set @Y3 = floor(@C * rand());
# 在应用代码里面取Y1、Y2、Y3值，拼出SQL后执行
select * from t limit @Y1，1； 
select * from t limit @Y2，1；
select * from t limit @Y3，1；

同行不同列数据比较

coalesce：空值过滤

COALESCE 入参有多个，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。

select coalesce(column, 1) from tableA

窗口函数：over() + rownumber()、count(*)

窗口函数（Window Function）是 SQL2003 标准中定义的一项新特性，输入多行（一个窗口）、返回一个值。

特点是 over

下面介绍搭配 rank 的使用

排序：rank

先按 PARTITION 分区，同维度下，配合 order by 计算 rank 排名

之后通过 where 过滤排名即可

eg:

        SELECT *,
               rank() over(PARTITION BY xxx ORDER BY column DESC) AS row_rank
          FROM 

临时表：with as

WITH AS短语，也叫做子查询部分（subquery factoring），是用来定义一个 SQL 片段，该 SQL 片段会被整个 SQL 语句所用到。这个语句算是公用表表达式（CTE）。

with 
cr as 
( 
    select colomn from testtable 
)

如上所示，as 部分的查询结果也是一张表，通过这种方式就会被放到一张叫 cr 的临时中间表

后续要使用就可以：

select * from cr

全局变量：#set

在第一行首先指定全局变量

#set tmpdate = '20240606'

参考文档

https://developer.aliyun.com/article/632189

https://blog.csdn.net/weixin_40809627/article/details/129561470

https://zhuanlan.zhihu.com/p/557142139

https://help.aliyun.com/zh/sls/user-guide/unnest-clause

https://help.aliyun.com/zh/maxcompute/use-cases/transpose-rows-to-columns-or-columns-to-rows

https://www.studytime.xin/article/hive-knowledge-window-function.html

MySQL实战45讲