Spark SQL 中的array类的函数例子

CREATE TABLE student (name STRING, courses STRING)

INSERT INTO student (name, courses) VALUES ('Alice', 'Math'), ('Alice', 'Science'), ('Bob', 'English'), ('Bob', 'History'), ('Bob', 'Art'), ('Charlie', 'Math'), ('Charlie', 'Art'), ('David', 'Science'), ('Emma', 'Math'), ('Emma', 'English'), ('Emma', 'Science');

select 
    name
   ,concat_ws(', ', collect_list(courses)) as courses
from 
    student
group by 
    name

select 
   name,array_agg(courses) as courses
from 
   student
group by 
    name;

select name, collect_list(courses) as courses
from student
group by name

-- chat GPT 说这样也可以，但是我选择的版本不支持。

-- STRING_AGG 函数是 SQL:2016 标准中新增的函数，不是所有的数据库管理系统都支持该函数。
-- Spark 3.0 中，STRING_AGG 函数被引入作为 SQL:2016 标准的一部分。你可以使用 STRING_AGG 函数将每个分组的数据拼接成一个字符串。
select name, string_agg(courses, ',') as courses
from student
group by name;

select 
   name
  ,array(collect_list(courses)) as courses
from 
  student
group by 
   name

select 
     name
    ,explode(split(courses, ', ')) as course
from 
    temp

select name,course
from 
(
  select 
      name
     ,array_agg(courses) as courses
  from 
     student
  group by 
     name
 ) 
lateral view explode(courses) exploded_courses as course;

select
    course
   ,count(distinct name) as student_count
from 
(
  -- 踩坑1中的 temp 表，数据如需求2。
  select 
    name,explode(split(courses, ', ')) as course
  from 
    temp
) subquery
-- where course in ('math', 'english')
group by course;

select course,count(distinct name) as student_count
from 
(
  select 
     name
    ,explode(courses) as course
  from 
  (
    select 
        name
       ,array_agg(courses) as courses
    from 
       student
    group by 
        name
  )
) as temp
group by course;

-- 创建表的第二种形式，student_copy 是
create table student_copy as 
select name, collect_list(courses) as courses
from student
group by name

select * from student_copy where array_contains(courses, 'math')

select array_distinct(courses) from student_copy 
-- array_distinct（x ）→ x：去重：删除数组x中重复元素。 仅对一行的数组去重，不是对整个数组字段去重
-- https://docs.ucloud.cn/usql/common/func/array 

select collect_set(course) as courses
from (
  select explode(courses) as course
  from student_copy
) subquery;

select concat_ws('\n', collect_set(course)) as courses
from (
  select explode(courses) as course
  from student_copy
) subquery;

select course
from (
  select explode(courses) as course
  from student_copy
) subquery
group by course;

select 
  t1.name,array_append(t1.courses,t2.courses) as courses
from 
   student_copy as t1 
 left join 
   (SELECT * FROM student_copy WHERE array_contains(courses, 'Math')) as t2 
 on t1.name = t2.name

select 
  t1.name,array_append(t1.courses,t2.courses) as courses
from 
   student_copy as t1 
 left join 
   ( SELECT name, courses FROM temp) as t2 
 on t1.name = t2.name

select 
 a.name,array_distinct(a.courses) as courses
from 
(
  select 
    t1.name,array_append(t1.courses,t2.courses) as courses
  from 
     student_copy as t1 
   left join 
     ( SELECT name, courses FROM temp) as t2 
   on t1.name = t2.name
) as a

select 
   t1.name,array_sort(t1.courses) as courses
from
(
  select 
   name,array_agg(courses) as courses
  from 
     students
  group by 
      name
) as t1