Snowflake中Teradata Sys_Calendar.CALENDAR的替代方案咨询

Snowflake没有直接对应Teradata Sys_Calendar.CALENDAR 的系统视图，但可以通过两种方式实现类似功能：

1. 直接使用内置日期函数提取属性 #

Teradata日历视图中的日期属性（年、月、日、季度等），Snowflake都有对应的内置函数可以直接提取，无需额外关联表。常见映射关系如下：

示例查询：
原Teradata查询：

SELECT t.order_date, c.year, c.month_of_year, c.day_of_week
FROM orders t
JOIN Sys_Calendar.CALENDAR c ON t.order_date = c.calendar_date;

Snowflake等价查询：

SELECT 
  order_date,
  YEAR(order_date) AS year,
  MONTH(order_date) AS month_of_year,
  MOD(DAYOFWEEK(order_date) + 5, 7) + 1 AS day_of_week
FROM orders;

2. 创建自定义日历表 #

如果需要和Teradata一样固定范围（1900-2100年）的日历表，可以生成一个永久表，后续查询直接关联即可：

生成日历表的SQL：

CREATE OR REPLACE TABLE CALENDAR (
  CALENDAR_DATE DATE,
  YEAR NUMBER(4,0),
  MONTH_OF_YEAR NUMBER(2,0),
  DAY_OF_MONTH NUMBER(2,0),
  DAY_OF_YEAR NUMBER(3,0),
  QUARTER NUMBER(1,0),
  DAY_OF_WEEK NUMBER(1,0),
  MONTH_NAME VARCHAR(15),
  WEEK_OF_YEAR NUMBER(2,0)
) AS
SELECT
  DATEADD(day, seq4(), '1900-01-01'::DATE) AS CALENDAR_DATE,
  YEAR(CALENDAR_DATE) AS YEAR,
  MONTH(CALENDAR_DATE) AS MONTH_OF_YEAR,
  DAY(CALENDAR_DATE) AS DAY_OF_MONTH,
  DAYOFYEAR(CALENDAR_DATE) AS DAY_OF_YEAR,
  QUARTER(CALENDAR_DATE) AS QUARTER,
  MOD(DAYOFWEEK(CALENDAR_DATE) + 5, 7) + 1 AS DAY_OF_WEEK,
  TO_CHAR(CALENDAR_DATE, 'FMMONTH') AS MONTH_NAME, -- FMMONTH去除空格填充
  WEEKOFYEAR(CALENDAR_DATE) AS WEEK_OF_YEAR
FROM TABLE(GENERATOR(ROWCOUNT => (SELECT DATEDIFF(day, '1900-01-01', '2100-12-31') + 1)));

使用示例：

SELECT t.order_date, c.year, c.month_name
FROM orders t
JOIN CALENDAR c ON t.order_date = c.calendar_date;

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