SQL常见查询-SELECT

一、查看某一产品销量城市排行榜前10名，并展示其同环比

主要考察：

        开窗函数：first_value（num） over（partition by index1 order by index2 desc）--第一个数据、 lead（num,1） over（partition by index1 order by index2 desc）--第二个数据

思路：取出今日、昨日、上周该商品的销量，通过开窗函数计算同环比

代码：

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