我的數據格式如下:
料品代碼 天數 單價
WB-D230A-1 4 5.331222
WB-D230A-1 4 5.331222
WB-D230A-1 8 6.15141
WB-D230A-1 85 6.15105
WB-D230A-1 85 6.15105
WB-D230A-1 91 6.15201
WB-D230A-1 97 6.15105
WB-D230A-1 98 6.15105
WB-D230A-1 99 6.15105
WB-D230A-1 105 6.15105
WB-D230A-1 112 6.15492
WB-D230A-1 112 6.15492
WB-D230A-1 120 6.15492
..... ... ....
N條紀錄.
我想根據天數來設置時間段,然後求各個時間段的第一筆紀錄的單價,我最後的效果是下面的格式:
料品代碼 0-15天 15-30天 30-60天 60-90天 90-120天 120天以上
WB-D230A-1 5.331222 0 0 6.15105 6.15201 6.15492
.... ... ... ... ... ... ...
如果某個時間段沒有紀錄,單價用0替代,抓取的各個區間的第一筆紀錄單價,不是該區間的最大單價和最小單價.
請各位高手指點以下.
料品代碼 天數 單價
WB-D230A-1 4 5.331222
WB-D230A-1 4 5.331222
WB-D230A-1 8 6.15141
WB-D230A-1 85 6.15105
WB-D230A-1 85 6.15105
WB-D230A-1 91 6.15201
WB-D230A-1 97 6.15105
WB-D230A-1 98 6.15105
WB-D230A-1 99 6.15105
WB-D230A-1 105 6.15105
WB-D230A-1 112 6.15492
WB-D230A-1 112 6.15492
WB-D230A-1 120 6.15492
..... ... ....
N條紀錄.
我想根據天數來設置時間段,然後求各個時間段的第一筆紀錄的單價,我最後的效果是下面的格式:
料品代碼 0-15天 15-30天 30-60天 60-90天 90-120天 120天以上
WB-D230A-1 5.331222 0 0 6.15105 6.15201 6.15492
.... ... ... ... ... ... ...
如果某個時間段沒有紀錄,單價用0替代,抓取的各個區間的第一筆紀錄單價,不是該區間的最大單價和最小單價.
請各位高手指點以下.
min(case when 天數 <=15 then 單價 else null end ) as "0-15",
min(case when 天數 >15 and 天數 <=30 then 單價 else null end ) as "15-30",
min(case when 天數 >30 and 天數 <=60 then 單價 else null end ) as "30-60"
from test
group by 料品代碼;
料品代碼 天數 單價
WB-D230A-1 4 5.331222
WB-D230A-1 4 5.331222
WB-D230A-1 8 6.15141
WB-D230A-1 85 6.15105
WB-D230A-1 85 6.15105
WB-D230A-1 91 6.15201
WB-D230A-1 97 6.15105
WB-D230A-1 98 6.15105
WB-D230A-1 99 6.15105
WB-D230A-1 105 6.15105
WB-D230A-1 112 6.15492
WB-D230A-1 112 6.15492
WB-D230A-1 120 6.15492
..... ... ....
行列互換,我會的,關鍵是怎麼抓取各個區間的第一筆紀錄.
nvl(min(case
when 天數 <= 15 then
單價
end),
0) "0-15天",
nvl(min(case
when 天數 > 15 and 天數 <= 30 then
單價
end),
0) "15-30天",
nvl(min(case
when 天數 > 30 and 天數 <= 60 then
單價
end),
0) "30-60天",
nvl(min(case
when 天數 > 60 and 天數 <= 90 then
單價
end),
0) "60-90天",
nvl(min(case
when 天數 > 90 and 天數 <= 120 then
單價
end),
0) "90-120天",
nvl(min(case
when 天數 > 120 then
單價
end),
0) "90-120天"
from test
group by 料品代碼
select 料品代碼,
min(case when 天數 <=15 then 單價 else null end ) as "0-15",
min(case when 天數 >15 and 天數 <=30 then 單價 else null end ) as "15-30",
min(case when 天數 >30 and 天數 <=60 then 單價 else null end ) as "30-60"
from
(select row_number() over(partition by trunc(a.天数/15) ORDER BY 天数 ) rn ,a.* from yourtable
)
where rn=1
create table test(料品代碼 varchar(20),天數 int,單價 float);
insert into test select 'WB-D230A-1','4','5.331222' from dual;
insert into test select 'WB-D230A-1','4','5.331222' from dual;
insert into test select 'WB-D230A-1','8','6.15141' from dual;
insert into test select 'WB-D230A-1','85','6.15105' from dual;
insert into test select 'WB-D230A-1','85','6.15105' from dual;
insert into test select 'WB-D230A-1','91','6.15201' from dual;
insert into test select 'WB-D230A-1','97','6.15105' from dual;
insert into test select 'WB-D230A-1','98','6.15105' from dual;
insert into test select 'WB-D230A-1','99','6.15105' from dual;
insert into test select 'WB-D230A-1','105','6.15105' from dual;
insert into test select 'WB-D230A-1','112','6.15492' from dual;
insert into test select 'WB-D230A-1','112','6.15492' from dual;
insert into test select 'WB-D230A-1','120','6.15492' from dual;
--测试
/*select 料品代碼,
min(case when 天數 <=15 then 單價 else 0 end ) as "0-15",
min(case when 天數 >15 and 天數 <=30 then 單價 else 0 end ) as "15-30",
min(case when 天數 >30 and 天數 <=60 then 單價 else 0 end ) as "30-60"
from test
group by 料品代碼;*/
select 料品代碼,
max(case when 天數 ='0-15' then 單價 else 0 end ) as "0-15",
max(case when 天數 ='15-30' then 單價 else 0 end ) as "15-30",
max(case when 天數 ='30-60' then 單價 else 0 end ) as "30-60",
max(case when 天數 ='60-90' then 單價 else 0 end ) as "60-90",
max(case when 天數 ='90-120' then 單價 else 0 end ) as "90-120",
max(case when 天數 ='120以上' then 單價 else 0 end ) as "120以上"
from(
select t.* from(
select a.*,row_number() over(partition by 天數
order by rn)as ro
from (select rownum as rn,case when 天數<15 then '0-15' else
case when 天數<30 then '15-30' else
case TRUNC(天數/30) when 1 then '30-60' when 2 then '60-90' when 3 then '90-120' else '120以上' end end
end as 天數,單價,料品代碼 from test) a)t
where ro=1)tt
group by 料品代碼;
--删除测试环境
drop table test
nvl(min(case
when 天數 <= 15 then
單價
end),
0) "0-15天",
nvl(min(case
when 天數 > 15 and 天數 <= 30 then
單價
end),
0) "15-30天",
nvl(min(case
when 天數 > 30 and 天數 <= 60 then
單價
end),
0) "30-60天",
nvl(min(case
when 天數 > 60 and 天數 <= 90 then
單價
end),
0) "60-90天",
nvl(min(case
when 天數 > 90 and 天數 <= 120 then
單價
end),
0) "90-120天",
nvl(min(case
when 天數 > 120 then
單價
end),
0) "90-120天" from (select 料品代碼, 天數, 單價
from (select 料品代碼,
天數,
單價,
row_number() over(partition by 料品代碼,(case
when 天數 >= 1 and
天數 < 30 then
1
when 天數 >= 30 and
天數 < 60 then
2
when 天數 >= 60 and
天數 < 90 then
3
when 天數 >= 90 and
天數 < 120 then
4
when 天數 >= 120 then
5
end) order by rowid) rn
from test)
where rn = 1) group by 料品代碼
select 料品代碼,
min(case when 天數 <=15 then 單價 else null end ) as "0-15",
min(case when 天數 >15 and 天數 <=30 then 單價 else null end ) as "15-30",
min(case when 天數 >30 and 天數 <=60 then 單價 else null end ) as "30-60",
min(case when 天數 >60 and 天數 <=90 then 單價 else null end ) as "60-90",
min(case when 天數 >90 and 天數 <=120 then 單價 else null end ) as "90-120",
min(case when 天數 >120 then 單價 else null end ) as "120以上"
from
(select row_number() over(partition by trunc(a.天數/15) ORDER BY 天數 ) rn ,a.* from test a)
where rn=1
group by 料品代碼
select 料品代碼,
min(case when 天數 <=15 then 單價 else null end ) as "0-15",
min(case when 天數 >15 and 天數 <=30 then 單價 else null end ) as "15-30",
min(case when 天數 >30 and 天數 <=60 then 單價 else null end ) as "30-60",
min(case when 天數 >60 and 天數 <=90 then 單價 else null end ) as "60-90",
min(case when 天數 >90 and 天數 <=120 then 單價 else null end ) as "90-120",
min(case when 天數 >120 then 單價 else null end ) as "120以上"
from(select 料品代碼,天數,單價 from
(select row_number() over(partition by 料品代碼,trunc(a.天數/30) ORDER BY 天數 ) rn ,a.* from test a)
where rn=1)
group by 料品代碼