Conditional SQL Query with Self Join with given conditions

Question

I have an Iceberg table something like:

Columns: [custID,X, uniqueTransId ------]

[127, 2, a0, -----]

[127,2, a1, -----]

[127,3, a2, -----]

[127,4, a3, -----]

[127,5, a4, -----]

[128,6, a5, -----]

[129, 7, a6, -----]

[129, 8, a7, -----]

[130, 2, a8, -----]

[130, 2, a9, -----]

[Null, 3, a10, -----]

Result Columns: [custId, X, uniqueTransId, newField, ------]

[127, 2, a0, 2_3_4_5, -----]

[127, 2, a1, 2_3_4_5, ------]

[127, 3, a2, 2_4_5, ------]

[127, 4, a3, 2_3_5, ------]

[127, 5, a4, 2_3_4, ------]

[128, 6, a5, Null, ------]

[129, 7, a6, 8, ------]

[129, 8, a7, 7, ------]

[130, 2, a8, 2, ------]

[130, 2, a9, 2, ------]

[Null, 3, a10, Null, ------]

how can we achieve this?

This is kind of for same Customer ID, I need to put X of all instance other than the current one to a new column. For example: For custId 127, there are 4 rows and the Value of field X are 2, 3, 4, 5. For each row, i need the Value of field X of other transactions: For transaction a1, it will be 3,4 and 5. Similarly for transaction a2, it will be 2,4 and 5.

For custId 128, there are no matching rows where same custID is present, so there is no value for new Field and it should be set to Null in result.

May be it doesn't need a self join and some other query can do the work here, but with my limited understanding, it seems a self join problem.

Answer 1

For SQL (untested):

SELECT
      t1.custID
    , t1.X
    , t1.uniqueTransId
    , concat_ws('_', collect_list(t2.X)) AS newfield
FROM custTable t1
LEFT JOIN custTable t2 ON t1.custID = t2.custID AND t1.X != t2.X
GROUP BY
      t1.custID
    , t1.X
    , t1.uniqueTransId
ORDER BY
      t1.custID
    , t1.X;

Here it is (in Postgres) as a fiddle: https://dbfiddle.uk/5vOBqjBA

---

original reply

Try the following - but note it is untested

from pyspark.sql import SparkSession
from pyspark.sql.functions import collect_list, array_remove, array_join, col

# Create a Spark session
spark = SparkSession.builder.getOrCreate()

df = spark.read.format("iceberg").load("custTable")

# group by 'custId' and collect all 'X' values into an array
df_grouped = df.groupBy("custId").agg(collect_list("X").alias("X_values"))

# Perform a self join on 'custId'
df_joined = df.join(df_grouped, "custId")

# exclude the current row's 'X' value
# and concatenate the remaining 'X' values
df_final = df_joined.withColumn("newField", array_join(array_remove("X_values", "X"), "_"))

df_final.show()