【开窗函数】三个SQL题

         本文主要练习一下lag开窗函数的使用！！！

一、第一题

 建表语句：

create table m1(
    dt string,
    name string,
    dept string,
    score int
);
insert into m1 values ('202101','张三','销售',90),
                      ('202101','李四','技术',90),
                      ('202101','王五','运营',80),
                      ('202101','赵六','销售',70),
                      ('202101','孙七','技术',95),
                      ('202101','周八','运营',93),
                      ('202101','吴九','销售',84),
                      ('202101','郑十','技术',83),
                      ('202102','张三','销售',95),
                      ('202102','李四','技术',95),
                      ('202102','王五','运营',95),
                      ('202102','赵六','销售',95);

注意：实际这个是有一年的数据，这里只列举了部分数据 ！

1、计算评分大于等于90的人数以及评分大于等于90的人数环比增长率

with t as (
    select dt,
           dept,
           count(*) people_count
           from m1 where score>=90 group by dt,dept
),t1 as (
   select dt,
        dept,
        people_count,
        lag(people_count,1,people_count) over(partition by dept order by dt) last_count
        from t
) select dt,
         dept,
         people_count,
         (people_count/last_count)-1 huanbi_rate
         from t1;

2、 计算评分有提高的人数

with t as(
    select dt,dept,score,
           lag(score,1) over (partition by name,dept order by dt) last_score
           from m1
)select dt,
        dept,
        count(*) people_count
        from t where score>last_score group by dt,dept;

二、第二题 

建表语句：

create table m2(
    dt string,
    order_id1 string,
    order_id0 string,
    info int,
    product string,
    buyer string,
    amount int,
    num int
);
insert into m2 values
('202101','s100000','s100000',0,'苹果','A',10,2),
('202101','s100001','s100000',1,'苹果','A',-10,2),
('202101','s100002','s100002',0,'西瓜','C',50,1),
('202106','s100003'	,'s100003',0,'西瓜','C',50,1),
('202101','s100004'	,'s100004',0,'西瓜','A',50,1);

注意：实际这个是有一年的数据，这里只列举了部分数据 ！

1、计算购货人数和购货金额 购货金额排名 

with t as(
   select  dt,
        product,
        count(distinct buyer) purchase_count,
        sum(amount) purchase_amount
        from m2 where amount>0 group by  dt,product
)select * ,
        rank() over (partition by dt order by purchase_amount desc) rank
        from t;

2、 计算复购人数

select d1.dt,d1.product,
       count(1) num
       from m2 d1 join m2 d2
       on d1.buyer=d2.buyer and d1.product=d2.product
       where d1.dt-d2.dt<=5 and d1.dt>d2.dt group by d1.dt,d1.product;

三、第三题 

 1、计算每个月购货人同时购买苹果和西瓜的金额

select dt,
       buyer,
       sum(amount) purchase_amount
       from m2 where product in ("苹果","西瓜") and amount>0
       group by dt,buyer
       having count(product)=2;