各种窗口函数

（1）排名类（row_number, rank, dense_rank）
参考另一篇文章：https://blog.csdn.net/qq_37687649/article/details/106674082?spm=1011.2415.3001.5331

-- 每门课前三名
select  *  from(
select  *, rank()  over(partition by cid ORDER BY cscore DESC) as rnk from score
) as temp1 where temp1.rnk <= 3

pyspark写法：

df_1=df_2.withColumn(
		"rnk",
		rank().over(
			window.partitionBy("cid").orderBy("cscore")
		)
)

（2）聚合类（sum, avg, count, min, max）
写法类似上面

-- 学生每门课近五年成绩平均值

select  *, avg(cscore) over(partition by sid,cid ORDER BY years ASC rows between 4 preceding and current row ) as avg_price from score

pyspark写法

df_1=df_2.withColumn(
	"avg_cscore",
	avg("cscore").over(
		window.partitionBy("sid","cid").orderBy("year").rangeBetween(-4, 0)
	)
)

# 官方里另有一种写法 https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.Window.html?highlight=window#pyspark.sql.Window
# 以下写法纯属炫技，借鉴语法
window_1 = window.partitionBy("sid","cid")
	.orderBy(
		"year",
		col("month").desc()
		)
	.rowsBetween(
			window.unboundPreceding,
			window.currentRow
		)
df_1=df_2.withColumn(
	"avg_cscore",
	avg("cscore").over( window_1 )
)

ROWS BETWEEN 是 SQL 窗口函数中用于‌按物理行偏移量精确定义计算范围‌的子句，必须配合 ORDER BY 使用 。‌‌

核心语法与边界
‌基本结构‌：ROWS BETWEEN <start_bound> AND <end_bound>
‌常用边界值‌：
UNBOUNDED PRECEDING：分区第一行
n PRECEDING：当前行之前的第 n 行
CURRENT ROW：当前行
n FOLLOWING：当前行之后的第 n 行
UNBOUNDED FOLLOWING：分区最后一行
‌典型示例‌：ROWS BETWEEN 2 PRECEDING AND CURRENT ROW（取当前行及前 2 行，共 3 行）‌‌

官方文档也有：
https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-window.html#parameters

（3）滑动（led，lag）

lag，我前面是多少？
lead，我后面是多少？

-- 每门课前一名成绩多少？后面第2名成绩多少？
SELECT sid,cid,cscore,
    LAG(cscore) OVER (PARTITION BY sid,cid ORDER BY cscore DESC) AS lag,
    LEAD(cscore, 2) OVER (PARTITION BY sid,cid ORDER BY cscore DESC) AS lead
    FROM employees;

-- 官方写法 https://spark.apache.org/docs/latest/sql-ref-syntax-qry-select-window.html#parameters
SELECT name, salary,
    LAG(salary) OVER (PARTITION BY dept ORDER BY salary) AS lag,
    LEAD(salary, 1, 0) OVER (PARTITION BY dept ORDER BY salary) AS lead
    FROM employees;

SELECT id, v,
    LEAD(v, 0) IGNORE NULLS OVER w lead,
    LAG(v, 0) IGNORE NULLS OVER w lag,
    NTH_VALUE(v, 2) IGNORE NULLS OVER w nth_value,
    FIRST_VALUE(v) IGNORE NULLS OVER w first_value,
    LAST_VALUE(v) IGNORE NULLS OVER w last_value
    FROM test_ignore_null
    WINDOW w AS (ORDER BY id)
    ORDER BY id;