阿里云 MaxCompute 的使用笔记（一）：笛卡尔积、常量表、时间和日期

出于工作的原因，不得不使用以及阿里云的 Dataworks 与 Maxcompute。本系列记录在使用 MaxCompute 过程中的笔记。

启用笛卡尔积 🔗

默认情况下，MaxCompute 会禁用笛卡尔积，导致无法使用 CROSS JOIN 等查询。如有必要，可以打开：

SET odps.sql.allow.cartesian = true

启用全表扫描 🔗

set odps.sql.allow.fullscan = true

创建常量表 🔗

在查询中创建常量表（constant table）的最直观的方式是使用 UNION 语句，比如：

SELECT 'north' as code, '华北' as region
UNION
SELECT 'south', '华南'
UNION
SELECT 'northwest', '西北'

但 MaxCompute 支持 VALUES 列表，可以简化为：

SELECT  *
FROM    (VALUES
            ('north', '华北'),
            ('south', '华南'),
            ('northwest', '西北')) AS t(code, region)

这里的 VALUES 语法与 PostgreSQL 的语法类似。

时间相关的类型 🔗

目前阿里云提供了三种不同类型的数据类型，见其官方文档《数据类型版本说明》，不同版本的行为有一定的差异。在 MaxCompute 中可使用 TIMESTAMP、TIMESTAMP_NTZ 和 DATETIME 类型，三者有一些关键差异。在 《2.0数据类型版本 》 中，它们的说明如下：

在实际使用中，可能会遇到在查询到数据中TIMESTAMP类型的数据以 UTC 时区显示；但是在基于 TIMESTAMP 类型的字段进行计算时，比如获取其日期，那么其实是将其转换为东八区的时间后，然后提取日期的。作为一个老生常谈的经验，在处理与时间相关的字段时，需要特别注意：时间字段的物理存储、时间字段在不同位置显示、时间在处理时的时区，可能会有让人惊讶。大胆假设、小心验证才能避免大的错误和工作返工。

日期相关函数 🔗

在数据处理中，日期可能是“日期类型”，也可能是“字符串类型”。当作为字符串时，又会有不同的格式。阿里云的文档里对日期函数给出了专门的说明。

下面是我在项目中的常用法。

TO_DATE 和 DATE 🔗

TO_DATE 的功能是：将指定格式的字符串转换为日期值。

举例：

SELECT to_date('20240101', 'yyyyMMdd')

返回：

需要特别注意的是，虽然这个函数的名字是TO_DATE，但它返回的是DATETIME类型。假如在 MaxCompute 中 report_date 的类型是 DATE，但是当把 TO_DATE('20240428','yyyyMMdd') 写入 report_date 字段时候，就会报错：

FAILED: ODPS-0130071:[x,x] Semantic analysis exception - column report_date in source has incompatible type DATETIME with destination column report_date, which has type DATE

因此将日期字符串转换成日期的正确姿势，是使用DATE 将 TO_DATE 的结果转换成日期类型，比如：

SELECT DATE(TO_DATE('20240428', 'yyyyMMdd'))

返回结果为

DATE_ADD 与 LAST_DAY 🔗

DATE_ADD 的功能是：按照指定的幅度增减天数。

获取某一天（字符串）的前一天 🔗

SELECT date_add(to_date('20240101', 'yyyyMMdd'))

返回：

注：获取前一天也可使用LAST_DAY。

获取上一个月的最后一天 🔗

select LAST_DAY(ADD_MONTHS('2024-04-20', -1))

或者

SELECT LAST_DAY(ADD_MONTHS(TO_DATE('20240420', 'yyyyMMdd'), -1))

返回：

获取上一个月的第一天 🔗

SELECT DATE_ADD(LAST_DAY(ADD_MONTHS('2024-04-24', -2)), 1)

返回：

TO_CHAR 🔗

TO_CHAR 的功能是：将日期按照指定格式转换为字符串。

SELECT to_char(date_add(to_date('20240101', 'yyyyMMdd'), -1), 'yyyyMMdd')

返回：