我有按时间戳排序的数据,具有以下结构:
+------------+--------+--------+----------+-------+
| timestamp | value | device | subgroup | group |
+------------+--------+--------+----------+-------+
| 1377986440 | 0 | 1 | 0 | 5 |
| 1377986440 | 2.25 | 1 | 0 | 5 |
| 1377986440 | 0 | 2 | 0 | 6 |
| 1377986440 | 0.135 | 0 | 0 | 6 |
| 1377986440 | 0.355 | 0 | 0 | 6 |
+------------+--------+--------+----------+-------+
我已经将时间戳从Long类型更改为TimestampType,并且记录从2013年9月1日到2013年9月30日。
我需要计算整个数据集中值的平均值和标准差,分别在以下时间窗口内进行:[00:00, 6:00), [06:00,12:00), [12:00, 18:00), [18:00,00:00),并针对每个组。例如,输出应该像这样:
+-------+--------------+------+-------+
| group | timeSlot | avg | std |
+-------+--------------+------+-------+
| 0 | 00:00 6:00 | 1.4 | 0.25 |
| 0 | 06:00 12:00 | 2.4 | 0.25 |
| ... | ... | .... | .... |
| 3 | 00:00 6:00 | 2.3 | 0.1 |
| 3 | 06:00 12:00 | 0.0 | 0.0 |
| ... | ... | ... | ... |
+-------+--------------+------+-------+
我尝试使用窗口功能,如这里所述,因此我将Unix时间戳转换为格式为HH:mm:ss的TimestampType。然后我的代码是:
val data = df
.select("*")
.withColumn("timestamp", from_unixtime($"timestamp", "HH:mm:ss"))
val res = data.select("*")
.groupBy($"group", window($"timestamp", "6 hours", "6 hours"))
.agg(avg("value"), stddev("value"))
.orderBy("group")
但是,如果我没有为时间窗口指定起始点,第一个时间段并不是从00:00开始,而是从02:00:00开始。我得到的输出结果是:
+--------+------------------------------------------+---------------------+---------------------+
|group |window |avg(cons) |stddev_samp(cons) |
+--------+------------------------------------------+---------------------+---------------------+
|0 |[2018-05-18 02:00:00, 2018-05-18 08:00:00]|1.781448366186445E-4 |0.004346229072242386 |
|0 |[2018-05-18 14:00:00, 2018-05-18 20:00:00]|0.0045980360360061865|0.7113464184007158 |
|0 |[2018-05-18 20:00:00, 2018-05-19 02:00:00]|2.7686190915763437E-4|6.490469208721791E-4 |
|0 |[2018-05-17 20:00:00, 2018-05-18 02:00:00]|0.0016399597206953798|0.12325297254169619 |
|0 |[2018-05-18 08:00:00, 2018-05-18 14:00:00]|2.3354306613988956E-4|5.121337883543223E-4 |
|1 |[2018-05-18 20:00:00, 2018-05-19 02:00:00]|8.319111249637333E-4 |0.00163300686441327 |
|1 |[2018-05-18 14:00:00, 2018-05-18 20:00:00]|0.006463708881068344 |0.7907138759032012 |
|1 |[2018-05-18 02:00:00, 2018-05-18 08:00:00]|6.540241054052753E-4 |0.020490123866864617 |
我应该如何修改我的代码?我尝试过其他解决方案,但都没有起作用。