Day04 2020.11.18 开窗函数LAG()的应用

题目

有如下ABC三列和几组数据

想得到如下结果

该如何写查询？
提示：可以使用聚合函数或者lag函数来求解

数据准备

    create table day_04 (
    a varchar(20),
    b int,
    c varchar(10)
    );
    insert into day_04 values('aaa',1,'X');
    insert into day_04 values('aaa',2,'Y');
    insert into day_04 values('bbb',3,'X');
    insert into day_04 values('bbb',4,'X');
    insert into day_04 values('ccc',5,'Y');
    insert into day_04 values('ccc',6,'Y');

解题思路

1. 题目提示lag函数,利用开窗函数先求出C字段中两两是否相同,不同则为1,相同则为C字段 ```sql select a , sum(b) over(partition by a) as b , case when lag(c,1) over(partition by a order by b) = c then c else ‘1’ end as c from day_04

![image.png](https://cdn.nlark.com/yuque/0/2020/png/2586916/1605663125616-5877148e-aeed-4898-93fd-b5f695280614.png#align=left&display=inline&height=164&margin=%5Bobject%20Object%5D&name=image.png&originHeight=164&originWidth=212&size=4379&status=done&style=none&width=212)
2. 再通过rn=1取出每个组的第一行
```sql
    select a,b,c 
    from 
    (
    select a 
    , sum(b) over(partition by a) as b 
    , case when lag(c,1) over(partition by a order by b) = c then c else '1' end as c 
    , row_number() over(partition by a order by b desc) as rn 
    from day_04 
    ) t1 where t1.rn = 1 ;