mysql、oracle构建临时连续日期序列

mysql、oracle构建临时连续日期序列

做统计分析的时候，总有些按日期查询。比如近 7 天数据，近 30 天数据，当月数据之类的。但是表中的日期并不一定是连续的，这就需要构建一个日期序列作为主表查询。每次做这类查询都要baidu半天怎么构建日期序列，今天记录一下。

mysql

mysql 这里百度了半天，没有找到像 oracle 那样的语法函数，只能另想他法了。

1、创建日期表

这个方法是在前公司偶然见到的。顾名思义就是创建一张数据表，里面保存了从当天开始一直向前推得日期数据，示例如下：

这种方式太简单了，就不贴 sql 了。

2、创建数字序列表

使用日期序列表太麻烦了，我们可以借助一个数字序列表来实现上述查询，示例如下：

然后 sql 就是这样的了：

-- 最近7天序列
SELECT
	DATE_FORMAT(date_add(now(), INTERVAL @i := @i - 1 DAY), '%Y-%m-%d') date 
FROM
	number join (SELECT @i := 1) t 
WHERE
	seq <= 7;

-- 当前周每天序列
SELECT
	DATE_FORMAT(date_add(now(), INTERVAL @i := @i - 1 DAY), '%Y-%m-%d') date 
FROM
	number join (SELECT @i := 1) t 
WHERE
	seq <= (WEEKDAY(now()) + 1);

-- 当前月每天序列
SELECT
	DATE_FORMAT(date_add(now(), INTERVAL @i := @i - 1 DAY), '%Y-%m-%d') date 
FROM
	number join (SELECT @i := 1) t 
WHERE
	seq <= (DAYOFMONTH(now()));

-- 最近6个月序列
SELECT
	DATE_FORMAT(date_add(now(), INTERVAL @i := @i - 1 MONTH), '%Y-%m') date 
FROM
	number join (SELECT @i := 1) t 
WHERE
	seq <= 6;

-- 当前年每月序列
SELECT
	DATE_FORMAT(date_add(now(), INTERVAL @i := @i - 1 MONTH), '%Y-%m') date 
FROM
	number join (SELECT @i := 1) t 
WHERE
	seq <= (MONTh(now()));

oracle

不得不说，还是 oracle 强大

-- 最近7天序列
select to_char(sysdate - level + 1, 'yyyy-MM-dd') date_seq FROM DUAL connect BY LEVEL <= 7;

-- 当前周每天序列
select to_char(sysdate - level + 1, 'yyyy-MM-dd') date_seq FROM DUAL connect BY LEVEL <= trunc(SYSDATE - trunc(sysdate, 'D') - 1) + 1;

-- 当前月每天序列
select to_char(sysdate - level + 1, 'yyyy-MM-dd') date_seq FROM DUAL connect BY LEVEL <= TO_CHAR(SYSDATE, 'DD');

-- 最近6个月序列
SELECT to_char(add_months(SYSDATE, -level + 1), 'yyyy-MM') month FROM dual CONNECT BY level <= 6;

-- 当前年每月序列
SELECT to_char(add_months(SYSDATE, -level + 1), 'yyyy-MM') month FROM dual CONNECT BY level <= to_char(sysdate, 'MM');

有了这几个基本的，其他的变种应该也不难写了。