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%的品
