Monica 是由 GPT-4 驱动的人工智能助手，现在注册赠送你 100 个免费 GPT 次数，包括 GPT-4，GPT-3.5，AI 画图。 https://monica.im/?c=Q1WKG1YR

生成简单的日期序列

select date_add('2021-01-01',interval @i:=@i+1 day) as date 
from ( select 1 union all select 1 union all select 1 union all select 1) as tmp,
 (select @i:= -1) t

MySQL中没有内置的sequence函数。sequence函数通常是在一些其他数据库中使用的函数，例如PostgreSQL。

SELECT sequence(1, 10) AS seq, DATE_ADD('2023-06-09 00:00:00', INTERVAL (seq - 1) * 5 SECOND) AS time_interval

 

生成全年的日期序列，这个查询语句会返回一个包含 365 行记录的结果集，每行记录代表一个日期。

SELECT DATE_FORMAT(date_range.date, '%Y-%m-%d') AS date
FROM (
  SELECT ADDDATE('2022-01-01', seq.seq) AS date
  FROM (
    SELECT t1.i + t10.i*10 + t100.i*100 AS seq
    FROM (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
         (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t10,
         (SELECT 0 AS i UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t100
  ) seq
  WHERE ADDDATE('2022-01-01', seq.seq) <= '2022-12-31'
) date_range

 

MYSQL 生成每5秒间隔的时刻序列：

SELECT DATE_ADD('2023-06-09 00:00:00', INTERVAL (seq - 1) * 5 SECOND) AS time_interval
FROM (SELECT @row := @row + 1 AS seq
      FROM (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) t1
      CROSS JOIN (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) t2
	  CROSS JOIN (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) t3
	  CROSS JOIN (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) t4
	  CROSS JOIN (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) t5
      CROSS JOIN (SELECT @row := -1) r
      WHERE @row < 20000) s;

由于能得到 连续的整数序列，后面结合MYSQL的时间函数，可以很容易的得到其它各种不同维度的时间序列了，

一天内每分钟间隔的时间序列

SELECT DATE_ADD('2023-06-09 00:00:00', INTERVAL seq  MINUTE) AS time_interval
FROM (SELECT @row := @row + 1 AS seq
      FROM (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) t1
      CROSS JOIN (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) t2
	  CROSS JOIN (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) t3
	  CROSS JOIN (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) t4
	  CROSS JOIN (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) t5
      CROSS JOIN (SELECT @row := -1) r
      WHERE @row < 1440) s;

 

 

简单的demo，按时间周期累计求和：

SELECT
	date,
	sales,
	sum( sales ) over ( ORDER BY date ) AS cum_sales 
FROM
	sales 
ORDER BY
	date ASC;

再来一个demo，按字段class（自定义）进行分类并累计求和：

SELECT
	date,
	sales,
	sum( sales ) over ( PARTITION BY class ORDER BY date ) AS cum_sales 
FROM
	sales 
ORDER BY
	date ASC;

 

以上SQL可以在 https://xchart.online/ 这里在线测试下

 

 

By:xSky | Unknown |