数据仓库——collect_set函数、NVL函数、日期处理

目录

1.collect_set函数

2.nvl函数

3.日期处理函数

---

1.collect_set函数

 数据：

id	order-type	order-number
1	aa	5
2	bb	2
1	bb	1
2	aa	3
1	aa	2

 

需求：对用户的订单进行分析，将不同订单类型分别多少单展示出来，每个用户一行

实现：

1.使用concat（）函数将order_type和order_number连接起来

concat（order_type,'(',order_number,')'）

2.使用concat_ws（）和collect_set（）进行合并行

将上面列表中一个user可能会占用多行转换为每个user占一行的目标表格式，实际是“列转行”

select user,concat_ws(',',collect_set(concat（order_type,'(',order_number,')'）))  order from table group by user

order是别名

作用：

• 去重，对group by后面的id去重
• 对group by以后属于同一id的形成一个集合，结合concat_ws对集合中元素使用，进行分隔形成字符串

2.nvl函数

NVL（表达式1，表达式2）

nvl是一个空值转换函数。

如果表达式1为空值，NVL返回值为表达式2的值，否则返回表达式1的值。 该函数的目的是把一个空值（null）转换成一个实际的值。其表达式的值可以是数字型、字符型和日期型。但是表达式1和表达式2的数据类型必须为同一个类型。

应用场景：

•   可以设置字段如果为空的默认值。例如如果一个人在注册app时不填写用户名称时默认取你注册用的微信名称一样。
•   也可以用于外关联(join等)时两个表中有重复字段但是值不一样时，可以设置该字段取值的优先级别。例如两个事件表，一个是紧急事件表，另一个是基本事件表，两个表中都有一个字段名为事件紧急程度，这里我们就可以先将两个表进行关联，在设置事件紧急程度时首先取紧急事件表中该字段的内容，如果为空再取基本事件表中该字段的内容。

3.日期处理函数

• data_format函数（根据格式整理日期）

select data_format('2020-11-6','yyyy-mm');

• data_add函数（加减日期）

前一天： select date_add('2020-11-6',-1);

后一天： select date_add('2020-11-6',1);

• next_day函数

（1）取当前天的下一个周一

select next_day('2020-11-06','MO');

（2）取当前周的周一

select date_add(next_day('2020-11-06','MO'),-7);

• last_day函数（求当月的最后一天日期）

select last_day('2020-10-30');