最近建立数据立方体需要将表的数据结构进行转换,进行列转行,觉得用途还很大,所以就整理一下,当做自己的笔记拉。
1、列转行(主要)
表weatherdata结构:
create table WEATHERDATA
(
路径 VARCHAR2(100),
日期 VARCHAR2(50),
时间 VARCHAR2(30),
温度 VARCHAR2(50),
P0 VARCHAR2(50),
P VARCHAR2(50),
PA VARCHAR2(50),
湿度 VARCHAR2(10),
风向 VARCHAR2(100),
风速 VARCHAR2(50),
FF10 VARCHAR2(100),
FF3 VARCHAR2(100),
总云量 VARCHAR2(100),
WW VARCHAR2(200),
W1 VARCHAR2(100),
W2 VARCHAR2(100),
TN VARCHAR2(100),
TX VARCHAR2(100),
CL VARCHAR2(200),
NH VARCHAR2(100),
H VARCHAR2(100),
CM VARCHAR2(200),
CH VARCHAR2(200),
VV VARCHAR2(100),
TD VARCHAR2(100),
RRR VARCHAR2(100),
TR VARCHAR2(100),
E VARCHAR2(100),
TG VARCHAR2(100),
E1 VARCHAR2(100),
SSS VARCHAR2(100)
)
数据示例如下:
需要装换的效果:
转换成五列,原来表中温度,P0,P,PA,湿度,风向,风速等对应INDI的属性id值,VALUE为每个属性对应值。
sql代码:
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '24' indi,温度 as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '25' indi,P0 as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '26' indi,P as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '27' indi,PA as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '28' indi,湿度 as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '29' indi,风向 as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '30' indi,风速 as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '31' indi,FF10 as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '32' indi,FF3 as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '33' indi,总云量 as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '34' indi,WW as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '35' indi,W1 as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '36' indi,W2 as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '37' indi,TN as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '38' indi,TX as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '39' indi,CL as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '40' indi,NH as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '41' indi,H as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '42' indi,CM as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '43' indi,CH as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '44' indi,VV as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '45' indi,TD as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '46' indi,RRR as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '47' indi,TR as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '48' indi,E as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '49' indi,TG as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '50' indi,E1 as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
union
select key2 as 路径,to_date(replace(replace(replace(substr(日期,0,12),'年 ','/'),'月 ','/'),'日',''),'yy/mm/dd') as 日期,key as 时间, '51' indi,SSS as value from weatherdata a,T72090608 b,area_id c where a.时间 = b.name and a.路径 = c.路径
order by 路径,日期,时间,indi
按照自己的需求可以参考。
2、行转列
行转列效果相反,主要原理是利用decode函数(将查询结果翻译成其他值)、聚集函数(sum,max,min),结合group by 分组实现。这边简单说下:
sql代码如下:
select t.area,t.date1,t.time,
sum(decode(t.indi, '24', value,null)) as 温度,
sum(decode(t.indi, '25', value,null)) as P0,
sum(decode(t.indi, '26', value,null)) as P,
sum(decode(t.indi, '27', value,null)) as PA,
max(decode(t.indi, '28', value,null)) as 湿度,
max(decode(t.indi, '29', value,null)) as 风向,
sum(decode(t.indi, '30', value,null)) as 风速,
sum(decode(t.indi, '31', value,null)) as FF10,
sum(decode(t.indi, '32', value,null)) as FF3,
sum(decode(t.indi, '33', value,null)) as 总云量,
sum(decode(t.indi, '34', value,null)) as WW,
sum(decode(t.indi, '35', value,null)) as W1,
sum(decode(t.indi, '36', value,null)) as W2,
sum(decode(t.indi, '37', value,null)) as TN,
sum(decode(t.indi, '38', value,null)) as TX,
sum(decode(t.indi, '39', value,null)) as CL,
sum(decode(t.indi, '40', value,null)) as NH,
sum(decode(t.indi, '41', value,null)) as H,
sum(decode(t.indi, '42', value,null)) as CM,
sum(decode(t.indi, '43', value,null)) as CH,
sum(decode(t.indi, '44', value,null)) as VV,
sum(decode(t.indi, '45', value,null)) as TD,
sum(decode(t.indi, '46', value,null)) as RRR,
sum(decode(t.indi, '47', value,null)) as TR,
sum(decode(t.indi, '48', value,null)) as E,
sum(decode(t.indi, '49', value,null)) as TG,
sum(decode(t.indi, '50', value,null)) as E1,
sum(decode(t.indi, '51', value,null)) as SSS
from WEATHER1 t
group by t.area,t.date1,t.time
decode函数代表含义:
if (t.indi==24)
then
return(value)//温度对应值
else
return(null)
注意:行转列时,聚集函数的使用,一般非数字类型的字段不能用sum,可用max或min代替。