GENERATE_SERIES (Lakehouse v1)

生成一个从指定起点开始、到另一指定终点结束的数据集，并可选择按递增值生成。GENERATE_SERIES 函数适用于以下数据类型：

• 整数
• 日期
• 时间戳

Analyze 语法

func.generate_series(<start>, <stop>[, <step_interval>])

Analyze 示例

func.generate_series(1, 10, 2);

generate_series|
---------------+
              1|
              3|
              5|
              7|
              9|

SQL 语法

GENERATE_SERIES(<start>, <stop>[, <step_interval>])

参数

参数	说明
start	起始值，即序列中的第一个数字、日期或时间戳。
stop	结束值，即序列中的最后一个数字、日期或时间戳。
step_interval	步长，决定序列中相邻值之间的差值。对于整数序列，默认值为 1。对于日期序列，默认步长为 1 天。对于时间戳序列，默认步长为 1 微秒。

注意

对于 GENERATE_SERIES 和 RANGE 这类函数，关键区别在于边界特性。GENERATE_SERIES 同时包含左右两端边界，而 RANGE 只包含左端边界。例如，使用 RANGE(1, 11) 等同于 GENERATE_SERIES(1, 10)。

返回类型

返回一个列表，其中包含从 start 到 stop 的连续数值、日期或时间戳序列。

SQL 示例

SQL 示例 1：生成数值、日期和时间戳数据

SELECT * FROM GENERATE_SERIES(1, 10, 2);

generate_series|
---------------+
              1|
              3|
              5|
              7|
              9|

SELECT * FROM GENERATE_SERIES('2023-03-20'::date, '2023-03-27'::date);

generate_series|
---------------+
     2023-03-20|
     2023-03-21|
     2023-03-22|
     2023-03-23|
     2023-03-24|
     2023-03-25|
     2023-03-26|
     2023-03-27|

SELECT * FROM GENERATE_SERIES('2023-03-26 00:00'::timestamp, '2023-03-27 12:00'::timestamp, 86400000000);

generate_series    |
-------------------+
2023-03-26 00:00:00|
2023-03-27 00:00:00|

SQL 示例 2：填补查询结果中的空缺

此示例使用 GENERATE_SERIES 函数和左联接运算符，处理因特定范围内缺少信息而导致的查询结果空缺。

CREATE TABLE t_metrics (
  date Date,
  value INT
);

INSERT INTO t_metrics VALUES
  ('2020-01-01', 200),
  ('2020-01-01', 300),
  ('2020-01-04', 300),
  ('2020-01-04', 300),
  ('2020-01-05', 400),
  ('2020-01-10', 700);

SELECT date, SUM(value), COUNT() FROM t_metrics GROUP BY date ORDER BY date;

date      |sum(value)|count()|
----------+----------+-------+
2020-01-01|       500|      2|
2020-01-04|       600|      2|
2020-01-05|       400|      1|
2020-01-10|       700|      1|

要填补 2020 年 1 月 1 日至 1 月 10 日之间的空缺，请使用以下查询：

SELECT t.date, COALESCE(SUM(t_metrics.value), 0), COUNT(t_metrics.value)
FROM generate_series(
  '2020-01-01'::Date,
  '2020-01-10'::Date
) AS t(date)
LEFT JOIN t_metrics ON t_metrics.date = t.date
GROUP BY t.date ORDER BY t.date;

date      |coalesce(sum(t_metrics.value), 0)|count(t_metrics.value)|
----------+---------------------------------+----------------------+
2020-01-01|                              500|                     2|
2020-01-02|                                0|                     0|
2020-01-03|                                0|                     0|
2020-01-04|                              600|                     2|
2020-01-05|                              400|                     1|
2020-01-06|                                0|                     0|
2020-01-07|                                0|                     0|
2020-01-08|                                0|                     0|
2020-01-09|                                0|                     0|
2020-01-10|                              700|                     1|