1.查看某一产品销量城市排行榜前10名，并展示其同环比
思路：取出今日、昨日、上周该商品的销量，通过开窗函数计算同环比
代码：
SELECT
*
FROM
(
SELECT
n.*,
(
( FIRST_VALUE(n.num) OVER (PARTITION by city_id order by dt DESC) -LEAD(n.num,1) OVER (PARTITION by city_id order by dt DESC))*1.0/
LEAD(n.num,1) OVER (PARTITION by city_name order by dt DESC)
)*1.0 as rate_day, ----日环比
(
(FIRST_VALUE(n.num) over(partition by city_id order by dt DESC)-LEAD(n.num,2) OVER (PARTITION by city_id order by dt DESC))*1.0/
(LEAD(n.num,2) OVER (PARTITION by city_id order by dt DESC))
)*1.0 as rate_week ----------周同比
from
(
SELECT
m.*,
n.city_name
from (
SELECT
to_date(to_char(to_date(statis_time,'yyyymmddhh24mi'),'yyyymmdd'),'yyyymmdd') AS dt, --时间
city_name, --城市名称
COALESCE(sum(finish_order_num),0) AS num 销量
FROM 城市销售数据表
WHERE
(
to_date(to_char(to_date(statis_time,'yyyymmddhh24mi'),'yyyymmdd'),'yyyymmdd') = CURRENT_DATE --取出今天数据
or
to_date(to_char(to_date(statis_time,'yyyymmddhh24mi'),'yyyymmdd'),'yyyymmdd') = CURRENT_DATE-1 --取出昨天数据
or
to_date(to_char(to_date(statis_time,'yyyymmddhh24mi'),'yyyymmdd'),'yyyymmdd') = CURRENT_DATE-7 --取出上周数据
)
GROUP BY
city_name
,to_date(to_char(to_date(statis_time,'yyyymmddhh24mi'),'yyyymmdd'),'yyyymmdd')
) as m
WHERE m.dt=CURRENT_DATE --------只筛选当天数据
order by m.num desc ---------按销售额排序、倒叙
limit 10 -------取出前十名
结果展示：
dt 城市 销售额 日环比 周同比
20221223 北京 500000 -0.05 0.1
20221223 上海 400000 -0.6 -0.3
20221223 广州 300000 0.7 0.1
20221223 南京 200000 -0.05 0.1
20221223 武汉 100000 -0.05 0.1
