SparkSQL/Hive 提供了许多的分析函数,用于完成复杂统计分析。
sum、avg、min、max,分别用于计算分组内相关统计信息。
1、用SQL实现下面的操作
测试数据:
±-------±------------------±–+
|cookieid| createtime| pv|
±-------±------------------±–+
| cookie1|2015-04-10 00:00:00| 1|
| cookie1|2015-04-11 00:00:00| 5|
| cookie1|2015-04-12 00:00:00| 7|
| cookie1|2015-04-13 00:00:00| 3|
| cookie1|2015-04-14 00:00:00| 2|
| cookie1|2015-04-15 00:00:00| 4|
| cookie1|2015-04-16 00:00:00| 4|
| cookie2|2015-04-10 00:00:00| 2|
| cookie2|2015-04-11 00:00:00| 3|
| cookie2|2015-04-12 00:00:00| 5|
| cookie2|2015-04-13 00:00:00| 6|
| cookie2|2015-04-14 00:00:00| 3|
| cookie2|2015-04-15 00:00:00| 9|
| cookie2|2015-04-16 00:00:00| 7|
±-------±------------------±–+
根据以下要求实现新的列:
根据cookieid分组,按照createtime排序,新列的值为第一行到当前行pv的合计值
SQL实现:
val sql =
"""select cookieid,createtime,pv,
|sum(pv) over(partition by cookieid order by createtime) as pv1
|from t1""".stripMargin
spark.sql(sql).show()
结果:
计算方式:sum(pv)
分组方式:partition by cookieid
排序方式:order by createtime
计算范围:未指定,默认第一行到当前行
SQL实现pv2列指定计算范围:
val sql =
"""select cookieid,createtime,pv,
|sum(pv) over(partition by cookieid order by createtime) as pv1,
|sum(pv) over(partition by cookieid order by createtime
|rows between unbounded preceding and current row) as pv2
|from t1""".stripMargin