我不明白如何才能將這樣的 2 DataFrame 彼此加入。第一個 DataFrame 存儲有關用戶向服務中心請求時間的信息。我們稱之為 DataFrame df1：+-----------+---------------------+| USER_NAME | REQUEST_DATE |+-----------+---------------------+| Alex | 2018-03-01 00:00:00 || Alex | 2018-09-01 00:00:00 || Bob | 2018-03-01 00:00:00 || Mark | 2018-02-01 00:00:00 || Mark | 2018-07-01 00:00:00 || Kate | 2018-02-01 00:00:00 |+-----------+---------------------+第二個 DataFrame 存儲有關用戶可以使用服務中心服務的可能期限（許可期限）的信息。讓我們稱之為df2。+-----------+---------------------+---------------------+------------+| USER_NAME | START_SERVICE | END_SERVICE | STATUS |+-----------+---------------------+---------------------+------------+| Alex | 2018-01-01 00:00:00 | 2018-06-01 00:00:00 | Active || Bob | 2018-01-01 00:00:00 | 2018-02-01 00:00:00 | Not Active || Mark | 2018-01-01 00:00:00 | 2018-05-01 23:59:59 | Active || Mark | 2018-05-01 00:00:00 | 2018-08-01 23:59:59 | VIP |+-----------+---------------------+---------------------+------------+如何加入這 2 個 DataFrame 并返回這樣的結果？治療時如何獲取用戶許可證類型列表？+-----------+---------------------+----------------+| USER_NAME | REQUEST_DATE | STATUS |+-----------+---------------------+----------------+| Alex | 2018-03-01 00:00:00 | Active || Alex | 2018-09-01 00:00:00 | No information || Bob | 2018-03-01 00:00:00 | Not Active || Mark | 2018-02-01 00:00:00 | Active || Mark | 2018-07-01 00:00:00 | VIP || Kate | 2018-02-01 00:00:00 | No information |+-----------+---------------------+----------------+代碼：import org.apache.spark.sql.DataFrameval df1: DataFrame = Seq( ("Alex", "2018-03-01 00:00:00"), ("Alex", "2018-09-01 00:00:00"), ("Bob", "2018-03-01 00:00:00"), ("Mark", "2018-02-01 00:00:00"), ("Mark", "2018-07-01 00:00:00"), ("Kate", "2018-07-01 00:00:00")).toDF("USER_NAME", "REQUEST_DATE")
2 回答
繁花如伊
TA貢獻2012條經驗 獲得超12個贊
如何加入這 2 個 DataFrame 并返回這樣的結果？
df_joined = df1.join(df2, Seq('USER_NAME'), 'left' )
如何獲取許可證仍然相關的所有用戶的列表？
df_relevant = df_joined
.withColumn('STATUS', when(col('REQUEST_DATE') > col('START_SERVICE') and col('REQUEST_DATE') < col('END_SERVICE'), col('STATUS')).otherwise('No information')
.select('USER_NAME', 'REQUEST_DATE', 'STATUS' )
烙印99
TA貢獻1829條經驗 獲得超13個贊
您在不正確的字符串值上比較 <= 和 >=。在進行此類比較之前，您應該將它們轉換為時間戳。下面的代碼對我有用。
順便說一句..您使用的過濾條件沒有給出您在問題中發布的結果。請再次檢查。
scala> val df= Seq(("Alex","2018-03-01 00:00:00"),("Alex","2018-09-01 00:00:00"),("Bob","2018-03-01 00:00:00"),("Mark","2018-02-01 00:00:00"),("Mark","2018-07-01 00:00:00"),("Kate","2018-02-01 00:00:00")).toDF("USER_NAME","REQUEST_DATE").withColumn("REQUEST_DATE",to_timestamp('REQUEST_DATE))
df: org.apache.spark.sql.DataFrame = [USER_NAME: string, REQUEST_DATE: timestamp]
scala> df.printSchema
root
|-- USER_NAME: string (nullable = true)
|-- REQUEST_DATE: timestamp (nullable = true)
scala> df.show(false)
+---------+-------------------+
|USER_NAME|REQUEST_DATE |
+---------+-------------------+
|Alex |2018-03-01 00:00:00|
|Alex |2018-09-01 00:00:00|
|Bob |2018-03-01 00:00:00|
|Mark |2018-02-01 00:00:00|
|Mark |2018-07-01 00:00:00|
|Kate |2018-02-01 00:00:00|
+---------+-------------------+
scala> val df2 = Seq(( "Alex","2018-01-01 00:00:00","2018-06-01 00:00:00","Active"),("Bob","2018-01-01 00:00:00","2018-02-01 00:00:00","Not Active"),("Mark","2018-01-01 00:00:00","2018-05-01 23:59:59","Active"),("Mark","2018-05-01 00:00:00","2018-08-01 23:59:59","VIP")).toDF("USER_NAME","START_SERVICE","END_SERVICE","STATUS").withColumn("START_SERVICE",to_timestamp('START_SERVICE)).withColumn("END_SERVICE",to_timestamp('END_SERVICE))
df2: org.apache.spark.sql.DataFrame = [USER_NAME: string, START_SERVICE: timestamp ... 2 more fields]
scala> df2.printSchema
root
|-- USER_NAME: string (nullable = true)
|-- START_SERVICE: timestamp (nullable = true)
|-- END_SERVICE: timestamp (nullable = true)
|-- STATUS: string (nullable = true)
scala> df2.show(false)
+---------+-------------------+-------------------+----------+
|USER_NAME|START_SERVICE |END_SERVICE |STATUS |
+---------+-------------------+-------------------+----------+
|Alex |2018-01-01 00:00:00|2018-06-01 00:00:00|Active |
|Bob |2018-01-01 00:00:00|2018-02-01 00:00:00|Not Active|
|Mark |2018-01-01 00:00:00|2018-05-01 23:59:59|Active |
|Mark |2018-05-01 00:00:00|2018-08-01 23:59:59|VIP |
+---------+-------------------+-------------------+----------+
scala> df.join(df2,Seq("USER_NAME"),"leftOuter").filter(" REQUEST_DATE >= START_SERVICE and REQUEST_DATE <= END_SERVICE").select(df("*"),df2("status")).show(false)
+---------+-------------------+------+
|USER_NAME|REQUEST_DATE |status|
+---------+-------------------+------+
|Alex |2018-03-01 00:00:00|Active|
|Mark |2018-02-01 00:00:00|Active|
|Mark |2018-07-01 00:00:00|VIP |
+---------+-------------------+------+
scala>
添加回答
舉報
0/150
提交
取消