SQL实现日期自动填充

SQL实现日期自动填充

在使用SQL进行数据处理时,经常会遇到需要补齐日期的需求,今天聊一聊几个主流数据库的实现方式。

下面以生成2021-09-01到2021-09-30之间所有日期为例进行说明

Oracle

connect by的递归查询还是比较强大的,实现起来也比较简单

SELECT TO_DATE('2021-08-31', 'yyyy-mm-dd') + ROWNUM as date_list
FROM DUAL
CONNECT BY ROWNUM <= 30;

MySQL

  • 在MySQL8之前的版本,通过先生成序号列表,再使用起始日期增加相应天数来实现
SELECT date_add('2021-09-01', INTERVAL d_num DAY) date_list
FROM (
-- 使用@变量的形式,得到序号列表
SELECT @num := @num + 1 as d_num
from
-- 通过两个临时表做笛卡尔积,得到一个记录数为两个表乘积的数据
-- 此处为5*6=30天
(SELECT 1 UNION SELECT 2 UNION 
 SELECT 3 UNION SELECT 4 UNION SELECT 5) t1,
    
(SELECT 1 UNION SELECT 2 UNION SELECT 3
 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) t2,
-- 声明从-1开始
(SELECT @num := -1) a
) a
  • 在MySQL8以后,可以通过递归语法实现
WITH RECURSIVE cte (d) AS
(SELECT '2021-09-01'
 UNION ALL
 SELECT d + INTERVAL 1 DAY
 FROM cte
 WHERE d + INTERVAL 1 DAY <= '2021-09-30')
SELECT *
FROM cte
ORDER BY cte.d;

PostgreSQL

实现起来比较简单粗暴,直接使用generate_series函数生成

select generate_series(
    '2021-09-01'::date,
    '2021-09-30',
    '1 day') date_list;

Hive 函数

在Hive上面的实现相对复杂一些,实现思路是构建出起始日期需要增加天数,然后使用起始日期增加相应的天数实现日期补齐

  1. 使用datediff计算起始日期与结束日期的日期差的天数n

  2. 使用repeat生成一个字符串s,字符串内容为重复某字符n遍

  3. 使用split将字符串s拆分成数组a

  4. 使用posexplode将数组a转为n行带序号的数据

    posexplode说明:behaves like explode for arrays, but includes the position of items in the original array

  5. 通过date_add函数,从起始日期加上序号达到日期补齐

注:本SQL是在Hive3.1.2中执行

select date_add('2021-09-01', a.pos) as d
from (select posexplode(
    split(
        repeat('m', 29)
      , 'm')
)) a

欢迎关注微信公众号,干货满满

  • 0
    点赞
  • 14
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值