一、聚合函数
【count】计数
-- distinct 去重函数 ：统计个数，不会统计为null的数据
select count(distinct member_id) as member_num from table
【sum】求和
select sum(amount) as all_amount from table
【avg】平均值
select avg(amount) as avg_amount from table
【max】最大值
select max(amount) as max_amount from table
【min】最小值
select min(amount) as min_amount from table
【percentile_approx】 百分位数 >> 报错
select percentile_approx(amount,0.75) as up_quartile from table
【array_agg】聚合为数组
select
member_id,
-- array_distinct对数组进行去重操作
array_distinct(array_agg(class)) as class
from
table
group by
member_id
二、时间函数
文本时间格式转时间戳
select to_unixtime(cast('2018-09-05' as timestamp))
select to_unixtime(TIMESTAMP '2023-07-09 12:59:01')
1.
文本时间格式转日期格式
select CAST('2023-07-09'AS DATE)
2.
时间戳转化文本时间格式
select format_datetime(from_unixtime(1234567890),'yyyy-MM-dd')
select from_unixtime(1688878741) -- 时间戳转日期
3.
时间加减
select cast(current_date - interval '1' day as varchar) -- 日期/月份/年 减函数
select date_add('DAY', 4, current_date) -- 日期/月份/年 加函数
select date_diff('day', cast('2023-02-01' as timestamp),cast('2023-01-01' as timestamp)) -- 日期差函数
select date_trunc('quarter',current_date) -- 返回当周/当月/当季度/当年的第一天
4.
当前日期（日期）
Select now() -- 当前时间（年月日时分秒）
Select current_date -- 当前日期
Select current_time -- 当前时间
5.
三、窗口函数
聚合函数作为窗口函数
SELECT SUM(字段名称) over(partition by 字段1 ORDER BY 字段2)
SELECT COUNT(字段名称) over(partition by 字段1 ORDER BY 字段2)
SELECT AVG(字段名称) over(partition by 字段1 ORDER BY 字段2)
SELECT MIN(字段名称) over(partition by 字段1 ORDER BY 字段2)
SELECT MAX(字段名称) over(partition by 字段1 ORDER BY 字段2)
1.排序窗口函数
Row_number() over(partition BY 字段1 ORDER BY 字段2) --返回的排序可以无重复数字,比如1,2,3
rank() over(partition BY 字股1 ORDER BY 字段2) --返回的排序可以有重复数字，有排序间隔比如1,1,3
dense_rank() over (partition BY 字段1 ORDER BY 字段2) --返回的排序可以有重复数字，但没有排序间隔 比如1,1,2
2.窗口函数(取第一行或最后一行的数据)
--返回字段a的第一行数据，加上order by的话，相当于min，max对应的窗口函数
SELECT first_value(字段a)over (partition BY 字段1 ORDER BY 字段2)
--返回字段a的最后一行数据，加上order by的话，相当于min，max对应的窗口函数
SELECT last_value(字段a)over(partition By 字段1 ORDER BY 字段2)
4. 分布窗口函数(适用于presto，计算某个值在一组有序的数据中累计分布百分比)
SELECT cume_dist() over(partition by 字段1 ORDER BY 字段2)-- 当前计算值所在位置/总行数，如果存在重复值，计算的时候，取重复值的最后
一行的位置
SELECT percent_rank(partition by 字段1 ORDER BY 字段2) -- 返回值=(rank-1)/(R0WS-1) 如果存在重复值，计算的时候，取重复值的最后一
行的位置
5. 前后窗口函数（数据偏移函数）
Select lag(字段a, 偏移行数 , 无偏移的取值) over(partition By 字段1 ORDER by 字段2) --返回字段a的前x行数据，可用于计算同一列数据前
后两行的差值
Select lead(字段a, 偏移行数, 无偏移的取值)over(partiton Bx 字段1 ORDER by 字段2)--返回字段a的后m行数据，可用于计算同一列数据前后两
行的差值
四、数学函数
SELECT RAND() --生成0-1的随机数字
SELECT ABS(-3.1415926) --绝对值函数
SELECT FLOOR(3.9415926) --向下取整
SELECT CEIL/CEILING(3.3415926) --向上取整
SELECT ROUND(3.94159263) --四舍五入函数
SELECT MOD(9,2) --取余所数
五、字符函数
1. 字符串查找函数
SELECT COALESCE (NULL,'abc') --查找返回非空的字段
SELECT strpos('acdefg','fg') --presto字符串查找函
SELECT INDEX('acdefg','fg') --presto字符串查找函数
2. 字符串替换函数
SELECT REPLACE('我是chinese'，'chinese'，中国人') -- 字符串替换函数
SELECT regexp_replace('abcde][d]','(\])|(\[)','') -- 字符串替换函数(转义字符是'\')
3. 字符串截取函数
SELECT SUBSTRING('上海市浦东新区',1,10) -- 字符串截取函数
SELECT substr('上海市浦东新区',1,10) -- 字符串截取函数
4. 字符串长度计算函数
SELECT LENGTH('abc') -- 字符串长度计算函数（字节数）
5. 字符串连接函数
SELECT CONCAT('2023', "-" , "09" , "-"，"16") --字符串连接函数
6. 字符串分割函数
SELECT split('a,b,c,d',’,’),split('192.168.0.1',’\\.') --presto/hive 字符串分割为 arry函数(doris暂时没用)
结果:["a","b","c","d"],["192","168","0","1"]
7. 其他字符串函数
SELECT LOWER('ABC") --字符串的小写格式
SELECT UPPER('abc') --字符串的大写格式
SELECT REVERSE('abcdefg') --宁符串反转函数
SELECT REPEAT('a',4) --返回重复n次的字符串
如需转载请保留本文出处: https://zhe94.com/972.html
