Teradata 学习笔记5

系统日历
SQL有非常有限的日期运算功能，对日期运算比较困难。因此，有必要开发更
复杂的基于时间的计算工具，这就是开发系统日历的原因。系统日历与用户自己定
义的日历相比，最重要的一点是性能提高。
Teradata的系统日历涵盖200年的范围，没有性能问题。因为日历表仅仅按照

当前执行的查询物化所需要的实际行数据。

日历表的布局
系统日历包含从1900-01-01到2100-12-31的每天的数据，每天在表中都有一行
数据。
下面是系统日历可以访问的列：
calendar_date DATE UNIQUE (标准Teradata日期)
day_of_week BYTEINT, (1-7，星期几，1代表星期天)
day_of_month BYTEINT, (1-31，本月中的第几号)
day_of_year SMALLINT, (1-366，本年中的第几天)
day_of_calendar INTEGER, (从01/01/1900开始的天, 本日历中第几天)
weekday_of_month BYTEINT, (本月中该星期几出现的次数)

week_of_month BYTEINT, (本月中第几周，以星期天到星期六为一周。0，表
示月的第一个不完整的周；1表示月的第一个完整的周)
week_of_year BYTEINT, (0-53) (本年中第几周，0表示第一个不完整的周)
week_of_calendar INTEGER, (0-n) (本日历中的第几周，0表示第一个不完整的
周)
month_of_quarter BYTEINT, (1-3，本季度中第几月)
month_of_year BYTEINT, (1-12，本年中第几月)
month_of_calendar INTEGER, (1-n，本日历中第几月，从1900年1月起)
quarter_of_year BYTEINT, (1-4，本年中第几季度)
quarter_of_calendar INTEGER, (本日历中第几季度，从1900年1月起)
year_of_calendar SMALLINT, (年份，从1900起)

系统日历有下列特性：
! 基础表是Sys_calendar.Caldates
! 它只有一列"cdate"，数据类型是DATE

! 日历中的每个日期都有一行数据
! 唯一主索引(UPI)是"cdate"
! 每个视图都增加了一些智能的日期功能

SELECT *
FROM Sys_calendar.Calendar
WHERE calendar_date = current_date;
结果
calendar_date: 98/09/21
day_of_week: 2

day_of_month: 21
day_of_year: 264
day_of_calendar: 36058
weekday_of_month: 3
week_of_month: 3
week_of_year: 38
week_of_calendar: 5151
month_of_quarter: 3
month_of_year: 9
month_of_calendar: 1185
quarter_of_year: 3
quarter_of_calendar: 395
year_of_calendar: 1998
注：SELECT CURRENT_DATE是ANSI标准，等同于SELECT DATE。

使用日历
下面是使用日历的例子：
CREATE SET TABLE daily_sales ,NO FALLBACK
,NO BEFORE JOURNAL
,NO AFTER JOURNAL
(itemid INTEGER
,salesdate DATE FORMAT 'YY/MM/DD'
,sales DECIMAL(9,2))
PRIMARY INDEX ( itemid );

问题
显示1998年1季度item 10的销售总额。
解答
SELECT ,ds.itemid
,SUM(ds.sales)
FROM sys_calendar.calendar sc
,daily_sales ds
WHERE sc.calendar_date = ds.salesdate
AND sc.quarter_of_year = 1
AND sc.year_of_calendar = 1998AND ds.itemid = 10
GROUP BY 1;

结果
itemid Sum(sales)
10 4050.00

下面是使用日历的例子：
CREATE SET TABLE daily_sales ,NO FALLBACK
,NO BEFORE JOURNAL
,NO AFTER JOURNAL
(itemid INTEGER
,salesdate DATE FORMAT 'YY/MM/DD'
,sales DECIMAL(9,2))
PRIMARY INDEX ( itemid );

问题
获得item 10在当前月的销售额。
解答
SELECT SUM(ds.sales)

FROM sys_calendar.calendar sc
,daily_sales ds
,today td
WHERE sc.calendar_date = ds.salesdate
AND sc.month_of_calendar = td.month_of_calendar
AND ds.itemid = 10;
结果
Sum(sales)
2550.00

查询相对于今天的信息
连接视图"Today"，查询相对于今天的信息。下面是一个典型的例子。
问题
比较item 10在今年和去年的这个月和上个月的销售额。

解答
SELECT sc.year_of_calendar AS "year"
,sc.month_of_year AS "month"
,ds.itemid
,sum(ds.sales)

FROM sys_calendar.calendar sc
,daily_sales ds
,today td
WHERE sc.calendar_date = ds.salesdate
AND ((sc.month_of_calendar BETWEEN td.month_of_calendar - 1
AND td.month_of_calendar)
OR (sc.month_of_calendar BETWEEN td.month_of_calendar - 13
AND td.month_of_calendar - 12))
AND ds.itemid = 10
GROUP BY 1,2,3
ORDER BY 1,2;

结果
year month itemid Sum(sales)
1997 8 10 1950.00
1997 9 10 2100.00
1998 8 10 2200.00
1998 9 10 2550.00

分组结果
现在我们做前面同样的查询，但是按周分隔信息。我们在SELECT语句中增加
一列"Week of Month" 。
解答
SELECT sc.year_of_calendar AS "year"

分组结果
现在我们做前面同样的查询，但是按周分隔信息。我们在SELECT语句中增加
一列"Week of Month" 。
解答
SELECT sc.year_of_calendar AS "year"

,sc.month_of_year AS "month"
,sc.week_of_month AS "Week of//Month"
,ds.itemid
,SUM(ds.sales)
FROM sys_calendar.calendar sc
,daily_sales ds
,today td
WHERE sc.calendar_date = ds.salesdate
AND ((sc.month_of_calendar BETWEEN td.month_of_calendar - 1
AND td.month_of_calendar)
OR (sc.month_of_calendar BETWEEN td.month_of_calendar - 13
AND td.month_of_calendar - 12))
AND ds.itemid = 10
GROUP BY 1,2,3,4
ORDER BY 1,2,3;

结果
year month week_of_month itemid Sum(sales)
1997 8 0 10 350.00
1997 8 1 10 600.00
1997 8 2 10 550.00
1997 8 3 10 150.00
1997 8 4 10 200.00
1997 8 5 10 100.00
1997 9 0 10 750.00
1997 9 2 10 1000.00
1997 9 3 10 350.00
1998 8 0 10 150.00
1998 8 1 10 1050.00
1998 8 2 10 550.00

比较相关周
系统日历使用数字0到6表示每月的周。0，如果有的话，表示月的第一个不完
整的周；1表示月的第一个完整的周。
问题
显示item 10在上月第一个完整周和去年对应周的销售额。

解答
SELECT sc.year_of_calendar AS "year"
,sc.month_of_year AS "month"
,sc.week_of_month AS "Week of//Month"
,ds.itemid
,SUM(ds.sales)
FROM sys_calendar.calendar sc
,daily_sales ds

,today td
WHERE sc.calendar_date = ds.salesdate
AND sc.week_of_month = 1
AND ((sc.month_of_calendar = td.month_of_calendar - 1)
OR (sc.month_of_calendar = td.month_of_calendar - 13))
AND ds.itemid = 10
GROUP BY 1,2,3,4
ORDER BY 1,2;

结果
year month week_of_month itemid Sum(sales)
1997 8 1 10 600.00
1998 8 1 10 1050.00

按星期聚合
按照星期几来汇总数据是很常见的需求。系统日历具有这个能力，使用数字1
至7来表示星期，星期