DataWork之 MaxComputer的使用

2023-12-11 11:26:33

注意: 由于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);
上一篇：40 开窗函数OVER


下一篇：Focusable 属性和IsTabStop 属性之间的关系