数据:
+---+-------------------+-------------------+----+
| id| startTime| endTime|flow|
+---+-------------------+-------------------+----+
| 1|2020-02-18 14:20:30|2020-02-18 14:46:30| 20|
| 1|2020-02-18 14:47:20|2020-02-18 15:20:30| 30|
| 1|2020-02-18 15:37:23|2020-02-18 16:05:26| 40|
| 1|2020-02-18 16:06:27|2020-02-18 17:20:49| 50|
| 1|2020-02-18 17:21:50|2020-02-18 18:03:27| 60|
| 2|2020-02-18 14:18:24|2020-02-18 15:01:40| 20|
| 2|2020-02-18 15:20:49|2020-02-18 15:30:24| 30|
| 2|2020-02-18 16:01:23|2020-02-18 16:40:32| 40|
| 2|2020-02-18 16:44:56|2020-02-18 17:40:52| 50|
| 3|2020-02-18 14:39:58|2020-02-18 15:35:53| 20|
| 3|2020-02-18 15:36:39|2020-02-18 15:24:54| 30|
+---+-------------------+-------------------+----+
需求:同一个用户相差10分钟内的流量合并到一块
代码:
1,按照用户分组将endTime下移一行,形成新的一列
SELECT id, startTime, endTime, flow
, LAG(endTime, 1, startTime) OVER(PARTITION BY id ORDER BY startTime) last_endTime
FROM t_flow
每组第一行的last_endTIme取当前的开始时间
结果:
2,计算当前行startTime和last_endTime的相差是否小于10分钟(满足条件),是的话标记0,否1
SELECT id, startTime, endTime, flow
, IF((to_unix_timestamp(startTime) - to_unix_timestamp(last_endTime))/60 < 10 , 0, 1) flag
FROM (SELECT id, startTime, endTime, flow
, LAG(endTime, 1, startTime) OVER(PARTITION BY id ORDER BY startTime) last_endTime
FROM t_flow) t1
3,每组内将flag依次sum,得到相同结果的sum(flag)则是满足条件的
SELECT t2.*,sum(flag) OVER(PARTITION BY t2.id ORDER BY startTime) fid
FROM(
SELECT id, startTime, endTime, flow
, IF((to_unix_timestamp(startTime) - to_unix_timestamp(last_endTime))/60 < 10 , 0, 1) flag
FROM (SELECT id, startTime, endTime, flow
, LAG(endTime, 1, startTime) OVER(PARTITION BY id ORDER BY startTime) last_endTime
FROM t_flow) t1) t2
3,按照id和fid分组聚合,取出最终结果
SELECT id,MIN(startTime) startTime, MAX(endTime) endTime ,SUM(flow) totalFlow
FROM(
SELECT t2.*,SUM(flag) OVER(PARTITION BY t2.id ORDER BY startTime) fid
FROM(
SELECT id, startTime, endTime, flow
, IF((to_unix_timestamp(startTime) - to_unix_timestamp(last_endTime))/60 < 10 , 0, 1) flag
FROM (SELECT id, startTime, endTime, flow
, LAG(endTime, 1, startTime) OVER(PARTITION BY id ORDER BY startTime) last_endTime
FROM t_flow) t1) t2) t3
GROUP BY id, fid
DSL风格:
import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.{DataFrame, SparkSession}
object DSLFlowCount {
def main(args: Array[String]): Unit = {
val spark: SparkSession = SparkSession.builder()
.appName(this.getClass.getSimpleName)
.master("local[*]")
.getOrCreate()
//创建DataFrame
val df: DataFrame = spark
.read
.csv("data/data.csv").toDF("uid", "start_time", "end_time", "down_flow")
import spark.implicits._
import org.apache.spark.sql.functions._
df.select(
'uid,
'start_time,
'end_time,
'down_flow,
expr("LAG(end_time, 1, start_time)") over(Window.partitionBy("uid").orderBy($"start_time".asc)) as "lag_time"
).select(
'uid,
'start_time,
'end_time,
'down_flow,
expr("IF((UNIX_TIMESTAMP(start_time) - UNIX_TIMESTAMP(lag_time)) / 60 > 10, 1, 0)") as "flag"
).select(
'uid,
'start_time,
'end_time,
'down_flow,
sum('flag) over(Window.partitionBy("uid").orderBy("start_time")) as "fid"
).groupBy(
"uid",
"fid"
).agg(
min("start_time") as "start_time",
max("end_time").as("end_time"),
sum("down_flow").as("total_down_flow")
).drop("fid")
.show()
}
}