DataWork之 MaxComputer的使用
注意: 由于MaxComputer里面没有主键
默认主键为
保单号+8位险种代码+责任起期
若无主键限制,就对所有的字段进行分组
所以每次join的时候,where条件需要加上 a.主键 =b.主键 进行筛选
下面是各个函数的API,有需要直接按照需求搜
0.1 日期函数汇总
MaxCompute SQL提供了常见的日期函数，您可以根据实际需要选择合适的日期函数，完成日期计算、日期转换。本文为您提供MaxCompute SQL支持的日期函数的命令格式、参数说明及示例，指导您使用日期函数完成开发。
| 函数 | 功能 |
|---|---|
| DATEADD | 按照指定的单位和幅度修改日期值。 |
| DATE_ADD | 按照指定的幅度增减天数。 |
| DATEDIFF | 计算两个日期的差值并按照指定的单位表示。 |
| DATEPART | 提取日期中符合指定时间单位的字段值。 |
| DATETRUNC | 提取日期按照指定时间单位截取后的值。 |
| FROM_UNIXTIME | 将数字型的UNIX值转换为日期值。 |
| GETDATE | 获取当前系统时间。 |
| ISDATE | 判断一个日期字符串能否根据指定的格式串转换为一个日期值。 |
| LASTDAY | 获取日期所在月的最后一天。 |
| TO_DATE | 将指定格式的字符串转换为日期值。 |
| TO_CHAR | 将日期按照指定格式转换为字符串。 |
| UNIX_TIMESTAMP | 将日期转换为整型的UNIX格式的日期值。 |
| WEEKDAY | 返回日期值是当前周的第几天。 |
| WEEKOFYEAR | 返回日期值位于当年的第几周。 |
| ADD_MONTHS | 计算日期值增加指定月数后的日期。 |
| CURRENT_TIMESTAMP | 返回当前TIMESTAMP类型的时间戳。 |
| DAY | 返回日期值的天。 |
| DAYOFMONTH | 返回日部分的值。 |
| EXTRACT | 获取日期TIMESTAMP中指定单位的部分。 |
| FROM_UTC_TIMESTAMP | 将一个UTC时区的时间戳转换为一个指定时区的时间戳。 |
| HOUR | 返回日期小时部分的值。 |
| LAST_DAY | 返回日期值所在月份的最后一天日期。 |
| MINUTE | 返回日期分钟部分的值。 |
| MONTH | 返回日期值所属月份。 |
| MONTHS_BETWEEN | 返回指定日期值间的月数。 |
| NEXT_DAY | 返回大于日期值且与指定周相匹配的第一个日期。 |
| QUARTER | 返回日期值所属季度。 |
| SECOND | 返回日期秒数部分的值。 |
| YEAR | 返回日期值的年。 |
0.2 窗口函数
您可以在MaxCompute SQL中使用窗口函数对指定开窗列的数据灵活地进行分析处理工作。本文为您提供MaxCompute SQL支持的窗口函数的命令格式、参数说明及示例，指导您使用窗口函数完成开发。
| 函数 | 功能 |
|---|---|
| COUNT | 计算计数值。 |
| AVG | 计算平均值。 |
| MAX | 计算最大值。 |
| MIN | 计算最小值。 |
| MEDIAN | 计算中位数。 |
| STDDEV | 计算总体标准差。 |
| STDDEV_SAMP | 计算样本标准差。 |
| SUM | 计算汇总值。 |
| DENSE_RANK | 计算连续排名。 |
| RANK | 计算跳跃排名。 |
| LAG | 按偏移量取当前行之前第几行的值。 |
| LEAD | 按偏移量取当前行之后第几行的值。 |
| PERCENT_RANK | 计算一组数据中某行的相对排名。 |
| ROW_NUMBER | 计算行号。 |
| CLUSTER_SAMPLE | 用于分组抽样。 |
| CUME_DIST | 计算累计分布。 |
| NTILE | 将分组数据按照顺序切片，并返回切片值。 |
0.3 聚合函数
聚合（Aggregate）函数的输入与输出是多对一的关系，即将多条输入记录聚合成一条输出值，可以与MaxCompute SQL中的group by语句配合使用。本文为您提供MaxCompute SQL支持的聚合函数的命令格式、参数说明及示例，指导您使用聚合函数完成开发。
| 函数 | 功能 |
|---|---|
| AVG | 计算平均值。 |
| COUNT | 计算记录数。 |
| COUNT_IF | 计算指定表达式为True的记录数。 |
| MAX | 计算最大值。 |
| MIN | 计算最小值。 |
| MEDIAN | 计算中位数。 |
| STDDEV | 计算总体标准差。 |
| STDDEV_SAMP | 计算样本标准差。 |
| SUM | 计算汇总值。 |
| WM_CONCAT | 用指定的分隔符连接字符串。 |
| ANY_VALUE | 在指定范围内任选一个值返回。 |
| APPROX_DISTINCT | 返回输入的非重复值的近似数目。 |
| ARG_MAX | 返回指定列的最大值对应行的列值。 |
| ARG_MIN | 返回指定列的最小值对应行的列值。 |
| COLLECT_LIST | 将指定的列聚合为一个数组。 |
| COLLECT_SET | 将指定的列聚合为一个无重复元素的数组。 |
| COVAR_POP | 计算指定两个数值列的总体协方差。 |
| COVAR_SAMP | 计算指定两个数值列的样本协方差。 |
| NUMERIC_HISTOGRAM | 统计指定列的近似直方图。 |
| PERCENTILE | 计算精确百分位数，适用于小数据量。 |
| PERCENTILE_APPROX | 计算近似百分位数，适用于大数据量。 |
| VARIANCE/VAR_POP | 计算指定数值列的方差。 |
| VAR_SAMP | 计算指定数值列的样本方差。 |
0.4 字符串函数
您可以在MaxCompute SQL中使用字符串函数对指定字符串进行灵活处理。本文为您提供MaxCompute SQL支持的字符串函数的命令格式、参数说明及示例，指导您使用字符串函数完成开发。
| 函数 | 功能 |
|---|---|
| ASCII | 返回字符串的第一个字符的ASCII码。 |
| BASE64 | 将二进制表示值转换为BASE64编码格式字符串。 |
| CHAR_MATCHCOUNT | 计算A字符串出现在B字符串中的字符个数。 |
| CHR | 将指定ASCII码转换成字符。 |
| CONCAT | 将字符串连接在一起。 |
| FROM_JSON | 根据给定的JSON字符串和输出格式信息，返回ARRAY、MAP或STRUCT类型。 |
| GET_JSON_OBJECT | 在一个标准JSON字符串中，按照指定方式抽取指定的字符串。 |
| INSTR | 计算A字符串在B字符串中的位置。 |
| IS_ENCODING | 判断字符串是否可以从指定的A字符集转换为B字符集。 |
| KEYVALUE | 将字符串拆分为Key-Value对，并将Key-Value对分开，返回Key对应的Value。 |
| LENGTH | 计算字符串的长度。 |
| LENGTHB | 计算字符串以字节为单位的长度。 |
| MD5 | 计算字符串的MD5值。 |
| PARSE_URL | 对URL进行解析返回指定信息。 |
| REGEXP_COUNT | 计算字符串从指定位置开始，匹配指定规则的子串数。 |
| REGEXP_EXTRACT | 将字符串按照指定规则拆分为组后，返回指定组的字符串。 |
| REGEXP_INSTR | 返回字符串从指定位置开始，与指定规则匹配指定次数的子串的起始或结束位置。 |
| REGEXP_REPLACE | 将字符串中，与指定规则在指定次数匹配的子串替换为另一字符串。 |
| REGEXP_SUBSTR | 返回字符串中，从指定位置开始，与指定规则匹配指定次数的子串。 |
| SPLIT_PART | 按照分隔符拆分字符串，返回指定部分的子串。 |
| SUBSTR | 返回STRING类型字符串从指定位置开始，指定长度的子串。 |
| SUBSTRING | 返回STRING或BINARY类型字符串从指定位置开始，指定长度的子串。 |
| TO_CHAR | 将BOOLEAN、BIGINT、DECIMAL或DOUBLE类型值转为对应的STRING类型表示。 |
| TO_JSON | 将指定的复杂类型输出为JSON字符串。 |
| TOLOWER | 将字符串中的英文字符转换为小写形式。 |
| TOUPPER | 将字符串中的英文字符转换为大写形式。 |
| TRIM | 去除字符串的左右空格。 |
| LTRIM | 去除字符串的左边空格。 |
| RTRIM | 去除字符串的右边空格。 |
| REPEAT | 返回将字符串重复指定次数后的结果。 |
| REVERSE | 返回倒序字符串。 |
| UNBASE64 | 将BASE64编码格式字符串转换为二进制表示值。 |
| CONCAT_WS | 将参数中的所有字符串按照指定的分隔符连接在一起。 |
| JSON_TUPLE | 在一个标准的JSON字符串中，按照输入的一组键抽取各个键指定的字符串。 |
| LPAD | 将字符串向左补足到指定位数。 |
| RPAD | 将字符串向右补足到指定位数。 |
| REPLACE | 将字符串中与指定字符串匹配的子串替换为另一字符串。 |
| SOUNDEX | 将普通字符串替换为SOUNDEX字符串。 |
| SUBSTRING_INDEX | 截取字符串指定分隔符前的字符串。 |
| TRANSLATE | 将A出现在B中的字符串替换为C字符串。 |
| URL_ENCODE | 将字符串编码为application/x-www-form-urlencoded MIME格式。 |
| URL_DECODE | 将字符串从application/x-www-form-urlencoded MIME格式转为常规字符。 |
0.2 分析函数汇总
0.2.1 分析函数语法 (其实用个函数,既做了加工又排序或者分组后返回了整体数据)
function_name(<argument>,<argument>...) over(<partition_Clause><order by_Clause><windowing_Clause>);
function_name()：函数名称
argument：参数
over( )：开窗函数
partition_Clause：分区子句，数据记录集分组，partition by... // 其实就是group up
order by_Clause：排序子句，数据记录集排序，order by...
windowing_Clause：开窗子句，定义分析函数在操作行的集合，三种开窗方式：rows、range、Specifying // 目前未涉及到
0.2.2 分析函数汇总
-
count() over() ：统计分区中各组的行数，partition by 可选，order by 可选
select ename,esex,eage,count(*) over() from emp; --总计数 select ename,esex,eage,count(*) over(order by eage) from emp; --递加计数 select ename,esex,eage,count(*) over(partition by esex) from emp; --分组计数 select ename,esex,eage,count(*) over(partition by esex order by eage) from emp;--分组递加计数 -
sum() over() ：统计分区中记录的总和，partition by 可选，order by 可选
select ename,esex,eage,sum(salary) over() from emp; --总累计求和 select ename,esex,eage,sum(salary) over(order by eage) from emp; --递加累计求和 select ename,esex,eage,sum(salary) over(partition by esex) from emp; --分组累计求和 select ename,esex,eage,sum(salary) over(partition by esex order by eage) from emp; --分组递加累计求和 -
avg() over() ：统计分区中记录的平均值，partition by 可选，order by 可选
select ename,esex,eage,avg(salary) over() from emp; --总平均值 select ename,esex,eage,avg(salary) over(order by eage) from emp; --递加求平均值 select ename,esex,eage,avg(salary) over(partition by esex) from emp; --分组求平均值 select ename,esex,eage,avg(salary) over(partition by esex order by eage) from emp; --分组递加求平均值 -
min() over() ：统计分区中记录的最小值，partition by 可选，order by 可选
select ename,esex,eage,salary,min(salary) over() from emp; --求总最小值 select ename,esex,eage,salary,min(salary) over(order by eage) from emp; --递加求最小值 select ename,esex,eage,salary,min(salary) over(partition by esex) from emp; --分组求最小值 select ename,esex,eage,salary,min(salary) over(partition by esex order by eage) from emp; --分组递加求最小值max() over() ：统计分区中记录的最大值，partition by 可选，order by 可选
select ename,esex,eage,salary,max(salary) over() from emp; --求总最大值 select ename,esex,eage,salary,max(salary) over(order by eage) from emp; --递加求最大值 select ename,esex,eage,salary,max(salary) over(partition by esex) from emp; --分组求最大值 select ename,esex,eage,salary,max(salary) over(partition by esex order by eage) from emp; --分组递加求最大值 -
rank() over() ：跳跃排序，partition by 可选，order by 必选
select ename,eage,rank() over(partition by job order by eage) from emp; select ename,eage,rank() over(order by eage) from emp; -
dense_rank() ：连续排序，partition by 可选，order by 必选
select ename,eage,dense_rank() over(partition by job order by eage) from emp; select ename,eage,dense_rank() over(order by eage) from emp; -
row_number() over() ：排序，无重复值，partition by 可选，order by 必选
select ename,eage,row_number() over(partition by job order by eage) from emp; select ename,eage,row_number() over(order by eage) from emp; -
ntile(n) over() ：每组平均分 partition by 可选，order by 必选
n表示将分区内记录平均分成n份，多出的按照顺序依次分给前面的组
select ename,salary,ntile(3) over(order by salary desc) from emp; select ename,salary,ntile(3) over(partition by job order by salary desc) from emp; -
first_value() over() ：取出分区中第一条记录的字段值，partition by 可选，order by 可选
select ename,first_value(salary) over() from emp; select ename,first_value(salary) over(order by salary desc) from emp; select ename,first_value(salary) over(partition by job) from emp; select ename,first_value(salary) over(partition by job order by salary desc) from emp;last_value() over() ：取出分区中最后一条记录的字段值，partition by 可选，order by 可选
select ename,last_value(ename) over() from emp; select ename,last_value(ename) over(order by salary desc) from emp; select ename,last_value(ename) over(partition by job) from emp; select ename,last_value(ename) over(partition by job order by salary desc) from emp; -
lag() over() ：取出前n行数据，partition by 可选，order by 必选
lead() over() ：取出后n行数据，partition by 可选，order by 必选
select ename,eage,lag(eage,1,0) over(order by salary), lead(eage,1,0) over(order by salary) from emp; select ename,eage,lag(eage,1) over(partition by esex order by salary), lead(eage,1) over(partition by esex order by salary) from emp; -
ratio_to_report(a) over(partition by b) ：求按照b分组后a的值在所属分组中总值的占比,a的值必须为数值或数值型字段
partition by 可选，order by 不可选
select ename,job,salary,ratio_to_report(1) over() from emp; --给每一行赋值1，求当前行在总值的占比，总是0.1 select ename,job,salary,ratio_to_report(salary) over() from emp; --当前行的值在所有数据中的占比 select ename,job,salary,ratio_to_report(1) over(partition by job) from emp; --给每一行赋值1，求当前行在分组后的组内总值的占比 select ename,job,salary,ratio_to_report(salary) over(partition by job) from emp; --当前行的值在分组后组内总值占比 -
percent_rank() over() ：partition by 可选，order by 必选
所在组排名序号-1除以该组所有的行数-1，排名跳跃排序
select ename,job,salary,percent_rank() over(order by salary) from emp; select ename,job,salary,percent_rank() over(partition by job order by salary) from emp;下面函数也不是太常用,上面很重要,下面函数用的时候百度就好或者来这里瞅瞅
-
ume_dist() over() ：partition by 可选，order by必选
所在组排名序号除以该组所有的行数，注意对于重复行，计算时取重复行中的最后一行的位置
select ename,job,salary,cume_dist() over(order by salary) from emp; select ename,job,salary,cume_dist() over(partition by job order by salary) from emp; -
precentile_cont( x ) within group(order by ...) over() ：over()中partition by可选，order by 不可选
x为输入的百分比，是0-1之间的一个小数，返回该百分比位置的数据，若没有则返回以下计算值（r）：
a=1+( x *(N-1) ) x为输入的百分比，N为分区内的记录的行数
b=ceil ( a ) 向上取整
c = floor( a ) 向下取整
r=a * 百分比位置上一条数据 + b * 百分比位置下一条数据
select ename,job,salary,percentile_cont(0.5) within group(order by salary) over() from emp; select ename,job,salary,percentile_cont(0.5) within group(order by salary) over(partition by job) from emp; -
precentile_disc( x ) within group(order by ...) over() ：over()中partition by可选，order by 不可选
x为输入的百分比，是0-1之间的一个小数，返回百分比位置对应位置上的数据值，若没有对应数据值，就取大于该分布值的下一个值
select ename,job,salary,percentile_disc(0.5) within group(order by salary) over()from emp; select ename,job,salary,percentile_disc(0.5) within group(order by salary) over(partition by job) from emp; -
stddev() over()：计算样本标准差，只有一行数据时返回0，partition by 可选，order by 可选
stddev_samp() over()：计算样本标准差，只有一行数据时返回null，partition by 可选，order by 可选
stddev_pop() over()：计算总体标准差，partition by 可选，order by 可选
select stddev(stu_age) over() from student; --计算所有记录的样本标准差 select stddev(stu_age) over(order by stu_age) from student; --计算递加的样本标准差 select stddev(stu_age) over(partition by stu_major) from student; --计算分组的样本标准差 select stddev(stu_age) over(partition by stu_major order by stu_age) from student; --计算分组递加的样本标准差 select stddev_samp(stu_age) over() from student; --计算所有记录的样本标准差 select stddev_samp(stu_age) over(order by stu_age) from student; --计算递加的样本标准差 select stddev_samp(stu_age) over(partition by stu_major) from student; --计算分组的样本标准差 select stddev_samp(stu_age) over(partition by stu_major order by stu_age) from student; --计算分组递加的样本标准差 select stddev_pop(stu_age) over() from student; --计算所有记录的总体标准差 select stddev_pop(stu_age) over(order by stu_age) from student; --计算递加的总体标准差 select stddev_pop(stu_age) over(partition by stu_major) from student; --计算分组的总体标准差 select stddev_pop(stu_age) over(partition by stu_major order by stu_age) from student;--计算分组递加的总体标准差剩下的都是一些数据统计方面东西,方差,相关系数什么的
1. 查询的格式
select [all | distinct] <select_expr>, <select_expr>, ...
from <table_reference>
[where <where_condition>]
[group by <col_list>]
[having <having_condition>]
[order by <order_condition>]
[distribute by <distribute_condition> [sort by <sort_condition>] ]
[limit <number>]
2. 去重
去重多列时，distinct的作用域是select的列集合，不是单个列
select distinct region, sale_date from sale_detail;
3. WHERE子句（where_condition）
可选。 where子句为过滤条件。如果表是分区表，可以实现列裁剪。使用规则如下：
- 配合关系运算符，筛选满足指定条件的数据。关系运算符包含：
>、<、=、>=、<=、<>like、rlikein、not inbetween…and
4. GROUP BY分组查询（col_list）
可选。通常， group by和 聚合函数配合使用，根据指定的普通列、分区列或正则表达式进行分组。 group by使用规则如下：
group by操作优先级高于select操作，因此group by的取值是select输入表的列名或由输入表的列构成的表达式。需要注意的是：- 不允许是
select语句的输出列的别名。 group by取值为正则表达式时，必须使用列的完整表达式。select语句中没有使用聚合函数的列必须出现在group by中。
- 不允许是
5. HAVING子句（having_condition）
可选。通常 having子句与聚合函数一起使用，实现过滤。命令示例如下。
--为直观展示数据呈现效果，向sale_detail表中追加数据。
insert into sale_detail partition (sale_date='2014', region='shanghai') values ('null','c5',null),('s6','c6',100.4),('s7','c7',100.5);
--使用having子句配合聚合函数实现过滤。
select region,sum(total_price) from sale_detail
group by region
having sum(total_price)<305;
6.ORDER BY全局排序（order_condition）
-
默认对数据进行升序，如果降序排序，需要使用
desc关键字。 -
order by默认要求带limit数据行数限制，没有limit会返回报错。(实测不会报错,) -
在使用
order by排序时，NULL会被认为比任何值都小， -
order by后面需要加上select列的别名。当select某列时，如果没有指定列的别名，则列名会被作为列的别名。order by加列的别名。命令示例如下。select total_price as t from sale_detail order by total_price limit 3; --等效于如下语句。 select total_price as t from sale_detail order by t limit 3;
7.常见 select语句的执行顺序如下：
- 场景1：
from->where->group by->having->select->order by->limit - 为避免混淆，MaxCompute支持以执行顺序书写查询语句，语法结构可改为如下形式：
from <table_reference>
[where <where_condition>]
[group by <col_list>]
[having <having_condition>]
select [all | distinct] <select_expr>, <select_expr>, ...
[order by <order_condition>]
[limit <number>]
使用示例
--按照select语法书写。
select region,max(total_price)
from sale_detail
where total_price > 100
group by region
having sum(total_price)>305
order by region
limit 5;
--按照执行顺序书写。与上一写法等效。
from sale_detail
where total_price > 100
group by region
having sum(total_price)>305
select region,max(total_price)
order by region
limit 5;
8.子查询（SUBQUERY）
-
基础子查询 (示例)
select * from (select shop_name from sale_detail) a; -
in subquery (示例)
select * from sale_detail where shop_name in (select shop_name from shop); -
not in subquery (示例)
select * from shop1 where shop_name not in (select shop_name from sale_detail); -
exists subquery
使用exists subquery时，当子查询中有至少一行数据时，返回True，否则返回False。
select * from sale_detail where exists (select * from shop where customer_id = sale_detail.customer_id); -
not exists subquery
使用
not exists subquery时，当子查询中无数据时，返回True，否则返回False。select * from sale_detail where not exists (select * from shop where shop_name = sale_detail.shop_name);
