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/ 这里在线测试下