胖头鱼的技术专栏-473 26ai新特性实战：日历函数（20261004）

作者：胖头鱼的鱼缸（尹海文）
Oracle ACE Pro: Database
PostgreSQL ACE
10年+数据库行业经验
拥有OCM 11g/12c/19c、MySQL 8.0 OCP、Exadata、CDP等认证
墨天轮MVP，ITPUB认证专家
圈内拥有“总监”称号，非著 名社恐（社交恐怖分子）
全网同名：胖头鱼的鱼缸
ITPUB：yhw1809
除授权转载并标明出处外，均为“非法”抄袭

特性介绍

Oracle AI Database 23.26.1 引入了一组日历函数和财政日历函数，用于从日期值中提取各种时间维度的信息。这些函数简化了时间维度分析，无需手动编写复杂的 EXTRACT 或 TO_CHAR 表达式。

函数与语法

标准日历函数（参数为 DATE 或 TIMESTAMP）：

1. CALENDAR_YEAR(date)
   返回日期的日历年份
   示例：CALENDAR_YEAR(DATE ‘2026-03-15’) → 2026

2. CALENDAR_QUARTER(date)
   返回日期的日历季度（1-4）
   示例：CALENDAR_QUARTER(DATE ‘2026-03-15’) → 1

3. CALENDAR_MONTH(date)
   返回日期的日历月份（1-12）
   示例：CALENDAR_MONTH(DATE ‘2026-03-15’) → 3

4. CALENDAR_WEEK(date)
   返回日期的 ISO 周数（1-53）
   示例：CALENDAR_WEEK(DATE ‘2026-03-15’) → 周数

5. CALENDAR_DAY(date)
   返回日期的日历日（1-31）
   示例：CALENDAR_DAY(DATE ‘2026-03-15’) → 15

财政日历函数（参数必须为 TIMESTAMP，不支持 DATE）：

1. FISCAL_YEAR(timestamp, fiscal_month_start)
   返回时间戳所属的财政年度
   当月份 >= fiscal_month_start 时属于当年财政年，否则属于上年

2. FISCAL_QUARTER(timestamp, fiscal_month_start)
   返回时间戳所属的财政季度（1-4）
   财政季度相对于 fiscal_month_start 计算

语法细节：

CALENDAR_YEAR(d)         -- d 是 DATE 或 TIMESTAMP
CALENDAR_QUARTER(d)      -- 返回 1, 2, 3, 4
CALENDAR_MONTH(d)        -- 返回 1-12
CALENDAR_WEEK(d)         -- 返回 ISO 周数 1-53
CALENDAR_DAY(d)          -- 返回 1-31
FISCAL_YEAR(ts, m)       -- ts 必须是 TIMESTAMP，m 是财政月开始月份
FISCAL_QUARTER(ts, m)    -- ts 必须是 TIMESTAMP，m 是财政月开始月份

使用 TO_CHAR 进行格式化输出：

'Q' || CALENDAR_QUARTER(d) || '-' || CALENDAR_YEAR(d)    → Q3-2026
TO_CHAR(d, 'MON') || '-' || CALENDAR_YEAR(d)             → JUL-2026
'W' || CALENDAR_WEEK(d) || '-' || CALENDAR_YEAR(d)       → W28-2026

注意事项：

• FISCAL_YEAR 和 FISCAL_QUARTER 只接受 TIMESTAMP 参数，不接受 DATE
• CALENDAR_WEEK 返回 ISO 8601 标准周数
• 财政函数依赖于 fiscal_month_start 参数定义财政年开始月份

适用场景

• 销售报表：按日、周、月、季度汇总销售数据
• 财务分析：基于财政年（如 4 月开始）生成季度和年度报表
• 时间维度建模：在数据仓库中构建时间维度表
• 业务仪表盘：生成带格式标签的报表输出（如 Q3-2026、JUL-2026）
• 趋势分析：按周或季度进行同比和环比分析
• 预算管理：按财政年度和季度分配和跟踪预算

实战演示

同之前一样，使用用户NFTEST，已授权DB_DEVELOPER_ROLE角色，测试操作均在NFTEST用户下执行。

创建测试表并插入测试数据

CREATE TABLE calendar_sales (
  sale_id   NUMBER PRIMARY KEY,
  product   VARCHAR2(30),
  amount    NUMBER,
  sale_date DATE);
  
INSERT INTO calendar_sales VALUES (1, 'Widget', 100, DATE '2026-01-15');
INSERT INTO calendar_sales VALUES (2, 'Widget', 150, DATE '2026-01-20');
INSERT INTO calendar_sales VALUES (3, 'Gadget', 200, DATE '2026-02-10');
INSERT INTO calendar_sales VALUES (4, 'Gadget', 120, DATE '2026-02-15');
INSERT INTO calendar_sales VALUES (5, 'Widget',  80, DATE '2026-03-01');
INSERT INTO calendar_sales VALUES (6, 'Gadget', 300, DATE '2026-03-05');
INSERT INTO calendar_sales VALUES (7, 'Tablet', 250, DATE '2026-04-10');
INSERT INTO calendar_sales VALUES (8, 'Tablet', 180, DATE '2026-05-20');
INSERT INTO calendar_sales VALUES (9, 'Phone',  400, DATE '2026-06-15');
INSERT INTO calendar_sales VALUES (10,'Phone',  350, DATE '2026-07-01');
INSERT INTO calendar_sales VALUES (11,'Widget', 220, DATE '2026-08-15');
INSERT INTO calendar_sales VALUES (12,'Gadget', 280, DATE '2026-09-10');
COMMIT;

测试1：标准日历函数

SELECT
  sale_id, product, sale_date,
  CALENDAR_YEAR(sale_date)    AS cal_year,
  CALENDAR_QUARTER(sale_date) AS cal_qtr,
  CALENDAR_MONTH(sale_date)   AS cal_month,
  CALENDAR_WEEK(sale_date)    AS cal_week,
  CALENDAR_DAY(sale_date)     AS cal_day
FROM calendar_sales
ORDER BY sale_id;

测试2：格式化输出标签

SELECT
  sale_id, product, sale_date, amount,
  CALENDAR_QUARTER(sale_date) AS quarter_label,
  CALENDAR_MONTH(sale_date)   AS month_label,
  CALENDAR_WEEK(sale_date)    AS week_label
FROM calendar_sales
ORDER BY sale_id;

测试3：日历函数与GROUP BY

SELECT
  CALENDAR_YEAR(sale_date)    AS cal_year,
  CALENDAR_QUARTER(sale_date) AS cal_qtr,
  SUM(amount) AS total_sales,
  COUNT(*)    AS num_sales
FROM calendar_sales
GROUP BY ALL
ORDER BY cal_year, cal_qtr;
SELECT
  CALENDAR_YEAR(sale_date)  AS cal_year,
  CALENDAR_MONTH(sale_date) AS cal_month,
  SUM(amount) AS total_sales,
  COUNT(*)    AS num_sales
FROM calendar_sales
GROUP BY ALL
ORDER BY cal_year, cal_month;

测试4：财政日历函数

SELECT
  sale_id, product, sale_date,
  FISCAL_YEAR(CAST(sale_date AS TIMESTAMP))    AS fiscal_year,
  FISCAL_QUARTER(CAST(sale_date AS TIMESTAMP))  AS fiscal_qtr
FROM calendar_sales
ORDER BY sale_id;

测试5：日历函数 WHERE 过滤

SELECT sale_id, product, sale_date, amount
FROM calendar_sales
WHERE CALENDAR_QUARTER(sale_date) = 'Q1-2026'
ORDER BY sale_date;

测试6：财务汇总 - 按会计季度统计的总销售额

SELECT
  FISCAL_YEAR(CAST(sale_date AS TIMESTAMP))    AS fiscal_year,
  FISCAL_QUARTER(CAST(sale_date AS TIMESTAMP))  AS fiscal_qtr,
  SUM(amount) AS total_sales,
  COUNT(*)    AS num_sales
FROM calendar_sales
GROUP BY ALL
ORDER BY fiscal_year, fiscal_qtr;

总结

本期对 Oracle AI Database 26ai，23.26.1 版本新引入的日历函数进行了完整介绍与实战演示。

老规矩，知道写了些啥。