[1040]DataWorks中MaxCompute的常用操作命令

2023-05-16

文章目录

- 表操作
- - 1、查看表的详细信息：
  - 2、通过 `create table...as select...`语句创建表，并在建表的同时将数据复制到新表中：
  - 3、如果希望源表和目标表具有相同的表结构，可以尝试使用 `create table...like`操作，如下所示：
  - 4、创建Hash Clustering表示例:
  - 5、删除表
  - 6. 重命名表：
  - 7、修改表owner
  - 8、修改表的注释
  - 9、修改表的Hash Clustering属性
  - 10、清空非分区表里的数据
- 生命周期操作
- - 1、修改表的生命周期属性的语法格式，如下所示：
  - 2、禁止生命周期：
- 分区和列操作
- - 1、添加分区操作
  - 2、删除分区操作
  - 3、添加列操作
  - 4、修改列名称
  - 5、修改列、分区注释
  - 6、修改分区值
- 视图操作
- INSERT操作
- - 1、基本的命令格式：
- SELECT语句
- - 1、命令格式：
  - 2、 Distinct去重
  - 3、 select_expr正则表达式
  - 4、 Group BY分组查询，Order by/Sort by/Distribute by
  - 5、 Select语序
  - 6、select子查询
  - 7、交集、并集和补集
  - 8. Join操作
  - 9、 SEMI JOIN和ANTI JOIN
  - - LEFT SEMI JOIN
    - LEFT ANTI JOIN
  - 10、 MAP JOIN HINT
- Explain
- VALUES
- - 1、特定分区内插入数据
  - 2、非特定分区内插入数据
- Values table功能
- 内建函数
- - 1、窗口函数

表操作

1、查看表的详细信息：

odps@ YITIAN_BJ_MC>desc sale_detail;
 
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$yitian.z@foxmail.com | Project: yitian_bj_mc                         |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2019-05-31 10:43:36                                      |
| LastDDLTime:              2019-05-31 10:43:36                                      |
| LastModifiedTime:         2019-05-31 14:59:55                                      |
+------------------------------------------------------------------------------------+
| InternalTable: YES      | Size: 1752                                               |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field           | Type       | Label | Comment                                     |
+------------------------------------------------------------------------------------+
| shop_name       | string     |       |                                             |
| customer_id     | string     |       |                                             |
| total_price     | double     |       |                                             |
+------------------------------------------------------------------------------------+
| Partition Columns:                                                                 |
+------------------------------------------------------------------------------------+
| sale_date       | string     |                                                     |
| region          | string     |                                                     |
+------------------------------------------------------------------------------------+

说明通过Describe Table查看到的Size包含了在回收站的数据Size。如果您需要清空回收站，可以先执行 purge table table_name;，然后再 Describe Table查看除回收站以外的数据大小。您也可以执行show recyclebin;查看本项目中回收站内的数据明细。

使用DESC EXTENDED table_name;命令查看Hash Clustering Table的Clustering属性，如下所示，Clustering属性将显示在Extended Info中：

odps@ YITIAN_BJ_MC>desc extended t2;
 
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$yitian.z@foxmail.com | Project: yitian_bj_mc                                |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2019-06-01 15:06:52                                      |
| LastDDLTime:              2019-06-01 15:06:52                                      |
| LastModifiedTime:         2019-06-01 15:06:52                                      |
+------------------------------------------------------------------------------------+
| InternalTable: YES      | Size: 0                                                  |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field           | Type       | Label | ExtendedLabel | Comment                     |
+------------------------------------------------------------------------------------+
| a               | string     |       |               |                             |
| b               | string     |       |               |                             |
| c               | bigint     |       |               |                             |
+------------------------------------------------------------------------------------+
| Partition Columns:                                                                 |
+------------------------------------------------------------------------------------+
| dt              | string     |                                                     |
+------------------------------------------------------------------------------------+
| Extended Info:                                                                     |
+------------------------------------------------------------------------------------+
| TableID:                  4dac5206ec7443808f11f34f9a9e3a26                         |
| IsArchived:               false                                                    |
| PhysicalSize:             0                                                        |
| FileNum:                  0                                                        |
| StoredAs:                 CFile                                                    |
| CompressionStrategy:      normal                                                   |
| ClusterType:              hash                                                     |
| BucketNum:                1024                                                     |
| ClusterColumns:           [c]                                                      |
| SortColumns:              [c ASC]                                                  |
+------------------------------------------------------------------------------------+

2、通过 `create table...as select...`语句创建表，并在建表的同时将数据复制到新表中：

odps@ YITIAN_BJ_MC>create table sale_detail_ctasl as select * from sale_detail where sale_date='201312' and region='hangzhou';

此时，如果sale_detail中存在数据，上面的示例会将sale_detail的数据全部复制到sale_detail_ctas1表中。

说明此处sale_detail是一张分区表，而通过create table...as select...语句创建的表不会复制分区属性，只会把源表的分区列作为目标表的一般列处理，即sale_detail_ctas1是一个含有5列的非分区表。

odps@ YITIAN_BJ_MC>desc sale_detail_ctasl;
 
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$yitian.z@foxmail.com | Project: yitian_bj_mc                                |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2019-06-01 14:45:12                                      |
| LastDDLTime:              2019-06-01 14:45:12                                      |
| LastModifiedTime:         2019-06-01 14:45:12                                      |
+------------------------------------------------------------------------------------+
| InternalTable: YES      | Size: 1400                                               |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field           | Type       | Label | Comment                                     |
+------------------------------------------------------------------------------------+
| shop_name       | string     |       |                                             |
| customer_id     | string     |       |                                             |
| total_price     | double     |       |                                             |
| sale_date       | string     |       |                                             |
| region          | string     |       |                                             |
+------------------------------------------------------------------------------------+

在 create table...as select...语句中，如果在select子句中使用常量作为列的值，建议指定列的名字，如下所示：

odps@ YITIAN_BJ_MC>create table sale_detail_ctas2 as
                  >select shop_name, customer_id, total_price, '2013' as sale_date, 'china' as region
                  >from sale_detail where sale_date='201312' and region='hangzhou';

odps@ YITIAN_BJ_MC>select * from sale_detail_ctas2;
 
ID = 20190601064947117gdd3eqsa
Log view:...
Job Queueing...
Summary:
 
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | sale_date  | region     |
+------------+-------------+-------------+------------+------------+
| sample     | NULL        | NULL        | 2013       | china      |
| sample     | NULL        | NULL        | 2013       | china      |
| sample     | NULL        | NULL        | 2013       | china      |
| sample     | NULL        | NULL        | 2013       | china      |
| sample     | NULL        | NULL        | 2013       | china      |
| sample     | NULL        | NULL        | 2013       | china      |
| sample     | NULL        | NULL        | 2013       | china      |
| sample     | NULL        | NULL        | 2013       | china      |
| sample     | NULL        | NULL        | 2013       | china      |
| sample     | NULL        | NULL        | 2013       | china      |
| shopx      | x_id        | 100.0       | 2013       | china      |
| shopy      | y_id        | 200.0       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

如果不加列的别名，如下所示：

odps@ YITIAN_BJ_MC>create table sale_detail_ctas3 as
                  >select shop_name, customer_id, total_price, '2013', 'china'
                  >from sale_detail where sale_date='201312' and region='hangzhou';

则创建的表sale_detail_ctas3的第四、五列类似于_c5、_c6:

odps@ YITIAN_BJ_MC>select * from sale_detail_ctas3;
 
ID = 20190601065159966g2ww66pr2
Log view:...
Job Queueing...
Summary:
 
+------------+-------------+-------------+------------+------------+
| shop_name  | customer_id | total_price | _c3        | _c4        |
+------------+-------------+-------------+------------+------------+
| sample     | NULL        | NULL        | 2013       | china      |
| sample     | NULL        | NULL        | 2013       | china      |
| sample     | NULL        | NULL        | 2013       | china      |
| sample     | NULL        | NULL        | 2013       | china      |
| sample     | NULL        | NULL        | 2013       | china      |
| sample     | NULL        | NULL        | 2013       | china      |
| sample     | NULL        | NULL        | 2013       | china      |
| sample     | NULL        | NULL        | 2013       | china      |
| sample     | NULL        | NULL        | 2013       | china      |
| sample     | NULL        | NULL        | 2013       | china      |
| shopx      | x_id        | 100.0       | 2013       | china      |
| shopy      | y_id        | 200.0       | 2013       | china      |
+------------+-------------+-------------+------------+------------+

3、如果希望源表和目标表具有相同的表结构，可以尝试使用 `create table...like`操作，如下所示：

odps@ YITIAN_BJ_MC>create table sale_detail_like like sale_detail;
odps@ YITIAN_BJ_MC>desc sale_detail_like;
 
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$yitian.z@foxmail.com | Project: yitian_bj_mc                                |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2019-06-01 14:52:51                                      |
| LastDDLTime:              2019-06-01 14:52:51                                      |
| LastModifiedTime:         2019-06-01 14:52:51                                      |
+------------------------------------------------------------------------------------+
| InternalTable: YES      | Size: 0                                                  |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field           | Type       | Label | Comment                                     |
+------------------------------------------------------------------------------------+
| shop_name       | string     |       |                                             |
| customer_id     | string     |       |                                             |
| total_price     | double     |       |                                             |
+------------------------------------------------------------------------------------+
| Partition Columns:                                                                 |
+------------------------------------------------------------------------------------+
| sale_date       | string     |                                                     |
| region          | string     |                                                     |
+------------------------------------------------------------------------------------+

此时，sale_detail_like的表结构与sale_detail完全相同。除生命周期属性外，列名、列注释以及表注释等均相同。但sale_detail中的数据不会被复制到sale_detail_like表中。

4、创建Hash Clustering表示例:

odps@ YITIAN_BJ_MC>create table t1(a string, b string, c bigint)
                  >clustered by (c) sorted by (c) into 1024 buckets; -- 创建非分区表
odps@ YITIAN_BJ_MC>desc t1;
 
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$yitian.z@foxmail.com | Project: yitian_bj_mc                                |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2019-06-01 15:05:27                                      |
| LastDDLTime:              2019-06-01 15:05:27                                      |
| LastModifiedTime:         2019-06-01 15:05:27                                      |
+------------------------------------------------------------------------------------+
| InternalTable: YES      | Size: 0                                                  |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field           | Type       | Label | Comment                                     |
+------------------------------------------------------------------------------------+
| a               | string     |       |                                             |
| b               | string     |       |                                             |
| c               | bigint     |       |                                             |
+------------------------------------------------------------------------------------+

创建分区表：

odps@ YITIAN_BJ_MC>create table t2(a string, b string, c bigint)
                  >partitioned by (dt string)
                  >clustered by (c) sorted by (c) into 1024 buckets;
odps@ YITIAN_BJ_MC>desc t2;
 
+------------------------------------------------------------------------------------+
| Owner: ALIYUN$yitian.z@foxmail.com | Project: yitian_bj_mc                                |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2019-06-01 15:06:52                                      |
| LastDDLTime:              2019-06-01 15:06:52                                      |
| LastModifiedTime:         2019-06-01 15:06:52                                      |
+------------------------------------------------------------------------------------+
| InternalTable: YES      | Size: 0                                                  |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field           | Type       | Label | Comment                                     |
+------------------------------------------------------------------------------------+
| a               | string     |       |                                             |
| b               | string     |       |                                             |
| c               | bigint     |       |                                             |
+------------------------------------------------------------------------------------+
| Partition Columns:                                                                 |
+------------------------------------------------------------------------------------+
| dt              | string     |                                                     |
+------------------------------------------------------------------------------------+

创建内部或外部分区表

-- 查看创建表语句
show create table ods_poi_xiao_dian_miniapp_total;

-- 创建外部分区表
CREATE EXTERNAL TABLE IF NOT EXISTS gs_dw_prd_dev.poi_id_map
(
    geohash7 STRING COMMENT 'geohash7',
    poiid_1 STRING COMMENT '店铺的ID',
    source_1 STRING COMMENT '来源1',
	poiid_2 STRING COMMENT '店铺的ID',
	source_2 STRING COMMENT '来源2'
)
PARTITIONED BY (ds STRING COMMENT '日期') STORED AS PARQUET 
LOCATION 'oss://oss-cn-shanghai-internal.aliyuncs.com/dwd_dev/poi_id_map/' 
TBLPROPERTIES ('comment'='竟品POI共存');

-- 创建内部分区表
CREATE TABLE IF NOT EXISTS gs_dw_prd_dev.poi_id_map
(
    geohash7 STRING COMMENT 'geohash7',
    poiid_1 STRING COMMENT '店铺的ID',
    source_1 STRING COMMENT '来源1',
	poiid_2 STRING COMMENT '店铺的ID',
	source_2 STRING COMMENT '来源2'
)
PARTITIONED BY (ds STRING COMMENT '日期') STORED AS ALIORC
TBLPROPERTIES ('comment'='竟品POI共存');

5、删除表

DROP TABLE [IF EXISTS] table_name;

如果不指定if exists选项而表不存在，则返回异常。若指定此选项，无论表是否存在，皆返回成功。
删除外部表时，OSS上的数据不会被删除。

-- 删除表
DROP TABLE IF EXISTS gs_dw_prd_dev.poi_id_map;
-- 截断表
truncate table test;
-- 删除某个分区
ALTER TABLE gs_dw_prd_dev.poi_id_map DROP IF EXISTS PARTITION(ds='20210801');

6. 重命名表：

ALTER TABLE table_name RENAME TO new_table_name;

rename操作仅修改表的名字，不改动表中的数据。
如果已存在与new_table_name同名表，则报错。
如果table_name不存在，则报错。

7、修改表owner

alter table table_name changeowner to 'ALIYUN$xxx@aliyun.com';

8、修改表的注释

ALTER TABLE table_name SET COMMENT 'tbl comment';

table_name必须是已存在的表。
comment最长1024字节。

9、修改表的Hash Clustering属性

ALTER TABLE table_name     
[CLUSTERED BY (col_name [, col_name, ...]) [SORTED BY (col_name [ASC | DESC] [, col_name [ASC | DESC] ...])] INTO number_of_buckets BUCKETS]

去除表的hash clustering属性：

ALTER TABLE table_name NOT CLUSTERED;

alter table改变聚集属性，只对于分区表有效，非分区表一旦聚集属性建立就无法改变。
由于alter table只影响新分区，所以该语句不可以再指定PARTITIONALTER TABLE语句适用于存量表，在增加了新的聚集属性之后，新的分区将做hash cluster存储。

10、清空非分区表里的数据

将指定的非分区表中的数据清空，该命令不支持分区表。对于分区表，可以用ALTER TABLE table_name DROP PARTITION的方式将分区里的数据清除。

清空非分区表里的数据的语法格式，如下所示：

TRUNCATE TABLE table_name;

生命周期操作

1、修改表的生命周期属性的语法格式，如下所示：

ALTER TABLE table_name SET lifecycle days;

说明

days参数为生命周期时间，只接受正整数，单位为天。
如果表table_name是非分区表，自最后一次数据被修改开始计算，经过days天后数据仍未被改动，则此表无需您干预，将会被MaxCompute自动回收（类似drop table操作）。
在MaxCompute中，每当表的数据被修改后，表的LastDataModifiedTime将会被更新，因此，MaxCompute会根据每张表的LastDataModifiedTime以及lifecycle的设置来判断是否要回收此表。
如果table_name是分区表，则根据各分区的LastDataModifiedTime判断该分区是否该被回收。
不同于非分区表，分区表的最后一个分区被回收后，该表不会被删除。
生命周期只能设定到表级别，不能再分区级设置生命周期。
创建表时即可指定生命周期。
非分区表不支持取消lifecycle，只能修改lifecycle。分区表可以取消某个具体分区的lifecycle

create table test_lifecycle(key string) lifecycle 100;
 -- 新建test_lifecycle表，生命周期为100天。
 alter table test_lifecycle set lifecycle 50;
 -- 修改test_lifecycle表，将生命周期设为50天。

2、禁止生命周期：

ALTER TABLE table_name partition_spec ENABLE|DISABLE LIFECYCLE;

使用示例：

ALTER TABLE trans PARTITION(dt='20141111') DISABLE LIFECYCLE;

分区和列操作

1、添加分区操作

语法格式：

ALTER TABLE TABLE_NAME ADD [IF NOT EXISTS] PARTITION partition_spec
partition_spec:(partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, ...);

说明

分区名必须小写。
仅支持新增分区，不支持新增分区字段。
如果未指定if not exists而同名的分区已存在，则返回报错。
目前MaxCompute单表支持的分区数量上限为6万。
对于多级分区的表，如果想添加新的分区，必须指明全部的分区值。

使用示例如下：

alter table sale_detail add if not exists partition (sale_date='201312', region='hangzhou');
-- 成功添加分区，用来存储2013年12月杭州地区的销售记录。
alter table sale_detail add if not exists partition (sale_date='201312', region='shanghai');
-- 成功添加分区，用来存储2013年12月上海地区的销售记录。
alter table sale_detail add if not exists partition(sale_date='20111011');
-- 仅指定一个分区sale_date，出错返回
alter table sale_detail add if not exists partition(region='shanghai');
-- 仅指定一个分区region，出错返回

2、删除分区操作

ALTER TABLE TABLE_NAME DROP [IF EXISTS] PARTITION partition_spec;
partition_spec:(partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, ...)

示例如下，假设从表sale_detail中删除一个分区，如下所示：

alter table sale_detail drop if exists partition(sale_date='201312',region='hangzhou'); 
-- 成功删除2013年12月杭州分区的销售记录。

3、添加列操作

添加列的语法格式，如下所示：

ALTER TABLE table_name ADD COLUMNS (col_name1 type1,col_name2 type2...);

同时添加列和注释，如下所示：

ALTER TABLE table_name ADD COLUMNS (col_name1 type1 comment 'XXX',col_name2 type2 comment 'XXX');

说明添加的新列不支持指定顺序，默认在最后一列。

4、修改列名称

ALTER TABLE table_name CHANGE COLUMN old_col_name RENAME TO new_col_name;

5、修改列、分区注释

修改列、分区注释的语法格式，如下所示：

ALTER TABLE table_name CHANGE COLUMN col_name COMMENT comment_string;

6、修改分区值

MaxCompute SQL支持通过rename操作更改对应表的分区值。

修改分区值的语法格式，如下所示：

ALTER TABLE table_name PARTITION (partition_col1 = partition_col_value1, partition_col2 = partiton_col_value2, ...) 
RENAME TO PARTITION (partition_col1 = partition_col_newvalue1, partition_col2 = partiton_col_newvalue2, ...);

说明

不支持修改分区列列名，只能修改分区列对应的值。
修改多级分区的一个或者多个分区值，多级分区的每一级的分区值都必须写上。

样例

ALTER TABLE gs_dw_prd_dev.dwd_poi_coexisting_wf PARTITION (ds='20210801') 
RENAME TO PARTITION (ds='20210730');

视图操作

创建视图：https://help.aliyun.com/document_detail/73770.html?spm=a2c4g.11186623.6.653.2f775a230EiwVQ
删除视图
重命名视图

INSERT操作

1、基本的命令格式：

INSERT OVERWRITE|INTO TABLE tablename 
[PARTITION (partcol1=val1, partcol2=val2 ...)] 
[(col1,col2 ...)]
select_statement FROM from_statement;

说明

MaxCompute的insert语法与通常使用的MySQL或Oracle的insert语法有差别，在insert overwrite/into后需要加入table关键字，而非直接使用tablename。
当insert的目标表是分区表时，指定分区值[PARTITION (partcol1=val1, partcol2=val2 …)]语法中不允许使用函数等表达式。
目前insert overwrite还不支持指定插入列的功能，暂时只能用insert into。
不支持insert into到hash clustering表。
当遇到并发写入时，MaxCompute会根据ACID进行并发写的保障。关于ACID的具体语义，请参见.MaxCompute的ACID。

在MaxCompute SQL处理数据的过程中，insert overwrite/into用于将计算的结果保存目标表中。insert into与insert overwrite的区别是：insert into会向表或表的分区中追加数据，而insert overwrite会在向表或分区中插入数据前清空表中的原有数据。

比如计算sale_detail表中不同地区的销售额，操作如下：

> create table sale_detail_insert like sale_detail;
> alter table sale_detail_insert add partition(sale_date='2013', region='china');
> insert overwrite table sale_detail_insert partition (sale_date='2013', region='china') select shop_name, customer_id,total_price from sale_detail;

向某个分区插入数据时，分区列不允许出现在select列表中:

insert overwrite table sale_detail_insert partition (sale_date='2013', region='china')
select shop_name, customer_id, total_price, sale_date, region  from sale_detail;
-- 报错返回，sale_date，region为分区列，不允许出现在静态分区的insert语句中。

同时，partition的值只能是常量，不可以出现表达式。以下用法是非法的：

insert overwrite table sale_detail_insert partition (sale_date=datepart('2016-09-18 01:10:00', 'yyyy') , region='china')
select shop_name, customer_id, total_price from sale_detail;

更新表数据到动态分区

动态分区使用注意事项：

在您insert into partition时，如果分区不存在，会自动创建分区。
如果多个insert into partition作业并发，同时发现分区不存在，都会主动创建分区，但是同时只有一个会创建成功，其它的都会失败。
insert into partition作业如果不能控制并发，只能通过预创建分区来避免问题。

2、多路输出（MULTI INSERT）

https://help.aliyun.com/document_detail/73776.html?spm=a2c4g.11186623.6.656.3ea32b5aP58bKB

3、输出到动态分区

https://help.aliyun.com/document_detail/73779.html?spm=a2c4g.11186623.6.657.7689289dhGNpVJ

SELECT语句

1、命令格式：

SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY order_condition]
[DISTRIBUTE BY distribute_condition [SORT BY sort_condition] ]
[LIMIT number]

Select分区表时禁止全表扫描。

2018-01-10 20点后创建的新项目，默认情况下执行SQL时，针对该project里的分区表不允许全表扫描，必须有分区条件指定需要扫描的分区，由此减少SQL的不必要I/O，从而减少计算资源的浪费，同时也减少了不必要的后付费模式的计算费用（后付费模式中，数据输入量是计量计费参数之一）。

若实在需要对分区表进行全表扫描，可以在对分区表全表扫描的SQL语句前加一个set语句set odps.sql.allow.fullscan=true;，并和SQL语句一起提交执行。假设sale_detail表为分区表，则要全表扫描需同时提交如下简单查询命令：

set odps.sql.allow.fullscan=true;
select * from sale_detail;

如果需要整个项目都允许全表扫描，可以通过开关自行打开或关闭（true/false），命令：

setproject odps.sql.allow.fullscan=true;

2、 Distinct去重

Distinct：如果有重复数据行时，在字段前使用distinct，会将重复字段去重，只返回一个值，而使用all将返回字段中所有重复的值，不指定此选项时默认效果和all相同。

使用distinct只返回一行记录，如下所示：

select distinct region from sale_detail;
select distinct region, sale_date from sale_detail;
-- distinct多列，distinct的作用域是 Select 的列集合，不是单个列。

3、 select_expr正则表达式

MaxCompute sql支持使用select_expr正则表达式选列。

使用select_expr正则表达式时，需要使用 ` （反单引号）将正则表达式括起来，举例如下。

SELECT `abc.*` FROM t;选出 t 表中所有列名以abc开头的列。
SELECT `(ds)?+.+` FROM t;选出t表中列名不为ds的所有列。
SELECT `(ds|pt)?+.+` FROM t;选出t表中排除ds和pt两列的其他列。
SELECT `(d.*)?+.+` FROM t;选出t表中排除列名以d开头的其他列。

4、 Group BY分组查询，Order by/Sort by/Distribute by

https://help.aliyun.com/document_detail/73777.html?spm=a2c4g.11186623.6.659.5c1612b0Wc7EXD

5、 Select语序

按照Select语法格式书写的Select语句，实际上的逻辑执行顺序与标准的书写语序实际并不相同。

以下用示例进行说明：

SELECT  key
        ,MAX(value)
FROM    src t
WHERE   value > 0
GROUP BY key
HAVING  SUM(value) > 100
ORDER BY key
LIMIT   100
;

实际上的逻辑执行顺序是FROM->WHERE->GROUY BY->HAVING->SELECT->ORDER BY->LIMIT：

order by中只能引用Select列表中生成的列，而不是访问From的源表中的列。
Having可以访问的是group by key和聚合函数。
Select的时候，如果有group by，便只能访问group key和聚合函数，而不是From中源表中的列。

为了避免混淆，MaxCompute支持以执行顺序书写查询语句，例如上面的语句可以写为：

FROM    src t
WHERE   value > 0
GROUP BY key
HAVING  SUM(value) > 100
SELECT  key
        ,MAX(value)
ORDER BY key
LIMIT   100
;

6、select子查询

https://help.aliyun.com/document_detail/73781.html?spm=a2c4g.11186623.6.661.8a70184alRQBEp

7、交集、并集和补集

语法格式如下：

select_statement UNION ALL select_statement;
select_statement UNION [DISTINCT] select_statement;
select_statement INTERSECT ALL select_statement;
select_statement INTERSECT [DISTINCT] select_statement;
select_statement EXCEPT ALL select_statement;
select_statement EXCEPT [DISTINCT] select_statement;
select_statement MINUS ALL select_statement;
select_statement MINUS [DISTINCT] select_statement;

UNION：求两个数据集的并集。即将两个数据集合并成一个数据集。
INTERSECT：求两个数据集的交集。即输出两个数据集均包含的记录。
EXCEPT：求第二个数据集在第一个数据集中的补集。即输出第一个数据集包含而第二个数据集不包含的记录。
MINUS：等同于EXCEPT。

8. Join操作

https://help.aliyun.com/document_detail/73783.html?spm=a2c4g.11186623.6.663.61377eb54svzlN

9、 SEMI JOIN和ANTI JOIN

LEFT SEMI JOIN

当Join条件成立时，返回左表中的数据。也就是mytable1中某行的Id在mytable2的所有Id中出现过，此行就保留在结果集中。

示例如下：

SELECT * from mytable1 a LEFT SEMI JOIN mytable2 b on a.id=b.id;

只会返回mytable1中的数据，只要mytable1的Id在mytable2的Id中出现。

LEFT ANTI JOIN

当Join条件不成立时，返回左表中的数据。也就是mytable1中某行的Id在mytable2的所有Id中没有出现过，此行便保留在结果集中。

示例如下：

SELECT * from mytable1 a LEFT ANTI JOIN mytable2 b on a.id=b.id;

只会返回mytable1中的数据，只要mytable1的Id在mytable2的Id没有出现。

10、 MAP JOIN HINT

当一个大表和一个或多个小表JOIN时，您可以使用MAPJOIN提升性能。

MAPJOIN的基本原理：在小数据量情况下，SQL会将您指定的小表全部加载到执行JOIN操作的程序的内存中，从而加快JOIN的执行速度。

https://help.aliyun.com/document_detail/73785.html?spm=a2c4g.11186623.6.665.1524526f6nhiHD

###11、Lateral View

Lateral View和split，explode等UDTF一起使用，它能够将一行数据拆成多行数据，并在此基础上对拆分后的数据进行聚合。

https://help.aliyun.com/document_detail/87722.html?spm=a2c4g.11186623.6.666.274741d9xO01i5

###12、 HAVING子句

由于MaxCompute SQL的Where关键字无法与合计函数一起使用，可以采用HAVING子句。

命令格式如下。

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

示例如下。

比如有一张订单表Orders，包括客户名称（Customer），订单金额（OrderPrice），订单日期（Order_date），订单号（Order_id）四个字段。现在希望查找订单总额少于2000的客户。SQL语句如下所示：

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000

Explain

MaxCompute SQL提供Explain操作，用来显示对应于DML语句的最终执行计划结构的描述。所谓执行计划就是最终用来执行SQL语义的程序。

命令格式如下：

EXPLAIN <DML query>;

Explain的执行结果包含如下内容：

对应于该DML语句的所有Task的依赖结构。
Task中所有Task的依赖结构。
Task中所有Operator的依赖结构。

VALUES

本文向您介绍INSERT … VALUES命令操作。

通常在业务测试阶段，需要给一个小数据表准备些基本数据，您可以通过 INSERT … VALUES的方法在测试表中快速写入一些测试数据。

说明目前INSERT OVERWRITE不支持这种指定插入列的功能，只能使用INSERT INTO实现。

命令格式如下：

INSERT INTO TABLE tablename 
[PARTITION (partcol1=val1, partcol2=val2,...)][(co1name1,colname2,...)] 
[VALUES (col1_value,col2_value,...),(col1_value,col2_value,...),...]

1、特定分区内插入数据

示例

drop table if exists srcp;
create table if not exists srcp (key string,value bigint) partitioned by (p string);
insert into table srcp partition (p='abc') values ('a',1),('b',2),('c',3);

INSERT … VALUES语句执行成功后，查询表srcp分区 p=abc，结果如下。

+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| a          | 1          | abc        |
| b          | 2          | abc        |
| c          | 3          | abc        |
+------------+------------+------------+

2、非特定分区内插入数据

当表有很多列，而准备数据的时候希望只插入部分列的数据，此时可以使用插入列表功能。

示例

drop table if exists srcp;
create table if not exists srcp (key string,value bigint) partitioned by (p string);
insert into table srcp partition (p)(key,p) values ('d','2019'),('e','2019'),('f','2019');

查询结果如下：

odps@ YITIAN_BJ_MC>select * from srcp where p='2019';
 
ID = 20190602023446900g7i62ssa
Log view:...
Job Queueing...
Summary:
 
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| d          | NULL       | 2019       |
| e          | NULL       | 2019       |
| f          | NULL       | 2019       |
+------------+------------+------------+

Values table功能

Values table并不限于在insert语句中使用，任何DML语句都可以使用。

INSERT … VALUES有一个限制：values必须是常量。但是当需要在插入的数据中进行一些简单的运算时，可使用MaxCompute的values table功能。示例:

drop table if exists srcp;
create table if not exists srcp (key string,value bigint) partitioned by (p string);
insert into table srcp partition (p) select concat(a,b), length(a)+length(b),'2019' from values ('d',4),('e',5),('f',6) t(a,b);

查询数据如下：

odps@ YITIAN_BJ_MC>select * from srcp where p='2019';
 
ID = 20190602024518126gjll2tsa
Job Queueing...
Summary:
 
+------------+------------+------------+
| key        | value      | p          |
+------------+------------+------------+
| d4         | 2          | 2019       |
| e5         | 2          | 2019       |
| f6         | 2          | 2019       |
+------------+------------+------------+

说明其中的 values (…), (…) t(a, b)相当于定义了一个名为t，列为a、b的表，类型分别为STRING、BIGINT，其中的类型从values列表中推导。这样在不准备任何物理表时，可以模拟一个有任意数据的、多行的表，并进行任意运算。（这种方式不会创建物理表，可以看做是一种临时表）

VALUES TABLE这个用法还可以取代 select * from dual与 union all组合的方式，来拼出常量表，如下所示。

select 1 c from dual 
union all
select 2 c from dual;
--等同于 
select * from values (1), (2) as t(c);
--返回结果：
+------------+
| c          |
+------------+
| 1          |
| 2          |
+------------+

还有一种values表的特殊形式，如下所示。

select abs(-1), length('abc'), getdate();
--返回结果
+------------+------------+------------+
| _c0        | _c1        | _c2        |
+------------+------------+------------+
| 1          | 3          | 2019-06-02 10:51:03 |
+------------+------------+------------+

如上述语句所示，可以不写from语句，直接执行select（只要select表达式列表中不出现上游表的数据）。其底层实现为从一个1行，0列的匿名values表选取。这样，在您试图测试UDF或其他函数时，可免去手工创建DUAL表的过程。

说明：通过values写入DATETIME、TIMESTAMP类型，需要在values中指定类型名称，如下所示。

insert into table srcp (p='abc') values (datetime'2017-11-11 00:00:00',timestamp'2017-11-11 00:00:00.123456789');

内建函数

1、窗口函数

MaxCompute SQL中可以使用窗口函数进行灵活的分析处理工作，窗口函数只能出现在select子句中。窗口函数中请不要嵌套使用窗口函数和聚合函数，窗口函数不可以和同级别的聚合函数一起使用。目前在一个MaxCompute SQL语句中，最多可以使用5个窗口函数。

窗口函数的语法声明：

window_func() over (partition by [col1,col2…]
[order by [col1[asc|desc], col2[asc|desc]…]] windowing_clause)

partition by部分用来指定开窗的列。分区列的值相同的行被视为在同一个窗口内。现阶段，同一窗口内最多包含1亿行数据（建议不超过500万行），否则运行时报错。
order by用来指定数据在一个窗口内如何排序。
windowing_clause部分可以用rows指定开窗方式，有以下两种方式：
- rows between x preceding|following and y preceding|following表示窗口范围是从前或后x行到前或后y行。
- rows x preceding|following窗口范围是从前或后第x行到当前行。

其他窗口函数：https://help.aliyun.com/document_detail/34994.html?spm=a2c4g.11186623.6.678.37c83804R51871

COUNT/AVG/MAX/MIN/MEDIAN/STDDEV/STDDEV_SAMP/SUM/DENSE_RANK/RANK/LAG/LEAD/PERCENT_RANK/ROW_NUMBER/CLUSTER_SAMPLE/CUME_DIST/NTILE/

来源：https://blog.csdn.net/yitian_z/article/details/90729172
https://blog.csdn.net/weixin_51754359/article/details/109716917

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)