Presto SQL中如何将VARCHAR转为数组以计算杰卡德相似度？

不同SQL数据库提供了不同的函数来实现将字符串拆分为单个字符的数组，以下是常见数据库的具体实现：

PostgreSQL #

PostgreSQL直接支持用string_to_array函数，传入null作为分隔符即可拆分每个字符：

SELECT 
  column_name,
  string_to_array(column_name, null) AS char_array
FROM sample_table;

也可以使用正则拆分函数regexp_split_to_array：

SELECT 
  column_name,
  regexp_split_to_array(column_name, '') AS char_array
FROM sample_table;

MySQL 8.0+ #

MySQL没有直接的单字符拆分函数，但可以通过JSON_TABLE结合正则替换来实现：

SELECT 
  t.column_name,
  JSON_ARRAYAGG(j.char) AS char_array
FROM sample_table t
JOIN JSON_TABLE(
  CONCAT('["', REGEXP_REPLACE(t.column_name, '(.)', '\\1","'), '"]'),
  '$[*]' COLUMNS(char VARCHAR(1) PATH '$')
) j
GROUP BY t.column_name;

或者使用递归CTE生成每个字符的位置，再提取并聚合：

WITH RECURSIVE chars AS (
  SELECT 
    column_name,
    1 AS pos,
    SUBSTRING(column_name, 1, 1) AS char
  FROM sample_table
  WHERE column_name IS NOT NULL AND column_name != ''
  UNION ALL
  SELECT 
    c.column_name,
    c.pos + 1,
    SUBSTRING(c.column_name, c.pos + 1, 1) AS char
  FROM chars c
  WHERE c.pos < LENGTH(c.column_name)
)
SELECT 
  column_name,
  JSON_ARRAYAGG(char) AS char_array
FROM chars
GROUP BY column_name;

Hive #

Hive可以通过正则替换给每个字符添加分隔符，再用split函数拆分：

SELECT 
  column_name,
  split(regexp_replace(column_name, '(.)', '$1\\|'), '\\|') AS char_array
FROM sample_table;

注意：如果字符串为空，会生成包含一个空元素的数组，可根据需求过滤。

SQL Server 2022+ #

SQL Server 2022引入了STRING_SPLIT的enable_ordinal参数，结合JSON_ARRAY_AGG生成数组：

SELECT 
  t.column_name,
  JSON_ARRAY_AGG(s.value) AS char_array
FROM sample_table t
CROSS APPLY STRING_SPLIT(t.column_name, '', 1) s
GROUP BY t.column_name;

旧版本SQL Server可通过XML拆分后聚合：

SELECT 
  column_name,
  JSON_QUERY('[' + STRING_AGG('"' + SUBSTRING(column_name, n, 1) + '"', ',') + ']') AS char_array
FROM sample_table
CROSS JOIN (
  SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
  FROM sys.all_columns
) nums
WHERE n <= LEN(column_name)
GROUP BY column_name;

内容的提问来源于stack exchange，提问作者Nini