DuckDB 宏（Macros）完全指南：把重复 SQL 片段打包成可复用函数

-- 定义：计算两个日期之间的工作日天数
CREATE MACRO workdays(start_date, end_date) AS
  (end_date::DATE - start_date::DATE + 1)
  - (date_part('dow', start_date::DATE) + date_part('dow', end_date::DATE));

-- 调用
SELECT workdays('2026-08-10', '2026-08-17') AS result;
-- 返回：5（去掉了周六周日）

-- 定义：判断是否为节假日（简化版）
CREATE MACRO is_holiday(date_val) AS
  CASE date_val::DATE
    WHEN '2026-01-01' THEN true
    WHEN '2026-05-01' THEN true
    WHEN '2026-10-01' THEN true
    ELSE false
  END;

-- 在查询中使用
SELECT
  order_date,
  amount,
  is_holiday(order_date) AS is_holiday_flag
FROM orders
WHERE is_holiday(order_date);

-- 安全除法：除数为 0 时返回默认值
CREATE MACRO safe_divide(a, b, default_val := 0) AS
  CASE WHEN b = 0 THEN default_val ELSE a / b END;

SELECT
  safe_divide(10, 3) AS normal,    -- 3.333...
  safe_divide(10, 0) AS zero;      -- 0

-- 把日期拆成年/月/日
CREATE MACRO parse_date(d) AS
  struct_pack(
    year  := extract('year'  FROM d),
    month := extract('month' FROM d),
    day   := extract('day'   FROM d)
  );

SELECT parse_date('2026-08-13'::DATE);
-- 返回：{'year': 2026, 'month': 8, 'day': 13}

-- 解构使用
SELECT
  (parse_date(order_date)).year  AS order_year,
  (parse_date(order_date)).month AS order_month,
  SUM(amount) AS total
FROM orders
GROUP BY order_year, order_month;

-- 生成 N 个连续日期
CREATE MACRO date_range(start, count) AS
  generate_series(start, start + INTERVAL (count - 1) DAY, INTERVAL 1 DAY);

SELECT date_range('2026-08-01', 7);
-- 返回：[2026-08-01, 2026-08-02, ..., 2026-08-07]

CREATE MACRO fmt_money(val, currency := '¥') AS
  currency || ROUND(val, 2);

SELECT fmt_money(1234.5), fmt_money(567.8, '$');
-- 返回：¥1234.50 | $567.80

CREATE MACRO calc_mom(current_val, prev_val) AS
  ROUND(100.0 * (current_val - prev_val) / NULLIF(prev_val, 0), 2);

-- 在分析查询中使用
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
  calc_mom(revenue, LAG(revenue) OVER (ORDER BY month)) AS mom_pct
FROM monthly_sales;

CREATE MACRO check_null_ratio(table_ref, col_name) AS
  ROUND(
    100.0 * SUM(CASE WHEN {col_name} IS NULL THEN 1 ELSE 0 END)
    / COUNT(*),
    2
  );

-- 使用：检查orders表的user_id列空值率
SELECT check_null_ratio(orders, user_id) AS null_ratio_pct;