常用sql汇总
- 数学函数
- 聚合函数
- 日期函数
- 条件函数
- 字符串函数
-
- 字符串长度函数:length
- 字符串反转函数:reverse
- 字符串连接函数:concat
- 带分隔符字符串连接函数:concat_ws
- 字符串截取函数:substr
- 字符串转大写函数:upper
- 字符串转小写函数:lower
- 去空格函数:trim
- 左边/右边去空格函数:ltrim/rtrim
- 正则表达式替换函数:regexp_replace
- 正则表达式解析函数:regexp_extract
- URL解析函数:parse_url
- json解析函数:get_json_object
- 空格字符串函数:space
- 重复字符串函数:repeat
- 首字符编码函数:ascii
- 左补足函数/右补足函数:lpad/rpad
- 分割字符串函数: split
- 集合查找函数: find_in_set
- 窗口函数:over()
- 排序函数:rank()/dense_rank()\row_number()
- 行偏移函数:lead/lag
- 分组聚合函数:grouping sets/with cube
数学函数
取整函数:round
语法:round(double a)
返回值:BIGINT
说明:对小数a进行四舍五入不保留小数
实例:
select round(3.14) --结果:3
union ALL
select round(3.94) --结果:4
指定精度取整函数: round
语法: round(double a, int b)
返回值: DOUBLE
说明: 对小数a进行四舍五入保留b位小数
select round(5.1461,2) --结果:5.15
union ALL
select round(3.9422,2) --结果:3.94
向下取整函数: floor
语法: floor(double a)
返回值: BIGINT
说明: 返回等于或者小于a的最大的整数
select floor(5.1461) --结果:5
union ALL
select floor(3.9422) --结果:3
向上取整函数: ceil/ ceiling
语法: ceil(double a)
返回值: BIGINT
说明: 返回等于或者大于a的最小的整数
select ceil(5.1461) --结果:6
union ALL
select ceiling(3.9422) --结果:4
union ALL
select ceiling(3) --结果:3
正取余函数: pmod
语法: pmod(int a, int b),pmod(double a, double b)
返回值: int double
说明: 返回正的a除以b的余数
select pmod(9,4) --结果:9=4*2+1 故结果为1
union ALL
select pmod(9.9,3.1) --结果:9.9=3.1*3+0.6 故结果为0.6
分位函数(中位数):percentile_approx
语法: percentile_approx (col,p,B)
返回值: double
说明: 求近似的第pth个百分位数,p必须介于0和1之间,返回类型为double,但是col字段支持浮点类型。参数B控制内存消耗的近似精度,B越大,结果的准确度越高。默认为10,000。当col字段中的distinct值的个数小于B时,结果为准确的百分位数
with a as (
select 1 as itype
union all
select 2 as itype
union all
select 3 as itype
union all
select 6 as itype
union all
select 8 as itype
)
select percentile_approx(itype,0.5,9999) from a --结果:3
union ALL
select percentile_approx(itype,0.1,9999) from a --结果:1
union ALL
select percentile_approx(itype,0.3,9999) from a --结果:2
对数函数:log、ln、log2、log10
log语法:log(double x,double y)
返回值:double
ln语法:ln(double x)
返回值:double
说明:返回以自然常数e为底x的对数
说明:返回以x为底y的对数
log2语法:log2(double x)
返回值:double
说明:返回以2为底x的对数
log10语法:log10(double x)
返回值:double
说明:返回以10为底x的对数
select log(3,9); --结果:2.0 3的2次方=9
select ln(7.389); --结果:1.9999 自然常数e的平方约等于7.389,所以结果约等于2
select log2(8); --结果:3.0 2的3次方=8
select log10(10000); --结果:4.0 10的4次方=10000
幂运算函数:pow/power
语法:pow(double x,double y)
返回值:double
说明:返回x的y次方
select pow(2,3); --结果:8.0 2的3次方
select power(4,2); --结果:16.0 4的平方
自然常数的幂:exp
语法:exp(x)
返回值:double
说明:返回自然常数e的x次方
select exp(1); --结果:2.71828
select exp(3); --结果:20.0855
应用案例:求某个字段中所有数字的乘积
说明:这个情况可以利用对数加法的运算法则,即:同一底数的这两个数的对数的和等于两个正数的积的对数
with info as(
select 2 as value_1 union all
select 2 as value_1 union all
select 3 as value_1 union all
select 5 as value_1
)
select exp(sum(ln(value_1))) from info as method_1
--结果:59.99999999 说明:结果约等于60,即2*2*3*5=60,此处是以e为底数
select pow(10,sum(log(10,value_1))) from info as method_1
--结果也是约等于60,此处是以10为底数,也可以加上向上取整ceil函数
select ceil(pow(3,sum(log(3,value_1)))) from info as method_1
--结果为60,需要注意,使用pow方式的时候,给定的两个底数必须相同,底数不可为1、0、负数
select pow(1,sum(log(1,value_1))) from info as method_1
--此时结果为null
聚合函数
取最大值:max 取最小值:min 取平均值:avg 求和:sum
使用以上函数时,字段类型最好为数字类型,若为字符串类型,结果可能会有错误
计数:count
计数针对数据条数,多与distinct联用,如count(distinct uid)对uid去重计数,但不建议这样使用,效率低下
select
max(a) --结果:6
,min(a) --结果:3
,avg(a)