那些常忘的ODPS函数用法

前言

经常用不会忘记的，我这就不再赘述了

字符串相关

常用函数汇总

函数名	函数功能	函数定义
CHAR_MATCHCOUNT	返回str1中有多少个字符出现在str2中	bigint char_matchcount(string str1, string str2)
CHR	将给定ASCII码ascii转换成字符	string chr(bigint ascii)
CONCAT	连接字符串	string concat(string a, string b...)
KEYVALUE	返回key-value对中key所对应的value	KEYVALUE(STRING srcStr，STRING split1，STRING split2， STRING key) KEYVALUE(STRING srcStr， STRING key) //split1 = ";"，split2 = ":"
REGEXP_REPLACE	字符串替换	string regexp_replace(string source, string pattern, string replace_string[, bigint occurrence])
SPLIT_PART	根据分隔符拆分字符串	string split_part(string str, string separator, bigint start[, bigint end]))
SUBSTR	返回字符串指定位置指定长度的的子串。字符串替换	string substr(string str, bigint start_position[, bigint length])

CHAR_MATCHCOUNT: 计算str1中有多少个字符出现在str2中

函数声明：

bigint char_matchcount(string str1, string str2)

用途：用于计算str1中有多少个字符出现在str2中。

参数说明：

• str1，str2：String类型，必须为有效的UTF-8字符串，如果对比中发现有无效字符则函数返回负值。

返回值：Bigint类型。任一输入为NULL返回NULL。

示例：

char_matchcount('abd','aabc') = 2
-- str1中得两个字符串'a', 'b'在str2中出现过

FIND_IN_SET：查找字符串str1在以逗号（,）分隔的字符串str2中的位置（是否存在）

函数声明：

bigint find_in_set(string <str1>, string <str2>)

用途： 查找字符串str1在以逗号（,）分隔的字符串str2中的位置，从1开始计数

参数说明：

• str1：必填。STRING类型。待查找的字符串。
• str2：必填。STRING类型。以逗号（,）分隔的字符串。

返回值说明： 返回BIGINT类型。返回规则如下：

• 当str2中无法匹配到str1或str1中包含逗号（,）时，返回0。
• 当str1或str2为NULL时，返回结果为NULL

示例1：查找字符串ab在字符串abc,hello,ab,c中的位置。命令示例如下。

select find_in_set('ab', 'abc,hello,ab,c');
--返回3

备注：判断是否存在时场景，只需要设置返回值>0即可

行列转化

列转行

方式一：自身join

在ODPS中，可以通过自身的JOIN操作来实现列转行的操作。具体步骤如下：

假设有一个表t1，包含三列id、name、value，如下所示：

id	name	value
1	A	value1
1	A	value2
1	A	value3
2	B	value2
2	B	value3
3	C	value1
3	C	value2
3	C	value3

现在需要将value列中的值转为一行，可以使用自身的JOIN操作来实现，如下所示：

SELECT
  t1.id,
  t1.name,
  t2.value
FROM
  t1
JOIN
  t1 t2
ON
  t1.id = t2.id
WHERE
  t1.value = 'value1'
AND t2.value = 'value2';

执行以上SQL语句后，可以得到以下结果：

id	name	value
1	A	value2
3	C	value2

在结果表中，只保留了满足条件的数据，即t1表中value列为'value1'且t2表中value列为'value2'的行。通过自身JOIN操作，将满足条件的数据连接到一起。

需要注意的是，自身JOIN操作会产生笛卡尔积，因此在实际使用时需要根据实际情况添加适当的筛选条件（例如WHERE子句），以保证结果的准确性。

方式二： COLLECT_LIST + CONCAT_WS

COLLECT_LIST:

将colname指定的列值聚合为一个数组 (此函数为MaxCompute 2.0扩展函数)

• 命令格式

array collect_list(<colname>)

• 返回值说明

返回ARRAY类型。colname值为NULL时，该行不参与计算。

CONCAT_WS

返回将参数中的所有字符串或ARRAY数组中的元素按照指定的分隔符连接在一起的结果。此函数为MaxCompute 2.0扩展函数。

• 命令格式

string concat_ws(string <separator>, string <str1>, string <str2>[,...])
string concat_ws(string <separator>, array<string> <a>)

用COLLECT_LIST和CONCAT_WS函数实现行转列的示例：

SELECT
  id,
  CONCAT_WS(',', COLLECT_LIST(value)) AS merged_col
FROM
  t
GROUP BY
  id;

在这个示例中，我们使用COLLECT_LIST函数将每个id对应的value列的值收集为一个数组。然后，我们使用CONCAT_WS函数将数组中的值连接成一个字符串，使用逗号作为分隔符。

执行上述查询后，将得到以上所示的结果。

可以看到，我们成功地将每个id对应的所有值合并为一个列，并按id进行了分组。

需要注意的是，行转列的结果将依赖于原始数据的分组情况。在上述示例中，我们按id进行了分组，因此每个id对应的值被合并为一个单独的字符串。

如果想要将多个列转换为一列，只需在CONCAT_WS函数中添加需要合并的列即可。

方式三： WM_CONCAT

用指定的separator做分隔符，连接colname中的值。

命令格式

string wm_concat(string <separator>, string <colname>)

参数说明

• separator：必填。STRING类型常量，分隔符。
• colname：必填。STRING类型。如果输入为BIGINT、DOUBLE或DATETIME类型，会隐式转换为STRING类型后参与运算。
• 示例 下面是如何使用WM_CONCAT函数的示例：

假设有一个表student_scores，结构如下：

| student_id | subject | score |
|------------|---------|-------|
| 1          | Math    | 90    |
| 1          | English | 85    |
| 2          | Math    | 75    |
| 2          | English | 80    |

我们想要将每个学生的所有科目和分数合并为一个字符串，可以使用以下SQL查询：

1SELECT
2  student_id,
3  WM_CONCAT(',', subject) AS subjects,
4  WM_CONCAT(',', CAST(score AS STRING)) AS scores
5FROM
6  student_scores
7GROUP BY
8  student_id;

执行上述查询后，你将得到每个student_id对应的所有subject和score，合并成一个以逗号分隔的字符串。示例如下：

| student_id | subjects       | scores |
|------------|----------------|--------|
| 1          | Math,English   | 90,85  |
| 2          | Math,English   | 75,80  |

请注意，WM_CONCAT函数的参数是要连接的字段，以及用作分隔符的字符串（在这个例子中是逗号）。由于WM_CONCAT只接受字符串类型，因此需要将数值类型的score转换为字符串类型，这里使用了CAST函数。

这种方法有个潜在的问题：默认情况下，WM_CONCAT函数不保证元素的顺序。如果顺序对你的应用很重要，你可能需要考虑其他方法来确保顺序，比如使用ROW_NUMBER()函数先对数据进行排序。

备注：wm_concat无法作用于数组

行转列

Lateral View

DPS中的Lateral View语句用于将一个表的列（含分隔符）展开成多行，通常用于处理数组或集合类型的列

SELECT ...
FROM table
LATERAL VIEW [OUTER] udtf(expression) AS alias_column

假设已有一张表pageAds，它有三列数据，第一列是pageid string，第二列是col1 array<int>，第三列是col2 array<string>，详细数据如下。

pageid	col1	col2
front_page	[1, 2, 3]	[“a”, “b”, “c”]
contact_page	[3, 4, 5]	[“d”, “e”, “f”]

单个 Lateral View语句拆分col1。命令示例如下：

select pageid, col1_new, col2 from pageAds lateral view explode(col1) adTable as col1_new;

-- 返回结果如下：
+--------------+------------+-------------+
| pageid       | col1_new   | col2        |
+--------------+------------+-------------+
| front_page   | 1        | ["a","b","c"] |
| front_page   | 2        | ["a","b","c"] |
| front_page   | 3        | ["a","b","c"] |
| contact_page | 3        | ["d","e","f"] |
| contact_page | 4        | ["d","e","f"] |
| contact_page | 5        | ["d","e","f"] |
+------------+------------+------------+

集合操作：交集、并集和补集

• 交集：求两个数据集的交集，即输出两个数据集均包含的记录。
• 并集：求两个数据集的并集，即将两个数据集合并成一个数据集。
• 补集：求第二个数据集在第一个数据集中的补集，即输出第一个数据集包含而第二个数据集不包含的记录。

交集 ( intersect all/intersect [distinct] )

方式一：INNER JOIN 。。。基操忽略

方式二：

• 命令格式

--取交集不去重。
<select_statement1> intersect all <select_statement2>;
--取交集并去重。intersect效果等同于intersect distinct。
<select_statement1> intersect [distinct] <select_statement2>;

• 参数说明
• select_statement1、select_statement2：必填。select语句，格式请参见SELECT语法。
• distinct：可选。对两个数据集取交集的结果去重。

并集 ( union all/union [distinct] )

• 命令格式

--取并集不去重。
<select_statement1> union all <select_statement2>;
--取并集并去重。
<select_statement1> union [distinct] <select_statement2>;

• 注意事项
• 存在多个union all时，支持通过括号指定union all的优先级。
• union后如果有cluster by、distribute by、sort by、order by或limit子句时，如果设置set odps.sql.type.system.odps2=false;，其作用于union的最后一个select_statement；如果设置set odps.sql.type.system.odps2=true;时，作用于前面所有union的结果。
• 参数说明
• select_statement1、select_statement2：必填。select语句
• distinct：可选。对两个数据集取并集的结果去重。

补集( except all/minus all/except [distinct]/minus [distinct] )

• 命令格式

--取补集不去重。
<select_statement1> except all <select_statement2>;
<select_statement1> minus all <select_statement2>;
--取补集并去重。
<select_statement1> except [distinct] <select_statement2>;
<select_statement1> minus [distinct] <select_statement2>;

说明 except和minus等效。

• 参数说明
• select_statement1、select_statement2：必填。select语句
• distinct：可选。对取补集的结果去重。

TopN（ROW_NUMBER() OVER）

按照某一个字段分组后取某个分组的前N行

诉求：假设您有一个名为table_name的ODPS表，其中有一个字段为group_field，您可以按照group_field字段进行分组，并取每个分组的前200条数据

方式1： 可以使用ODPS的分区操作来按照某个字段进行分组，并使用LIMIT子句来限制每个分组的数据条数。

方式2： 使用ROW_NUMBER窗口函数

SELECT *
FROM (
 SELECT *,
 ROW_NUMBER() OVER(PARTITION BY group_field ORDER BY your_order_field) AS rn
 FROM table_name
) t
WHERE rn <= 200

先利用ROW_NUMBER()函数为每个分组内的数据进行编号，然后再筛选出行号小于等于200的数据。

注意：请注意，这个方法适用于每个分组的数据量不大的情况，如果每个分组的数据量很大，可能会影响查询的性能。如果有大量数据需要处理，您可能需要考虑使用其他更高效的方法。

完整的文档，建议直接看官方文档:

https://help.aliyun-inc.com/internaldoc/detail/413657.html?spm=a2c1f.8259796.2.112.QbF0CL