CODEWITHSUNDEEP
sqlhivewindow-functions
user2242666
user2242666

Reputation: 211

While I try to use Partition over Hive,

SELECT
    prc.user_key,
    percentile(P.price, 0.5) OVER (PARTITION BY P.user_key) as median_price       
    FROM
    (
     SELECT 
     vfc.user_key,
     vfc.order_id,
     MIN(vddo.cc)  price
     FROM
     td_b.mv_a vfc
     JOIN
     td_b.dim_deal_option vddo
     ON vfc.d_key = vddo.d_key
     WHERE
     vfc.action = 'sale'
     GROUP BY  vfc.user_key, vfc.order_id
    ) prc limit 100;

Gives the error "FAILED: Parse Error: line 4:13 mismatched input '(' expecting FROM near 'OVER' in from clause" in Hive. When i remove the percentile and partition query works fine, any idea? I tried count() instead of percentile, still the same error.

Upvotes: 2

Views: 526

Answers (2)

MaHo
MaHo

Reputation: 316

P.Price and P.user_key do not exist.

Upvotes: 0

spfister
spfister

Reputation: 21

the percentile / partition line aliases the subquery with "P" (P.price and P.user_key) but the subquery is "prc". Sometimes bad aliases will throw that error

Upvotes: 1

Related Questions