SQL函数学习记录

聚合函数

函数是编程语言的基础之一，在对数字的运算中，我们用的最多的就是聚合函数，本篇接下来就详细阐述下SQL中聚合函数的运用。

什么是聚合函数（aggregate function）？

聚合函数指的是对一组值执行计算并返回单一的值。

常见聚合函数：

• 求个数、记录条数的count

select conut(*)/count(1) from 表名;

count_if函数

count_if(boolean expression) //参数值为布尔表达式，返回为interge；

eg：

统计request_uri字段的值是以file-0结尾的日志条数。

SELECT count_if(request_uri like'%file-0') AS count from 表名;

• 求合计数的sum

select sum(age) from 表名;

• 求平均数的avg

select avg(age) from 表名;

• 求最大值、最小值的max()，min()

select max(age) from 表名;

select min(age) from 表名;

附：max函数

max(x) 查询x中的最大值；

max(x,n) 查询x中最大的n个值，返回为数组；

eg：SELECT max(request_time,10) AS "top 10" from 表名;

mysql语法：SELECT max(money,3) AS "top 3" from TableC order by money desc;

利用如下数据库进行演示：

select * from TableC;

（1）分别算出各部门的人数、总业绩、平均业绩；

SELECT bm,count(name) 人数,sum(money) 总业绩,avg(money) 平均业绩 from TableC group by bm;

（2）公司要对各部门业绩最好的员工进行奖励，现在需要统计出每个部门业绩最好的员工信息，并展示业绩是多少；

select a.* from TableC a inner join (select bm,max(money) as money from TableC group by bm) b on a.bm=b.bm and a.money=b.money;

//通过部门和money列联系起来的，inner join 表a 表b on 联系条件

（3）显示总money超过300w的部门业绩明细；

select a.*from TableC a where bm in (select bm from TableC group by bm having sum(money)>300);

补充下与group by 联用的order by ，再提个用法，针对上面的例子，要查全公司里，业绩前三的员工及业绩，代码如下：

select top 3 * from TableC order by money desc;

//desc 降序排列,mysql不支持top命令；

mysql语法：

select * from TableC order by money desc LIMIT 3;

HAVING语句

Having语句是对分组之后的数据进行过滤，因此使用having前通常会使用group by；

select a.*from TableC a where bm in (select bm from TableC group by bm having sum(money)>300);

GROUP BY 语句

结合聚合函数，根据一个或多个列对分析结果进行分组。还支持搭配rollup，cube，groupping sets语句扩展分组功能。

• group by ：对分析结果进行分组；

语法：

SELECT 
 key,
  ...
 aggregate function 
 GROUP BY 
 key,...
	 
//参数解释：
	  key：日志字段名称或聚合函数结果列，支持单列或多列；
	  aggregate function：聚合函数。GROUP BY子句常与min、max、avg、sum、count等聚合函数       搭配使用。

示例：统计不同状态码对应的请求次数：

select status count(*) AS PV group by status;

示例：按照每小时的时间粒度计算网站访问PV。其中，__time__字段为日志服务中的保留字段，表示时间列。time为date_trunc('hour', __time__)的别名。date_trunc函数的更多信息，请参见date_trunc函数。

SELECT
  count(*) AS PV,
  date_trunc('hour', __time__) AS time
GROUP BY
  time
ORDER BY
  time
LIMIT
  1000                       

date_trunc函数

date_trunc函数会根据您指定的时间单位截断日期和时间表达式，并按照毫秒、秒、分钟，小时、日、月或年对齐。该函数常用于需要按照时间进行统计分析的场景。

语法：

date_trunc(unit, x)
	
//--unity：时间单位，取值为millisecond、second、minute、hour、day、week、month、quarter、year。请参见unit说明。
//--x：参数值为日期和时间表达式。

date_trunc函数只能按照固定的时间间隔统计（例如每分钟、每小时等）。如果您需要按照灵活的时间维度统计，请使用数学取模方法进行分组，例如统计每5分钟的数据。

SELECTcount(1) AS pv, __time__ - __time__ %300AStimeGROUPBYtime LIMIT 100;

示例：

按照每分钟的时间粒度计算请求时间的平均值，并按照时间进行分组和排序。

SELECT
  date_trunc('minute', __time__) AS time,
  truncate (avg(request_time)) AS avg_time,
  current_date AS date
GROUP BY time
ORDER BY time DESC
LIMIT 100;

SPLIT函数/SPLIT_PART函数

split函数通过指定的分隔符拆分字符串，并返回拆分后的子串集合。

split_part函数通过指定的分隔符拆分字符串，并返回指定位置的内容。

语法：

split (x,delimeter,limit)

split_part(x,delimiter,part)

示例：

select split(runoob_authour,'/',3) from runoon_tbl where runoob_id='19';

SELECT count(*), split_part(runoob_author, '?', 1) from runoon_tbl where runoob_id='19';

split_to_map函数

split_to_map函数用于使用指定的第一个分隔符拆分字符串，然后再使用指定的第二个分隔符进行第二次拆分。

语法：

split_to_map(x, delimiter01, delimiter02)

示例：

使用英文逗号（,）和英文冒号（:）拆分time字段的值，返回结果为MAP类型。

time:upstream_response_time:"80", request_time:"40"

select split_to_map(time,',',':') from xxxx

日期和时间函数

• current_date函数

语法：current_date //返回当前日期，格式为YYY-MMM-DDD;

示例：

• current_time函数

语法：current_time //返回当前日期和时区；

• current_timesamp函数

语法：current_timesamp //返回当前日期、时间和时区；

• current_timezone函数

语法：current_timezone（） //返回当前时区；

• date函数

语法：date（x） //返回当前日期和时间中的日期部分；

• localtime函数

语法：localtime //返回本地时间；

• localtimestamp函数

语法：localtimestamp //返回本地日期和时间；

• now函数

语法：now（） //返回当前日期和时间，同current_timesamp函数

• day函数=day_of_month函数

语法：day（x） 或 day_of_month（x） //提取日期和时间表达式中的天数，按月计算。

• day_of_week函数=dow函数,doy函数=day_of_year函数

语法：day_of_week(),dow(),doy(),day_of_year() //提取日期和时间表达式中的天数，按周或年计算。

• date_add函数

语法：date_add(unit,N,x) //在x上加伤N个时间单位。

sql拆分

SQL 一行转多行的处理方案_sql一行拆分成多行-CSDN博客

--存量域名查询sql
SELECT COUNT(newlist) as newnum,newlist FROM(
SELECT  
-- explode(list) as newlist 
EXPLODE(split(list, ';')) as newlist
FROM    afscdm.dwd_sec_evt_cloudshell_oneagent_white_domain_alert_hi
WHERE  dt>'20220811'and dt<'20220819'
--and hour="12"
LIMIT 9999) group by newlist 
HAVING 
newlist not like "%ping%" AND   newlist not like "%onlinedown%" 
AND   newlist NOT like  "%svchost%"
order by newnum DESC limit 10;

odps/Hive中一行转多行

• explode（）内置函数

接收一个array或map类型的数据作为输入（用split函数生成数组），然后将array或map里面的元素按照每行的形式输出。

• split（）函数

split（）通过指定分隔符对字符串进行切片，如果参数num有指定值，则只分割n+1个子字符串。

语法：str.split(str=" ",num=string.count(str))

参数介绍：

• • sep--可选参数，指定分隔符，默认为所有的空字符，包括空格" ",换行\n，制表\t等。
  • count--可选参数，分割次数，默认为空字符在字符串中出现的次数。

使用案例：

str = "this is string example....wow!!!"
print(str.split())
print(str.split("i", 1))
print(str.split("w"))

# 结果为
# ['this', 'is', 'string', 'example....wow!!!']
# ['th', 's is string example....wow!!!']
# ['this is string example....', 'o', '!!!']

@@@@@@@@@@@@@@以#为分隔符，指定第二个参数为2，返回3个参数列表@@@@@@@@@@@@@@@@@@@@@@@@@
	
tst = "Google#Runoob#Taobao#Facebook"
print(tst.split("#", 2))

# 结果为
# ['Google', 'Runoob', 'Taobao#Facebook']

附：

创建表：create table TableC (gh varchar(8),name varchar(8),bm varchar(8),money int(255));

插入表数据：insert into TableC values ('003','何三','销售1部',150);