1. space函数与split函数结合,得到空字符串数组;
select split(space(9), '');
2. 如何产生1-10的连续的数字?
- 先利用
space + split
函数生成长度为10的数组:[" ", " ", ...]
- 此时该数组的下标是
0 - 9
,是连续的数,所以结合posexplode
能得到0 - 9
的数字 - 所有数字再加上
1
,变为1 - 10
的连续数
select
start_num+pos as seq_num
from(
select distinct
1 as start_num,
10 as end_num
from order_detail
) s1 lateral view posexplode(split(space(end_num-start_num), '')) s2 as pos, null_ele;
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/b681ad494d98e9512aad292912cbe37a.png)
3. 如何产生开始日期到结束日期的连续的日期?
SELECT
DATE_ADD(START_DATE, pos)
FROM (
SELECT DISTINCT
"2023-03-13" AS START_DATE,
"2023-03-23" AS END_DATE
from order_detail
) s1 lateral VIEW posexplode(split(SPACE(DATEDIFF(END_DATE, START_DATE)), " ")) s2 AS pos, null_ele
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/5b6f417092d27b07ade470224f92f0ce.png)