Spark：2.4
数据框包含每个员工的平均登录时间
AverageLoginHour|employee
3.392265193 |emp_1
2.833333333 |emp_2
5.638888889 |emp_3
6.909090909 |emp_4
7.361445783 |emp_5代码：
tds.select("Employee","AverageLoginHour")
(count("AverageLoginHour").alias("logincnt"))
(sum("AverageLoginHour").alias("loginsum"))
.withColumn("TotalEmployeeavg",col("loginsum")/col("logincnt")*100)
Error: Cannot resolve symbol .withcolumn预期产量：
AverageLoginHour| employee Totalavg|Remarks
3.392265193 | Emp_1 |5.2 |Below Avg
2.833333333 | Emp_2 |5.2 |Below Avg
5.638888889 | Emp_3 |5.2 |Above Avg
6.909090909 | Emp_4 |5.2 |Above Avg
7.361445783 | Emp_5 |5.2 |Above Avg如果员工平均登录时间小于totalavg than，则列备注如下平均值，否则高于平均值。
请分享你的建议。
1条答案
按热度按时间ecr0jaav1#
使用
avg内置函数window本案的条款。Example:```df.show()
//+----------------+--------+
//|AverageLoginHour|employee|
//+----------------+--------+
//| 3.392265193| emp_1|
//| 2.833333333| emp_2|
//| 5.638888889| emp_3|
//| 6.909090909| emp_4|
//| 7.361445783| emp_5|
//+----------------+--------+
df.withColumn("Totalavg",avg(col("AverageLoginHour")).over()).
withColumn("Remarks",when(col("Totalavg") > col("AverageLoginHour"),lit("Below Avg")).otherwise(lit("Above Avg"))).
show()
//+----------------+--------+------------+---------+
//|AverageLoginHour|employee| Totalavg| Remarks|
//+----------------+--------+------------+---------+
//| 3.392265193| emp_1|5.2270048214|Below Avg|
//| 2.833333333| emp_2|5.2270048214|Below Avg|
//| 5.638888889| emp_3|5.2270048214|Above Avg|
//| 6.909090909| emp_4|5.2270048214|Above Avg|
//| 7.361445783| emp_5|5.2270048214|Above Avg|
//+----------------+--------+------------+---------+
//rounding to 1
df.withColumn("Totalavg",round(avg(col("AverageLoginHour")).over(),1)).withColumn("Remarks",when(col("Totalavg") > col("AverageLoginHour"),lit("Below Avg")).otherwise(lit("Above Avg"))).show()
//+----------------+--------+--------+---------+
//|AverageLoginHour|employee|Totalavg| Remarks|
//+----------------+--------+--------+---------+
//| 3.392265193| emp_1| 5.2|Below Avg|
//| 2.833333333| emp_2| 5.2|Below Avg|
//| 5.638888889| emp_3| 5.2|Above Avg|
//| 6.909090909| emp_4| 5.2|Above Avg|
//| 7.361445783| emp_5| 5.2|Above Avg|
//+----------------+--------+--------+---------+