天池 odps_SQL 常用函数和方法

清疚 2021-09-14 06:34 676阅读 0赞

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

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()的用法][Partition By_row_number]  
[sum(x) over( partition by y ORDER BY z ) 分析][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)
    // 拆分

[Partition By_row_number]: http://www.cnblogs.com/linJie1930906722/p/6036053.html
[sum_x_ over_ partition by y ORDER BY z _]: http://blog.csdn.net/zengmingen/article/details/50786229