如何在Big Query中将3位代码转换为两列形式的起止日期范围

实现逻辑 #

• 提取3位码前2位，拼接前缀生成4位完整年份（示例默认2000年之后的年份，若需兼容19xx年可自行调整拼接规则）
• 提取3位码第3位作为季度值
• 季度首月固定为季度值*3-2，即Q1对应1月、Q2对应4月、Q3对应7月、Q4对应10月
• 季度首日为对应首月的1号，季度末日直接调用BigQuery内置LAST_DAY函数按季度维度计算即可

示例代码 #

假设你的业务表名为your_dataset.your_table，存储3位周期码的字段名为period_code，完整查询语句如下：

SELECT
  period_code,
  -- 若需要输出dd/mm/yyyy格式的字符串，可替换为FORMAT_DATE('%d/%m/%Y', start_date)
  DATE(year, (quarter - 1)*3 + 1, 1) AS start_date,
  -- 若需要输出dd/mm/yyyy格式的字符串，可替换为FORMAT_DATE('%d/%m/%Y', end_date)
  LAST_DAY(DATE(year, (quarter -1)*3 +1, 1), QUARTER) AS end_date
FROM (
  SELECT
    period_code,
    -- 提取年份，默认匹配2000年后的周期，若需兼容19xx可修改CONCAT逻辑
    SAFE_CAST(CONCAT('20', LEFT(SAFE_CAST(period_code AS STRING), 2)) AS INT64) AS year,
    -- 提取季度值
    SAFE_CAST(RIGHT(SAFE_CAST(period_code AS STRING), 1) AS INT64) AS quarter
  FROM `your_dataset.your_table`
)

验证示例 #

输入period_code = '213'时：

• 计算得到year=2021，quarter=3
• start_date = 2021-07-01，格式化后为01/07/2021
• end_date = 2021-09-30，格式化后为30/09/2021，符合需求

注意事项 #

• 若period_code为数值类型，代码中已做安全转字符串处理，无需额外调整
• 若存在19xx年的周期码（如991对应1999年第一季度），可将年份计算逻辑替换为：

  SAFE_CAST(IF(SAFE_CAST(LEFT(SAFE_CAST(period_code AS STRING), 2) AS INT64) >= 50, CONCAT('19', LEFT(SAFE_CAST(period_code AS STRING), 2)), CONCAT('20', LEFT(SAFE_CAST(period_code AS STRING), 2))) AS INT64) AS year
  

  上述逻辑默认两位年份大于等于50的归为19xx年，可根据业务实际范围调整阈值。

内容的提问来源于stack exchange，提问作者Adam Mills