常用SQL函数

又可以改名了

已于 2023-02-06 14:57:13 修改

阅读量3.4k

点赞数 1

分类专栏：随笔文章标签： sql 数据库大数据

于 2022-10-27 19:00:29 首次发布

本文链接：https://blog.csdn.net/weixin_46935871/article/details/127509112

版权

随笔专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、数组

1、创建数组

--所有ARRAY元素必须为同一类型
SELECT ARRAY [1,2,3,4] AS items

--所有ARRAY元素必须为同一类型
SELECT ARRAY[ ARRAY[1,2], ARRAY[3,4] ] AS items

2、计算数组长度

SELECT cardinality(ARRAY[1,2,3,4]) AS item_count

3、访问数组元素

WITH dataset AS (
  SELECT ARRAY ['a1', 'a2', 'a3', 'b1', 'b2', 'b3'] AS items
)
SELECT items[3] AS item FROM dataset

WITH dataset AS (
  SELECT ARRAY ['a1', 'a2', 'a3', 'b1', 'b2', 'b3'] AS items
)
SELECT element_at(items, 3) AS item FROM dataset

4、数组展开

--常规展开
WITH dataset AS (
  SELECT ARRAY ['a1', 'a2', 'a3', 'b1', 'b2', 'b3'] AS items
)
SELECT item FROM dataset, UNNEST(items) as t(item)
--可配合CROSS JOIN进行笛卡尔积
SELECT item FROM dataset CROSS JOIN UNNEST(items) as t(item)

--展开所含所有列表并聚合
--注：所有ARRAY元素必须为同一类型
SELECT flatten(ARRAY[ ARRAY[1,2], ARRAY[3,4] ]) AS items

5、合并为数组

WITH
dataset AS (
  SELECT * FROM 
  (VALUES 1,1,2,2) as t(item1),
  (VALUES 'A','B','C','D') as t(item2)
)
SELECT item1, array_agg(DISTINCT item2) AS array_items FROM dataset GROUP BY item1

6、数组排序

WITH
dataset AS (
  SELECT ARRAY [1, 3, 98, 7, 65, 4, 24] AS items  
)
SELECT array_sort(DISTINCT items) AS sort_items FROM dataset

7、数组转字符串

WITH
dataset AS (
  SELECT ARRAY ['hello', 'amazon', 'athena'] AS words
)
SELECT array_join(words, ' ') AS welcome_msg
FROM dataset

二、ROW

1、创建ROW

WITH dataset AS (
  SELECT
    ROW('Bob', 38) AS users1, --生成匿名row
    CAST(
      ROW('Bob', 38) AS ROW(VARCHAR, INTEGER)
    ) AS users2, --生成匿名row
    CAST(
      ROW('Bob', 38) AS ROW(name VARCHAR, age INTEGER)
    ) AS users3, --生成非匿名row
    CAST(
      ROW('Bob', 38, ROW('Obo', 20)) 
      AS 
      ROW(name VARCHAR, age INTEGER, subordinate ROW(name VARCHAR, age INTEGER))
    ) AS users4  --嵌套row
)
SELECT users1,users2,users4,users4.subordinate.name FROM dataset

三、MAP

1、创建MAP

WITH dataset AS (
  SELECT
	MAP(ARRAY['name', 'email'], ARRAY['patsnap','xxx@patsnap.com']) AS some_map
)
SELECT some_map,some_map['name'] name FROM dataset

四、JSON

1、JSON提取

--通常使用json_extract
WITH dataset AS (
  SELECT
  	JSON '[{"email":"xxx@patsnap.com","name":"patsnap","age":18},{}]' 
    AS some_json
)
SELECT some_json,json_extract(some_json, '$.0.name') name FROM dataset

--json_extract_scalar仅返回标量值 (布尔值、数字或字符串)
WITH dataset AS (
  SELECT
  	JSON '[{"email":"xxx@patsnap.com","name":"patsnap","age":18},{}]' 
    AS some_json
)
SELECT some_json,json_extract_scalar(some_json, '$.0.name') name FROM dataset

2、JSON长度计算

--json_array_length只能获取json中list的长度
WITH dataset AS (
  SELECT JSON '{"name": "Bob Smith", "org": "legal", "projects": [1,2,3,"A","B","C"]}' AS some_json
)
SELECT
  json_extract_scalar(some_json, '$.name') as name,
  json_array_length(json_extract(some_json, '$.projects')) as projects_count
FROM dataset

--json_size可获取json对象、json中list的长度
WITH dataset AS (
  SELECT JSON '{"name": "Bob Smith", "org": "legal", "projects": [1,2,3,"A","B","C"]}' AS some_json
)
SELECT
  json_extract_scalar(some_json, '$.name') as name,
  json_size(some_json, '$') as json_count,
  json_size(some_json, '$.projects') as json_list_count
FROM dataset

3、JSON条件检索

--使用json_array_contains判断是否包含project2
WITH dataset AS (
  SELECT * FROM (VALUES
    (JSON '{"name": "Bob Smith", "org": "legal", "projects": ["project1"]}'),
    (JSON '{"name": "Susan Smith", "org": "engineering", "projects": ["project1", "project2", "project3"]}'),
    (JSON '{"name": "Jane Smith", "org": "finance", "projects": ["project1", "project2"]}')
  ) AS t (users)
)
SELECT json_extract_scalar(users, '$.name') AS user
FROM dataset
WHERE json_array_contains(json_extract(users, '$.projects'), 'project2')

五、字符串

1、字符串拼接

WITH dataset AS (
  SELECT 'hello' AS item
)
SELECT concat('"',item,'"') AS item FROM dataset

WITH dataset AS (
  SELECT 'hello' AS item
)
SELECT '"' || item || '"' AS item FROM dataset

2、字符串replace

WITH dataset AS (
  SELECT 'he-ll-o' AS item
)
SELECT replace(item, '-', '') AS item FROM dataset

3、获取字符串长度

WITH dataset AS (
  SELECT 'he-ll-o' AS item
)
SELECT length(item) AS co FROM dataset

4、字符串拆分

WITH dataset AS (
  SELECT 'he-ll-o' AS item
)
SELECT split(item, '-') AS item FROM dataset --拆分后为一个列表

5、字符串大写

WITH dataset AS (
  SELECT 'he-ll-o' AS item
)
SELECT upper(item) AS item FROM dataset

6、字符串小写

WITH dataset AS (
  SELECT 'HE-LL-O' AS item
)
SELECT lower(item) AS item FROM dataset

六、其它

1、类型转换

1、row转json

--row转json，转完只剩value
WITH dataset AS (
  SELECT
  	CAST(
	  ROW('xxx@patsnap.com', 'patsnap') AS JSON
	) some_json
)
SELECT * FROM dataset

2、map转json

--map转json
WITH dataset AS (
  SELECT
    CAST(
	  MAP(ARRAY['name', 'email'], ARRAY['patsnap','xxx@patsnap.com']) AS JSON
	) some_json
)
SELECT * FROM dataset

3、array转json

--array转json
WITH dataset AS (
  SELECT
  	CAST(
	  ARRAY[ ARRAY['name', 'email'], ARRAY['patsnap','xxx@patsnap.com'] ] AS JSON
	) some_json,
)
SELECT * FROM dataset

4、json转row

--json转row
WITH dataset AS (
  SELECT
    CAST(
	  JSON '["patsnap","xxx@patsnap.com",15]' AS ROW(VARCHAR, VARCHAR, INTEGER)
	) AS some_row
)
SELECT * FROM dataset

--嵌套json转row
WITH dataset AS (
  SELECT
    CAST(
	  JSON '{"name":"patsnap","age":15,"project":{"name":["synapse","bio"]}}'
	  AS ROW(name VARCHAR, age INTEGER, project ROW(name ARRAY(VARCHAR)))
	) AS some_row
)
SELECT * FROM dataset

5、json转map

--json转map
WITH dataset AS (
  SELECT
    CAST(
	  JSON '{"email":"xxx@patsnap.com","name":"patsnap"}' AS MAP(VARCHAR, VARCHAR)
	) AS some_map
)
SELECT * FROM dataset

--json转map
WITH dataset AS (
  SELECT
    CAST(
	  JSON '{"age":18,"wight":60}' AS MAP(VARCHAR, INTEGER)
	) AS some_map
)
SELECT * FROM dataset

6、json转array

--json转array
WITH dataset AS (
  SELECT
    CAST(
	  JSON '["patsnap","xxx@patsnap.com"]' AS ARRAY(VARCHAR)
	) AS some_array
)
SELECT * FROM dataset

--json转array
WITH dataset AS (
  SELECT
    CAST(
	  JSON '[{"email":"xxx@patsnap.com"},{"name":"patsnap"},{"age":18}]' AS ARRAY(JSON)
	) AS some_array
)
SELECT * FROM dataset

--json转array
WITH dataset AS (
  SELECT
    CAST(
	  JSON '[{"email":"xxx@patsnap.com","name":"patsnap","age":18},
             {"email":"xxx@hello.com","name":"hello","age":999}]'
	  AS ARRAY(ROW(email VARCHAR, name VARCHAR, age INTEGER))
	) AS some_array
)
SELECT * FROM dataset

2、查看类型

WITH dataset AS (
  SELECT 'hello' AS item
)
SELECT typeof(item) AS item FROM dataset

3、正则函数

见链接：正则函数

更多参考：阿里云帮助文档

又可以改名了

关注

1
点赞
踩
12

收藏

觉得还不错? 一键收藏
0
评论
常用SQL函数

一些常用的sql函数，
复制链接

扫一扫

专栏目录