之前的时候讲过一个关于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