sql常用函数_sql怎么返回一行数据的第一个字段-CSDN博客

本文链接：https://blog.csdn.net/weixin_45896475/article/details/104025627

sql相关函数学习

AVG函数

定义和用法：
AVG 函数返回数值列的平均值。NULL 值不包括在计算中。
语法：

SELECT AVG(column_name) FROM table_name

注意：NULL值不参与运算

FIRST()函数

定义和用法：
FIRST() 函数返回指定的字段中第一个记录的值。
注释：只有 MS Access 支持 FIRST() 函数。
语法：
SELECT FIRST(column_name) FROM table_name
注意：
MySql的使用方式：

SELECT column_name FROM table_name
ORDER BY column_name ASC
LIMIT 1;
sqlServer的使用方式
SELECT TOP 1 column_name FROM table_name
ORDER BY column_name ASC;
Oracle的使用方式：
SELECT column_name FROM table_name
ORDER BY column_name ASC
WHERE ROWNUM <=1;

LAST()函数

定义和用法：
LAST() 函数返回指定的列中最后一个记录的值。
注释：只有 MS Access 支持 LAST() 函数。
语法：

SELECT LAST(column_name) FROM table_name;

注意：
MySql的使用方式：

1.	SELECT column_name FROM table_name
2.	ORDER BY column_name DESC
3.	LIMIT 1;

sqlServer的使用方式：

1.	SELECT TOP 1 column_name FROM table_name
2.	ORDER BY column_name DESC;

Oracle的使用方式：

1.	SELECT column_name FROM table_name
2.	ORDER BY column_name DESC
3.	WHERE ROWNUM <=1;

MAX()函数

定义和用法：
MAX() 函数返回指定列的最大值。
语法：

SELECT MAX(column_name) FROM table_name;

注意：
对字符型数据的最大值，是按照首字母由A～Z的顺序排列，越往后，其值越大。当然，对于汉字则是按照其全拼拼音排列的，若首字符相同，则比较下一个字符，以此类推。
当然，对与日期时间类型的数据也可以求其最大/最小值，其大小排列就是日期时间的早晚，越早认为其值越小。

MIN()函数

定义和用法：
MIN() 函数返回指定列的最小值。
语法：

SELECT MIN(column_name) FROM table_name

SUM()函数

定义和用法：
SUM() 函数返回数值列的总数。
语法：

SELECT SUM(column_name) FROM table_name;

注意：
将本列中的所有数值相加的总和，如果没有数值，则为0

GROUP BY函数

定义和用法：
GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。
语法：

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator =
GROUP BY column_name;

注意：
经常和sum()一起使用。

HAVING函数

定义和用法：
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。HAVING 子句可以让我们筛选分组后的各组数据。
语法：

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

注意：
用having就一定要和group by连用。
用group by不一有having （它只是一个筛选条件用的）。

UCASE()函数

定义和用法：
UCASE() 函数把字段的值转换为大写。
语法：

SELECT UCASE(column_name) FROM table_name;

注意：
sqlServer中用法：

SELECT UPPER(column_name) FROM table_name;

LCASE()函数

定义和用法：
LCASE() 函数把字段的值转换为小写。
语法：

SELECT LCASE(column_name) FROM table_name;

注意：
sqlServer中用法：

SELECT LOWER(column_name) FROM table_name;

MID()函数

定义和用法：
MID() 函数用于从文本字段中提取字符。
语法：

SELECT MID(column_name,start,[length]) FROM table_name;

注意：
Oracle数据库中没有此类函数，但是有一个subStr(“列名”，开始字符位置（起始值是 1），长度)功能相似

select substr(("列名"，a,b) from <table_name>;

开始字符位置（起始值是 1）

substr()函数

定义: substr函数是用来截取数据库某一列字段中的一部分。
用法 :

SBUSTR(str,pos);

就是从pos开始的位置，一直截取到最后。
实例

LEN()函数

定义和用法：
LEN() 函数返回文本字段中值的长度。
语法：

SELECT LEN(column_name) FROM table_name;

注意：
MySql中的为LENGTH()：

SELECT LENGTH(column_name) FROM table_name;

ROUND()函数

定义和用法：
ROUND() 函数用于把数值字段舍入为指定的小数位数。
语法：

SELECT ROUND(column_name,decimals) FROM table_name;

column_name 必需。要舍入的字段。
decimals 必需。规定要返回的小数位数。
注意：

NOW()函数

定义和用法：
NOW() 函数返回当前系统的日期和时间
语法：

SELECT NOW() FROM table_name;

注意：

FORMAT()函数

定义和用法：
FORMAT() 函数用于对字段的显示进行格式化。
语法：

SELECT FORMAT(column_name,format) FROM table_name;

column_name 必需。要格式化的字段。
format 必需。规定格式。

over()函数

over不能单独使用，要和分析函数：rank(),dense_rank(),row_number()等一起使用。
其参数：over（partition by columnname1 order by columnname2）
含义：按columname1指定的字段进行分组排序，或者说按字段columnname1的值进行分组排序。
例如：employees表中，有两个部门的记录：department_id ＝10和20

select department_id，rank（） over（partition by department_id order by salary) from employees

就是指在部门10中进行薪水的排名，在部门20中进行薪水排名。如果是partition by org_id，则是在整个公司内进行排名。
以下是个人见解：
sql中的over函数和row_numbert()函数配合使用，可生成行号。可对某一列的值进行排序，对于相同值的数据行进行分组排序。
执行语句：

select row_number() over(order by AID DESC) as rowid,* from bb

order by()函数

order by 是用在where条件之后，用来对查询结果进行排序
order by 字段名 asc/desc
asc 表示升序（默认为asc，可以省略）
desc表示降序
order by 无法用于子查询，否则会报错：除非另外还指定了 TOP 或 FOR XML，否则，ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。

报错提示简单明了

在子查询使用order by小技巧：在select后加top n，但是如果不知道结果大概行数，可以用percent（top 100 percent：百分之一百），top后可放百分比：

select * from （select top 100 percent * from table order by id desc）

date_add与date_sub函数

在mysql中date_add与date_sub函数分别date_add是对日期加减操作而date_sub是从日期减去指定的时间间隔了，下面我来介绍几个实例大家一起看看
mysql 中 DATE_ADD(date,INTERVAL expr type) 和 DATE_SUB(date,INTERVAL expr type)
这些函数执行日期运算。 date 是一个 DATETIME 或DATE值，用来指定起始时间。 expr 是一个表达式，用来指定从起始日期添加或减去的时间间隔值。 Expr是一个字符串;对于负值的时间间隔，它可以以一个 ‘-’开头。 type 为关键词，它指示了表达式被解释的方式。
关键词INTERVA及 type 分类符均不区分大小写。

1.	DATE_ADD(date,INTERVAL expr type)
2.	DATE_SUB(date,INTERVAL expr type)

concat行转列函数

定义: CONCAT函数用于将两个字符串连接起来，形成一个单一的字符串
用法示例:

SELECT * FROM employee_tbl;
+------+------+------------+--------------------+
| id   | name | work_date  | daily_typing_pages |
+------+------+------------+--------------------+
|    1 | John | 2007-01-24 |                250 |
|    2 | Ram  | 2007-05-27 |                220 |

现在，假设根据上述表要连接名员工ID和work_date，那么你可以使用下面的命令：

SQL> SELECT concat(id, name, work_date)
    -> FROM employee_tbl;
+-----------------------------+
| CONCAT(id, name, work_date) |
+-----------------------------+
| 1John2007-01-24             |
| 2Ram2007-05-27              |

Concat_ws原名concat with separator(合并和分离)
语法为:concat_ws(separator,str1,str2,…)是concat()的特殊格式,他的第一个参数是其他参数的分隔符
例如:

select concat_ws(‘|’,id,name)as con from info返回结果过为
+----------+
| con      |
+----------+
| 1_BioCyc |
+----------+

collect行转列函数

定义:在sql中关于collect的函数有两个分别是collect_list和collect_set,不同的是collect_list不去重而collect_set去重
用法例子:
Name movie date
张三盗梦空间 20181516
李四天下无贼 20181516
张三神探狄仁杰 20181516
李四霸王别姬 20181516
李四霸王别姬 20181516
需求: 按用户分组，取出每个用户每天看过的所有视频的名字：
查询:

select name,collect_list(movie) from table group by name;

Name movie
张三盗梦空间,神探狄仁杰
李四天下无贼,霸王别姬,霸王别姬
但是上面的查询结果有点问题，因为霸王别姬实在太好看了，所以李四这家伙看了两遍，这直接就导致得到的观看过视频列表有重复的，所以应该增加去重，使用collect_set，其与collect_list的区别就是会去重：

 Select name,collect_set(movie) from table group by name ;
Name movie

张三盗梦空间,神探狄仁杰
李四天下无贼,霸王别姬
我们也可以突破group by 的限制

Select name,collect set(movie)[1] from table group by name;
Name movie

张三盗梦空间
李四天下无贼

explode炸裂函数和lateral view 拼接函数(列转行)

数据准备table name= movie_info
movie.txt
《疑犯追踪》悬疑,动作,科幻,剧情
《Lie to me》悬疑,警匪,动作,心理,剧情
《战狼2》战争,动作,灾难
次数据因为第二列为复杂结构所以尽量在hive中创建表,这样可以将第二列创建为一个数组array

创建空表格:

create table movie(name string,  category array<string>) 
row format delimited fields terminated by "\t"
collection items terminated by ",";

load data local inpath "/hive/movie.txt" into table movie_info;

a.) 将数据炸开用explode(装复杂数组的列名)函数炸裂!

Select explode (category) from movie_info;

b.)用lateral view隐藏的拼接条件将炸开的数据连接(他自己会知道他自己是一行的)

select name,ty  from movie_info lateral view explode(category) t as  ty;