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 分析函数汇总

1. 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;--分组递加计数
   

2. 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; --分组递加累计求和
   

3. 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; --分组递加求平均值
   

4. 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; --分组递加求最大值
   

5. 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;
   

6. 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;
   

7. 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;
   

8. 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;
   

9. 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;
   

10. 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;
    

11. 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; --当前行的值在分组后组内总值占比
    

12. 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;
    

    下面函数也不是太常用,上面很重要,下面函数用的时候百度就好或者来这里瞅瞅

    ---

13. 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;
    

14. 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;
    

15. 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;
    

16. 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、rlike
  • in、not in
  • between…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);