Oracle行列转换-进阶

发表于 分类于

准备

多行(按季度)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
create table t_fruit_row
(
  id number,
  name varchar(255),
  year varchar(10),
  quarter varchar(255),
  sales number
);
COMMENT ON COLUMN t_fruit_row.year IS '年份';
COMMENT ON COLUMN t_fruit_row.quarter IS '季度';
COMMENT ON COLUMN t_fruit_row.sales IS '销售量(单位吨)';

insert into t_fruit_row values(1, '苹果', '2020', 'Q1', 10);
insert into t_fruit_row values(2, '苹果', '2020', 'Q2', 20);
insert into t_fruit_row values(3, '苹果', '2020', 'Q3', 30);
insert into t_fruit_row values(4, '苹果', '2020', 'Q4', 20);
insert into t_fruit_row values(5, '橘子', '2020', 'Q1', 10);
insert into t_fruit_row values(6, '橘子', '2020', 'Q2', 50);
insert into t_fruit_row values(7, '橘子', '2020', 'Q3', 5);
insert into t_fruit_row values(8, '橘子', '2020', 'Q4', 5);
insert into t_fruit_row values(9, '葡萄', '2020', 'Q1', 15);
insert into t_fruit_row values(10, '葡萄', '2020', 'Q2', 25);
insert into t_fruit_row values(11, '葡萄', '2020', 'Q3', 10);
insert into t_fruit_row values(12, '葡萄', '2020', 'Q4', 30);
insert into t_fruit_row values(13, '芒果', '2020', 'Q1', 5);
insert into t_fruit_row values(14, '芒果', '2020', 'Q2', 15);
insert into t_fruit_row values(15, '芒果', '2020', 'Q3', 25);
insert into t_fruit_row values(16, '芒果', '2020', 'Q4', 35);

insert into t_fruit_row values(17, '苹果', '2021', 'Q1', 1000);
insert into t_fruit_row values(18, '苹果', '2021', 'Q2', 2000);
insert into t_fruit_row values(19, '苹果', '2021', 'Q3', 4000);
insert into t_fruit_row values(20, '苹果', '2021', 'Q4', 8000);
insert into t_fruit_row values(21, '橘子', '2021', 'Q1', 5000);
insert into t_fruit_row values(22, '橘子', '2021', 'Q2', 3000);
insert into t_fruit_row values(23, '橘子', '2021', 'Q3', 8000);
insert into t_fruit_row values(24, '橘子', '2021', 'Q4', 7000);
insert into t_fruit_row values(25, '葡萄', '2021', 'Q1', 3000);
insert into t_fruit_row values(26, '葡萄', '2021', 'Q2', 5000);
insert into t_fruit_row values(27, '葡萄', '2021', 'Q3', 2000);
insert into t_fruit_row values(28, '葡萄', '2021', 'Q4', 9000);
insert into t_fruit_row values(29, '芒果', '2021', 'Q1', 2000);
insert into t_fruit_row values(30, '芒果', '2021', 'Q2', 3000);
insert into t_fruit_row values(31, '芒果', '2021', 'Q3', 6000);
insert into t_fruit_row values(32, '芒果', '2021', 'Q4', 6000);

多列(按季度)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
create table t_fruit_column
(
  id number,
  name varchar(255),
  year varchar(10),
  Q1 number,
  Q2 number,
  Q3 number,
  Q4 number
);
COMMENT ON COLUMN t_fruit_column.year IS '年份';
COMMENT ON COLUMN t_fruit_column.Q1 IS '第一季度';
COMMENT ON COLUMN t_fruit_column.Q2 IS '第二季度';
COMMENT ON COLUMN t_fruit_column.Q3 IS '第三季度';
COMMENT ON COLUMN t_fruit_column.Q4 IS '第四季度';

insert into t_fruit_column values(1, '苹果', '2020', 10, 20, 30, 20);
insert into t_fruit_column values(2, '橘子', '2020', 10, 50, 5, 5);
insert into t_fruit_column values(3, '葡萄', '2020', 15, 25, 10, 30);
insert into t_fruit_column values(4, '芒果', '2020', 5, 15, 25, 35);
insert into t_fruit_column values(5, '苹果', '2021', 1000, 2000, 4000, 8000);
insert into t_fruit_column values(6, '橘子', '2021', 5000, 3000, 8000, 7000);
insert into t_fruit_column values(7, '葡萄', '2021', 3000, 5000, 2000, 9000);
insert into t_fruit_column values(8, '芒果', '2021', 2000, 3000, 6000, 6000);

多行转换成字符串

wm_concat函数

效果1：默认逗号分隔

1
select t.name, to_char(wm_concat(t.quarter)) from t_fruit_row t group by t.name;

效果2：把结果里的逗号替换成 “|”

1
2
3
select t.name, replace(to_char(wm_concat(t.quarter)), ',', '|')
  from t_fruit_row t
 group by t.name;

多列转换成字符串

1
2
select concat(concat(t.id, t.name), t.quarter) from t_fruit_row t;
select t.id || t.name || t.quarter from t_fruit_row t;

字符串转换成多行

union all

字符串转换成多列

实际上就是字符串拆分问题，可以使用 substr、instr、regexp_substr、regexp_instr 结合 decode、case when 函数。

regexp_substr

1
2
3
4
5
-- 找出匹配的数字
select regexp_substr('hello my phone is 520 ', '[0-9]+') from dual;

-- 返回第三个字符串
select regexp_substr('i like china spa', '(\S*)(\s)', 1, 3) from dual;

行转列

decode+wm_concat

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
select t.name,
       t.year,
       to_char(wm_concat(t.Q1)) Q1,
       to_char(wm_concat(t.Q2)) Q2,
       to_char(wm_concat(t.Q3)) Q3,
       to_char(wm_concat(t.Q4)) Q4
  from (select t.name,
               t.year,
               decode(t.quarter, 'Q1', sum(sales), null) Q1,
               decode(t.quarter, 'Q2', sum(sales), null) Q2,
               decode(t.quarter, 'Q3', sum(sales), null) Q3,
               decode(t.quarter, 'Q4', sum(sales), null) Q4
          from t_fruit_row t
         group by t.name, t.year, t.quarter
         order by name) t
 group by t.name, t.year
 order by t.year, t.name;

 
-- 与下面结果对比
select * from t_fruit_column order by year, name;

pivot(行转列)

1
2
3
4
5
6
7
SELECT ...
  FROM  ...
PIVOT [XML]
   (pivot_clause
    pivot_for_clause
    pivot_in_clause )
 WHERE ...

按照序号编写好理解

3、 pivot_clause：指定聚合函数，对值进行聚合。允许指定多个，决定转换后列的倍数。

1、 pivot_for_clause：指定行转列的字段，允许指定多字段。

2、 pivot_in_clause：对 pivot_for_clause 指定的字段进行过滤，只将满足条件的行转成列。可以是固定值、any、子查询(未测试成功)。

转换后的列数 = pivot_clause * pivot_in_clause 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 单字段转列
select *
  from (select name, year, quarter, sales from t_fruit_row) t
pivot(sum(t.sales) as total
   for quarter in('Q1' as Q1, 'Q2' as Q2, 'Q3' as Q3, 'Q4' as Q4)) t2
 order by t2.year, t2.name;
 
-- 与下面结果对比
select * from t_fruit_column order by year, name;

-- 多字段转列
select *
  from (select name, year, quarter, sales from t_fruit_row) t
pivot(sum(t.sales) as total
   for(year, quarter) in(('2020', 'Q1') as "2020_Q1",
                         ('2020', 'Q2') as "2020_Q2",
                         ('2020', 'Q3') as "2020_Q3",
                         ('2020', 'Q4') as "2020_Q4",
                         ('2021', 'Q1') as "2021_Q1",
                         ('2021', 'Q2') as "2021_Q2",
                         ('2021', 'Q3') as "2021_Q3",
                         ('2021', 'Q4') as "2021_Q4")) t2
 order by t2.name;

统计各季度的总销量和该季度最大销量：行转多列 

1
2
3
4
5
select *
  from (select name, quarter, sales from t_fruit_row) t
pivot(sum(t.sales) as total, max(sales) as max
   for quarter in('Q1' as Q1, 'Q2' as Q2, 'Q3' as Q3, 'Q4' as Q4)) t2
 order by t2.name;

按照oracle的文档，pivot语句中in后面的列如果不固定，只能使用xml格式的返回结果

1
2
3
4
5
6
7
8
9
10
11
12
-- 单统计列
select *
  from (select name, year, quarter, sales from t_fruit_row) t
pivot xml(sum(t.sales) as total
   for quarter in(any)) t2
 order by t2.year, t2.name;
-- 多统计列
select *
  from (select name, year, quarter, sales from t_fruit_row) t
pivot xml(sum(t.sales) as total
   for(year, quarter) in(any, any)) t2
 order by t2.name;

列转行

union all

1
2
3
4
5
6
7
8
9
10
11
select t.name, t.year, 'Q1' as quarter, t.Q1 as sales from t_fruit_column t
 union all
select t.name, t.year, 'Q2' as quarter, t.Q2 as sales from t_fruit_column t
 union all
select t.name, t.year, 'Q3' as quarter, t.Q3 as sales from t_fruit_column t
 union all
select t.name, t.year, 'Q4' as quarter, t.Q4 as sales from t_fruit_column t
 order by name, year, quarter;

-- 与下面结果对比
select * from t_fruit_row order by name, year, quarter;

unpivot(列转行)

1
2
3
4
5
6
7
SELECT ...
  FROM  ...
UNPIVOT [INCLUDE|EXCLUDE NULLS]
   (unpivot_clause
    unpivot_for_clause
    unpivot_in_clause )
 WHERE ...

INCLUDE NULLS：保留null数据；

EXCLUDE NULLS（默认）：不保留null数据。

按照序号编写好理解

3、 unpivot_clause：指定原列值对应的新字段名。

2、 unpivot_for_clause：指定列转行后的字段名，允许多个。

1、 unpivot_in_clause：指定具体列到行的字段名。

1
2
3
4
5
6
7
8
select *
  from (select name, year, q1, q2, q3, q4 from t_fruit_column) t
unpivot INCLUDE NULLS (sales
   for quarter in(q1 as '第一季度', q2, q3, q4)) t2
 order by t2.name, t2.year, t2.quarter;

-- 与下面结果对比
select * from t_fruit_row order by name, year, quarter;

懒人扩展用法

**案例：**现在要写一个视图，类似 create or replace view as select 字段1,...字段50 from tablename ，基表有50多个字段，要是靠手工写太麻烦了，有没有什么简便的方法？应用 wm_concat 来让这个需求变简单，如下：

1
2
3
4
-- 这里的表名默认区分大小写
select 'create or replace view viewName as select ' || wm_concat(column_name) || ' from t_fruit_column' as sqlStr
  from user_tab_columns
 where table_name = 'T_FRUIT_COLUMN';