【MaxCompute】学习笔记常用查询sql

SELECT 查询

命令格式

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

注意：

1. [GROUP BY col_list]：必须写列的完整表达式，不可用列的 column-alias。
2. [LIMIT number]的 number 是常数。

SELECT 操作从表中读取数据，要读的列可以用列名指定，或者用*代表所有的列，常用的 SELECT 如下

SELECT * FROM shop;

只读取 shop 的一列 shop_name

SELECT shop_name FROM shop;

在 WHERE 中可以指定过滤的条件

SELECT * FROM shop
WHERE shop_name LIKE ' hangzhou%';

在 select语句的 where 条件中可以指定 partition 范围，这样可以仅仅扫描表的指定部分，避免全表扫描。如下所示，假设 page_views 使用 date 作为 partition column。

SELECT page_views.* 
FROM page_views 
WHERE page_views.date >= ' 2008-03-01' AND page_views.date <= ' 2008-03-31';

在 table_reference 中支持使用嵌套子查询。

ALL：如果有重复数据行时，返回所有的行，不指定此选项时默认效果和 ALL 相同

DISTINCT ：如果有重复数据行时，只返回一行记录，如：

SELECT DISTINCT shop_name FROM shop;

AND &OR 可在 where 语句中把两个或多个条件结合起来。AND 是第一个和第二个条件都成立。OR 是第一个和第二个条件只要有一个成立，如：

SELECT *FROM shop
WHERE shop_name LIKE 'hangzhou%' OR shop_name LIKE 'suzhou%';

GROUP BY：在 SELECT 中包含聚类函数时，用 GROUP BY 指定分类的列，如：

SELECT shop_location FROM shop
Group by shop_location;

注意：

1. 在使用 order by 排序时，NULL 会被认为比任何值都小，这个行为与 mysql一致，但是与 oracle 不一致。
2. LIMIT 限制只输出结果中的几行，当使用 SELECT 语句直接从屏幕输出查看结果时，最多只输出 1000 行。

UNION ALL 查询

将两个或多个 SELECT操作返回的数据集联合成一个数据集，如果结果有重复行时，会返回所有符合条件的行，不进行重复行的去重处理。需要注意的是：MaxCompute 不支持顶级的两个查询结果合并，要改写为一个子查询的形式，如：

SELECT * FROM A UNION ALL SELECT * FROM B;

需要改成：

SELECT * FROM (SELECT * FROM A UNION ALL SELECT * FROM B) tmp;

备注：

1. UNION ALL 操作对应的各个子查询的列个数、名称和类型必须一致。如果列名不一致时，可以使用列的别名加以解决。
2. 上述 query 中的 tmp 别名不能省略。
3. 一般情况下，我们规定最多允许 128 个表的 UNION ALL。

子查询

普通的 SELECT 是从几张表中读数据，如 SELECT column_1, column_2 … FROM table_name，查询的对象也可以是另外一个 SELECT 操作，如：

SELECT * FROM (SELECT shop_name FROM shop) a;

注意：子查询必须要有别名。

在 FROM 子句中，子查询可以当作一张表来使用，与其它的表或子查询进行 JOIN 操作，如：

SELECT a.shop_name, b.customer_id,b.total_price FROM 
(SELECT shop_name FROM shop) a
JOIN
(SELECT shop_name,customer_id,total_price FROM sale) b
ON a.shop_name=b.shop_name;

JOIN 操作

join_table:
        table_reference JOIN table_factor [join_condition]
    | table_reference {LEFT|RIGHT|FULL} OUTER JOIN table_reference join_condition
    
table_reference:
        table_factor
    | join_table
    
table_factor:
        tbl_name [alias]
    | table_subquery alias
    | ( table_references )
    
join_condition:
    ON equality_expression ( AND equality_expression )*
    
equality_expression:
    expression = expression

LEFT OUTER JOIN 左连接，返回左表中的所有记录，即使在右表中没有记录与它匹配，
例如：

SELECT * FROM shop a LEFT OUTER JOIN sale_detail b ON a.shop_name=b.shop_name;

RIGHT OUTER JOIN 右连接，返回右表中的所有记录，即使在左表中没有记录与它匹配，
例如：

SELECT *FROM shop a RIGHT OUTER JOIN sale_detail b ON a.shop_name=b.shop_name;

FULL OUTER JOIN 全连接, 返回左右表中的所有记录，例如：

SELECT * FROM shop a FULL OUTER JOIN sale_detail b ON a.shop_name=b.shop_name;

连接条件，普通 JOIN 中只允许 AND 连接的等值条件，MAPJOIN 时允许有不等值连接和 使用 OR 连接的条件。

MAP JOIN HINT

当一个大表和一个或多个小表做 JOIN 时，可以使用 MAPJOIN，性能比普通的 JOIN 要快很多，下面是一个例子。

需要注意，使用 MAPJOIN 时，LEFT OUTER JOIN 的左表必须是大表，
RIGHT OUTER JOIN 的右表必须是大表，INNER JOIN 左右表都可以是大表。FULL OUTER JOIN
不能使用 MAPJOIN。

对于小表的限制，目前定为在解压后在内存里的数据不超过 512M，如果 mapjoin 中指定多个小表，则小表占用的内存总和不得超过 512M。

SELECT /* + MAPJOIN(a) */ a.shop_name, b.customer_id,b.total_price
FROM shop a JOIN sale_detail b
ON a.shop_name = b.shop_name;

备注：

1. MAP JOIN 支持小表为子查询，最多允许指定 6 张小表。
2. 使用 map join 时需要引用小表或是子查询时，需要引用别名。

CASE WHEN 表达式

有两种不同的 case when 表达式

CASE value
 WHEN (_condition1) THEN result1
 WHEN (_condition2) THEN result2
 …
 ELSE resultn
END

CASE
 WHEN (_condition1) THEN result1
 WHEN (_condition2) THEN result2
 WHEN (_condition3) THEN result3
 ……
 ELSE resultn
END

CASE WHEN 表达式可以根据表达式的计算结果灵活返回不同的值， 如以下语句根据SHOP_NAME 的不同情况得出所属区域：

SELECT 
CASE 
WHEN SHOP_NAME IS NULL THEN 'DEFAULT_REGION'
WHEN SHOP_NAME LIKE 'HZ%' THEN 'ZJ_REGION'
END AS REGION
FROM SHOP;