在下面的資料框中，有幾個apartments不同job的：
--- --------- ------
|id |apartment|job |
--- --------- ------
|1 |Ap1 |dev |
|2 |Ap1 |anyl |
|3 |Ap2 |dev |
|4 |Ap2 |anyl |
|5 |Ap2 |anyl |
|6 |Ap2 |dev |
|7 |Ap2 |dev |
|8 |Ap2 |dev |
|9 |Ap3 |anyl |
|10 |Ap3 |dev |
|11 |Ap3 |dev |
--- --------- ------
對于每個公寓，帶有的行數job='dev'應等于帶有的行數job='anyl'（如 Ap1）。如何洗掉'dev'所有公寓中的多余行？
預期結果：
--- --------- ------
|id |apartment|job |
--- --------- ------
|1 |Ap1 |dev |
|2 |Ap1 |anyl |
|3 |Ap2 |dev |
|4 |Ap2 |anyl |
|5 |Ap2 |anyl |
|6 |Ap2 |dev |
|9 |Ap3 |anyl |
|10 |Ap3 |dev |
--- --------- ------
我想我應該使用 Window 函式來處理這個問題，但我想不通。
uj5u.com熱心網友回復：
我認為您首先需要找出每個“公寓”有多少“任何”，然后用它來洗掉所有多余的“開發”。因此，首先是聚合，join然后是視窗函式，然后row_number才能過濾掉不需要的內容。
設定：
from pyspark.sql import functions as F, Window as W
df = spark.createDataFrame(
[(1, 'Ap1', 'dev'),
(2, 'Ap1', 'anyl'),
(3, 'Ap2', 'dev'),
(4, 'Ap2', 'anyl'),
(5, 'Ap2', 'anyl'),
(6, 'Ap2', 'dev'),
(7, 'Ap2', 'dev'),
(8, 'Ap2', 'dev'),
(9, 'Ap3', 'anyl'),
(10, 'Ap3', 'dev'),
(11, 'Ap3', 'dev')],
['id', 'apartment', 'job']
)
腳本：
df_grp = df.filter(F.col('job') == 'anyl').groupBy('apartment').count()
df = df.join(df_grp, 'apartment', 'left')
w = W.partitionBy('apartment', 'job').orderBy('id')
df = df.withColumn('_rn', F.row_number().over(w))
df = df.filter('_rn <= count')
df = df.select('id', 'apartment', 'job')
df.show()
# --- --------- ----
# | id|apartment| job|
# --- --------- ----
# | 2| Ap1|anyl|
# | 1| Ap1| dev|
# | 4| Ap2|anyl|
# | 5| Ap2|anyl|
# | 3| Ap2| dev|
# | 6| Ap2| dev|
# | 9| Ap3|anyl|
# | 10| Ap3| dev|
# --- --------- ----
uj5u.com熱心網友回復：
使用 @ZygD 建議的左半連接而不是groupBy filter組合可能更有效：
>>> from pyspark.sql import Window
>>> from pyspark.sql.functions import *
>>> df1 = df.withColumn('rn', row_number().over(Window.partitionBy('apartment', 'job').orderBy('id')))
>>> df2 = df1.join(df1.alias('dfa').where("job='anyl'"),(df1.apartment==dfa.apartment)&(df1.rn==dfa.rn),'leftsemi')
>>> df2.show(truncate=False)
--- --------- ---- ---
|id |apartment|job |rn |
--- --------- ---- ---
|1 |Ap1 |dev |1 |
|2 |Ap1 |anyl|1 |
|3 |Ap2 |dev |1 |
|4 |Ap2 |anyl|1 |
|5 |Ap2 |anyl|2 |
|6 |Ap2 |dev |2 |
|9 |Ap3 |anyl|1 |
|10 |Ap3 |dev |1 |
--- --------- ---- ---
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/479937.html
標籤：阿帕奇火花 pyspark 筛选 apache-spark-sql 窗函数