EDITED:
I have modified the logic to compute the average only from the filtered dataframe, so that it addresses the gaps.
//input structure
case class StreamInput(event_time: Long, device_id: Int, signal_strength: Int)
//columns for which we want to maintain state
case class StreamState(prevSum: Int, prevRowCount: Int, prevTime: Long, prevSignalStrength: Int, currentTime: Long, totalRow: Int, totalSum: Int, avg: Double)
//final result structure
case class StreamResult(event_time: Long, device_id: Int, signal_strength: Int, avg: Double)
val filteredDF = ??? //get input(filtered rows only)
val interval = 5 // event_time interval
// using .mapGroupsWithState to maintain state for runningSum & total row count till now
// you need to set the timeout threshold to indicate how long you wish to maintain the state
val avgDF = filteredDF.groupByKey(_.device_id)
.mapGroupsWithState[StreamState, StreamResult](GroupStateTimeout.NoTimeout()) {
case (id: Int, eventIter: Iterator[StreamInput], state: GroupState[StreamState]) => {
val events = eventIter.toSeq
val updatedSession = if (state.exists) {
//if state exists update the state with the new values
val existingState = state.get
val prevTime = existingState.currentTime
val currentTime = events.map(x => x.event_time).last
val currentRowCount = (currentTime - prevTime)/interval
val rowCount = existingState.rowCount + currentRowCount.toInt
val currentSignalStength = events.map(x => x.signal_strength).last
val total_signal_strength = currentSignalStength +
(existingState.prevSignalStrength * (currentRowCount -1)) +
existingState.total_signal_strength
StreamState(
existingState.total_signal_strength,
existingState.rowCount,
prevTime,
currentSignalStength,
currentTime,
rowCount,
total_signal_strength.toInt,
total_signal_strength/rowCount.toDouble
)
} else {
// if there are no earlier state
val runningSum = events.map(x => x.signal_strength).sum
val size = events.size.toDouble
val currentTime = events.map(x => x.event_time).last
StreamState(0, 1, 0, runningSum, currentTime, 1, runningSum, runningSum/size)
}
//save the updated state
state.update(updatedSession)
StreamResult(
events.map(x => x.event_time).last,
id,
events.map(x => x.signal_strength).last,
updatedSession.avg
)
}
}
val result = avgDF
.writeStream
.outputMode(OutputMode.Update())
.format("console")
.start
The idea is to calculate two new Columns:
- totalRowCount: the running total of number of rows that are supposed to be present if you have not filtered.
- total_signal_strength: the running total of
signal_strength till now. (this INCLUDES missed row totals too).
Its calculated by:
total_signal_strength =
current row's signal_strength +
(total_signal_strength of previous row * (rowCount -1)) +
//rowCount is the count of missed rows computed by comparing previous and current event_time.
previous total_signal_strength
format of the intermediate state:
+----------+---------+---------------+---------------------+--------+
|event_time|device_id|signal_strength|total_signal_strength|rowCount|
+----------+---------+---------------+---------------------+--------+
| 0| 1| 5| 5| 1|
| 5| 1| 4| 9| 2|
| 30| 1| 5| 30| 7|
| 45| 1| 6| 46| 10|
| 55| 1| 5| 57| 12|
+----------+---------+---------------+---------------------+--------+
final output:
+----------+---------+---------------+-----------------+
|event_time|device_id|signal_strength| avg|
+----------+---------+---------------+-----------------+
| 0| 1| 5| 5.0|
| 5| 1| 4| 4.5|
| 30| 1| 5|4.285714285714286|
| 45| 1| 6| 4.6|
| 55| 1| 5| 4.75|
+----------+---------+---------------+-----------------+
