oracle转置函数、偏移函数

转置函数

PIVOT，行转列；UNPIVOT，列转行

直接上示例代码：

/*
create table tmp_qzl_1(name varchar2(20), class varchar2(20), grade number);
insert into tmp_qzl_1 (NAME, CLASS, GRADE) values ('张三', '语文', 87);
insert into tmp_qzl_1 (NAME, CLASS, GRADE) values ('张三', '数学', 99);
insert into tmp_qzl_1 (NAME, CLASS, GRADE) values ('张三', '英语', 76);
insert into tmp_qzl_1 (NAME, CLASS, GRADE) values ('王五', '语文', 56);
insert into tmp_qzl_1 (NAME, CLASS, GRADE) values ('王五', '数学', 67);
insert into tmp_qzl_1 (NAME, CLASS, GRADE) values ('王五', '英语', 78);
commit;
*/
select * from tmp_qzl_1;
--1 列转行
select *
  from tmp_qzl_1
pivot(max(grade)
   for class in('语文' as chinese, '数学' as math, '英语' as english));
--2 行转列
with tb as
 (select *
    from tmp_qzl_1
  pivot(max(grade)
     for class in('语文' as chinese,
                  '数学' as math,
                  '英语' as english)))
select *
  from tb unpivot(grade for quarter in(chinese as '语文',
                                       math as '数学',
                                       english as '英语'))

偏移函数

LEAD，向下偏移取数；LAG，向上偏移取数，偏移结果可以作为新的字段计算指标，尤其是涉及升幅、降幅、每日浮动相关指标。

直接上示例代码：

/*
create table tmp_qzl_2(name varchar2(20), class varchar2(20), item number, itemname varchar2(20), grade number);
insert into tmp_qzl_2 (NAME, CLASS, ITEM, ITEMNAME, GRADE) values ('张三', '语文', 1, '第一次模拟考', 87);
insert into tmp_qzl_2 (NAME, CLASS, ITEM, ITEMNAME, GRADE) values ('张三', '语文', 2, '第二次模拟考', 97);
insert into tmp_qzl_2 (NAME, CLASS, ITEM, ITEMNAME, GRADE) values ('张三', '语文', 3, '第三次模拟考', 67);
insert into tmp_qzl_2 (NAME, CLASS, ITEM, ITEMNAME, GRADE) values ('张三', '语文', 4, '第四次模拟考', 57);
commit;
*/
select t.*,
       
       --向上取数，提取上一次模拟考试成绩
       lag(t.grade, 1, null) over(order by t.item) as t1，
       
       --向上取数，自定义未匹配结果
       lag(t.grade, 1, 0) over(order by t.item) as t2，
       
       --向上取数，自定义偏移行数
       lag(t.grade, 2, 0) over(order by t.item) as t3,
       
       --考试成绩浮动，进步、退步
       t.grade - lag(t.grade, 1, null) over(order by t.item) as t4
       
       --向下取数，略
  from tmp_qzl_2 t;

END