SQL查詢：
SELECT dt,
id,
a.val1,
CASE
WHEN b.val1 = FALSE THEN TRUE
ELSE FALSE
END AS inf,
CASE
WHEN b.val1 = FALSE THEN coalesce(b.val2, a.val2)
ELSE a.val2
END mob,
CASE
WHEN b.val1 = FALSE THEN coalesce(b.val3, a.val3)
ELSE a.val3
END cli,
a.val3 fam,
count(*) unique_val
FROM c
JOIN a ON (c.e_id = a.e_id)
LEFT OUTER JOIN b ON (c.m_id = b.m_id)
GROUP BY 1,
2,
3,
4,
5,
6,
7
我正在嘗試將此查詢轉換為 python spark 版本。我嘗試了不同的方法，但它不適用于加入多個資料幀。
我的 PySpark 代碼版本以錯誤告終：
joined = c.join(a, c.e_id == a.e_id).join(b, c.m_id == b.m_id, "left_outer")
df = (
joined.select(["dt", "id", "a.val1"])
.withColumn("inf", when(F.col("b.val1") == False, True).otherwise(False))
.withColumn(
"mob",
when(F.col("b.val1") == False, coalesce(b.val2, a.val2)).otherwise(a.val2),
)
.withColumn(
"cli",
when(F.col("b.val1") == False, coalesce(b.val3, a.val3)).otherwise(a.val3),
)
.withColumnRenamed("a.val3", "fam")
.groupby(["dt", "id", "a.val1", "inf", "mob", "cli", "fam"])
.count()
.withColumnRenamed("count", "unique_val")
)
如果有人可以提供幫助，將不勝感激。謝謝你。
uj5u.com熱心網友回復：
- 更換
df.column_name與df["column_name"]作為托架內字串不區分大小寫的。 - 您是否選擇作為一個陳述句，而
withColumn在您的 sql 查詢中沒有as。 b["val1"] == False應替換為~b["val1"](sqlnot b.val1)- 如果您不解釋錯誤是什么（因為我們無法在沒有任何相關資料的情況下重現您當前的代碼），則幾乎不可能理解可能出錯的地方。
df = (
joined.groupBy(
"dt",
"id",
a["val1"],
when(~b["val1"], True).otherwise(False).alias("inf"),
when(~b["val1"], coalesce(b["val2"], a["val2"])).otherwise(a["val2"]).alias("mob"),
when(~b["val1"], coalesce(b["val3"], a["val3"])).otherwise(a["val3"]).alias("cli"),
a["val3"].alias("fam"),
)
.count()
.withColumnRenamed("count", "unique_val")
)
你有沒有嘗試過 ：
df = spark.sql("""
SELECT dt,
id,
a.val1,
CASE
WHEN b.val1 = FALSE THEN TRUE
ELSE FALSE
END AS inf,
CASE
WHEN b.val1 = FALSE THEN coalesce(b.val2, a.val2)
ELSE a.val2
END mob,
CASE
WHEN b.val1 = FALSE THEN coalesce(b.val3, a.val3)
ELSE a.val3
END cli,
a.val3 fam,
count(*) unique_val
FROM c
JOIN a ON (c.e_id = a.e_id)
LEFT OUTER JOIN b ON (c.m_id = b.m_id)
GROUP BY 1,
2,
3,
4,
5,
6,
7
""")
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/358086.html