【SQL案例】Pivot函数解决社区网友问题

浏览: 2338

今天网友发来一个问题,描述如下:

有一个表(其实是他建的视图):

Clipboard Image.png

Clipboard Image.png

最终效果图如下:

Clipboard Image.png


建表语句如下:

 -- Create table
create table DATAS
(
  id       NUMBER(38) not null,
  modelid  VARCHAR2(8) not null,
  value    VARCHAR2(2000),
  areacode VARCHAR2(10) not null

数据请见附件

然后这位兄台自己建了个视图,就是一开始说的那个。

create view Vdatas as
select id,
case when substr(modelid,7,2)='38' then modelid||'R6_1'||value end as R6_1,
case when substr(modelid,7,2)='39' then modelid||'R6_2'||value end as R6_2,
case when substr(modelid,7,2)='40' then modelid||'R6_3'||value end as R6_3,
case when substr(modelid,7,2)='41' then modelid||'R6_4'||value end as R6_4,
case when substr(modelid,7,2)='43' then modelid||'R6_5'||value end as R6_5
 from datas  where substr(modelid,7,2) in ('38','39','40','41','43')


最一开始我用了很笨的办法:一个视图读五遍

select t1.id, t1.R6_1, t2.R6_2, t3.R6_3, t4.R6_4, t5.R6_5
  from Vdatas t1, Vdatas t2,Vdatas t3, Vdatas t4, Vdatas t5
 where substr(t1.R6_1, 1, 6) = substr(t2.R6_2, 1, 6)
   and substr(t2.R6_2, 1, 6) = substr(t3.R6_3, 1, 6)
   and substr(t3.R6_3, 1, 6) = substr(t4.R6_4, 1, 6)
   and substr(t4.R6_4, 1, 6) = substr(t5.R6_5, 1, 6)
   and t1.id = t2.id
   and t2.id = t3.id
   and t3.id = t4.id
   and t4.id = t5.id
 order by id


后来询问得知预计有2KW数据量,这么算下去5次扫描基本就要读2亿的数据。这个性能太慢,于是重新使用pivot函数实现了。

最后修改后的SQL如下:

with laotouzi as
 (select a.id,
         a.modelid,
         a.value,
         a.areacode,
         substr(modelid, 7, 2) as str,
         substr(modelid, 1, 6) as en,
         case substr(modelid, 7, 2)
           when '38' then
            'R6_1'
           when '39' then
            'R6_2'
           when '40' then
            'R6_3'
           when '41' then
            'R6_4'
           when '43' then
            'R6_5'
         end as title
    from datas a
   where substr(modelid, 7, 2) in ('38', '39', '40', '41', '43'))
select *
  from laotouzi t pivot(max(case str
         when '38' then
          modelid || 'R6_1' || value
         when '39' then
          modelid || 'R6_2' || value
         when '40' then
          modelid || 'R6_3' || value
         when '41' then
          modelid || 'R6_4' || value
         when '43' then
          modelid || 'R6_5' || value
       end) for title in('R6_1', 'R6_2', 'R6_3', 'R6_4', 'R6_5'));


关于Pivot函数可以参考附件的文档学习。

推荐 4
本文由 老头子 创作,采用 知识共享署名-相同方式共享 3.0 中国大陆许可协议 进行许可。
转载、引用前需联系作者,并署名作者且注明文章出处。
本站文章版权归原作者及原出处所有 。内容为作者个人观点, 并不代表本站赞同其观点和对其真实性负责。本站是一个个人学习交流的平台,并不用于任何商业目的,如果有任何问题,请及时联系我们,我们将根据著作权人的要求,立即更正或者删除有关内容。本站拥有对此声明的最终解释权。

2 个评论

爱死这个大神啦,解决了我一直困扰的问题,谢谢你,谢谢你,非常感谢你,推荐他,推荐他,强力推荐他,重要的是说三遍。
感谢分享,学习。

要回复文章请先登录注册