SQL学习笔记（一）排序和求众数

排序 

1、row_number() over(partition by col1 order by col2)：相等的值对应的排名相同，序号从1到n连续。

`Species`;`Sepal_Width`;`count`;row_number() over (partition by `Sepal_Width` order by `count` desc) as num

2、rank() over(partition by col1 order by col2)：但若有并列的名称，会占用下一名次的，则序号从1到n不连续。如果有两个人都排在第3名，则没有第4名。

`Species`;rank() over (order by `Species`) as rank

3、dense_rank() over(partition by col1 order by col2)：但若有并列的名称，不会占用下一名次的，则序号从1到n连续。如果有两个人都排在第3名，则下一名还是第4名。

`Species`;dense_rank() over (order by `Species`) as dsr

4、ntile( n ) over(partition by col1 order by col2)：将每个分区内排序后的结果均分成N个桶，排序对应的数字为桶号。如果不能平均分配，则较小桶号的桶分配额外的行，并且各个桶中能放的数据条数最多相差1。

`Sepal_Width`;`Species`;ntile(4) over (order by `Species`) as ntile)（以Species列均分四桶）

众数（sentosa）

1、求平均数等可以利用count；

2、求众数：

方法1（SQL算子）：

select Sepal_Width,count(1) from $iris

group by 1

order by 2 desc

limit 1

（需要在查表时，给表名加上$）

方法2(PySpark算子)：

# 初始化 Spark 会话
spark = SparkSession.builder.appName("IrisAnalysis").getOrCreate()

# 读取 iris 数据表为 DataFrame
iris_df = spark.read.csv("/home/sentosa/project/iris.csv", header=True, inferSchema=True)

# 计算 Sepal_Width 的频率并选择出现次数最多的记录
output_data = (iris_df
               .groupBy("Sepal_Width")              
               # GROUP BY Sepal_Width
               .agg(count("*").alias("count"))      
               # COUNT(*)
               .orderBy("count", ascending=False)   
               # ORDER BY count DESC
               .limit(1))                        
               # LIMIT 1

# 显示结果
output_data.show()

方法3：

聚合-->排序desc-->过滤

方法4：利用两个选择算子嵌套查询

1、count(`Sepal_Width`)over(partition by `Sepal_Width`) as count

2、sum(‘count’)
方法5：聚合后选择算子查询

1、count聚合

2、max(‘count’)

注意：group by是分组，对行数有影响。窗口函数(count() over(), sum() over()等)和group by的语法不同，不能混合使用。