Spark SQL连续4个时间间隔

Question

这是我的表：

+--------+----+----------+----+
| station|temp|     dateS|hour|
+--------+----+----------+----+
|Roma    | 2.2|2018-10-02|   1|
|Roma    | 1.5|2018-10-02|   2|
|Roma    | 1.4|2018-10-02|   3|
|Roma    | 1.4|2018-10-02|   4|
|Milano  | 0.6|2018-11-02|  12|
|Milano  | 1.0|2018-11-02|  13|
|Napoli  | 0.3|2018-12-02|  20|
|Napoli  | 0.0|2018-12-02|  21|
|Napoli  | 1.8|2018-12-02|   4|
|Napoli  | 2.0|2018-12-03|   5|
|Napoli  | 1.8|2018-12-03|   6|
+--------+----+----------+----+

我想连续4小时记录在记录中的站（显然是连续4个小时）。例如，仅显示罗姆人，因为同一天连续4个小时（2018-10-02）...

我尝试编写查询，但没有...

Answer 1

我尝试使用铅窗口功能。我通过在站和日期进行分区并按小时订购来安排数据框，并计算了上一个小时的差异。如果我们连续4个小时考虑，则在"差异"列中应该有三个1。为了找出这一点，我已经根据站和日期收集了所有差异，并检查了它是否包含" 1 1 1"。同一代码如下所示。我希望这很有帮助。

//Creating Test Data
  val df = Seq(("Roma",2.2,"2018-10-02",1 ) 
    , ("Roma",1.5,"2018-10-02",2 ) 
    , ("Roma",1.4,"2018-10-02",3 ) 
    , ("Roma",1.4,"2018-10-02",4 ) 
    , ("Milano",0.6,"2018-11-02",12 ) 
    , ("Milano",1.0,"2018-11-02",13 ) 
    , ("Napoli",0.3,"2018-12-02",20 ) 
    , ("Napoli",0.0,"2018-12-02",21 ) 
    , ("Napoli",1.8,"2018-12-02",4 ) 
    , ("Napoli",2.0,"2018-12-03",5 ) 
    , ("Napoli",1.8,"2018-12-03",6))
    .toDF("station", "temp", "dateS", "hour")
  val filterDF = df.withColumn("hour_lead", lead($"hour", 1)
                   .over(Window.partitionBy("station","dateS")
                               .orderBy(col("hour")))
                   .filter($"hour_lead".isNotNull)
                   .withColumn("hour_diff", $"hour_lead" - $"hour")
                   .groupBy("station","dateS")
          .agg(collect_list($"hour_diff".cast("string")).as("hour_diff_list"))
          .withColumn("hour_diff_list_str", 
                      concat(lit(" "), 
                      concat_ws(" ", $"hour_diff_list"), 
                      lit(" ")))
          .filter($"hour_diff_list_str".contains(" 1 1 1 "))
  filterDF.show(false)
  +-------+----------+--------------+------------------+
  |station|dateS     |hour_diff_list|hour_diff_list_str|
  +-------+----------+--------------+------------------+
  |Roma   |2018-10-02|[1, 1, 1]     | 1 1 1            |
  +-------+----------+--------------+------------------+