取排名前50%的数据

 1     SELECT
 2         goods_id,
 3         mall_id,
 4         gmv
 5     FROM(
 6         SELECT
 7             goods_id,
 8             mall_id,
 9             gmv,
10             dense_rank() over (partition by mall_id order by gmv desc ) as dr_desc,
11             dense_rank() over (partition by mall_id order by gmv ) as dr_asc
12         FROM(
13             SELECT
14                 goods_id,
15                 mall_id,
16                 SUM(amt) as gmv
17             FROM
18                 table
19             WHERE
20                 pt <= '2021-05-01'
21                 AND pt > date_sub('2021-05-01',7)
22             GROUP BY 
23                 goods_id,
24                 mall_id
25             HAVING
26                 SUM(amt) > 0
27             )t11 
28         )t12 
29     WHERE
30         dr_desc >= (dr_asc+dr_desc)/2    ---- 取gmv排序前50%的品

posted on 2021-05-20 21:31 大鹏的鸿鹄之志阅读(167) 评论(0) 收藏举报