Spark：如何將間隔除以間隔

我有以下結構的資料框：

timeStatistics.show(10, False)

 ------ --------------------------------------- --------------------------------------- -------------------------------------- ----- 
|idByte|min(time_delta)                        |max(time_delta)                        |avg(time_delta)                       |count|
 ------ --------------------------------------- --------------------------------------- -------------------------------------- ----- 
|1002b0|INTERVAL '0 00:00:00.046' DAY TO SECOND|INTERVAL '0 00:00:00.054' DAY TO SECOND|INTERVAL '0 00:00:00.05' DAY TO SECOND|4198 |
|1002b1|INTERVAL '0 00:00:00.046' DAY TO SECOND|INTERVAL '0 00:00:00.054' DAY TO SECOND|INTERVAL '0 00:00:00.05' DAY TO SECOND|4198 |
|1002b2|INTERVAL '0 00:00:00.046' DAY TO SECOND|INTERVAL '0 00:00:00.054' DAY TO SECOND|INTERVAL '0 00:00:00.05' DAY TO SECOND|4198 |
|1002b3|INTERVAL '0 00:00:00.046' DAY TO SECOND|INTERVAL '0 00:00:00.054' DAY TO SECOND|INTERVAL '0 00:00:00.05' DAY TO SECOND|4198 |
|1002b4|INTERVAL '0 00:00:00.046' DAY TO SECOND|INTERVAL '0 00:00:00.054' DAY TO SECOND|INTERVAL '0 00:00:00.05' DAY TO SECOND|4198 |
|1002b5|INTERVAL '0 00:00:00.046' DAY TO SECOND|INTERVAL '0 00:00:00.054' DAY TO SECOND|INTERVAL '0 00:00:00.05' DAY TO SECOND|4198 |
|1002b6|INTERVAL '0 00:00:00.046' DAY TO SECOND|INTERVAL '0 00:00:00.054' DAY TO SECOND|INTERVAL '0 00:00:00.05' DAY TO SECOND|4198 |
|1002b7|INTERVAL '0 00:00:00.046' DAY TO SECOND|INTERVAL '0 00:00:00.054' DAY TO SECOND|INTERVAL '0 00:00:00.05' DAY TO SECOND|4198 |
|1004b0|INTERVAL '0 00:00:00.046' DAY TO SECOND|INTERVAL '0 00:00:00.054' DAY TO SECOND|INTERVAL '0 00:00:00.05' DAY TO SECOND|4198 |
|1004b1|INTERVAL '0 00:00:00.046' DAY TO SECOND|INTERVAL '0 00:00:00.054' DAY TO SECOND|INTERVAL '0 00:00:00.05' DAY TO SECOND|4198 |
 ------ --------------------------------------- --------------------------------------- -------------------------------------- ----- 
only showing top 10 rows

我想添加一個列，它給了我不同的min(time_delta)因素max(time_delta)。

我的第一次嘗試是添加：

.withColumn("min_max_split", (F.col("max(time_delta)")/F.col("min(time_delta)")))

但是，似乎不支持劃分兩個間隔：

AnalysisException：由于資料型別不匹配，無法決議“（max(time_delta)/ ）”：引數 2 需要數字型別，但是，“ ”是間隔天到秒的型別。min(time_delta)min(time_delta)

我想過使用該unix_timestamp()函式轉換間隔。但是，我的間隔有時小于一秒，所以unix_timestamp()會回傳零。

uj5u.com熱心網友回復：

您可以將間隔添加到current_timestamp，將結果轉換為雙精度型別，然后除以：

from pyspark.sql import functions as F

df1 = df.withColumn(
    "min_max_split",
    (F.current_timestamp()   F.col("max(time_delta)")).cast('double') / (
                F.current_timestamp()   F.col("min(time_delta)")).cast('double')
)

df1.show(1)
# ------ -------------------- -------------------- -------------------- ----- ------------------ 
#|idByte|     min(time_delta)|     max(time_delta)|     avg(time_delta)|count|     min_max_split|
# ------ -------------------- -------------------- -------------------- ----- ------------------ 
#|1002b0|INTERVAL '0 00:00...|INTERVAL '0 00:00...|INTERVAL '0 00:00...| 4198|1.0000000000048699|
# ------ -------------------- -------------------- -------------------- ----- ------------------ 

uj5u.com熱心網友回復：

我找到了一個解決方案，這是對@blackbishop 的回答稍作修改：

.withColumn("min_max_split",\
  (\
    (F.to_date(F.from_unixtime(F.lit(0))) F.col("min(time_delta)")).cast('double')\
    / (F.to_date(F.from_unixtime(F.lit(0))) F.col("max(time_delta)")).cast('double')\
  )\
)

標籤：

上一篇：PySpark：范圍內的亂數（基于列）

下一篇：PySpark根據陣列列中的值替換字串列中的多個單詞