天池 odps_SQL 常用函数和方法

平均值、中值、最大值、最小值：

avg(distance) as merchant_avg_distance,
median(distance) as merchant_median_distance,
max(distance) as merchant_max_distance,
min(distance) as merchant_min_distance

统计数量、求和：

count(*)
COUNT(DISTINCT column_name) //满足条件的行数
sum(cnt)

更改数据类型、小数位数：

round(col,2)
// 保留两位

cast(avg(col) as decimal(15,2))
// 15为有效数字,小数点后保留2位

cast(merchant_max_distance as bigint) as merchant_max_distance
// 改成bigint类型

判断语句：

case when total_sales is null then 0.0 else total_sales end as total_sales

select max(case 课程 when '数学' then 分数 else 0 end) [数学]
// 求最大值并赋值给列数学

isnull(sum( case  stdsubject when  ' 化学 '  then Result end), 0 )
// 如果isnull，赋值0

排序：

SELECT a,b,c,
sum(c) over(order by b) sum1, sum(c) over() sum2 from aa
// 在执行sum(c) over(order by b)此步时先对表a中按b排序，排序完计算c所在列到列顶所有数据值和

select *,row_number() over(order by Score desc) as Sequence from Student
// 按照score降序使用row_number()排名

分组：

create table wepon_d3_f5_temp as
select max(date_received) as max_date_received
(select a.user_id,a.coupon_id,a.date_received from otable)t 
group by user_id,coupon_id;
// 对user_id,coupon_id分组后，再在组内使用函数max求字段date_received最大值

分区：
与分组不同，分组后select一般返回一行数据。而分区可返回多条数据。
partition by order by。可以这样理解：使用partition by分区后再使用函数是对区内所有行使用该函数，为了返回多行，对所有行赋相同的函数计算值。如果再在partition by后使用order by，那就是在每个分区内按照order by的要求，从区顶求值到当前行。二者区别见该文：
分区函数Partition By的与row_number()的用法
sum(x) over( partition by y ORDER BY z ) 分析

select *,row_number() over(partition by Grade order by Score desc) as Sequence from Student
// 对student按grade分区后在区内按照降序排序，然后在区内分别从1开始自增赋值sequence排序

SELECT a,b,c,sum(c) over( partition by b ) partition_sum,
sum(c) over( partition by b order by a desc) partition_order_sum
  FROM aa;

时间：

// 计算时间差
datediff(to_date(date_received,"yyyymmdd"),to_date("20160413","yyyymmdd"),"dd") as days_distance
// to_date转换成时间类型
// datediff计算前一个时间减去后一个时间的天数("dd"参数)

// 获取时间星期
weekday(to_date(date_received,"yyyymmdd")) // 0=星期一
dayofweek('1998-02-03') // 1=星期一
区别在于DAYOFWEEK获取的星期索引是以1开始，而WEEKDAY获取的星期索引是以0开始

字符串：

cast(substr(date_received,7,2) as bigint) as day_of_month
// 剪切

instr(discount_rate,":")=0
// 判断是否包含

split_part(discount_rate,":",1)
// 拆分