本文已参与「新人创作礼」活动，一起开启掘金创作之路。
- row_number() / rank()/ dense_rank() 这几个排序的区别！！！
以上几个函数均来自与 import org.apache.spark.sql.functions._ 中，同样可以用在 MySQL 中。
需求：比如 我有以下数据，我想要计算以年级分组，求分组后每组中的前2名。
//创建表 CREATE TABLE rownumber ( id varchar(10) CHARACTER SET utf8 NOT NULL, name varchar(10) CHARACTER SET utf8 DEFAULT NULL, grade varchar(10) CHARACTER SET utf8 DEFAULT NULL, salary int(11) DEFAULT NULL, PRIMARY KEY (id) ) //插入数据 INSERT INTO rownumber VALUES ('1','李逵','一年级',8000), ('2','郑王','一年级',7500), ('3','王行','一年级',7500), ('4','张正','一年级',7000), ('5','侯七','二年级',4500), ('6','赵六','二年级',8000), ('7','王五','二年级',20000), ('8','李四','三年级',30000), ('9','张三','三年级',8000), ('10','小红','三年级',8000); ('11','冯巩','三年级',7000);
代码：
//第一种方式：Spark sql 开窗函数实现排序 spark.read.format("jdbc").options(map).load().createTempView("rownumber") //Spark sql functions 中的函数实现排序 spark.table("rownumber") .select( col("name"), col("grade"), col("salary"), row_number().over(Window.partitionBy("grade").orderBy(col("salary").desc)).as("rowNum"), rank().over(Window.partitionBy("grade").orderBy(col("salary").desc)).as("rankNum"), dense_rank().over(Window.partitionBy("grade").orderBy(col("salary").desc)).as("denseRankNum") ) // .where(col("rowNum") <3) //取每组前2名 .show(false) //第二种方式：Spark sql 实现排序 spark.sql( """ |select | name, | grade, | salary, | row_number() over(partition by grade order by salary desc) as rowNum, | rank() over(partition by grade order by salary desc) as rankNum, | dense_rank() over(partition by grade order by salary desc) as denseRankNum |from rownumber |""".stripMargin) // .where(col("rowNum") <3) //取每组前2名 .show(false)
结果：
+----+-----+------+------+-------+------------+ |name|grade|salary|rowNum|rankNum|denseRankNum| +----+-----+------+------+-------+------------+ |李逵 |一年级 |8000 |1 |1 |1 | |郑王 |一年级 |7500 |2 |2 |2 | |王行 |一年级 |7500 |3 |2 |2 | |张正 |一年级 |7000 |4 |4 |3 | |李四 |三年级 |30000 |1 |1 |1 | |小红 |三年级 |8000 |2 |2 |2 | |张三 |三年级 |8000 |3 |2 |2 | |冯巩 |三年级 |7000 |4 |4 |3 | |王五 |二年级 |20000 |1 |1 |1 | |赵六 |二年级 |8000 |2 |2 |2 | |侯七 |二年级 |4500 |3 |3 |3 | +----+-----+------+------+-------+------------+ =============================================================== +----+-----+------+------+-------+------------+ |name|grade|salary|rowNum|rankNum|denseRankNum| +----+-----+------+------+-------+------------+ |李逵 |一年级 |8000 |1 |1 |1 | |郑王 |一年级 |7500 |2 |2 |2 | |王行 |一年级 |7500 |3 |2 |2 | |张正 |一年级 |7000 |4 |4 |3 | |李四 |三年级 |30000 |1 |1 |1 | |小红 |三年级 |8000 |2 |2 |2 | |张三 |三年级 |8000 |3 |2 |2 | |冯巩 |三年级 |7000 |4 |4 |3 | |王五 |二年级 |20000 |1 |1 |1 | |赵六 |二年级 |8000 |2 |2 |2 | |侯七 |二年级 |4500 |3 |3 |3 | +----+-----+------+------+-------+------------+
总结：
#由上述结果可以看出： row_number() // 排序值从小到大，依次排列 rank() // 相同数据，并列保存，下一个值跳值(断续) dense_rank() // 相同数据，并列保存，不存在断值(一直连续)，dense稠密的意思
-
SUM/AVG/MIN/MAX
-
concat
对字符串进行拼接：concat(str1, str2, ..., strN) ，参数：str1、str2...是要进行拼接的字符串
-- return the concatenation of str1、str2、..., strN -- SparkSQL select concat('Spark', 'SQL'); 返回：SparkSQL
- initcap / lower / upper
initcap：将每个单词的首字母转为大写，其他字母小写。单词之间以空白分隔。
upper： 全部转为大写。
lower： 全部转为小写。
-- Spark Sql select initcap("spaRk sql"); -- SPARK SQL select upper("sPark sql"); -- spark sql select lower("Spark Sql");
- 正则处理字符串
// regexp_extract 正则提取某些字符串 -- 2000 select regexp_extract("1000-2000", "(\d+)-(\d+)", 2); 返回：2000 // regexp_replace 正则替换 -- r-r select regexp_replace("100-200", "(\d+)", "r"); 返回：r-r
- instr / locate 返回截取字符串的位置。如果匹配的字符串不存在，则返回0
-- returns the (1-based) index of the first occurrence of substr in str. -- 6 select instr("SparkSQL", "SQL"); -- 0 select locate("A", "fruit");
- 时间函数
（1）current_date / current_timestamp 获取当前时间
select current_date; select current_timestamp;
（2）从日期时间中提取字段/格式化时间1）year、month、day、dayofmonth、hour、minute、second
select day("2020-12-20");
（3）trunc 截取某部分的日期，其他部分默认为01。第二个参数: YEAR、YYYY、YY、MON、MONTH、MM
-- 2020-01-01 select trunc("2020-12-12", "YEAR"); -- 2020-12-01 select trunc("2020-12-12", "MM");
（4）date_format 按照某种格式格式化时间
-- 2020-12-12 select date_format("2020-12-12 12:12:12", "yyyy-MM-dd");
（5）add_months 返回某日期后n个月后的日期。
-- 2020-12-28 select add_months("2020-11-28", 1);
（6）date_add(start_date, num_days) 返回指定时间增加num_days天后的时间
-- 2020-12-02 select date_add("2020-12-01", 1);
（7）datediff(endDate, startDate) 两个日期相差的天数
-- 3 select datediff("2020-12-01", "2020-11-28");
- 常用的开窗函数
//开窗函数格式通常满足： function_name( [argument_list] ) OVER ( [PARTITION BY partition_expression,…] [ORDER BY sort_expression, … [ASC|DESC] ] ) == 解释： function_name: 函数名称，比如SUM()、AVG() partition_expression：分区列 sort_expression： 排序列 === 举例： row_number() over( partition by grade order by salary desc ) as rowNum rank() over( partition by grade order by salary desc ) as rowNum dense_rank() over( partition by grade order by salary desc ) as rowNum
