apache-spark - Aliasing different WINDOW clauses in Spark SQL

Question

Is it possible to have aliases for multiple Windows in the same query ?

For e.g. -

select 
   cust_id,
   eff_dt,
   row_number() over w AS rec1
from cust
WINDOW w AS (PARTITION BY cust_id ORDER BY eff_dt desc);

The above runs fine. But it fails when I try to add another Window alias:

select 
   cust_id,
   eff_dt,
   row_number() over w AS rec1,
   rank() over w2 AS rec2
from cust
WINDOW w AS (PARTITION BY cust_id ORDER BY eff_dt desc),
WINDOW w2 AS (PARTITION BY cust_id ORDER BY version asc);

Can anyone please help on how to use both the Window aliases above ?

Thanks

标签： apache-sparkapache-spark-sql

Answer 1

您可以使用嵌套查询来做到这一点，

select
    cust_id,
    eff_dt,
    rec1,
    rank() over w2 AS rec2
from (
    select
    cust_id,
    eff_dt,
    version,
    row_number() over w AS rec1
    from cust
    WINDOW w AS (PARTITION BY cust_id ORDER BY eff_dt desc))
WINDOW w2 AS (PARTITION BY cust_id ORDER BY version asc);