基于时间计算函数的自动分区表-阿里云帮助中心

MaxCompute支持自动分区（AUTO PARTITION）表，自动分区表的分区列支持基于时间计算函数或基于数据写入时间两种方式自动生成。本文介绍基于时间计算函数生成的自动分区表的使用方式。

功能介绍

MaxCompute支持普通分区表和自动分区表(AUTO PARTITION)两种分区表类型，您可以根据不同场景下预期的分区列生成方式，选择不同的分区表类型。详细介绍请参考分区表概述。

相比于其他大数据产品支持基于日期/时间类型的列进行分区，或对其进行截取处理后进行分区，MaxCompute具备更灵活的分区能力。MaxCompute支持对表中的时间/日期类型（DATE、DATETIME、TIMESTAMP、TIMESTAMP_NTZ）的数据列使用特定的时间计算函数，自动根据数据列计算后的结果生成分区列的值，从而实现表分区。

在此基础上，MaxCompute也支持自动获取数据写入的时间，结合用户指定的时间计算函数，根据计算后的结果生成分区列的值，对表进行分区，详细介绍请参考基于数据写入时间的自动分区表。

使用限制

基于时间计算函数的自动分区表当前仅支持一个分区列，暂不支持多级分区。
基于时间计算函数的自动分区表的分区列，会按照表创建时指定的分区生成表达式自动生成，分区字段类型默认为STRING，不支持修改。

使用说明

创建基于时间计算函数的自动分区表

语法结构

创建基于时间计算函数的自动分区表。

CREATE [OR REPLACE] TABLE [IF NOT EXISTS] <table_name> (<col_name> <data_type>, ... )
[COMMENT <table_comment>]
AUTO PARTITIONED BY (<auto_partition_expression> [AS <auto_partition_column_name>]);

参数说明

参数	是否必填	描述
table_name	是	待创建的表名。
col_name	是	列名。
data_type	是	列数据类型。
table_comment	否	表注释。
auto_partition_expression	是	定义如何进行分区列计算的表达式。当前仅支持使用TRUNC_TIME函数来进行分区列的生成计算。说明当创建基于数据写入时间的自动分区表时，系统会自动获取数据写入时间，并通过TRUNC_TIME函数对写入时间进行截取，从而生成分区列的值。
auto_partition_column_name	否	生成分区列的列名，若未指定分区列的列名，则系统会以`_pt_col_0_`为默认列名，依次检查表中是否存在该列名，若存在则自动递增后缀（如`_pt_col_1_`、`_pt_col_2_`等），直到找到未被占用的列名为止。说明使用分区表达式进行分区计算后，会根据计算结果生成一个STRING类型的分区列，列的类型和列的取值均不支持直接操作。

使用示例

示例1：以天为粒度截取时间/日期类型的列数据计算并生成自动分区。

创建一张自动分区表 sale_detail，表中包含一个TIMESTAMP类型的时间数据列sale_time ，DDL语句中按天为粒度对该列数据进行截取计算，并基于计算后的结果生成AUTO PARTITION表的分区。

CREATE TABLE IF NOT EXISTS sale_detail(
 shop_name     STRING,
 total_price   DOUBLE,
 sale_time    TIMESTAMP )
AUTO PARTITIONED BY (TRUNC_TIME(sale_time, 'day'));

上述语句创建的 sale_detail自动分区表包含4列，分别是shop_name、total_price、sale_time和_pt_col_0_。其中_pt_col_0_是一个自动生成的STRING类型分区列。您可执行DESC sale_detail;语句查看表信息，返回结果示例如下：

+------------------------------------------------------------------------------------+
| Owner:                    ALIYUN$***                             |
| Project:                  ***                                    |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2025-06-26 11:21:55                                      |
| LastDDLTime:              2025-06-26 11:21:55                                      |
| LastModifiedTime:         2025-06-26 11:21:55                                      |
+------------------------------------------------------------------------------------+
| InternalTable: YES      | Size: 0                                                  |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field           | Type       | Label | Comment                                     |
+------------------------------------------------------------------------------------+
| shop_name       | string     |       |                                             |
| total_price     | double     |       |                                             |
| sale_time       | timestamp  |       |                                             |
+------------------------------------------------------------------------------------+
| Partition Columns:                                                                 |
+------------------------------------------------------------------------------------+
| _pt_col_0_      | string     |                                                     |
+------------------------------------------------------------------------------------+

说明

如需查看AUTO PARTITION分区列的分区生成计算逻辑，可以通过SHOW CREATE TABLE查看。

示例2：显式指定生成的分区列的名称。

创建一张自动分区表 sale_detail2，在示例1基础上增加了显式指定的分区列名称sale_date。

CREATE TABLE IF NOT EXISTS sale_detail2(
 shop_name     STRING,
 total_price   DOUBLE,
 sale_time    TIMESTAMP )
AUTO PARTITIONED BY (TRUNC_TIME(sale_time, 'day') AS sale_date);

示例3：若TRUNC_TIME函数的输入数据列为DATE数据类型，且按天截取计算生成分区列时，支持如下简化写法。

CREATE TABLE IF NOT EXISTS sale_detail3(
 shop_name     STRING,
 total_price   DOUBLE,
 sale_date    DATE )
AUTO PARTITIONED BY (TRUNC_TIME(sale_date , 'day'));

-- 上述sql语句可以简化写成
CREATE TABLE IF NOT EXISTS sale_detail3(
 shop_name     STRING,
 total_price   DOUBLE,
 sale_date    DATE )
AUTO PARTITIONED BY (sale_date);

重要

这种简化写法只支持DATE数据类型，不支持DATETIME/TIMESTAMP/TIMESTAMP_NTZ数据类型。

写入基于时间计算函数的自动分区表

当用户使用SQL语句向AUTO PARTITION表中写入数据时，系统会根据用户创建表时定义的分区自动生成计算表达式，再根据表中数据决定其对应的分区取值，不支持用户显式地指定分区取值。

示例：将数据写入基于时间计算函数创建的分区表sale_detail2。

-- 设置项目本地时区为东八区时区。
set odps.sql.timezone=Asia/Shanghai;

-- 同上文分区表sale_detail2的创建方式。
CREATE TABLE IF NOT EXISTS sale_detail2(
 shop_name     STRING,
 total_price   DOUBLE,
 sale_time    TIMESTAMP )
AUTO PARTITIONED BY (TRUNC_TIME(sale_time, 'day') AS sale_date);


INSERT INTO sale_detail2 VALUES
  ('chongqin_shop',101101,timestamp '2025-02-04 01:15:30'),
  ('shenzhen_shop',202202,timestamp '2024-03-29 15:30:30'),
  ('hangzhou_shop',303303,timestamp '2025-02-04 08:30:30'),
  ('shanghai_shop',404404,timestamp '2025-02-28 01:50:30');

在该示例中，sale_time时间数据列的值，经过TRUNC_TIME计算后生成的分区值对应关系如下：

sale_time列-Asia/Shanghai时区	分区列sale_date值（对时间列截取到天后生成，代表UTC时间）
2025-02-04 01:15:30	2025-02-03
2024-03-29 15:30:30	2024-03-29
2025-02-04 08:30:30	2025-02-04
2025-02-28 01:50:30	2025-02-27

说明

注意，对于时区相关的数据类型如DATETIME/TIMESTAMP，TRUNC_TIME会将本地时间转换为UTC时间再进行截断。
本示例中通过set odps.sql.timezone=Asia/Shanghai;设置项目本地时区为东八区时区，在TRUNC_TIME截取生成的分区值时，会受到时区影响。

查询基于时间计算函数的自动分区表

示例1：当执行`select *` 查询的时候，默认不显示生成分区列。

-- 开启全表扫描 
set odps.sql.allow.fullscan=true;

SELECT * FROM sale_detail2;

-- 返回结果
+------------+-------------+------------+
| shop_name  | total_price | sale_time  | 
+------------+-------------+------------+
| hangzhou_shop | 303303.0    | 2025-02-04 08:30:30 | 
| shanghai_shop | 404404.0    | 2025-02-28 01:50:30 | 
| shenzhen_shop | 202202.0    | 2024-03-29 15:30:30 | 
| chongqin_shop | 101101.0    | 2025-02-04 01:15:30 | 
+------------+-------------+------------+

示例2：若您希望显示生成的分区列，可执行`SELECT *,sale_date from sale_detail2;`，其中`sale_date`是在该表创建时指定的生成分区列的列名。

SELECT *,sale_date from sale_detail2;

-- 返回结果
+------------+-------------+------------+------------+
| shop_name  | total_price | sale_time  | sale_date  | 
+------------+-------------+------------+------------+
| shanghai_shop | 404404.0    | 2025-02-28 01:50:30 | 2025-02-27 | 
| hangzhou_shop | 303303.0    | 2025-02-04 08:30:30 | 2025-02-04 | 
| shenzhen_shop | 202202.0    | 2024-03-29 15:30:30 | 2024-03-29 | 
| chongqin_shop | 101101.0    | 2025-02-04 01:15:30 | 2025-02-03 | 
+------------+-------------+------------+------------+

基于时间计算函数的自动分区表分区裁剪

对自动分区表进行查询过滤时，仅在以下几种场景中支持分区裁剪。

说明

您可以通过Logview或使用EXPLAIN命令查看SQL执行计划，判断分区裁剪是否生效，参考文档：分区剪裁合理性评估。

场景1：使用生成的分区列作为过滤条件，查询语句执行时支持分区裁剪。

对于表sale_detail2，查询时可以通过生成的分区列sale_date过滤数据。使用WHERE sale_date > '2025-02-03';过滤条件，此时仅扫描满足过滤条件sale_date > '2025-02-03'的分区。

SELECT * FROM sale_detail2 WHERE sale_date > '2025-02-03';

-- 返回结果
+------------+-------------+------------+
| shop_name  | total_price | sale_time  | 
+------------+-------------+------------+
| shanghai_shop | 404404.0    | 2025-02-28 01:50:30 | 
| hangzhou_shop | 303303.0    | 2025-02-04 08:30:30 | 
+------------+-------------+------------+

场景2：使用生成分区列的时间/日期类型数据列（不对该列进行函数运算）作为过滤条件，查询语句执行时支持分区裁剪。

对于表sale_detail2，查询时可以通过表中的时间数据列sale_time过滤数据。使用WHERE sale_time > '2025-02-04 08:00:00';过滤条件，此时系统会判断哪些分区数据满足扫描 sale_time 的过滤条件，并仅扫描对应满足条件的分区。

SELECT * FROM sale_detail2 WHERE sale_time > '2025-02-04 08:00:00';

-- 返回结果
+------------+-------------+------------+
| shop_name  | total_price | sale_time  | 
+------------+-------------+------------+
| shanghai_shop | 404404.0    | 2025-02-28 01:50:30 | 
| hangzhou_shop | 303303.0    | 2025-02-04 08:30:30 | 
+------------+-------------+------------+

场景3：对建表时生成分区列的时间/日期类型数据列进行函数计算，并将计算结果作为过滤条件，目前在部分函数计算场景支持分区裁剪。

支持分区裁剪的函数	分区裁剪生效条件
TRUNC_TIME	时间截取粒度与建表语句相同时，支持分区裁剪。
DATETRUNC	时间截取粒度与建表语句相同时，支持分区裁剪。
CAST AS DATE	建表时分区生成的截取粒度为天时，支持分区裁剪。
TO_DATE	建表时分区生成的截取粒度为天时，支持分区裁剪。

示例1：对sale_time时间列使用TRUNC_TIME或不带时区参数的DATETRUNC函数进行过滤，且时间截取粒度与建表语句相同，此时分区裁剪生效。

SELECT * FROM sale_detail2 WHERE TRUNC_TIME(sale_time, 'day') > '2025-02-04';

SELECT * FROM sale_detail2 WHERE DATETRUNC(sale_time, 'dd') > '2025-02-04 00:00:00';

-- 返回结果
+------------+-------------+------------+
| shop_name  | total_price | sale_time  | 
+------------+-------------+------------+
| shanghai_shop | 404404.0    | 2025-02-28 01:50:30 | 
+------------+-------------+------------+

示例2：对sale_time时间列使用CAST AS DATE进行过滤或不带时区参数的TO_DATE函数进行过滤，且建表时的截取粒度为天，此时分区裁剪生效。

SELECT * FROM sale_detail2 WHERE CAST(sale_time AS date) = '2025-02-04';

SELECT * FROM sale_detail2 WHERE TO_DATE(sale_time) = '2025-02-04';
-- 返回结果
+------------+-------------+------------+
| shop_name  | total_price | sale_time  | 
+------------+-------------+------------+
| hangzhou_shop | 303303.0    | 2025-02-04 08:30:30 | 
+------------+-------------+------------+