Hive中高频常用的函数和语法梳理及业务场景示例

聚合函数

collect_list - 收集列值到一个数组

collect_list函数用于将指定列的值收集到一个数组中，并返回该数组作为结果。它通常在GROUP BY子句中使用，以将相同键的值收集到一个数组中进行聚合操作

以下是collect_list函数的语法：

collect_list(column)

其中，column是要收集的列名或表达式。

collect_list函数将指定列的值收集到一个数组中，并返回结果数组。例如，如果要对某个表按照category列进行分组，并将每个分组中的product列的值收集到一个数组中，可以使用以下查询：

SELECT category, collect_list(product) AS products
FROM your_table
GROUP BY category;

此查询将按照category列进行分组，并为每个分组创建一个数组products，其中包含该分组中的所有product值。

注意事项：

• collect_list函数返回的是一个数组，可以包含重复的值。
• 如果要去除数组中的重复值，可以使用collect_set函数代替。
• Hive中的collect_list函数要求所有要收集的值都能适应内存，因此在处理大数据量时需要注意内存限制。如果数据量太大，可能需要考虑其他方式进行聚合操作。

函数示例用法：

假设有一个名为orders的表，包含以下列：order_id（订单ID）、customer_id（客户ID）和product（产品名称）。

+----------+-------------+-------------------+
| order_id | customer_id | product           |
+----------+-------------+-------------------+
| 1        | 101         | iPhone            |
| 2        | 101         | MacBook Pro       |
| 3        | 102         | iPad              |
| 4        | 102         | Apple Watch       |
| 5        | 102         | AirPods           |
+----------+-------------+-------------------+

要按照customer_id进行分组，并将每个分组中的product收集到一个数组中，可以使用以下查询：

SELECT customer_id, collect_list(product) AS products
FROM orders
GROUP BY customer_id;

查询结果将如下所示：

+-------------+----------------------------------+
| customer_id | products                         |
+-------------+----------------------------------+
| 101         | ["iPhone", "MacBook Pro"]         |
| 102         | ["iPad", "Apple Watch", "AirPods"]|
+-------------+----------------------------------+

对于每个customer_id分组，collect_list函数将该分组中的product值收集到一个数组中，并将该数组作为结果返回。

size - 返回数组或Map元素数量

SIZE()函数用于返回数组或Map的大小（元素数量）。它可以用于计算集合类型的列中元素的数量，例如数组和Map

SIZE()函数主要用于计算集合类型（数组和Map）的大小，提供了对集合元素数量的统计和分析能力

功能介绍： SIZE(collection)函数接受一个集合类型的参数（数组或Map），并返回该集合中元素的数量。

示例： 假设有一个表employees，其中包含员工ID（employee_id）和所掌握技能的数组（skills）。为了计算每个员工所掌握技能的数量，可以使用SIZE()函数：

SELECT employee_id, SIZE(skills) AS num_skills
FROM employees;

这将返回一个结果集，包含员工ID和他们所掌握技能的数量。SIZE()函数将计算每个数组的大小，并将其作为列num_skills的值返回。

另外，SIZE()函数也可以用于计算Map中键值对的数量。假设有一个表product_sales，其中包含产品ID（product_id）和销售额度的Map（sales_by_month）。为了计算每个产品的销售月份数量，可以使用SIZE()函数：

SELECT product_id, SIZE(sales_by_month) AS num_months
FROM product_sales;

这将返回一个结果集，包含产品ID和销售月份的数量。SIZE()函数将计算每个Map中键值对的数量，并将其作为列num_months的值返回。

SIZE()函数在Hive中是用于计算集合类型（数组和Map）大小的常用函数之一。它可以帮助我们进行集合元素数量的统计和分析，从而洞察数据的结构和特征。

示例业务场景：

1. 社交媒体分析： 假设有一个社交媒体平台的用户表，其中包含用户ID（user_id）和用户的好友列表（friends）。好友列表是一个存储好友ID的数组。为了分析每个用户的好友数量分布，可以使用SIZE()函数计算好友列表的大小：

SELECT user_id, SIZE(friends) AS num_friends
FROM user_friends;

1. 购物篮分析： 假设有一个电子商务平台的订单表，其中包含订单ID（order_id）和商品列表（items）。商品列表是一个包含多个商品ID的数组，表示一个订单中购买的多个商品。为了分析每个订单中购买的商品数量，可以使用SIZE()函数计算商品列表的大小：

SELECT order_id, SIZE(items) AS num_items
FROM orders;

1. 日志分析： 假设有一个日志表，其中包含用户访问网页的日志信息，包括用户ID（user_id）和访问的页面列表（pages）。页面列表是一个存储页面URL的数组。为了分析每个用户访问的页面数量分布，可以使用SIZE()函数计算页面列表的大小：

SELECT user_id, SIZE(pages) AS num_pages
FROM user_logs;

length - 返回字符串长度

length() 函数用于返回字符串的长度（字符数）。它接受一个字符串作为参数，并返回该字符串中字符的数量

函数使用方法示例：

SELECT length('Hello, World!') AS str_length;

输出结果：

str_length
------------
13

在上面的示例中，length('Hello, World!') 返回字符串 'Hello, World!' 中字符的数量，即 13。

LENGTH()函数主要用于计算字符串类型的长度，可用于验证字符串的长度限制、进行字符串截取等操作

对于字符串，LENGTH()函数返回字符串的字符数（包括空格和特殊字符）

窗口函数

lag - 取结果集中当前行之前的行的值

LAG()函数用于获取结果集中当前行前面的行的值。它可以用于执行窗口函数操作，为每一行提供前一个行的值。

功能介绍： LAG(expression, offset, default_value)函数返回当前行指定偏移量之前的行的值。如果没有前面的行（例如，当前行是第一行），则返回指定的默认值。它通常与OVER子句和ORDER BY子句一起使用。

示例业务场景：

1. 销售增长率计算： 假设有一个销售数据表，其中包含每个月的销售额（sales_amount）。为了计算每个月的销售增长率，可以使用LAG()函数获取上个月的销售额，并计算增长率：

SELECT month, sales_amount,
    (sales_amount - LAG(sales_amount, 1, 0) OVER (ORDER BY month)) / LAG(sales_amount, 1, 1) OVER (ORDER BY month) AS sales_growth_rate
FROM sales_data;

1. 用户行为分析： 假设有一个用户日志表，其中包含用户ID（user_id）和登录时间（login_time）。为了分析用户的登录间隔时间，可以使用LAG()函数获取上次登录的时间，并计算间隔时间：

SELECT user_id, login_time,
    login_time - LAG(login_time, 1, login_time) OVER (PARTITION BY user_id ORDER BY login_time) AS time_interval
FROM user_logs;

1. 库存变动计算： 假设有一个库存交易表，其中包含产品ID（product_id）、交易日期（transaction_date）和交易数量（transaction_quantity）。为了计算每次交易的库存变动量，可以使用LAG()函数获取上次交易的数量，并计算变动量：

SELECT product_id, transaction_date, transaction_quantity,
    transaction_quantity - LAG(transaction_quantity, 1, 0) OVER (PARTITION BY product_id ORDER BY transaction_date) AS inventory_change
FROM inventory_transactions;

在这些示例中，LAG()函数被用于获取结果集中的前一行的值，以进行相关的计算或分析。这样可以轻松处理时间序列、前后行数据比较等情况，帮助进行更深入的数据分析和洞察。根据具体的业务需求，你可以结合其他函数和子句来构建复杂的分析查询。

lead - 取结果集中当前行之后的行的值

LEAD()函数用于获取结果集中当前行后面的行的值。它可以用于执行窗口函数操作，为每一行提供后一个行的值。

功能介绍： LEAD(expression, offset, default_value)函数返回当前行指定偏移量之后的行的值。如果没有后面的行（例如，当前行是最后一行），则返回指定的默认值。它通常与OVER子句和ORDER BY子句一起使用。

示例业务场景：

1. 周期性数据分析： 假设有一个销售数据表，其中包含产品ID（product_id）、销售日期（sale_date）和销售量（sales_quantity）。为了计算每个产品的销售增长率，可以使用LEAD()函数获取后一天的销售量，并计算增长率：

SELECT product_id, sale_date, sales_quantity,
    (LEAD(sales_quantity, 1, 0) OVER (PARTITION BY product_id ORDER BY sale_date) - sales_quantity) / sales_quantity AS sales_growth_rate
FROM sales_data;

1. 用户活跃度分析： 假设有一个用户活跃度表，其中包含用户ID（user_id）和活跃日期（active_date）。为了分析每个用户的连续活跃天数，可以使用LEAD()函数获取下一天的活跃日期，并计算连续活跃天数：

SELECT user_id, active_date,
    DATEDIFF(LEAD(active_date, 1, active_date) OVER (PARTITION BY user_id ORDER BY active_date), active_date) AS consecutive_active_days
FROM user_activity;

1. 股票数据分析： 假设有一个股票交易数据表，其中包含股票代码（stock_code）、交易日期（trade_date）和收盘价（closing_price）。为了计算每只股票的涨跌幅，可以使用LEAD()函数获取后一天的收盘价，并计算涨跌幅：

SELECT stock_code, trade_date, closing_price,
    (LEAD(closing_price, 1, closing_price) OVER (PARTITION BY stock_code ORDER BY trade_date) - closing_price) / closing_price AS price_change_rate
FROM stock_data;

在这些示例中，LEAD()函数被用于获取结果集中的后一行的值，以进行相关的计算或分析。这样可以轻松处理时间序列、前后行数据比较等情况

row_number - 返回结果集中行号

row_number()是一个窗口函数，用于为查询结果集中的每一行分配一个唯一的序号。它常用于对查询结果进行排序或分组后，为每一行分配一个序号，以便进行进一步的数据处理或筛选。

row_number()函数的语法如下：

row_number() over ([partition by col1, col2, ...] order by col3, col4, ...)

• partition by子句可选，用于指定分组的列。如果指定了分组列，那么在每个分组内，row_number()会独立计算序号，即每个分组的第一行序号为1。
• order by子句用于指定排序的列。查询结果将按照指定的列顺序进行排序。

下面是一个示例，展示了如何使用row_number()函数：

SELECT col1, col2, col3, row_number() OVER (ORDER BY col3) as row_num
FROM table_name;

在上述示例中，row_number()函数根据 col3 列的值进行排序，并为每一行分配一个序号，存储在名为 row_num 的新列中。

条件函数

ifnull - 如第一个表达式为空，返回第二个表达式值，否则返回第一个表达式值

IFNULL()函数用于将NULL值替换为指定的默认值。它接受两个参数：要检查的表达式和默认值。如果表达式的值为NULL，IFNULL()函数将返回默认值；否则，它将返回表达式的值。

功能介绍： IFNULL(expression, default_value)函数用于处理NULL值，当表达式的值为NULL时，返回指定的默认值，以确保结果集中不包含NULL值。

示例业务场景：

1. 计算平均评分： 假设有一个电影评分表，其中包含电影ID（movie_id）和评分（rating）。在某些情况下，评分列可能包含NULL值。为了计算电影的平均评分，可以使用IFNULL()函数将NULL值替换为0，并计算平均值：

SELECT movie_id, AVG(IFNULL(rating, 0)) AS avg_rating
FROM movie_ratings
GROUP BY movie_id;

1. 调整销售额度： 假设有一个销售订单表，其中包含客户ID（customer_id）和订单金额（order_amount）。在某些情况下，订单金额可能为NULL。为了进行销售额度分析，可以使用IFNULL()函数将NULL值替换为0，并计算调整后的销售额度：

SELECT customer_id, IFNULL(order_amount, 0) AS adjusted_amount
FROM sales_orders;

1. 统计空值数量： 在数据质量分析中，统计列中的空值数量是常见的需求。假设有一个用户表，其中包含用户ID（user_id）和电子邮件地址（email）。为了统计空值数量，可以使用IFNULL()函数将NULL值替换为1，并统计替换后的值的和：

SELECT SUM(IFNULL(email, 1)) AS null_count
FROM users;

在这些示例中，IFNULL()函数被用于处理NULL值，将其替换为默认值或特定的计算结果。这样可以确保在进行聚合计算、数据分析或数据质量检查时，结果集中不会包含NULL值，同时提供了一种方式来处理缺失或无效的数据。根据具体的业务需求，可以灵活应用IFNULL()函数来满足不同的数据处理需求。

nvl - 如第一个表达式为空，返回第二个表达式值，否则返回第一个表达式值

NVL()函数用于处理空值（NULL）的情况。它接受两个参数：第一个参数是待检查的表达式或列，第二个参数是替代值。如果第一个参数为空（NULL），则返回第二个参数作为替代值；否则，返回第一个参数的值。

以下是NVL()函数的语法示例：

NVL(expression, substitute_value)

• expression：待检查的表达式或列，如果为空（NULL），则返回替代值。
• substitute_value：替代值，如果表达式为空，则返回该值。

下面是一个示例查询，演示如何在Hive中使用NVL()函数：

SELECT name, NVL(age, 0) AS age
FROM persons;

在上述示例中，persons表中有两列：name和age。如果age列为空，则使用0作为替代值。查询结果将返回name列和age列（如果不为空）或替代值0（如果为空）。

NVL()函数对于处理空值非常有用，它允许在查询中指定替代值，以避免可能引起问题的空值。

coalesce - 返回参数列表中第一个非空表达式值

COALESCE() 函数用于从一组表达式中返回第一个非空（非 NULL）的值。它接受多个参数，并按照参数顺序逐个检查，返回第一个非空值。如果所有参数均为空，则返回 NULL

函数使用语法:

COALESCE(expr1, expr2, expr3, ...)

参数说明：

• expr1, expr2, expr3, ...：要检查的表达式列表。

使用 COALESCE() 函数时，Hive 会从左到右逐个检查参数，返回第一个非空的值。如果所有参数均为空，则返回 NULL。

示例用法： 假设我们有一个表 my_table，其中包含两列 col1 和 col2，我们希望获取这两列中的第一个非空值。

SELECT COALESCE(col1, col2) AS result
FROM my_table;

上述示例中，COALESCE(col1, col2) 表达式会先检查 col1 的值，如果非空则返回 col1 的值；如果 col1 为空，则继续检查 col2 的值并返回。最终，我们通过 AS result 给结果取了一个别名，该别名为 result。

COALESCE() 函数对于处理可能为空的列或变量很有用。它可以确保在处理表达式时始终有一个非空的值，从而避免出现 NULL 值的情况。

具体到代码中的使用：

coalesce(`eid`,'')

函数的意义是从 eid 列和空字符串之间选择一个非空值作为结果。如果 eid 列的值非空，则返回 eid 列的值；如果 eid 列的值为空，则返回空字符串

字符串函数

split - 将字符串按指定分隔符进行拆分

SPLIT() 函数用于将一个字符串按指定的分隔符进行拆分，并返回一个字符串数组。该函数接受两个参数：要拆分的字符串和分隔符

函数语法格式如下：

SPLIT(str, delimiter)

参数说明：

• str：要拆分的字符串。
• delimiter：分隔符，用于指定拆分字符串的位置。

返回值： SPLIT() 函数返回一个字符串数组，其中包含按指定分隔符拆分后的子字符串。

示例用法：

SELECT SPLIT('Hello,World,How,Are,You', ',') AS result;

输出结果：

["Hello", "World", "How", "Are", "You"]

在上述示例中，SPLIT() 函数将字符串 'Hello,World,How,Are,You' 按逗号 , 进行拆分，返回一个字符串数组 ["Hello", "World", "How", "Are", "You"]。每个逗号分隔的部分成为数组的一个元素。注意，返回的结果是一个字符串数组，每个元素用双引号括起来。

具体到代码中的使用：

split(`hisentname`,';')

将字段hisentname中值使用;拆分

concat - 连接两个或多个字符串

CONCAT() 函数用于将多个字符串连接成一个字符串。它接受两个或多个字符串作为参数，并返回这些字符串连接后的结果

函数示例用法：

假设我们有两个字符串 'Hello' 和 'World'，我们想将它们连接成一个字符串 'Hello World'。

SELECT CONCAT('Hello', ' ', 'World') AS result;

上述示例中，CONCAT('Hello', ' ', 'World') 表达式将 'Hello'、空格和 'World' 这三个字符串连接起来，得到了 'Hello World'。

CONCAT() 函数可以接受多个参数，它们可以是字符串常量、列名或其他表达式。它会按照参数在函数中出现的顺序将它们连接成一个字符串。如果参数中存在 NULL 值，则该参数会被忽略，不会影响连接结果。

示例用法： 假设我们有一个表 my_table，其中包含 first_name 和 last_name 两列，我们想将它们连接成一个完整的姓名。

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM my_table;

在上述示例中，CONCAT(first_name, ' ', last_name) 表达式将 first_name 列的值、一个空格字符和 last_name 列的值连接起来，得到了完整的姓名。通过 AS full_name 给结果取了一个别名，该别名为 full_name。

注意：在 Hive SQL 中，CONCAT() 函数可以接受的参数数量是有限的（通常为 256），如果需要连接大量的字符串，可能需要拆分成多个 CONCAT() 函数的调用。

trim - 去除字符串两端空格

TRIM() 函数用于移除字符串开头和结尾的空格或指定的字符。

函数语法格式如下：

TRIM([BOTH | LEADING | TRAILING] trim_character FROM input_string)

参数说明：

• BOTH: 移除字符串开头和结尾的字符，默认情况下也是使用 BOTH。
• LEADING: 只移除字符串开头的字符。
• TRAILING: 只移除字符串结尾的字符。
• trim_character: 要移除的字符或字符串，默认为移除空格字符。
• input_string: 要进行处理的字符串。

示例：

SELECT TRIM('   Hello World   ') AS trimmed_string;

输出结果：Hello World

在上述示例中，TRIM() 函数移除了字符串 ' Hello World ' 开头和结尾的空格字符，返回了处理后的字符串 'Hello World'

具体到代码中的使用：

trim(`entname`)

将字段entname中开头和结尾的空格字符移除

regexp - 检查字符串是否匹配指定的正则表达式

regexp函数用于检查一个字符串是否与指定的正则表达式模式匹配

函数语法如下：

regexp(string, pattern)

• string：要匹配的字符串。
• pattern：正则表达式模式，用于指定匹配规则。

该函数返回一个布尔值，如果给定的字符串与正则表达式模式匹配，则返回true，否则返回false。

以下是一些示例：

1. 检查字符串是否符合指定的正则表达式模式：

SELECT regexp('hello', '^h.*');

输出：true

在此示例中，给定的字符串是"hello"，正则表达式模式是"^h.*"，该模式表示以字母"h"开头的任意字符串。因为字符串"hello"以"h"开头，所以匹配成功，返回true。

1. 在查询中使用regexp函数过滤数据：

SELECT column_name
FROM table_name
WHERE regexp(column_name, '[0-9]+');

该语句将选择表中某个列中匹配正则表达式模式[0-9]+的数据行。这个模式表示一个或多个数字的序列。只有匹配模式的行才会被返回。

注意：在Hive SQL中，正则表达式模式是基于Java的正则表达式语法。因此，你可以使用Java正则表达式的语法规则来构建模式。

具体到代码中的使用：

case when `credit_code` regexp '0{18}' then null 
else upper(regexp_replace(`credit_code`,'\\s',''))
end as `uscc_code` 

1. when credit_code regexp '0{18}' then null 表示如果credit_code列的值与正则表达式模式0{18}匹配（即连续18个0），则返回NULL。
2. else upper(regexp_replace(credit_code,'\\s','')) 表示如果credit_code列的值不匹配正则表达式模式0{18}，则将credit_code列的值进行upper处理。

• regexp_replace(credit_code,'\\s','') 将credit_code列中的空格字符替换为空字符串。\s是正则表达式中的空格字符的表示。
• upper(...) 将处理后的credit_code值转换为大写字母。

最终，根据条件的判断，uscc_code列的值可能为NULL（当credit_code匹配18个连续的0）或处理后的大写字母字符串（当credit_code不匹配18个连续的0）。

regexp_replace - 使用正则表达式替换字符串中匹配项

regexp_replace()函数用于替换字符串中匹配正则表达式的部分

函数的语法如下：

regexp_replace(string, pattern, replacement)

• string：要进行替换操作的字符串。
• pattern：要匹配的正则表达式模式。
• replacement：替换匹配的部分的字符串。

该函数将在给定的字符串中搜索匹配正则表达式模式的部分，并用替换字符串来替换它们。如果没有找到匹配的部分，则返回原始字符串。

以下是一些示例：

1. 替换字符串中的数字为特定字符：

SELECT regexp_replace('Hello123World456', '[0-9]', '*');

输出：Hello***World***

1. 删除字符串中的空格：

SELECT regexp_replace('Hello World', '\\s', '');

输出：HelloWorld

1. 将字符串中的所有逗号替换为分号：

SELECT regexp_replace('a,b,c,d', ',', ';');

输出：a;b;c;d

1. 使用空字符串删除字符串中的特定模式：

SELECT regexp_replace('abc123def456', '[a-z]', '');

输出：123456

注意：在Hive SQL中，正则表达式的语法可能稍有不同，需要根据具体的需求和Hive版本进行调整。

具体到代码中的使用：

regexp_replace(`credit_code`,'\\s','')

这段代码将替换字段credit_code列中的空格字符（\s表示空格）为空字符串。\\s中的双反斜杠是为了转义反斜杠，因为在正则表达式中反斜杠本身也需要转义。

这意味着，如果credit_code列包含任何空格字符，将使用空字符串替换它们。例如，如果credit_code的值为ABC 123 DEF，则替换后的结果为ABC123DEF，即去除了空格字符。

具体到代码中的使用：

regexp_replace(
	regexp_replace(
		regexp_replace(`hisentname`, '；', ';')
		,'&|nbsp;|&|/|:|：|\\.|企业基本信息|名称|企业(机构)名称|企业名称|名称序号|联系电话|第一名称|第二名称|序号|【变更前内容】|\\*|-|[0-9]|[a-zA-Z]', ''
		)
	, '\\s', '') 
AS `hisentname`

这段代码是用于对hisentname列的值进行多次替换操作，并将处理后的结果存储在hisentname列中。

1. regexp_replace(hisentname,'；',';') 将hisentname列中的中文分号（；）替换为英文分号（;）。这是第一次替换操作。
2. regexp_replace(...,'&|nbsp;|&|/|:|：|\\\.|企业基本信息|名称|企业(机构)名称|企业名称|名称序号|联系电话|第一名称|第二名称|序号|【变更前内容】|\\\*|-|[0-9]|[a-zA-Z]','') 使用正则表达式模式匹配，将hisentname列中的一些特殊字符和关键词进行替换。具体要替换的内容包括：&、nbsp;、&、/、:、：、.、企业基本信息、名称、企业(机构)名称、企业名称、名称序号、联系电话、第一名称、第二名称、序号、【变更前内容】、*（反斜杠需要进行转义）以及数字和字母。这是第二次替换操作。
3. regexp_replace(...,'\\s','') 将上述替换后的字符串中的空格字符替换为空字符串。这是第三次替换操作。\s是正则表达式中的空格字符的表示。

最终，经过三次替换操作后，处理后的字符串将存储在hisentname列中。

substr - 返回字符串的子串

SUBSTR()函数用于从字符串中提取子字符串

函数的语法如下：

SUBSTR(string, start, length)

其中：

• string是要提取子字符串的原始字符串。
• start是要开始提取的位置索引，索引从1开始。
• length是要提取的子字符串的长度。

SUBSTR()函数返回从原始字符串中提取的子字符串。

示例： 假设有一个字符串 Hello, World!，我们想从中提取子字符串 World，可以使用以下语句：

SUBSTR('Hello, World!', 8, 5)

-- SELECT SUBSTR('Hello, World!', 8, 5);
-- 输出结果为 World

在上面的代码中，SUBSTR('Hello, World!', 8, 5)表示从字符串的第8个位置开始提取长度为5的子字符串

upper / lower - 将字符串转换大/小写

upper()函数用于将字符串转换为大写字母形式

函数的语法如下：

upper(string)

• string：要转换为大写的字符串。

该函数将给定的字符串中的所有字符转换为大写形式，并返回转换后的结果。

以下是一些示例：

1. 将字符串转换为大写形式：

SELECT upper('hello world');

输出：HELLO WORLD

1. 转换列中的字符串为大写形式：

SELECT upper(column_name) FROM table_name;

该语句将选择表中的某个列，并将列中的所有字符串值转换为大写形式。

注意：upper()函数在Hive SQL中是不区分大小写的，因此它可以用于任何字符串，无论其原始大小写形式如何。

lower()使用同upper()，作用相反

explode- 将一个数组或Map炸裂拆分为多行

LATERAL VIEW EXPLODE() 通俗叫做炸裂函数，用于将一个数组列（Array）拆分成多行，并将每个数组元素生成为新的行。该函数通常与 SELECT 语句结合使用

函数使用语法:

SELECT ...
FROM ...
LATERAL VIEW EXPLODE(array_column) table_alias AS column_alias

参数说明：

• array_column：要拆分的数组列（Array）。
• table_alias：生成的表别名。
• column_alias：生成的列别名。

使用 LATERAL VIEW EXPLODE() 函数时，Hive 会将数组列中的每个元素作为新的行，并将其放置在由 table_alias 指定的表中。然后，您可以在 SELECT 语句中引用 column_alias，并对拆分后的行进行进一步的处理。

示例用法： 假设我们有一个表 my_table，其中包含一个名为 array_col 的数组列，我们想要将该数组拆分为多行。

SELECT column_alias
FROM my_table
LATERAL VIEW EXPLODE(array_col) my_table_alias AS column_alias;

在上述示例中，LATERAL VIEW EXPLODE() 函数将 my_table 表的 array_col 数组列拆分成多行。每个数组元素成为新的行，然后通过 my_table_alias 作为别名引用这些拆分后的行。您可以在 SELECT 语句中选择需要的列，并对拆分后的行进行进一步的操作。

注意，LATERAL VIEW EXPLODE() 函数只能用于数组列的拆分，而不能用于其他类型的列

日期函数

datediff - 返回两个日期之间天数差异

datediff() 函数用于计算两个日期之间的天数差。它接受两个日期作为输入参数，并返回一个整数，表示第一个日期与第二个日期之间的天数差。

函数语法如下：

datediff(enddate, startdate)

其中，enddate 和 startdate 是日期参数，可以是字符串类型或日期类型。enddate 表示较晚的日期，而 startdate 表示较早的日期。

以下是一些示例：

示例1：

SELECT datediff('2023-06-27', '2023-06-20');

输出结果为：7

示例2：

SELECT datediff('2023-06-01', '2023-07-01');

输出结果为：-30

在示例1中，第一个日期为 ‘2023-06-27’，第二个日期为 ‘2023-06-20’，它们之间的天数差为 7。

在示例2中，第一个日期为 ‘2023-06-01’，第二个日期为 ‘2023-07-01’，由于第一个日期较晚，所以结果为负数，表示第一个日期在第二个日期之前 30 天。

注意：datediff() 函数计算的是两个日期之间的天数差，不考虑时区和时间部分。

current_timestamp - 返回当前时间戳

CURRENT_TIMESTAMP() 函数用于获取当前的时间戳，表示当前的日期和时间。

CURRENT_TIMESTAMP() 函数没有参数，它返回一个时间戳值，通常以 ‘yyyy-MM-dd HH:mm:ss’ 或者 ‘yyyy-MM-dd HH:mm:ss.SSS’ 的格式表示。

函数示例用法：

SELECT CURRENT_TIMESTAMP() AS current_time;

输出结果：2023-06-05 12:34:56

在上述示例中，CURRENT_TIMESTAMP() 函数返回当前的日期和时间，即 '2023-06-05 12:34:56'。注意，实际的输出结果会根据当前的系统时间而变化。

数组函数

sort_array - 对数组进行排序

sort_array函数用于对数组进行排序，并返回排序后的数组作为结果。它可以用于对包含元素的数组进行排序操作。

sort_array()常与collect_list()函数结合使用

以下是sort_array函数的语法：

sort_array(array[, ascendingOrder])

其中，array是要排序的数组，ascendingOrder是可选参数，指定是否按升序排序，默认为true（升序）。

sort_array函数将给定的数组进行排序，并返回排序后的数组。如果未指定排序顺序，默认按升序进行排序。

sort_array函数的示例用法：

假设有一个名为numbers的表，包含以下列：id（编号）和values（包含整数的数组）。

+----+----------------------+
| id | values               |
+----+----------------------+
| 1  | [5, 3, 2, 4, 1]      |
| 2  | [9, 7, 6, 8, 10]     |
+----+----------------------+

要对values数组进行排序，可以使用以下查询：

SELECT id, sort_array(values) AS sorted_values
FROM numbers;

查询结果将如下所示：

+----+----------------------+
| id | sorted_values        |
+----+----------------------+
| 1  | [1, 2, 3, 4, 5]      |
| 2  | [6, 7, 8, 9, 10]     |
+----+----------------------+

对于每一行，sort_array函数对values数组进行排序，并返回排序后的数组作为结果。

需要注意的是，sort_array函数仅对数组中的元素进行排序，而不会改变其他列的值。在排序过程中，数组中的元素按照其默认数据类型进行排序，例如整数按照数值大小排序，字符串按照字母顺序排序。

array_contains - 检查数组中是否包含指定元素

ARRAY_CONTAINS()函数用于检查数组中是否包含指定的元素，并返回布尔值（true或false）。它可以用于在数组中进行成员检查和过滤操作。

功能介绍： ARRAY_CONTAINS(array, value)函数接受两个参数：一个数组和一个值。它会检查数组中是否包含指定的值，并返回布尔结果。

示例业务场景：

1. 用户标签匹配： 假设有一个用户表，其中包含用户ID（user_id）和用户的标签列表（tags）。标签列表是一个存储用户兴趣爱好的数组。为了查找具有特定兴趣标签的用户，可以使用ARRAY_CONTAINS()函数进行匹配：

SELECT user_id
FROM users
WHERE ARRAY_CONTAINS(tags, 'sports');

1. 商品筛选： 假设有一个产品表，其中包含产品ID（product_id）和适用行业的数组（industries）。为了筛选出适用于某个特定行业的产品，可以使用ARRAY_CONTAINS()函数进行过滤：

SELECT product_id
FROM products
WHERE ARRAY_CONTAINS(industries, 'technology');

1. 数组聚合统计： 假设有一个销售数据表，其中包含产品ID（product_id）和销售额度的数组（sales_amounts）。为了统计每个产品的销售次数，可以使用ARRAY_CONTAINS()函数计算满足条件的数组元素数量：

SELECT product_id, COUNT(*) AS sales_count
FROM sales_data
WHERE ARRAY_CONTAINS(sales_amounts, 0);

在这些示例中，ARRAY_CONTAINS()函数被用于在数组中进行成员检查，以满足特定的条件。它可以帮助进行标签匹配、数组过滤和数组聚合等操作，从而支持各种业务场景下的数据查询和分析。

需注意ARRAY_CONTAINS()函数对于数组中的复杂数据类型，如结构体或嵌套数组，可能需要更复杂的使用方法。在具体的环境和工具中使用该函数时，请参考相关文档和官方指南以了解准确的用法和行为。

加密函数

md5 - 计算字符串的MD5哈希值

MD5() 函数用于计算给定字符串的 MD5 哈希值。MD5 是一种常用的哈希算法，它将任意长度的输入数据转换为一个固定长度的哈希值（通常为 128 位），该哈希值在理论上是唯一的

MD5() 函数接受一个字符串作为输入，并返回该字符串的 MD5 哈希值，以字符串形式表示。它可以用于在 Hive SQL 中计算字符串的哈希值，常用于数据摘要、数据比对、数据加密等场景。

示例用法： 假设我们有一个字符串 'Hello, World!'，我们想计算它的 MD5 哈希值。

SELECT MD5('Hello, World!') AS hash_value;

上述示例中，MD5('Hello, World!') 表达式将字符串 'Hello, World!' 的 MD5 哈希值计算出来，并以字符串形式返回。结果类似于 '65a8e27d8879283831b664bd8b7f0ad4'。

注意：MD5 是一种较早的哈希算法，虽然在某些场景下仍然可用，但它已经被认为是不安全的。在实际应用中，特别是涉及敏感数据的情况下，建议使用更强大和安全的哈希算法，如 SHA-256。在 Hive 中，也提供了 SHA2() 函数用于计算 SHA-2 哈希值。

sha2 - 计算字符串的SHA-2哈希值

SHA2()函数用于计算给定字符串的SHA-2（Secure Hash Algorithm 2）哈希值。SHA-2是一组密码哈希函数，包括SHA-224、SHA-256、SHA-384和SHA-512等不同的变体。这些算法都是由美国国家安全局（NSA）设计的，并被广泛用于密码学和安全应用中。

SHA2()函数接受两个参数：要进行哈希计算的字符串和哈希算法的位数。位数可以是256、384或512，分别对应SHA-256、SHA-384和SHA-512。例如，SHA2('hello', 256)将返回字符串’hello’的SHA-256哈希值。

以下是一个示例查询，演示如何在Hive中使用SHA2()函数：

SELECT SHA2('hello', 256);

输出：

185f8db32271fe25f561a6fc938b2e264306ec304eda518007d1764826381969

这是字符串’hello’的SHA-256哈希值。注意，输出的哈希值是一个十六进制字符串。

SHA2()函数在Hive中通常用于数据安全、数据摘要和密码保护等场景。例如，可以在Hive表中存储敏感数据的哈希值，而不是明文数据，以提高安全性。

encrypt - 对字符串进行加密

encrypt()函数用于对给定的字符串进行加密处理。它使用指定的加密算法和密钥对字符串进行加密，并返回加密后的结果。这个函数可以用于保护敏感数据，如密码或其他机密信息。

encrypt()函数的语法如下：

encrypt(string input, string key);

参数说明：

• input: 要加密的字符串。
• key: 加密使用的密钥。

注意：Hive中的encrypt()函数需要安装并启用Hive加密插件才能正常使用。默认情况下，Hive不提供加密功能，需要额外的配置和插件才能使用该函数。

使用encrypt()函数的示例：

SELECT encrypt('password123', 'mySecretKey') AS encrypted_password FROM my_table;

上述示例中，将字符串"password123"使用密钥"mySecretKey"进行加密，并将加密后的结果作为"encrypted_password"返回。

请注意，具体的加密算法和加密插件取决于Hive配置和环境设置。常见的加密算法包括AES、DES、RSA等，具体使用哪种算法取决于Hive的配置和插件的支持。

类型转换函数

cast - 将表达式转换为指定的数据类型

cast()函数用于将一个表达式或列的值转换为指定的数据类型。它提供了类型转换的功能，可以将一个数据类型转换为另一个兼容的数据类型。

cast()函数的语法如下：

CAST(expression AS data_type)

其中，expression是要进行类型转换的表达式或列，data_type是要转换成的目标数据类型。

下面是一些常见的数据类型转换示例：

-- 将字符串转换为整数
CAST('123' AS INT)

-- 将字符串转换为浮点数
CAST('3.14' AS DOUBLE)

-- 将整数转换为字符串
CAST(456 AS STRING)

-- 将日期字符串转换为日期类型
CAST('2023-01-01' AS DATE)

-- 将NULL值转换为字符串类型
cast(null as string) 

-- 将当前的时间戳（即当前日期和时间）转换为字符串格式
cast(current_timestamp() as string

需要注意的是，cast()函数只能进行兼容的数据类型转换。如果转换不可行或存在不兼容的数据类型，会导致转换失败并抛出错误。

在Hive SQL中，cast()函数在数据类型转换、数据格式转换以及数据精度转换方面都非常有用，可以根据需要将数据转换为适合特定计算或处理需求的类型。

to_date - 将字符串转换为日期格式

to_date函数用于将字符串转换为日期格式。它将给定的字符串解析为日期，并返回对应的日期值。

功能介绍： to_date(string)函数接受一个字符串参数，并将其解析为日期格式。字符串参数必须符合Hive支持的日期格式，否则将返回NULL值。

使用场景举例：

1. 字符串日期转换： 假设有一个数据表包含日期字段date_str，以字符串形式存储日期（如’2023-06-29’）。为了进行日期计算和分析，需要将字符串日期转换为日期类型：

SELECT to_date(date_str) AS date
FROM table;

1. 日期比较和筛选： 假设有一个订单表，其中包含订单号（order_id）和下单日期（order_date）。为了筛选出特定日期范围内的订单，可以使用to_date函数将查询参数转换为日期格式，并与订单日期进行比较：

SELECT order_id, order_date
FROM orders
WHERE to_date(order_date) BETWEEN to_date('2023-01-01') AND to_date('2023-06-30');

1. 日期聚合统计： 假设有一个销售数据表，其中包含销售日期（sale_date）和销售金额（sale_amount）。为了按照日期进行销售金额的统计，可以使用to_date函数将日期字符串转换为日期，并进行聚合操作：

SELECT to_date(sale_date) AS date, SUM(sale_amount) AS total_sales
FROM sales
GROUP BY to_date(sale_date);

在这些示例中，to_date函数被用于将字符串日期转换为日期类型，以便进行日期比较、日期聚合和日期计算等操作。它在处理日期数据时非常有用，并帮助实现基于日期的查询和分析。

请注意，to_date函数依赖于输入字符串的日期格式，因此需要确保输入的字符串符合Hive支持的日期格式。

to_unix_timestamp - 将日期或时间字符串转换为UNIX时间戳格式

to_unix_timestamp函数用于将日期或时间字符串转换为UNIX时间戳格式。它将给定的日期或时间字符串解析为UNIX时间戳，并返回对应的整数值。

功能介绍： to_unix_timestamp(string)函数接受一个日期或时间字符串参数，并将其解析为UNIX时间戳格式。字符串参数必须符合Hive支持的日期或时间格式，否则将返回NULL值。UNIX时间戳是从1970年1月1日00:00:00 UTC起经过的秒数。

使用场景举例：

1. 时间比较和筛选： 假设有一个日志表，其中包含日志时间戳字段（log_timestamp）。为了筛选出特定时间范围内的日志，可以使用to_unix_timestamp函数将查询参数转换为UNIX时间戳，并与日志时间戳进行比较：

SELECT log_id, log_timestamp
FROM logs
WHERE to_unix_timestamp(log_timestamp) BETWEEN to_unix_timestamp('2023-06-29 00:00:00') AND to_unix_timestamp('2023-06-30 23:59:59');

1. 时间计算和转换： 假设有一个任务表，其中包含任务开始时间（start_time）和任务执行时长（duration，以秒为单位）。为了计算任务的结束时间，可以使用to_unix_timestamp函数将开始时间转换为UNIX时间戳，并结合任务执行时长进行计算：

SELECT task_id, start_time, duration,
    from_unixtime(to_unix_timestamp(start_time) + duration) AS end_time
FROM tasks;

1. 时间戳格式转换： 假设有一个数据表包含日期字段（date_str）以字符串形式存储日期（如’2023-06-29’）。为了将日期字段转换为UNIX时间戳，并在后续的计算和处理中使用，可以使用to_unix_timestamp函数进行转换：

SELECT date_str, to_unix_timestamp(date_str) AS unix_timestamp
FROM table;

在这些示例中，to_unix_timestamp函数被用于将日期或时间字符串转换为UNIX时间戳，以便进行时间比较、时间计算和时间格式转换。它在处理时间数据和进行时间相关的计算时非常有用。

请注意，to_unix_timestamp函数依赖于输入字符串的日期或时间格式，因此需要确保输入的字符串符合Hive支持的日期或时间格式

from_unixtime - 将UNIX时间戳转换为日期或时间字符串格式

from_unixtime函数用于将UNIX时间戳转换为日期或时间字符串格式。它将给定的UNIX时间戳解析为日期或时间字符串，并返回对应的字符串值。

功能介绍： from_unixtime(unix_timestamp[, format])函数接受一个UNIX时间戳参数，并将其转换为日期或时间字符串。它可以指定可选的格式参数，用于定义输出字符串的格式。如果未提供格式参数，则默认使用"yyyy-MM-dd HH:mm:ss"格式。

使用场景举例：

1. UNIX时间戳转换： 假设有一个数据表包含UNIX时间戳字段（unix_timestamp）。为了将UNIX时间戳转换为可读的日期和时间格式，可以使用from_unixtime函数进行转换：

SELECT unix_timestamp, from_unixtime(unix_timestamp) AS datetime
FROM table;

1. 日期格式定制： 假设有一个订单表，其中包含订单日期（order_date）。为了将订单日期按照自定义格式进行输出，可以使用from_unixtime函数并指定格式参数：

SELECT order_id, from_unixtime(order_date, 'yyyy/MM/dd') AS formatted_date
FROM orders;

1. 时间戳转换： 假设有一个日志表，其中包含日志时间戳字段（log_timestamp）。为了将日志时间戳转换为特定的时间格式，可以使用from_unixtime函数并指定格式参数：

SELECT log_id, from_unixtime(log_timestamp, 'HH:mm:ss') AS log_time
FROM logs;

在这些示例中，from_unixtime函数被用于将UNIX时间戳转换为日期或时间字符串，以便进行时间格式定制、时间戳转换和可读性输出。它在处理UNIX时间戳和日期/时间格式转换方面非常有用。

需要注意的是，from_unixtime函数返回的是字符串类型，因此在使用过程中需要根据需要进行后续的计算、比较或格式处理。

数学函数

greatest - 返回最大值

GREATEST() 函数用于从给定的一组值中返回最大值。它接受多个参数，并返回这些参数中的最大值。

语法：

GREATEST(value1, value2, ...)

参数：

• value1, value2, ...: 要比较的值，可以是数字、字符串或日期类型。

返回值：

• 返回参数中的最大值。

注意事项：

• 如果参数中包含 NULL 值，则返回结果为 NULL。
• GREATEST() 函数对于不同类型的参数进行比较时，会根据类型的比较规则进行转换和比较。

示例：

SELECT GREATEST(5, 10, 3, 8); -- 返回 10
SELECT GREATEST('apple', 'banana', 'orange'); -- 返回 'orange'
SELECT GREATEST(date '2021-01-01', date '2022-03-15', date '2020-12-25'); -- 返回 '2022-03-15'

floor - 返回不大于给定数的最大整数(向下取整)

floor函数用于返回不大于给定数的最大整数。它将给定的数值参数向下取整，并返回最接近且不大于该数的整数值。

功能介绍： floor(x)函数接受一个数值参数x，并返回不大于x的最大整数值。如果x是正数，则返回小于或等于x的最大整数；如果x是负数，则返回大于或等于x的最大整数。

使用场景举例：

1. 数值取整： 假设有一个销售表，其中包含销售订单的总金额（total_amount）。为了统计订单金额的整数部分，可以使用floor函数将总金额向下取整：

SELECT order_id, total_amount, floor(total_amount) AS rounded_amount
FROM sales;

1. 价格调整： 假设有一个产品表，其中包含产品价格（price）。为了进行价格调整，将价格向下取整到最接近的整数值，并作为调整后的价格进行处理：

SELECT product_id, price, floor(price) AS adjusted_price
FROM products;

1. 时间戳转换： 假设有一个日志表，其中包含日志时间戳字段（log_timestamp）。为了将日志时间戳向下取整到分钟级别，并对日志进行分组和聚合操作，可以使用floor函数：

SELECT floor(log_timestamp/60)*60 AS minute_timestamp, COUNT(*) AS count
FROM logs
GROUP BY floor(log_timestamp/60)*60;

在这些示例中，floor函数被用于将数值或时间戳向下取整，以便进行数值处理、价格调整、时间戳转换和聚合操作。它在处理数值和时间数据时非常有用，可用于各种业务场景中的数据处理和计算。

需要注意的是，floor函数返回的结果是整数类型，可以与其他数值进行计算和比较

逻辑函数

case when - 实现条件判断和分支逻辑

CASE WHEN语句用于根据条件执行不同的操作或返回不同的值。它类似于其他编程语言中的条件语句（如if-else语句）。

CASE WHEN语句的一般语法如下：

CASE WHEN condition1 THEN result1
     WHEN condition2 THEN result2
     ...
     ELSE resultN
END

• condition1, condition2, … 是要评估的条件表达式。
• result1, result2, … 是在满足相应条件时要返回的结果表达式。
• ELSE resultN 是可选的，用于指定当所有条件都不满足时要返回的默认结果表达式。

注意事项：

• CASE WHEN语句按顺序评估条件，一旦满足条件，将返回相应的结果，并且后续条件将不再进行评估。
• 可以使用多个WHEN子句，根据需要设置不同的条件和结果。
• 如果没有满足条件的子句，并且没有提供ELSE子句，则CASE WHEN语句将返回NULL。

以下是一个示例，演示如何在Hive SQL中使用CASE WHEN语句：

SELECT column1, column2,
       CASE WHEN column1 > 10 THEN 'Large'
            WHEN column1 > 5 THEN 'Medium'
            ELSE 'Small'
       END AS size
FROM table;

在上面的示例中，根据column1的值，根据不同的条件返回不同的size值。如果column1大于10，则返回'Large'；如果大于5但小于等于10，则返回'Medium'；否则返回'Small'。

with as

WITH AS 语句用于创建一个临时表或子查询，并为其指定一个别名。这个临时表或子查询可以在后续的查询中使用。

WITH AS 语句的语法如下：

WITH tmp AS (
    -- 子查询或临时表定义
)

使用 WITH tmp AS 语句可以提高查询的可读性和复用性，特别是当查询需要多次引用同一个子查询结果时。它可以避免重复编写相同的子查询，并简化查询语句的结构

使用WITH子句创建的临时表是会自动回收的，不需要手动进行回收操作。

临时表的生命周期与查询的执行周期相关联。当查询执行完毕后，临时表会被自动删除并释放其占用的资源。这意味着临时表在当前查询的上下文中可见，但在查询结束后将不再存在。

这种自动回收的特性使得临时表的管理更加方便，不需要手动删除或释放资源。每次执行查询时，临时表都会被重新创建，确保了查询的独立性和隔离性。

需要注意的是，临时表只在当前会话中有效，对于其他会话或并行执行的查询不可见。如果需要在多个查询之间共享临时表，可以考虑使用全局临时表（Global Temporary Table）或永久表。

if

在 Hive SQL 中，IF 是一种条件表达式，用于根据条件的结果选择执行不同的操作。

语法如下：

IF(condition, value_if_true, value_if_false)

其中：

• condition 是一个布尔表达式，用于指定条件。
• value_if_true 是在条件为真时要返回的值或表达式。
• value_if_false 是在条件为假时要返回的值或表达式。

使用示例：

SELECT IF(salary > 5000, 'High', 'Low') AS salary_category
FROM employees;

在上面的示例中，根据 salary 的值，如果工资大于 5000，则返回 'High'，否则返回 'Low'

select “1” as xxx from table

通常用于临时增加一个辅助列，用于区分表示不同的源，as前面使用一个常值字符串

例如:

select id,"1" as source from code_table_1
union all
select id,"2" as source from code_table_2

在这段示例代码中，'1’和’2’是用作源标识（source identifier）。它们是一个用于区分code_table_1和code_table_2的数据来源的辅助列

1. 第一个SELECT语句中的'1' as source表示该行来自表code_table_1，并将源标识设置为’1’，别名source
2. 第二个SELECT语句中的'2' as source表示该行来自表code_table_2，并将源标识设置为’2’，别名source

'1’和’2’在这段代码中仅作为辅助标识符，用于区分数据来源和帮助做查询筛选设置优先级，并无其他特殊含义。

使用示例：

SELECT `code`, name
FROM (
	SELECT `code`, name, row_number() OVER (PARTITION BY name ORDER BY source DESC) AS rn
	FROM (
		SELECT `code`, name, '1' AS source
		FROM n000_code_cb18
		UNION ALL
		SELECT `code`, name, '2' AS source
		FROM n000_code_cb18_new
		WHERE rn = 1
	) a
) aa
WHERE aa.rn = 1;

在查询的子查询部分，两个SELECT语句分别来自不同的表：

1. 第一个SELECT语句中的'1' as source表示该行来自表n000_code_cb18，并将源标识设置为’1’。
2. 第二个SELECT语句中的'2' as source表示该行来自表n000_code_cb18_new，并将源标识设置为’2’。

这样做的目的是将两个表的数据union all合并，并根据源标识的值进行排序和分区，以便在后续的ROW_NUMBER()函数中根据指定的规则选择每个分区中具有最高优先级的行。

在最终的查询部分，where aa.rn=1条件表示只选择具有行号（rn）为1的行，即每个分区中具有最高优先级的行