首页 > 云海(数据开放) > 帮助文档 > 语法支持说明

语法支持说明

一、系统变量:

${ date_ymd-n }是系统变量,运行时自动替换,其中 n为1到99之间的自然数。

对应的分区类型,分区字段与系统变量的对应关系列表为:

基础表分区类型

基础表分区字段

云海对应系统变量

示例

按月分区

mt

{date_ym}

mt={date_ym-1}

按天分区

dt

{date_ymd}

dt={date_ymd-1}


二、支持语法:

1.更新表中的数据(INSERT OVERWRITE INTO)

在SQL处理数据的过程中,INSERT OVERWRITE INTO用于将计算的结果保存目标表中。
INSERT OVERWRITE INTO TABLE tablename PARTITION
select_statement
FROM from_statement
WHERE dt='${ date_ymd }
如,可以用如下操作计算sale_detail表中不同地区的销售额
INSERT OVERWRITE INTO TABLE sales_by_region partition
SELECT region, sum(total_price)
FROM sale_detail
WHERE dt='${ date_ymd }'
GROUP BY region;

2.SELECT操作
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
FROM table_reference
[WHERE where_condition]
[GROUP BY col_list]
[ORDER BY order_condition]
[LIMIT number]
其中
> GROUP BY col_list必须写列的完整表达式,不可用列的column-alias。
> 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分区,且分区名称固定为dt。如下所示:
SELECT page_views.*
FROM page_views
WHERE page_views.dt > = '${ date_ymd-29}';
在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;
需要注意的是在使用order by排序时,NULL会被认为比任何值都小,这个行为与mysql一致,但是与oracle不一致。 LIMIT 限制只输出结果中的几行,当使用SELECT语句直接从屏幕输出查看结果时,会有行数的限制,对每个PROJECT的限制可能不同。

UNION ALL

将两个或多个SELECT操作返回的数据集联合成一个数据集,如果结果有重复行时,会返回所有符合条件的行,不进行重复行的去重处理。需要注意的是: SQL不支持顶级的两个查询结果合并,要改写为一个子查询的形式,如
SELECT * FROM A UNION ALL SELECT * FROM B;
需要改成:
SELECT * FROM (SELECT * FROM A UNION ALL SELECT * FROM B) tmp;
Note
> UNION ALL操作对应的各个子查询的列个数、名称和类型必须一致。如果列名不一致时,可以使用列的别名加以解决。
> 上述query中的tmp别名不能省略。
> 一般情况下,我们规定最多允许128个表的UNION ALL。

子查询

普通的SELECT是从几张表中读数据,如SELECT column_1, column_2 ... FROM table_name,查询的对象也可以是另外一个SELECT操作,如:
SELECT * FROM (SELECT shop_name FROM shop) a;
Note
子查询必须要有别名。
在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;
连接条件,只允许AND连接的等值条件。只有在map join中,可以使用不等值连接或者使用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;

Note

> MAP JOIN支持小表为子查询,最多允许指定6张小表。

> 使用map join时需要引用小表或是子查询时,需要引用别名。

复杂条件join

SQL支持在join的ON条件中使用:不等值表达式、OR 逻辑等复杂的join条件,但是当前仅支持在MAPJOIN中使用。
SELECT /*+ MAPJOIN(a) */
a.key,
b.key,
a.value,
b.value
FROM src a JOIN src1 b
ON a.key < b.key OR a.value + b.value < 10;

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;


三、常用函数:

SQL 语法