如何在BigQuery中生成包含上季度各月最后一天的数组？

可以通过BigQuery的日期函数组合实现需求，以下是具体的实现方案：

核心思路 #

1. 从表名（或分区后缀）中提取YYYYMMDD格式的日期，转换为DATE类型。
2. 计算目标日期所在上季度的起始日期。
3. 生成该季度三个月份的最后一天，按倒序排列组成数组。

示例代码 #

场景1：从表名中提取日期 #

WITH sample_table_names AS (
  SELECT 'project.dataset.table_20200110' AS table_name
)
SELECT
  table_name,
  ARRAY(
    SELECT LAST_DAY(DATE_ADD(quarter_start, INTERVAL m MONTH))
    FROM UNNEST([0, 1, 2]) AS m
    ORDER BY DATE_ADD(quarter_start, INTERVAL m MONTH) DESC
  ) AS last_days_of_previous_quarter
FROM (
  SELECT
    table_name,
    -- 提取表名末尾的8位日期并转为DATE类型
    PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(table_name, r'_(\d{8})$')) AS current_date,
    -- 计算上季度的起始日期
    DATE_TRUNC(DATE_SUB(PARSE_DATE('%Y%m%d', REGEXP_EXTRACT(table_name, r'_(\d{8})$')), INTERVAL 1 QUARTER), QUARTER) AS quarter_start
  FROM sample_table_names
)

执行后，输出的last_days_of_previous_quarter数组为['2019-12-31', '2019-11-30', '2019-10-31']，完全符合示例要求。

场景2：查询分区表时使用_TABLE_SUFFIX #

如果是查询分区表，直接用_TABLE_SUFFIX获取分区的YYYYMMDD字符串更高效：

SELECT
  _TABLE_SUFFIX AS partition_date,
  ARRAY(
    SELECT LAST_DAY(DATE_ADD(quarter_start, INTERVAL m MONTH))
    FROM UNNEST([0, 1, 2]) AS m
    ORDER BY DATE_ADD(quarter_start, INTERVAL m MONTH) DESC
  ) AS last_days_of_previous_quarter
FROM `project.dataset.table_*`
WHERE _TABLE_SUFFIX = '20200110' -- 指定目标分区
GROUP BY partition_date

关键函数说明 #

• REGEXP_EXTRACT：从表名中提取日期字符串。
• PARSE_DATE：将YYYYMMDD字符串转换为DATE类型。
• DATE_SUB + DATE_TRUNC：计算上季度的起始日期。
• LAST_DAY：获取指定日期所在月份的最后一天。
• UNNEST + ARRAY：生成包含三个月份最后一天的数组，通过ORDER BY调整为倒序。

内容的提问来源于stack exchange，提问作者Saverio Guzzo