注意: 由于MaxComputer里面没有主键

默认主键为

保单号+8位险种代码+责任起期(这个目前用不到)

所以每次join的时候,where条件需要加上 a.主键 =b.主键 进行筛选

下面是各个函数的API,有需要直接按照需求搜

0.1 日期函数汇总

MaxCompute SQL提供了常见的日期函数，您可以根据实际需要选择合适的日期函数，完成日期计算、日期转换。本文为您提供MaxCompute SQL支持的日期函数的命令格式、参数说明及示例，指导您使用日期函数完成开发。

0.2 窗口函数

您可以在MaxCompute SQL中使用窗口函数对指定开窗列的数据灵活地进行分析处理工作。本文为您提供MaxCompute SQL支持的窗口函数的命令格式、参数说明及示例，指导您使用窗口函数完成开发。

0.3 聚合函数

聚合（Aggregate）函数的输入与输出是多对一的关系，即将多条输入记录聚合成一条输出值，可以与MaxCompute SQL中的group by语句配合使用。本文为您提供MaxCompute SQL支持的聚合函数的命令格式、参数说明及示例，指导您使用聚合函数完成开发。

0.4 字符串函数

您可以在MaxCompute SQL中使用字符串函数对指定字符串进行灵活处理。本文为您提供MaxCompute SQL支持的字符串函数的命令格式、参数说明及示例，指导您使用字符串函数完成开发。

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

DataWork之 MaxComputer的使用