BigQuery中Netezza AGE年龄计算函数的等效替代方案查询

Netezza的AGE函数用于返回两个日期之间包含年、月、日维度的间隔文本结果，在BigQuery中可以通过日期差值计算加格式化拼接的方式实现完全等效的效果，以下是具体实现：

直接使用的SQL示例 #

WITH date_params AS (
  SELECT 
    CURRENT_DATE() AS end_date,
    DATE '1994-07-16' AS start_date
),
age_calc AS (
  SELECT
    -- 计算年份差，处理月日不足的进位场景
    DATE_DIFF(end_date, start_date, YEAR) 
      - IF(FORMAT_DATE('%m%d', end_date) < FORMAT_DATE('%m%d', start_date), 1, 0) AS years,
    -- 计算月份差，处理日期不足的进位场景
    MOD(DATE_DIFF(end_date, start_date, MONTH) 
      - IF(EXTRACT(DAY FROM end_date) < EXTRACT(DAY FROM start_date), 1, 0), 12) AS months,
    -- 计算日期差，处理跨月场景
    IF(EXTRACT(DAY FROM end_date) >= EXTRACT(DAY FROM start_date),
      EXTRACT(DAY FROM end_date) - EXTRACT(DAY FROM start_date),
      DATE_DIFF(end_date, DATE_SUB(DATE_TRUNC(end_date, MONTH), INTERVAL 1 DAY), DAY) 
        + EXTRACT(DAY FROM start_date)
    ) AS days
  FROM date_params
)
SELECT 
  CONCAT(years, ' years ', months, ' mons ', days, ' days') AS age
FROM age_calc;

上述代码运行输出和Netezza原生AGE函数完全一致，示例输入会返回27 years 2 mons 21 days（结果会随当前日期动态变化）。

封装为自定义函数复用 #

如果需要和Netezza完全一致的调用方式，可以封装为临时自定义函数：

CREATE TEMP FUNCTION AGE(end_date DATE, start_date DATE) 
RETURNS STRING AS (
  (
    WITH age_calc AS (
      SELECT
        DATE_DIFF(end_date, start_date, YEAR) 
          - IF(FORMAT_DATE('%m%d', end_date) < FORMAT_DATE('%m%d', start_date), 1, 0) AS years,
        MOD(DATE_DIFF(end_date, start_date, MONTH) 
          - IF(EXTRACT(DAY FROM end_date) < EXTRACT(DAY FROM start_date), 1, 0), 12) AS months,
        IF(EXTRACT(DAY FROM end_date) >= EXTRACT(DAY FROM start_date),
          EXTRACT(DAY FROM end_date) - EXTRACT(DAY FROM start_date),
          DATE_DIFF(end_date, DATE_SUB(DATE_TRUNC(end_date, MONTH), INTERVAL 1 DAY), DAY) 
            + EXTRACT(DAY FROM start_date)
        ) AS days
    )
    SELECT CONCAT(years, ' years ', months, ' mons ', days, ' days')
    FROM age_calc
  )
);

-- 调用方式和Netezza完全一致
SELECT AGE(CURRENT_DATE(), '1994-07-16');

内容的提问来源于stack exchange，提问作者Radha Raman