高级数据分析师必备SQL常用处理函数，我觉得收藏是必须的
sinye56 2024-09-22 08:26 6 浏览 0 评论
之前的时候讲过一个关于MySQL的相关文章，当时因为篇幅的原因，那里面只讲了基础的sql语句以及基础的函数应用，但是在MySQL里面，除了基础的函数之外，如果没有点高级的，或者说更加方便的操作的话，他怎么被程序员如此欢迎的呢，那今天，我就把后面一部分给大家整理出来，认真细细的查看哦，看完了自己去实践一下，实践出真知，这是我一贯坚持的标准
好啦，话不多说，来看重点
字符串处理以及条件查询
既然说是高级的操作，今天我们就从条件查询，字符串处理以及子查询等方面入手，开始我们今天的篇章
IF条件查询
#if的语法
IF(expr1,expr2,expr3)
#示例
SELECT IF(sva=1,"男","女") AS s FROM table_name WHERE sva != '';CASE WHEN条件查询
case when 可以实现if函数的功能，同时也可以联合各类聚合函数使用。
# case when也可以实和if一样的功能?
SELECT CASE WHEN sva=1 THEN '男' ELSE '女' END AS s FROM table_nameWHERE sva != '';
#case when可以联合聚合函数等使用?
SELECT count(DISTINCT CASE
WHEN sva=1 THEN 'id'
ELSE 'null'
END) AS s
FROM TABLE_NAMEWHERE sva != '';文本处理
/*SUBSTR()字符串截取
substr语法详解：
substr(strings|express,m,[n])
strings|express ：被截取的字符串或字符串表达式
m 从第m个字符开始截取
n 截取后字符串长度为n*/
select substr('abcdefg',3,4) from dual;
# 结果是cdef
select substr('abcdefg',-3,4) from dual;
# 结果efg
select substr('abcde',2),substr('abcde',-2),substr('abcde',2,3),substr('abcdewww',-7,3) from dual;
# 结果是bcde、de、bcd、bcd字符串拼接
1.使用特殊操作符拼接
#ACESS和SQL Serve使用+
SELECT vend_name + ' (' + vend_country + ')'FROM VendorsORDER BY vend_name;
#DB2,Oracle, PostgreSQL,SQLite ,Open Office Base使用||
SELECT vend_name || ' (' || vend_country || ')' FROM VendorsORDER BY vend_name;2.CONCAT()函数拼接
SELECT Concat(vend_name, ' (', vend_country, ')') FROM Vendors ORDER BY vend_name; SPLIT()字符串分割
语法结构
split(str, regex) - Splits
str：需要分割的字符
regex：以什么符号进行分割
1.基本用法
select substr('abcdefg',3,4) from dual;
# 结果是cdef
select substr('abcdefg',-3,4) from dual;
# 结果efg
select substr('abcde',2),substr('abcde',-2),substr('abcde',2,3),substr('abcdewww',-7,3) from dual;
# 结果是bcde、de、bcd、bcd篇幅原因，这些就展示着一些函数，我们接着看重点
取百分比
求近似的第pth个百分位数，p必须介于0和1之间，返回类型为double，但是col字段支持浮点类型。参数B控制内存消耗的近似精度，B越大，结果的准确度越高。默认为10,000。当col字段中的distinct值的个数小于B时，结果为准确的百分位数。
#percentile()
/*
语法格式：
percentile_approx(DOUBLE col, p ,[B])) 近似中位数函
percentile(DOUBLE col, p ) 中位函数
前者多了一个参数B，后者无参数，其余语法一致。
*/
select percentile(mmr,0.3) as 30_percentile,percentile_approx(mmr,0.5) 50_percentilefrom match_table限制行数
#ACESS和SQL Serve使用+
SELECT vend_name + ' (' + vend_country + ')'FROM VendorsORDER BY vend_name;
#DB2,Oracle, PostgreSQL,SQLite ,Open Office Base使用||
SELECT vend_name || ' (' || vend_country || ')' FROM VendorsORDER BY vend_name;格式化显示
SELECT Concat(vend_name, ' (', vend_country, ')') FROM Vendors ORDER BY vend_name; 接下来是重中之重，也是我们在日常的工作中经常使用的一个查询方式
子查询
#子查询条件过滤
SELECT cust_id FROM Orders
WHERE order_numIN (
SELECT order_numFROM OrderItemsWHERE prod_id = 'RGAN01');
#2.子查询作为计算字段
SELECT cust_name, cust_state,
(SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id) AS orders FROM CustomersORDER BY cust_name;
时间函数
看完上面的查询之后，在我们日常的生活中，还有一个更重要的因素，时间，那对于时间，数据库又提供了那些相应的操作呢，接下来我们就着重讲解SQL中的时间函数，包括常用的时间函数，时间提取函数以及时间计算函数。
to_date()函数
to_date()函数将字符串的日期转换为时间格式的日期，转换后的时间格式可以是如下形式：
具体示例如下：
SELECT TO_DATE('2006-05-01 19:25:34', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL
SELECT TO_DATE('2006-05-01 19:25', 'YYYY-MM-DD HH24:MI') FROM DUAL
SELECT TO_DATE('2006-05-01 19', 'YYYY-MM-DD HH24') FROM DUAL
SELECT TO_DATE('2006-05-01', 'YYYY-MM-DD') FROM DUAL
SELECT TO_DATE('2006-05', 'YYYY-MM') FROM DUAL
SELECT TO_DATE('2006', 'YYYY') FROM DUALcurrent_date()/current_time()/current_timestamp()
返回当前时间,某年某月某日，current_time()等同于current_date。current_timestamp()相当于now(),返回当前的时间戳。
SELECT CURDATE()
# 返回值：# '2020-10-09'?时间提取
时间提取函数包括year(),month(),day(),hour(),minute(),second()等，其用法都是一直的，这些函数分别返回 date 的年份，月份，日期，时间等，范围为1000-9999。当 date 为0时，返回0。
具体示例如下：
SELECT YEAR('98-02-03')
# 返回值 1998
SELECT MONTH('98-02-03')
# 返回值 02
SELECT DAY('98-02-03')
# 返回值 3时间计算函数
/*DATEDIFF()
语法：DATEDIFF(expr1,expr2)
DATEDIFF()将返回expr1 - expr2的值，用来表示两个日期相差的天数。
expr1 和 expr2 都是日期或日期时间表达式。运算中只用到了这些值的日期部分。*/
SELECT DATEDIFF('1997-12-31 23:59:59','1997-12-30')
#返回结果1date_sub()/date_add()
语法格式：
DATE_ADD(date,INTERVAL expr unit)
DATE_SUB(date,INTERVAL expr unit)执行日期计算的两种函数。date 是一个用来指定开始日期的 DATETIME 或 DATE 值。expr 是一种以字符串形式呈现的表达式，用来指定从开始日期增加或减少的间隔值。如果是负的间隔值，则 expr 值的第一个字符是-号。unit 是一个单位关键字，用来指定expr表达式应该采取的单位。
下表列出了每个单位数值所对应的 expr 参数的期望格式。
时间戳
时间戳是从1970年1月1日（UTC/GMT的午夜）开始所经过的秒数，不考虑闰秒。
UNIX时间戳的0按照ISO 8601规范为 ：1970-01-01T00:00:00Z.
一个小时表示为UNIX时间戳格式为：3600秒；一天表示为UNIX时间戳为86400秒，闰秒不计算。
在大多数的UNIX系统中UNIX时间戳存储为32位，这样会引发2038年问题或Y2038。UNIX_TIMESTAMP(date)
若无参数调用，则返回一个 Unix timestamp ('1970-01-01 00:00:00' GMT 之后的秒数) 作为无符号整数，得到当前时间戳 。
若用date 来调用 UNIX_TIMESTAMP()，它会将参数值以'1970-01-01 00:00:00' GMT后的秒数的形式返回。date 可以是一个 DATE 字符串、一个 DATETIME字符串、一个 TIMESTAMP或一个当地时间的YYMMDD或YYYMMDD格式的数字。
例如:
SELECT UNIX_TIMESTAMP() ; （执行时的时间:2009-08-06 10:10:40）# 1249524739
SELECT UNIX_TIMESTAMP('2009-08-06') ;# 1249488000from_unixtime(）
from_unixtime(t1,’yyyy-MM-dd HH:mm:ss’)
#其中t1是10位的时间戳值，即1970-1-1至今的秒，而13位的所谓毫秒的是不可以的。 对于13位时间戳，需要截取，然后转换成bigint类型，因为from_unixtime类第一个参数只接受bigint类型。
例如：
select from_unixtime(cast(substring(tistmp,1,10) as bigint),’yyyy-MM-dd HH’) tim ,count(*) cnfrom ttengine_hour_data where …
SELECT from_unixtime(cast(ts AS bigint))FROM temptable开窗函数
这是最后了，也是最难理解，但是在应用中最常用的技术，或者说是程序员在日常开发的工作过程中，帮助最大的函数之一，那就是开窗函数，我自己都没想到，在日常的工作中，这个函数类别会对我的帮助这么大，真的是需要好好的学习一下的
在数据分析中，窗口函数是我们经常用到的函数，今天这里连数据集带实际操作源码，我们来看一下开窗函数怎么使用
一 创建数据集
from pyspark.sql import SprkSession
import pandas as pd
spark = SparkSession.builder.appName('Windowfunction').enableHiveSupport().getOrCreate()
import pyspark.sql.functions
# 原始数据
test = spark.createDataFrame([('001','1',100,87,67,83,98), ('002','2',87,81,90,83,83), ('003','3',86,91,83,89,63),
('004','2',65,87,94,73,88), ('005','1',76,62,89,81,98), ('006','3',84,82,85,73,99),
('007','3',56,76,63,72,87), ('008','1',55,62,46,78,71), ('009','2',63,72,87,98,64)],
['number','class','language','math','english','physic','chemical'])
#查看原始数据
test.show()
#将原始数据存入中间表
test.createOrReplaceTempView('test_temp_table')
number|class|language|math|english|physic|chemical|
+------+-----+--------+----+-------+------+--------+
| 001| 1| 100| 87| 67| 83| 98|
| 002| 2| 87| 81| 90| 83| 83|
| 003| 3| 86| 91| 83| 89| 63|
| 004| 2| 65| 87| 94| 73| 88|
| 005| 1| 76| 62| 89| 81| 98|
| 006| 3| 84| 82| 85| 73| 99|
| 007| 3| 56| 76| 63| 72| 87|
| 008| 1| 55| 62| 46| 78| 71|
| 009| 2| 63| 72| 87| 98| 64|
+------+-----+--------+----+-------+------+--------+
#将数据转换为长数据
# 逆透视Unpivot
test_long =test.selectExpr("`number`","`class`",
"stack(5, 'language', `language`,'math', `math`, 'english', `english`, 'physic', `physic`,'chemical', `chemical`) as (`subject`,`grade`)").orderBy(["`class`", "`number`"])
?
test_long.show()
test_long.createOrReplaceTempView('test_long_temp_table')
?
+------+-----+--------+-----+
|number|class| subject|grade|
+------+-----+--------+-----+
| 001| 1| english| 67|
| 001| 1|language| 100|
| 001| 1| physic| 83|
| 001| 1| math| 87|
| 001| 1|chemical| 98|
| 005| 1|chemical| 98|
| 005| 1| english| 89|
| 005| 1| physic| 81|
| 005| 1| math| 62|
| 005| 1|language| 76|
| 008| 1| physic| 78|
| 008| 1| math| 62|
| 008| 1|chemical| 71|
| 008| 1|language| 55|
| 008| 1| english| 46|
| 002| 2|language| 87|
| 002| 2| math| 81|
| 002| 2| physic| 83|
| 002| 2|chemical| 83|
| 002| 2| english| 90|
+------+-----+--------+-----+
only showing top 20 rows二 聚合函数
聚合函数也可用于窗口函数当中，用法和专用窗口函数相同。
聚合函数sum、avg、count、max、min都是针对自身记录以及自身记录以上的所有数据进行计算的。
聚合函数作为窗口函数，可以在每一行的数据里直观看到截止到本行数据，统计数据是多少，比如：按照时间的顺序，计算各时期的销售总额就需要用到这种累计的统计方法。同时也可以看出每一行数据对整体数据的影响。聚合函数的开窗和专用的窗口函数是一致的，其形式为：
?窗口函数? over (partition by ?用于分组的列名? order by ?用于排序的列名?)
聚合函数的窗口函数中，加不加order by,order by的列名是否是用于分组的列名，这些情况都会影响到最终的结果，下面我们分别来讨论各种不同的情况。
2.1 窗口函数有无order by的区别
2.1.1 有order by且order by的字段不是用于分组的字段
这种情况下得到的结果是每个partition的累加的结果
test_sum=spark.sql("""
select *,sum(grade)over(partition by class,subject order by number) total_grade,
avg(grade)over(partition by subject,class order by number) avg_grade,
count(grade)over(partition by subject,class order by number) total_classmate,
max(grade)over(partition by subject,class order by number) max_grade,
min(grade)over(partition by subject,class order by number) min_grade
from test_long_temp_table
""")
test_sum.show()
?
number|class| subject|grade|total_grade| avg_grade|total_classmate|max_grade|min_grade|
+------+-----+--------+-----+-----------+-----------------+---------------+---------+---------+
| 001| 1| english| 67| 67| 67.0| 1| 67| 67|
| 005| 1| english| 89| 156| 78.0| 2| 89| 67|
| 008| 1| english| 46| 202|67.33333333333333| 3| 89| 46|
| 002| 2| english| 90| 90| 90.0| 1| 90| 90|
| 004| 2| english| 94| 184| 92.0| 2| 94| 90|
| 009| 2| english| 87| 271|90.33333333333333| 3| 94| 87|
| 003| 3|chemical| 63| 63| 63.0| 1| 63| 63|
| 006| 3|chemical| 99| 162| 81.0| 2| 99| 63|
| 007| 3|chemical| 87| 249| 83.0| 3| 99| 63|
| 003| 3| math| 91| 91| 91.0| 1| 91| 91|
| 006| 3| math| 82| 173| 86.5| 2| 91| 82|
| 007| 3| math| 76| 249| 83.0| 3| 91| 76|
| 001| 1| math| 87| 87| 87.0| 1| 87| 87|
| 005| 1| math| 62| 149| 74.5| 2| 87| 62|
| 008| 1| math| 62| 211|70.33333333333333| 3| 87| 62|
| 002| 2| math| 81| 81| 81.0| 1| 81| 81|
| 004| 2| math| 87| 168| 84.0| 2| 87| 81|
| 009| 2| math| 72| 240| 80.0| 3| 87| 72|
| 003| 3| physic| 89| 89| 89.0| 1| 89| 89|
| 006| 3| physic| 73| 162| 81.0| 2| 89| 73|
+------+-----+--------+-----+-----------+-----------------+---------------+---------+---------+
only showing top 20 rows2.1.2 有order by且order by的字段是用于分组的字段
该情况下得到的数据是每个partition的总和而不是累加
test_sum_1=spark.sql("""
select *,
sum(grade)over(partition by subject,class order by class) total_grade,
avg(grade)over(partition by subject,class order by class) avg_grade,
count(grade)over(partition by subject,class order by class) total_classmate,
max(grade)over(partition by subject,class order by class) max_grade,
min(grade)over(partition by subject,class order by class) min_grade
from test_long_temp_table
""")
test_sum_1.show()
?
number|class| subject|grade|total_grade| avg_grade|total_classmate|max_grade|min_grade|
+------+-----+--------+-----+-----------+-----------------+---------------+---------+---------+
| 003| 3| physic| 89| 234| 78.0| 3| 89| 72|
| 006| 3| physic| 73| 234| 78.0| 3| 89| 72|
| 007| 3| physic| 72| 234| 78.0| 3| 89| 72|
| 002| 2| physic| 83| 254|84.66666666666667| 3| 98| 73|
| 004| 2| physic| 73| 254|84.66666666666667| 3| 98| 73|
| 009| 2| physic| 98| 254|84.66666666666667| 3| 98| 73|
| 003| 3|chemical| 63| 249| 83.0| 3| 99| 63|
| 006| 3|chemical| 99| 249| 83.0| 3| 99| 63|
| 007| 3|chemical| 87| 249| 83.0| 3| 99| 63|
| 003| 3| math| 91| 249| 83.0| 3| 91| 76|
| 006| 3| math| 82| 249| 83.0| 3| 91| 76|
| 007| 3| math| 76| 249| 83.0| 3| 91| 76|
| 001| 1| english| 67| 202|67.33333333333333| 3| 89| 46|
| 005| 1| english| 89| 202|67.33333333333333| 3| 89| 46|
| 008| 1| english| 46| 202|67.33333333333333| 3| 89| 46|
| 002| 2| math| 81| 240| 80.0| 3| 87| 72|
| 004| 2| math| 87| 240| 80.0| 3| 87| 72|
| 009| 2| math| 72| 240| 80.0| 3| 87| 72|
| 002| 2|language| 87| 215|71.66666666666667| 3| 87| 63|
| 004| 2|language| 65| 215|71.66666666666667| 3| 87| 63|
+------+-----+--------+-----+-----------+-----------------+---------------+---------+---------+
only showing top 20 rows2.1.3 有partition by无order by
该情况下，sum()over()得到的数据是每个partition的总和而不是累加，和第二种情况是一致的。
test_sum_2=spark.sql("""
select *,
sum(grade)over(partition by subject,class) total_grade,
avg(grade)over(partition by subject,class) avg_grade,
count(grade)over(partition by subject,class) total_classmate,
max(grade)over(partition by subject,class ) max_grade,
min(grade)over(partition by subject,class) min_grade
from test_long_temp_table
""")
test_sum_2.show()
?
number|class| subject|grade|total_grade| avg_grade|total_classmate|max_grade|min_grade|
+------+-----+--------+-----+-----------+-----------------+---------------+---------+---------+
| 003| 3| physic| 89| 234| 78.0| 3| 89| 72|
| 006| 3| physic| 73| 234| 78.0| 3| 89| 72|
| 007| 3| physic| 72| 234| 78.0| 3| 89| 72|
| 002| 2| physic| 83| 254|84.66666666666667| 3| 98| 73|
| 004| 2| physic| 73| 254|84.66666666666667| 3| 98| 73|
| 009| 2| physic| 98| 254|84.66666666666667| 3| 98| 73|
| 003| 3|chemical| 63| 249| 83.0| 3| 99| 63|
| 006| 3|chemical| 99| 249| 83.0| 3| 99| 63|
| 007| 3|chemical| 87| 249| 83.0| 3| 99| 63|
| 003| 3| math| 91| 249| 83.0| 3| 91| 76|
| 006| 3| math| 82| 249| 83.0| 3| 91| 76|
| 007| 3| math| 76| 249| 83.0| 3| 91| 76|
| 001| 1| english| 67| 202|67.33333333333333| 3| 89| 46|
| 005| 1| english| 89| 202|67.33333333333333| 3| 89| 46|
| 008| 1| english| 46| 202|67.33333333333333| 3| 89| 46|
| 002| 2| math| 81| 240| 80.0| 3| 87| 72|
| 004| 2| math| 87| 240| 80.0| 3| 87| 72|
| 009| 2| math| 72| 240| 80.0| 3| 87| 72|
| 002| 2|language| 87| 215|71.66666666666667| 3| 87| 63|
| 004| 2|language| 65| 215|71.66666666666667| 3| 87| 63|
+------+-----+--------+-----+-----------+-----------------+---------------+---------+---------+
only showing top 20 rows2.2 窗口函数的平均移动
select *, avg(成绩) over (order by 学号 rows 2 preceding) as current_avg from 班级表;
rows和preceding这两个关键字，是“之前~行”的意思，上面的句子中，是之前2行。也就是得到的结果是自身记录及前2行的平均（相对应的preceding是following）
test_preceding=spark.sql("""
select *,
sum(grade)over(partition by subject order by number rows 2 preceding) total_preceding_grade
from test_long_temp_table
""")
test_preceding.show()
?
number|class| subject|grade|total_preceding_grade|
+------+-----+--------+-----+---------------------+
| 001| 1| physic| 83| 83|
| 002| 2| physic| 83| 166|
| 003| 3| physic| 89| 255|
| 004| 2| physic| 73| 245|
| 005| 1| physic| 81| 243|
| 006| 3| physic| 73| 227|
| 007| 3| physic| 72| 226|
| 008| 1| physic| 78| 223|
| 009| 2| physic| 98| 248|
| 001| 1|chemical| 98| 98|
| 002| 2|chemical| 83| 181|
| 003| 3|chemical| 63| 244|
| 004| 2|chemical| 88| 234|
| 005| 1|chemical| 98| 249|
| 006| 3|chemical| 99| 285|
| 007| 3|chemical| 87| 284|
| 008| 1|chemical| 71| 257|
| 009| 2|chemical| 64| 222|
| 001| 1|language| 100| 100|
| 002| 2|language| 87| 187|
+------+-----+--------+-----+---------------------+
only showing top 20 rows专用窗口函数
专用窗口函数包括rank() over,dense_rank() over,row_number() over()
1.rank() over
查出指定条件后的进行排名。特点是，加入是对学生排名，使用这个函数，成绩相同的两名是并列，下一位同学空出所占的名次。
test_rank=spark.sql("""
select *,
rank()over(partition by subject order by grade desc) rank
from test_long_temp_table
""")
?
test_rank.show()
?
number|class| subject|grade|rank|
+------+-----+--------+-----+----+
| 009| 2| physic| 98| 1|
| 003| 3| physic| 89| 2|
| 001| 1| physic| 83| 3|
| 002| 2| physic| 83| 3|
| 005| 1| physic| 81| 5|
| 008| 1| physic| 78| 6|
| 004| 2| physic| 73| 7|
| 006| 3| physic| 73| 7|
| 007| 3| physic| 72| 9|
| 006| 3|chemical| 99| 1|
| 001| 1|chemical| 98| 2|
| 005| 1|chemical| 98| 2|
| 004| 2|chemical| 88| 4|
| 007| 3|chemical| 87| 5|
| 002| 2|chemical| 83| 6|
| 008| 1|chemical| 71| 7|
| 009| 2|chemical| 64| 8|
| 003| 3|chemical| 63| 9|
| 001| 1|language| 100| 1|
| 002| 2|language| 87| 2|
+------+-----+--------+-----+----+
only showing top 20 rows2.dense_rank() over
与rank() over的区别是，两名学生的成绩并列以后，下一位同学并不空出所占的名次。
test_dense_rank=spark.sql("""
select *,
dense_rank()over(partition by subject order by grade desc) rank
from test_long_temp_table
""")
?
test_dense_rank.show()
?
number|class| subject|grade|rank|
+------+-----+--------+-----+----+
| 009| 2| physic| 98| 1|
| 003| 3| physic| 89| 2|
| 001| 1| physic| 83| 3|
| 002| 2| physic| 83| 3|
| 005| 1| physic| 81| 4|
| 008| 1| physic| 78| 5|
| 004| 2| physic| 73| 6|
| 006| 3| physic| 73| 6|
| 007| 3| physic| 72| 7|
| 006| 3|chemical| 99| 1|
| 001| 1|chemical| 98| 2|
| 005| 1|chemical| 98| 2|
| 004| 2|chemical| 88| 3|
| 007| 3|chemical| 87| 4|
| 002| 2|chemical| 83| 5|
| 008| 1|chemical| 71| 6|
| 009| 2|chemical| 64| 7|
| 003| 3|chemical| 63| 8|
| 001| 1|language| 100| 1|
| 002| 2|language| 87| 2|
+------+-----+--------+-----+----+
only showing top 20 rows3.row_number() over
这个函数不需要考虑是否并列，哪怕根据条件查询出来的数值相同也会进行连续排名!
test_row_number=spark.sql("""
select *,
row_number()over(partition by subject order by grade desc) rank
from test_long_temp_table
""")
?
test_row_number.show()
?
number|class| subject|grade|rank|
+------+-----+--------+-----+----+
| 009| 2| physic| 98| 1|
| 003| 3| physic| 89| 2|
| 001| 1| physic| 83| 3|
| 002| 2| physic| 83| 4|
| 005| 1| physic| 81| 5|
| 008| 1| physic| 78| 6|
| 006| 3| physic| 73| 7|
| 004| 2| physic| 73| 8|
| 007| 3| physic| 72| 9|
| 006| 3|chemical| 99| 1|
| 005| 1|chemical| 98| 2|
| 001| 1|chemical| 98| 3|
| 004| 2|chemical| 88| 4|
| 007| 3|chemical| 87| 5|
| 002| 2|chemical| 83| 6|
| 008| 1|chemical| 71| 7|
| 009| 2|chemical| 64| 8|
| 003| 3|chemical| 63| 9|
| 001| 1|language| 100| 1|
| 002| 2|language| 87| 2|
+------+-----+--------+-----+----+
only showing top 20 rows4、Lead和 Lag函数
lag和lead函数可以在同一次查询中取出同一字段的前N行数据（lag）和后N行数据（lead）。
lead和lag函数应用场景较为广泛，在计算前一天、前一个月以及后一天、后一个月等时间差时，我们通常会使用自连接来求差值，但是自连接有时候会出现重
复需要额外处理，而通过lag和lead函数正好能够实现这一功能。
4.1 Lead-后一行
语法：LEAD ( scalar_expression [ ,offset ] , [ default ] ) OVER ( [ partition_by_clause ] order_by_clause )
scalar_expression，要返回的值基于指定的偏移量。这是一个返回单个（标量）值的任何类型的表达式。scalar_expression 不能为分析函数。简单地
来说就是，要取的列。
offset默认值为1， offset 可以是列、子查询或其他求值为正整数的表达式，或者可隐式转换为bigint。offset 不能是负数值或分析函数。简单地来说就
是，取偏移后的第几行数据
default默认值为NULL， offset 可以是列、子查询或其他求值为正整数的表达式，或者可隐式转换为bigint。offset不能是负数值或分析函数。简单地来
说就是，没有符合条件的默认值
4.2 Lag-前一行
语法：Lag ( scalar_expression [ ,offset ] , [ default ] ) OVER ( [ partition_by_clause ] order_by_clause )
test_lead_lag=spark.sql("""
select *,
lead(grade)over(partition by number order by grade desc) lead_grade,
lag(grade)over(partition by number order by grade desc) lag_grade
from test_long_temp_table
""")
?
test_lead_lag.show()
?
number|class| subject|grade|lead_grade|lag_grade|
+------+-----+--------+-----+----------+---------+
| 009| 2| physic| 98| 87| null|
| 009| 2| english| 87| 72| 98|
| 009| 2| math| 72| 64| 87|
| 009| 2|chemical| 64| 63| 72|
| 009| 2|language| 63| null| 64|
| 006| 3|chemical| 99| 85| null|
| 006| 3| english| 85| 84| 99|
| 006| 3|language| 84| 82| 85|
| 006| 3| math| 82| 73| 84|
| 006| 3| physic| 73| null| 82|
| 003| 3| math| 91| 89| null|
| 003| 3| physic| 89| 86| 91|
| 003| 3|language| 86| 83| 89|
| 003| 3| english| 83| 63| 86|
| 003| 3|chemical| 63| null| 83|
| 005| 1|chemical| 98| 89| null|
| 005| 1| english| 89| 81| 98|
| 005| 1| physic| 81| 76| 89|
| 005| 1|language| 76| 62| 81|
| 005| 1| math| 62| null| 76|
+------+-----+--------+-----+----------+---------+
only showing top 20 rows五 分页思想
SQL查询语句中的limit 与 offset 的区别：
limit y 分句表示: 读取 y 条数据
limit x, y 分句表示: 跳过 x 条数据，读取 y 条数据
limit y offset x 分句表示: 跳过 x 条数据，读取 y 条数据
看下面例子：
比如分页获取数据：
第1页： 从第0个开始，获取20条数据
selete * from testtable limit 0, 20; selete * from testtable limit 20 offset 0; 第2页： 从第20个开始，获取20条数据
selete * from testtable limit 20, 20; selete * from testtable limit 20 offset 20; 第3页： 从第40个开始，获取20条数据
selete * from testtable limit 40, 20; selete * from testtable limit 20 offset 40; 下面再看几个leetcode上的题目：
--求第二高的薪水
首先先将数据去重：
SELECT DISTINCT Salary FROM Employee再将是数据按薪水降序排除：
SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC分页的思想是一页一条数据，第二高的薪水则在第二页：
SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1, 1考虑到极端情况：没有第二薪水则为空，使用ifnull判断：
SELECT IFNULL( (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1, 1),null) AS SecondHighestSalary相关推荐
- RHEL8和CentOS8怎么重启网络
-
本文主要讲解如何重启RHEL8或者CentOS8网络以及如何解决RHEL8和CentOS8系统的网络管理服务报错，当我们安装好RHEL8或者CentOS8，重启启动网络时，会出现以下报错：...
- Linux 内、外网双网卡路由配置
-
1.路由信息的影响Linux系统中如果有多张网卡的情况下，如果路由信息配置不正确，...
- Linux——centos7修改网卡名
-
修改网卡名这个操作可能平时用不太上，可作为了解。修改网卡默认名从ens33改成eth01.首先修改网卡配置文件名（建议将原配置文件进行备份）...
- CentOS7下修改网卡名称为ethX的操作方法
-
?Linux操作系统的网卡设备的传统命名方式是eth0、eth1、eth2等，而CentOS7提供了不同的命名规则，默认是基于固件、拓扑、位置信息来分配。这样做的优点是命名全自动的、可预知的...
- Linux 网卡名称enss33修改为eth0
-
一、CentOS修改/etc/sysconfig/grub文件（修改前先备份）为GRUB_CMDLINE_LINUX变量增加2个参数（net.ifnames=0biosdevname=0），修改完成...
- CentOS下双网卡绑定，实现带宽飞速
-
方式一1.新建/etc/sysconfig/network-scripts/ifcfg-bond0文件DEVICE=bond0IPADDR=191.3.60.1NETMASK=255.255.2...
- linux 双网卡双网段设置路由转发
-
背景网络情况linux双网卡:网卡A(ens3)和网卡B(...
- Linux-VMware设置网卡保持激活
-
Linux系统只有在激活网卡的状态下才能去连接网络，进行网络通讯。修改配置文件（永久激活网卡）...
- VMware虚拟机三种网络模式
-
01.VMware虚拟机三种网络模式由于linux目前很热门，越来越多的人在学习linux，但是买一台服务放家里来学习，实在是很浪费。那么如何解决这个问题？虚拟机软件是很好的选择，常用的虚拟机软件有v...
- 2023年最新版 linux克隆虚拟机 解决网卡uuid重复问题
-
问题描述1、克隆了虚拟机，两台虚拟机里面的ip以及网卡的uuid都是一样的2、ip好改，但是uuid如何改呢？解决问题1、每台主机应该保证网卡的UUID是唯一的，避免后面网络通信有问题...
- Linux网卡的Vlan配置，你可能不了解的玩法
-
如果服务器上连的交换机端口已经预先设置了TRUNK,并允许特定的VLAN可以通过，那么服务器的网卡在配置时就必须指定所属的VLAN，否则就不通了，这种情形在虚拟化部署时较常见。例如在一个办公环境中，办...
- Centos7 网卡绑定
-
1、切换到指定目录#备份网卡数据cd/etc/sysconfig/network-scriptscpifcfg-enp5s0f0ifcfg-enp5s0f0.bak...
- Linux搭建nginx+keepalived 高可用(主备+双主模式)
-
一：keepalived简介反向代理及负载均衡参考：...
- Linux下Route 路由指令使用详解
-
linuxroute命令用于显示和操作IP路由表。要实现两个不同子网之间的通信，需要一台连接两个网络的路由器，或者同时位于两个网络的网关来实现。在Linux系统中，设置路由通常是为了解决以下问题：该...
欢迎 你 发表评论:
- 一周热门
- 最近发表
- 标签列表
-
- oracle忘记用户名密码 (59)
- oracle11gr2安装教程 (55)
- mybatis调用oracle存储过程 (67)
- oracle spool的用法 (57)
- oracle asm 磁盘管理 (67)
- 前端 设计模式 (64)
- 前端面试vue (56)
- linux格式化 (55)
- linux图形界面 (62)
- linux文件压缩 (75)
- Linux设置权限 (53)
- linux服务器配置 (62)
- mysql安装linux (71)
- linux启动命令 (59)
- 查看linux磁盘 (72)
- linux用户组 (74)
- linux多线程 (70)
- linux设备驱动 (53)
- linux自启动 (59)
- linux网络命令 (55)
- linux传文件 (60)
- linux打包文件 (58)
- linux查看数据库 (61)
- linux获取ip (64)
- linux进程通信 (63)