SQL-不依赖任何表取连续数字和连续日期

在日常取数时，可能会存在这样的情况，需要统计2022年每日的订单量，但是表里只记录了产生订单的日期下的记录，如果当日没有订单，那么那一天的数据就不存在。这时就需要我们先生成一段完整的连续日期。

那么如何不依赖任何表就得到这样的连续日期呢？写法如下：

这里的2022-12-31是起始日期，为了后面取到2023年1月开始向后的连续日期

100是指连续生成数字和日期的个数，如果想取一整年的，可以改成365

select 
date_add('2022-12-31',row_number() over( ) ) as dt,
row_number() over( ) as id 
from 
(SELECT SPLIT(SPACE(100),'') AS x)LATERAL VIEW explode(x)a

结果如下

dt	id
2023-01-01	1
2023-01-02	2
2023-01-03	3
2023-01-04	4
2023-01-05	5
2023-01-06	6
2023-01-07	7
2023-01-08	8
2023-01-09	9
...	...

是不是很简单呢，那么我们来看下这段代码的操作原理吧。

step1：连续日期可以理解为初始日期+连续数字的结果

只要使用date_add(初试日期,连续数字)就可以实现，那么问题就简化为，如何得到连续数字

step2：计算连续数字需要用到如下三个函数

• 空格函数：space(n)：返回n个空格（即长度为n的空格字符串）
• 分割函数：split(带分隔的字符串，分割符号)：以分割符号划分带分隔的字符串，组成一个字符串列表

select split('a,b,c,d',',')--["a","b","c","d"]
select split('a,b,c,d','')--["a",",","b",",","c",",","d",""]
select split('abcd','')--["a","b","c","d",""]

• 转置函数：explode()，将横向的字符串组成的列表转为多行数据

转置函数更多知识参考：SQL :字符串合并拆分与行列转换：concat_ws()、LATERAL view explode、collect_set()|collect_list()函数_格勒王的博客-CSDN博客concat_ws()、LATERAL view explode、collect_set()|collect_list()函数讲解https://blog.csdn.net/weixin_47198715/article/details/130721818?spm=1001.2014.3001.5502

• 窗口函数：row_number()over()，将字段排序生成排序序号（不连续）

窗口函数更多知识参考：窗口函数学习https://blog.csdn.net/weixin_47198715/article/details/130829177?csdn_share_tail=%7B%22type%22%3A%22blog%22%2C%22rType%22%3A%22article%22%2C%22rId%22%3A%22130829177%22%2C%22source%22%3A%22weixin_47198715%22%7D

根据上面三个函数，如何生成0-100的数字呢？

1、space(100) 生成100个空格

2、split(space(100),'')以''为分割符号分割100个空格，将其命名为x

3、将这100个空格转为多行数据

select *
from 
(SELECT SPLIT(SPACE(100),'') AS x)LATERAL VIEW explode(x) as a

a

空格

空格

...(100个空格)

4、对a列进行排序，得到0-100个序号

select 
row_number() over( ) as id 
from 
(SELECT SPLIT(SPACE(100),'') AS x)LATERAL VIEW explode(x)a

5、将初始日期带入，加上序号值，得到100个连续日期

select 
date_add('2022-12-31',row_number() over( ) ) as dt,
row_number() over( ) as id 
from 
(SELECT SPLIT(SPACE(100),'') AS x)LATERAL VIEW explode(x)a