数据库|SQL内置函数使用手册_numeric histogram

SQL内置函数使用大全

本文中总结了SQL中常用的内置函数，包含通用聚合函数、安全检测函数、数学统计函数、字符串函数等

通用聚合函数

安全检测函数

Map映射函数

估算函数

桶是按照聚集程度均分的，每个结果里显示这个区域的均值和个数

数学统计函数

数学计算函数

支持的运算符号：+、-、*、/、%；可以用在select句子中

字符串函数

日期和时间函数

函数类型

日期和时间函数主要是包含：

• 日期函数
• 时间函数
• 区间函数
• 时序补全函数

日期时间类型

1. 时间戳类型unixtime：以int类型表示从1970年1月1日开始的秒数，例如1512374067表示的时间是Mon Dec 4 15:54:27 CST 2017。日志服务每条日志中内置的时间__time__即为这种类型。
2. timestamp类型：以字符串形式表示，例如：2017-11-01 13:30:00

日期函数

时间函数

• date_format(timestamp, format)
• date_parse(string, format)

格式说明

时间段对齐函数

按照秒、分钟、小时、日、月、年等进行对齐

date_trunc(unit,x)
1

• x是一个timestamp类型，也可以是unix time
• date_trunc只能按照固定时间间隔统计

*|select  date_trunc('minute' ,  __time__)  as t,
       truncate (avg(latency) ) ,
       current_date  
       group by   t
       order by t  desc 
       limit 60
1
2
3
4
5
6

时间间隔函数

时间间隔函数用来执行时间段相关的运算，如在日期中添加或减去指定的时间间隔、计算两个日期之间的时间

• date_add(unit, value, timestamp)：在timestamp的基础上加上value个unit；如果value是负值，则进行减法
• date_diff(unit, timestamp1, timestamp2)：计算两个timestamp之间差几个unit

函数区间单位

时序补全函数time_series

时序补全函数time_series必须和group by time order by time一起使用，且order by不支持desc排序方式

函数格式：

time_series(time_column, window, format, padding_data)
1

参数说明：

URL函数

URL函数支持从标准URL路径中提取字段，一个标准的URL如下：

[protocol:][//host[:port]][path][?query][#fragment]
1

正则式函数

JSON函数

JSON主要有两种结构：map和array。如果一个字符串解析成JSON失败，那么返回的是null。

• json_parse(string)：将字符串转成JSON类型
• json_format(json)：将json类型转成字符串

类型转换函数

类型转换函数用于在查询中转换指定值或指定列的数据类型。

函数格式

• 在查询中将某一列（字段）或某一个值转换成指定类型。其中，如果某一个值转换失败，将终止整个查询。

cast([key|value] as type)
1

• 在查询中将某一列（字段）或某一个值转换成指定类型。如果某一个值转换失败，该值返回NULL，并跳过该值继续处理。

参数

示例

• 将数字123转换为字符串（varchar）格式：

  cast(123 AS varchar)
  1

• 将uid字段转换为字符串（varchar）格式：

  cast(uid AS varchar)
  1

IP地理函数

IP 地理函数可以识别一个 IP 是内网 IP还是外网 IP，也可以判断 IP 所属的国家、省份、城市

常用函数

使用demo

• 过滤掉内网请求，查看top10的网络访问省份

* | select count(1) as pv, ip_to_province(ip) as province   -- 判断省份
where ip_to_domain(ip) != 'intranet'  -- 过滤内网
group by province  -- 分组
order by desc   -- 降序
limit 10  -- 取出前10个
1
2
3
4
5

• 查看不同国家的平均响应延时、最大响应延时以及最大延时对应的 request

  select avg(latency), 
  	   max(latency),
  	   max_by(requestId, latency), 
  	   ip_to_country(ip) as country 
  group by country 
  limit 100
  
  1
  2
  3
  4
  5
  6
  7

地理函数

常用的地理函数包含：

• geohash(string)
• geohash(lat, lon)

Group by用法

GROUP BY 支持多列。GROUP BY支持通过SELECT的列的别名来表示对应的KEY

select avg(latency), projectName, date_trunc('hour', __time__) as hour   -- 取别名
group by ProjectName, hour

1
2
3

group by 支持grouping sets, cube, rollup

method:PostLogstoreLogs |select avg(latency)   group by cube(projectName,logstore)
method:PostLogstoreLogs |select avg(latency)   group by GROUPING SETS ( ( projectName,logstore), (projectName,method))
method:PostLogstoreLogs |select avg(latency)   group by rollup(projectName,logstore)
1
2
3

在group by中提取非agg列：如果使用了group by语法，那么在select时，只能选择select group by 的列原始内容，或者对任意列进行聚合计算，不允许获取非group by列的内容

错误语法

*| select a,b, count(c),group by a   -- b行由多个可供选择，系统不知道选择哪个
1

解决办法：使用arbitrary函数

*| select a, arbitrary(b), count(c) group by a
1

窗口函数

窗口函数用处

在日常工作中，经常会遇到需要在每组内排名，比如下面的业务需求：

排名问题：每个部门按业绩来排名
topN问题：找出每个部门排名前N的员工进行奖励

面对这类需求，就需要使用sql的高级功能窗口函数了。

什么是窗口函数

窗口函数，也叫OLAP函数（online anallytical processing，联合分析处理）。窗口函数是用来跨行计算的。普通的SQL聚合函数是只能用来处理计算一行内的结果，窗口函数可以跨行计算，并且把结果填到每行中

基本语法

<窗口函数> over (partition by <用于分组的列名>
            order by <用于排序的列名>)
1
2

<窗口函数>的位置可以放置两种函数：

1. 专用窗口函数：rank、dense_rank、row_number
2. 聚合函数：sum、avg、count、max、min

因为窗口函数是对where或者group by子句处理后的结果进行操作，所以窗口函数原则上只能写在select子句中

使用demo

select key1,key2,value
	rank() over (pratition by key2
                order by value desc) as rnk
from orders 
order by key1, rnk
1
2
3
4
5

函数

demo-1

如果想对每个班级内按照成绩排名：

得到如下结果

select *,
	rank() over (partition by 班级  -- rank()是排序的函数；同时对表分组
                order by 成绩 desc) as ranking   -- 对分组后的结果进行降序
from 班级表
1
2
3
4

关键字：

• rank() over
• partition by
• order by

demo-2

• 查询在整个公司的人员中，获取每个人的薪水在部分内的排名

select department, personId, sallary, 
      rank() over (Partition by department
                  order by sallary desc) as sallary_rank
order by department, sallary_rank
1
2
3
4

• 在整个公司的人员中，获取每个人的薪水在部门中的占比

select department, personId, sallery * 1.0 / sum(sallery) over (partition by department) as sallary_percentage
1

having语法

having用于过滤group by之后的聚合计算结果，where是在聚合计算之前过滤原始数据

order by语法

对输出结果进行排序

order by column [desc|asc]
1

实例demo

select avg(latency) as avg_latency, projectName 
group by projectName
having avg(latency) > 570000
order by avg_latency desc;
1
2
3
4

limit语法

用法

用于限制输出结果的行数

limit N   -- 取前N行数据
limit S, N  -- 从S行开始，取出N行数据
1
2

• 不支持将limit语法用于子查询内部
• 翻页的offset不能超过1,000,000，即S+N必须小于1，000，000
• N不能超过10,000

demo

• 只获取100行结果：

  * | select distinct(url) from log limit 100
  
  1
  2

• 获取0行到第999行的结果，共计1000行：

  * | select distinct(url) from log limit 0,1000
  
  1
  2

• 获取第1000行到第1999行的结果，共计1000行：

  * | select distinct(url) from log limit 1000,1000
  
  1
  2

case when语法

case when语法对连续数据进行归类，例如从http_user_agent中提取信息，归类成Android和ios两种

select case 
when http_user_agent like '%android%' then 'andriod'   -- case的两个判断语法
when http_user_agent like '%ios%' then 'ios'
else 'unknown' end  -- else语句，end
as http_user_agent,
	count(1) as pv
	group by http_user_agent      

1
2
3
4
5
6
7
8

计算不同延时区间的分布

select case
when latency < 10 then 's10'
when latency < 100 then 's100'
when latency < 1000 then 's1000'
when latency < 10000 then 's10000'
else 's_large' end
as latency_slot,
count(1) as pv
group by latency_slot

1
2
3
4
5
6
7
8
9
10

if语法

if语法逻辑上等同于case when语法

case 
	when condition then true_value
	[ else false_value]
end

1
2
3
4
5

• if(condition, true_value)

如果condition 是true，返回true_value，否则返回的是NULL

• if(condition, true_value, false_value)

如果condition 是true，返回true_value，否则返回的是false_value

coalesce语法

返回的是多个列的第一个非NULL值

coalesce(value1,value2,...)

1
2

nullif语法

如果value1和value2相等，返回的是NULL；否则返回的是value1

nullif(value1,value2)    -- 如果value1和value2相等，返回的是NULL；否则返回的是value1

1
2

二进制字符串函数

二进制字符串类型varbinary有别于字符串类型varchar

• || ：连接函数，等同于concat函数
• to/from_base64
• to/from_base64url

位运算

同比和环比函数

比和环比函数用于比较当前区间的计算结果和之前一个指定区间的结果

demo-1

计算当前1小时和昨天同一个时间段的PV比例

笔记：PV（PageView） 页面点击量，每次刷新就算一次浏览，多次打开同一页面会累加

开始时间为2020-03-25 14:00:00；结束时间为2020-03-25 15:00:00。

select compare(pv, 86400) from (select count(1) as pv from log)   -- 86400表示当前时间段减去86400秒

1
2

假设结果为：

[9.0,19.0,0.47368421052631579]
1

其中：

• 9：03-25的14:00:00到15:00:00的PV值
• 19：03-24的14:00到03-25的14:00的PV值
• 0.47368421052631579：表示当前时间段与之前时间段的比值

将数组展开成3列的写法为：

select diff[1], diff[2], diff[3] 
from select(compare(pv, 86400) as diff 
            from (select count(1) as pv 
                  from log))
1
2
3
4

比较函数和运算符

比较运算判断参数的大小，可以适用于任何可比类型，例如：int、bigint、double、text等

比较运算符

当用比较运算符比较两个值得时候，如果逻辑成立，则表示为True，否则返回False

范围运算符 BETWEEN

between用于判断一个参数的值是否在另外两个参数之间，范围为闭区间。

• 如果逻辑成立，则返回true；否则返回false。

  示例：SELECT 3 BETWEEN 2 AND 6;逻辑成立，返回true。

  以上样例等同于SELECT 3 >= 2 AND 3 <= 6;

• BETWEEN可以跟在not之后，用于相反逻辑的判断。

  示例：SELECT 3 NOT BETWEEN 2 AND 6;，逻辑不成立，返回false。

  以上样例等同于SELECT 3 < 2 OR 3 > 6;

• 如果三个参数中任何一个包含Null，则返回的结果为Null。

IS NULL 和 IS NOT NULL

用于判断参数是否为NULL值

GREATEST 和 LEAST

用于获取多列中的最大值或者最小值

select greatest(1,3,4)   -- 返回的是4
1

比较判断：all、any、some

比较判断用于判断参数是否满足条件

• all：用于判断参数是否满足条件
• any：用于判断是否满足条件之一
• some：判断参数是否满足条件之一，同any的用法相同

三者必须紧跟在比较运算符之后

看几个例子

select 'hello' = any (values 'hello', 'world')    -- true，只要满足一个
select 21 < all (values 19,20,21)  -- false，需要全部满足
select 42 >= some(select 41 union all select 42 union all select 43);  -- true
1
2
3

Lambda表达式

lambda匿名函数的基本形式为->

x -> x + 1
(x, y) -> x + y
x -> regexp_like(x, 'a+')
x -> x[1] / x[2]
x -> IF(x > 0, x, -x)
x -> COALESCE(x, 0)
x -> CAST(x AS JSON)
x -> x + TRY(1 / 0)
1
2
3
4
5
6
7
8

filter

基本格式：filter(array<T>, function<T,boolean) --->array<T>

select filter (array [], x -> true);   -- []
select filter (array [5, -6, null, 7], x -> x >0);  --[5,7]
select filter (array [5,null,7,null], x -> x is not null);  -- [5,7]
1
2
3

map_filter

从map中过滤数据，只获取满足function且返回true的元素对

基本语法格式：map_filter(map<K, V>, function<K, V, boolean>) → MAP<K,V>

SELECT map_filter(MAP(ARRAY[], ARRAY[]), (k, v) -> true); -- {}
SELECT map_filter(MAP(ARRAY[10, 20, 30], ARRAY['a', NULL, 'c']), (k, v) -> v IS NOT NULL); -- {10 -> a, 30 -> c}
SELECT map_filter(MAP(ARRAY['k1', 'k2', 'k3'], ARRAY[20, 3, 15]), (k, v) -> v > 10); -- {k1 -> 20, k3 -> 15}

1
2
3
4

reduce

基本语法格式为：reduce(array<T>, initialState S, inputFunction<S, T, S>, outputFunction<S, R>) → R

详细步骤为

1. 初始状态S
2. 遍历每个元素T
3. 计算inputFunction(S,T)，生成新状态S
4. 重复步骤2~步骤3，直到最后一个元素被遍历以及生成新状态
5. 利用最终状态S，获取最终输出结果R

select reduce(array [], (s, x) -> s+x, s -> s);   -- 0
select reduce(array [5,20,50], (s, x) -> s+x, s -> s);   -- 75
select reduce(array [5,20,null,50], (s, x) -> s+x, s -> s);   -- null，任何数加上NULL都是NULL
select reduce(array [5,20,null,50], (s, x) -> s + coalesce(x,0), s -> s);  -- 75; coalesce返回的是多个列的第一个非NULL值
1
2
3
4

transform

基本语法：transform(array<T>, function<T, U>) → ARRAY<U>

对数组中的每个元素，一次调用function，生成新的结果U

select transform(array [], x -> x+1);   --[]
select transform(array [5,6], x -> x+1);   -- [6,7]
select transform(array [5,null,6], x -> coalesce(x,0) + 1);  -- [6,1,7]
1
2
3

zip_with

基本语法格式：zip_with(array<T>, array<U>, function<T, U, R>) → array<R>

将两个array合并，根据元素T、U，通过函数生成新的array中的元素R

SELECT zip_with(ARRAY[1, 3, 5], ARRAY['a', 'b', 'c'], (x, y) -> (y, x)); --调换前后两个数组的元素位置，生成新的数组:[['a', 1], ['b', 3],['c', 5]]

SELECT zip_with(ARRAY[1, 2], ARRAY[3, 4], (x, y) -> x + y); -- 对应位置上的元素相加，结果[4, 6]

SELECT zip_with(ARRAY['a', 'b', 'c'], ARRAY['d', 'e', 'f'], (x, y) -> concat(x, y)); -- 把前后两个数组的元素拼接，生成新的字符串：['ad', 'be', 'cf']
1
2
3
4
5

逻辑函数

运算符

NULL参与逻辑运算符

重点标记的一种情况

join

SQL中常用的4个join用法

• inner join
• left (outer) join
• right (outer) join
• full outer join

牢记此图！

牢记此图！

牢记此图!

以下面的两个表格为例子：

mysql> SELECT * FROM Table_A ORDER BY PK ASC;
+----+---------+
| PK | Value   |
+----+---------+
|  1 | both ab |
|  2 | only a  |
+----+---------+
2 rows in set (0.00 sec)

mysql> SELECT * from Table_B ORDER BY PK ASC;
+----+---------+
| PK | Value   |
+----+---------+
|  1 | both ab |
|  3 | only b  |
+----+---------+
2 rows in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

inner join

内连接，将左右两个表中能够关联的数据连接起来，并返回出结果；PK为公共字段

必须要有共同的字段！！！

SELECT A.PK AS A_PK, B.PK AS B_PK,
       A.Value AS A_Value, B.Value AS B_Value
FROM Table_A A
INNER JOIN Table_B B
ON A.PK = B.PK;

1
2
3
4
5
6

left (outer) join

左连接，返回左边表的全部记录，不管右表中有没有关联的数据。在右表中找到的关联数据列一并返回

select A.PK as A_PK,B.PK as B_PK,   -- 字段取别名
       A.Value as A_Value, B.Value as B_Value
From Table_A A  -- 表取别名
left join Table_B B
on A.PK = B.PK;

1
2
3
4
5
6

right (outer) join

右连接，返回右边表的全部记录，不管左表中有没有关联的数据。在左表中找到的关联数据列一并返回

select A.PK as A_PK,B.PK as B_PK,   -- 字段取别名
       A.Value as A_Value, B.Value as B_Value
From Table_A A  -- 表取别名
right join Table_B B
on A.PK = B.PK;
1
2
3
4
5

full (outer) join

常被称作外连接，全连接。外连接查询能返回左右表里的所有记录，其中左右表里能关联起来的记录被连接后返回。

mysql中目前没有full join功能

unnest语法

背景

在复杂的业务场景下，日志数据的某一列可能会是较为复杂的格式，例如数组（array）、对象(map)、JSON等格式。对这种特殊格式的日志字段进行查询分析，可以使用unnest语法。

语法结构

unnest接收的是array或者map类型

如果输入的是字符串类型，要先转成json类型，再转化成array类型或者map类型，转化格式：

cast(json_parse(array_column) as array(biginit))
1

遍历数组的每个元素

1. 展开数组

select array_column, a from log,  -- 展开每个元素
unnest(cast(json_parse(array_column) as array(bigint))) as t(a)  -- 以t来命名新表，使用a来引用展开后的列
1
2

2. 数组求和

select sum(a) from log,   -- 求和
unnest(cast(json_parse(array_column) as array(bigint))) as t(a)
1
2

3. 利用 group by 进行分组

select a, count(1) from log,
unnest(cast(json_parse(array_num) as array(bigint))) as t(a)
group by a
1
2
3

遍历map

1. 遍历map中的元素

select map_column,a,b from log,
unnest(cast(json_parse(map_column) as map(varchar, bigint))) as t(a,b)

1
2
3

2. 按照map的key进行统计

select key, sum(value) from log,
unnest(cast(json_parse(map_column) as map(varchar, bigint))) as t(key, value)
group by key;
1
2
3

格式化显示histogram,numeric_histogram

histogram

histogram函数类似于 count group by 语法，其结果通常是一连串的json数据

select key, value from (select histogram(method) as his from log),
unnest(his) as t(key,value)
1
2

numeric_histogram

numeric_histogram语法是将数值列分配到多个桶中去，相当于是对数值列进行group by

select key,value from (select numeric_histogram(10, Latency) as his from log),
unnest(his) as t(key,value)
1
2

电话号码函数

电话号码函数提供对中国内地区域电话号码的归属地查询功能

函数列表

• mobile_province：省份
• mobile_city：城市
• mobile_carrier：运营商

demo

select mobile_city(try_cast("mobile" as bigint)) as "城市",   -- try_cast将字符串转成数值型
       mobile_province(try_cast("mobile" as bigint)) asd "省份",
       count(1) as "请求次数"
       group by "省份", "城市"
       order by "请求次数" desc 
       limit 1000
       )
1
2
3
4
5
6
7

一切看似逝去的，都不曾离开，你所给与的爱与温暖，让我执着地守护着这里。

尤而小屋，一个温馨的小屋。小屋主人，一手代码谋求生存，一手掌勺享受生活，欢迎你的光临