英文:
Join two dataframes with limiting the rows of one dataframe
问题
我有两个数据帧：df1：+--------------+---------------------+|id_device |tracking_time |+--------------+---------------------+|20 |2020-02-19 02:37:45 ||5 |2020-02-17 17:15:45 |+--------------+---------------------+df2：+--------------+----------------------+|id_device |tracking_time |+--------------+----------------------+|20 | 2019-02-19 02:41:45 ||20 |2020-01-17 17:15:45 |+--------------+----------------------+我想要获得以下输出：+--------------+---------------------+------------------+|id_device |tracking_time | df2.tracking_time |+--------------+---------------------+------------------+|20 |2020-02-19 02:37:45 |2019-02-19 02:41:45||5 |2020-02-17 17:15:45 |null |+--------------+---------------------+------------------+我尝试了以下代码：df1.registerTempTable("data");df2.createOrReplaceTempView("tdays");Dataset<Row> d_f = sparkSession.sql("select a.* , b.* from data as a LEFT JOIN (select * from tdays ) as b on b.id_device == a.id_device and b.tracking_time < a.tracking_time ");我得到了以下输出：+----------------------+---------------------+--------------------+--------------------+|id_device |tracking_time | b.id_device |b.tracking_time |+----------------------+---------------------+--------------------+--------------------+|20 |2020-02-19 02:37:45 |20 | 2019-02-19 02:41:45||20 |2020-02-19 02:37:45 |20 | 2020-01-17 17:15:45||5 |2020-02-17 17:15:45 |null |null |+-----------------------+--------------------+--------------------+--------------------+我想要的是将第一个数据帧与左连接结果连接，“按df2.tracking_time降序排序并限制为1”。我需要您的帮助
英文:
I have two dataframes :
df1:+--------------+---------------------+|id_device |tracking_time |+--------------+---------------------+|20 |2020-02-19 02:37:45 ||5 |2020-02-17 17:15:45 |+--------------+---------------------+df2+--------------+----------------------+|id_device |tracking_time |+--------------+----------------------+|20 | 2019-02-19 02:41:45 ||20 |2020-01-17 17:15:45 |+--------------+----------------------+
I want to get the following output :
+--------------+---------------------+------------------+|id_device |tracking_time | df2.tracking_time |+--------------+---------------------+------------------+|20 |2020-02-19 02:37:45 |2019-02-19 02:41:45||5 |2020-02-17 17:15:45 |null |+--------------+---------------------+-------------------+
I tried the following code :
df1.registerTempTable("data");df2.createOrReplaceTempView("tdays");Dataset<Row> d_f = sparkSession.sql("select a.* , b.* from data as a LEFT JOIN (select * from tdays ) as b on b.id_device == a.id_device and b.tracking_time < a.tracking_time ");
I get the following output :
+----------------------+---------------------+--------------------+------------------ -+|id_device |tracking_time | b.id_device |b.tracking_time |+----------------------+---------------------+--------------------+--------------------+|20 |2020-02-19 02:37:45 |20 | 2019-02-19 02:41:45||20 |2020-02-19 02:37:45 |20 | 2020-01-17 17:15:45||5 |2020-02-17 17:15:45 |null |null |+-----------------------+--------------------+--------------------+--------------------+
What I want is to join the first dataframe with result of left join ordered by df2.tracking_time desc limit 1
I need your help
答案1
得分: 1
在连接操作之前，您可以将 df2 缩减为每个 id_device 的最小日期：
val df1 = ...val df2 = ...val df2min = df2.groupBy("id_device").agg(min("tracking_time")).as("df2.tracking_time")val result = df1.join(df2min, Seq("id_device"), "left")
df2min 只包含一个包含来自 df2 的最小日期的行，因此左连接将返回预期的结果。
英文:
Before the join, you can reduce df2 to the minimum dates for each id_device:
val df1 = ...val df2 = ...val df2min = df2.groupBy("id_device").agg(min("tracking_time")).as("df2.tracking_time")val result = df1.join(df2min, Seq("id_device"), "left")
df2min contains only a single row with the minimum date from df2 per id. Therefore the left join will return the expected result.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论