需求背景:
在理财 APP 中,素材、广告位、产品、策略有时候是多对多的关系。比如,在内容中台,一个素材可能关联理财、基金、存款某些产品,那我们统计该素材的好不好,转化率好不好,该归属于哪些业务?再进而计算某些业务的贡献,就可能需要用到数组。
还是不怎么看文档,因为文档的例子不够直观。
在https://community.cloud.databricks.com/ 上创建表的方法,可以参考文档,https://docs.databricks.com/sql/language-manual/sql-ref-syntax-ddl-create-table-using.html#examples
创表
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');
数据如下:
name | courses |
---|---|
Alice | Math |
Alice | Science |
Bob | English |
Bob | History |
Bob | Art |
Charlie | Math |
Charlie | Art |
David | Science |
Emma | Math |
Emma | English |
Emma | Science |
需求1
现在需要将 student 的数据变成一个学生一行数据,分别将课程拼接成字符串和组成数组类型。代码如下:
select
name
,concat_ws(', ', collect_list(courses)) as courses
from
student
group by
name
name | courses |
---|---|
Charlie | Math, Art |
Bob | English, History, Art |
Alice | Math, Science |
Emma | Math, English, Science |
David | Science |
select
name,array_agg(courses) as courses
from
student
group by
name;
name | courses |
---|---|
Charlie | ["Math","Art"] |
Bob | ["English","History","Art"] |
Alice | ["Math","Science"] |
Emma | ["Math","English","Science"] |
David | ["Science"] |
select name, collect_list(courses) as courses
from student
group