SQL统计城市商品销量&对应销量排名

--城市大类销量与排名
--运用窗口函数排序、增加排名列--

#tmp
insert into #tmp
select distinct
--distinct  去除重复数据--
    b.city,
    c.code,
    c.name,
    left(c.sort,2) as daleicode,
    --count(distinct(a.storecode))  rank--
    sum(a.realamt) over( PARTITION by b.city,a.gid) as city_xse,
    sum(a.qty) over( PARTITION by b.city,a.gid) as city_xl,

--代替窗口函数不支持distinct的功能的，count（distinct(a.storecode))  over(partition by XXX))--
    size(collect_set(a.storecode) over(partition by b.city,a.gid)) as xsmds
   

from 
    bdbase_gd.buy_detailed_lv0 a,
    bdbase_gd.store b,
    bdbase_gd.goods c

where 
    a.storecode = b.code
    and a.gid = c.gid
    and a.batch_date>='20221201'
    and a.batch_date<='20221205' 
    --and b.city in ('东莞市','广州市','深圳市')--

#tmp1