Generate new column by taking one column as a reference using hive or spark SQL

user_13345717 0
Viewed 42

Let's say we have a table as shown below:

    Band |name |age
     ---------------------------
    1        abc   18
    1        xyz   20
    2        aaa   18
    2        bbb   19
    3        ccc   22
    4        ddd   28

Expected output:

    Band |name |age |new_col
    ------------------------------
    1      abc   18  [aaa, bbb, ccc, ddd] 
    1      xyz   20  [aaa, bbb, ccc, ddd] 
    2      aaa   18  [ccc, ddd]
    2      bbb   19  [ccc, ddd]
    3      ccc   22  [ddd]
    4      ddd   28  [null]

Here based on the band, we need to get the new_col. Thank U

pyspark apache-spark-sql hive arrays sql
1 Answers

Try this

select band, name, age, 
       collect_set(name) over(order by band range between 1 following and unbounded following) new_col
 from table_name
user_27003440
Related