一条比较难实现的SQL,高手帮帮忙呀!

wenqc 2009-11-07 12:35:53
今天遇到了一条比较难的SQL,到现在还不知道怎么实现。请各位高手帮帮忙!!
原表如下:
产品ID 年月 生产天数
04kcmCSanj 200811 23.75
04kcmCSanj 200812 0
04kcmCSanj 200901 0
04kcmCSanj 200902 0
04kcmCSanj 200903 30.50
04kcmCSanj 200904 29.54
04kcmCSanj 200905 0
04kcmCSanj 200906 0
04kcmCSanj 200907 12.50
04kcmCSanj 200908 16.00
04kcmCSanj 200909 0
04kcmCSanj 200910 16.00

要实现的效果如下表,增加一列开始停产年月,当生产天数为0时,表示当月暂停生产,并记录停产的那个月份,如果下个月继续停产时,即连续停产,则只记录开始停产的那个月,生产天数不为0时,停产年月为空。

产品ID 年月 生产天数 开始停产年月
04kcmCSanj 200811 23.75
04kcmCSanj 200812 0 200812
04kcmCSanj 200901 0 200812
04kcmCSanj 200902 0 200812
04kcmCSanj 200903 30.50
04kcmCSanj 200904 29.54
04kcmCSanj 200905 0 200905
04kcmCSanj 200906 0 200905
04kcmCSanj 200907 12.50
04kcmCSanj 200908 16.00
04kcmCSanj 200909 0 200909
04kcmCSanj 200910 16.00
...全文
137 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
小灰狼W 2009-11-07
  • 打赏
  • 举报
回复
如果月份连续的话,可以这样

select * from tt a,
(select min(datetime) starttime
from tt
start with datetime='200906'
connect by prior datetime=to_char(add_months(to_date(datetime,'yyyymm'),1),'yyyymm')
and prior id=id
and time=0
and prior time=time)
where a.datetime='200906'
小灰狼W 2009-11-07
  • 打赏
  • 举报
回复
select a.id,a.datetime,a.time,min(b.datetime) from tt a left join tt b
on a.id=b.id and b.time=0
and a.datetime>=b.datetime
and not exists(select 1 from tt
where datetime>b.datetime and datetime<=a.datetime and time>0)
where a.datetime='200906'
group by a.id,a.datetime,a.time
wenqc 2009-11-07
  • 打赏
  • 举报
回复
[Quote=引用 6 楼 wildwave 的回复:]
是在我上面的语句外面再加一层
SQL codewith ttas(select'04kcmCSanj' id,'200811'datetime,23.75 timefrom dualunionallselect'04kcmCSanj' id,'200812'datetime,0 timefrom dualunionallselect'04kcmCSanj' id,'200901'datetime,0 timefrom dualunionallselect'04kcmCSanj' id,'200902'datetime,0 timefrom dualunionallselect'04kcmCSanj' id,'200903'datetime,30.50 timefrom dualunionallselect'04kcmCSanj' id,'200904'datetime,29.54 timefrom dualunionallselect'04kcmCSanj' id,'200905'datetime,0 timefrom dualunionallselect'04kcmCSanj' id,'200906'datetime,0 timefrom dualunionallselect'04kcmCSanj' id,'200907'datetime,12.50 timefrom dualunionallselect'04kcmCSanj' id,'200908'datetime,16.00 timefrom dualunionallselect'04kcmCSanj' id,'200909'datetime,0 timefrom dualunionallselect'04kcmCSanj' id,'200910'datetime,16.00 timefrom dual)select*from(select id,datetime,time,case timewhen0then first_value(datetime)over(partitionby id,time,rn2-rn1orderbydatetime)end starttimefrom(select tt.*,row_number()over(partitionby id,timeorderbydatetime)rn1,
row_number()over(partitionby idorderbydatetime)rn2from tt)
)wheredatetime='200906'

IDDATETIME TIME STARTTIME
04kcmCSanj2009060200905
[/Quote]


哦,我明白你的意思了,开始没看清楚前面有select * 这层,但这样的话,性能太差了,查一个月的数据时,要先把所有数据取出来后再过滤出当月的数据。
小灰狼W 2009-11-07
  • 打赏
  • 举报
回复
是在我上面的语句外面再加一层
with tt as(select '04kcmCSanj' id, '200811' datetime, 23.75  time from dual union all
select '04kcmCSanj' id, '200812' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200901' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200902' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200903' datetime, 30.50 time from dual union all
select '04kcmCSanj' id, '200904' datetime, 29.54 time from dual union all
select '04kcmCSanj' id, '200905' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200906' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200907' datetime, 12.50 time from dual union all
select '04kcmCSanj' id, '200908' datetime, 16.00 time from dual union all
select '04kcmCSanj' id, '200909' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200910' datetime, 16.00 time from dual)
select * from(
select id,datetime,time,
case time when 0 then first_value(datetime)over(partition by id,time,rn2-rn1 order by datetime) end starttime
from(select tt.*,row_number()over(partition by id,time order by datetime)rn1,
row_number()over(partition by id order by datetime)rn2
from tt)
)where datetime='200906'

ID DATETIME TIME STARTTIME
04kcmCSanj 200906 0 200905
wenqc 2009-11-07
  • 打赏
  • 举报
回复
[Quote=引用 4 楼 wildwave 的回复:]
select * from(
select id,datetime,time,
  case time when 0 then first_value(datetime)over(partition by id,time,rn2-rn1 order by datetime) end starttime
from(select tt.*,row_number()over(partition by id,time order by datetime)rn1,
  row_number()over(partition by id order by datetime)rn2
  from tt)
)where datetime='200906'

把条件加在外边
[/Quote]


对啊,把条件加在外边,但得到的结果是:
04kcmCSanj 200906 0 200906
即是开始停产日期是当月的了,但实际上,上个月或前几个月就开始停产了
所以我要得到的结果应该是
04kcmCSanj 200906 0 200905
小灰狼W 2009-11-07
  • 打赏
  • 举报
回复
select * from(
select id,datetime,time,
case time when 0 then first_value(datetime)over(partition by id,time,rn2-rn1 order by datetime) end starttime
from(select tt.*,row_number()over(partition by id,time order by datetime)rn1,
row_number()over(partition by id order by datetime)rn2
from tt)
)where datetime='200906'

把条件加在外边
wenqc 2009-11-07
  • 打赏
  • 举报
回复
[Quote=引用 1 楼 wildwave 的回复:]
SQL codewith ttas(select'04kcmCSanj' id,'200811'datetime,23.75 timefrom dualunionallselect'04kcmCSanj' id,'200812'datetime,0 timefrom dualunionallselect'04kcmCSanj' id,'200901'datetime,0 ti?-
[/Quote]
真强!!原来分析函数还可以这么用法,还有一点小问题。
如统计出来的数据
04kcmCSanj 200904 29.54
04kcmCSanj 200905 0 200905
04kcmCSanj 200906 0 200905
5月和6月的都为0,按你那SQL写时,如果只想查询200906的数据,即是在后面加个条件datetime = '200906'
这样查询出来的结果是
04kcmCSanj 200906 0 200906
我实际想得到的结果
04kcmCSanj 200906 0 200905
这样应该怎样实现呢?麻烦大大再指教一下,因为要实现增量更新,每个月更新一次。

小灰狼W 2009-11-07
  • 打赏
  • 举报
回复
再写一个,没有上面的好
with tt as(select '04kcmCSanj' id, '200811' datetime, 23.75  time from dual union all
select '04kcmCSanj' id, '200812' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200901' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200902' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200903' datetime, 30.50 time from dual union all
select '04kcmCSanj' id, '200904' datetime, 29.54 time from dual union all
select '04kcmCSanj' id, '200905' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200906' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200907' datetime, 12.50 time from dual union all
select '04kcmCSanj' id, '200908' datetime, 16.00 time from dual union all
select '04kcmCSanj' id, '200909' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200910' datetime, 16.00 time from dual)

select id,datetime,time,decode(time,0,d)starttime
from(
select t1.*,connect_by_root datetime d from (
select tt.*,row_number()over(partition by id order by datetime)rn,
lag(time)over(partition by id order by datetime)lg from tt)t1
start with time=0 and lg>0 or time>0
connect by prior rn=rn-1 and prior time=time and time=0)

ID DATETIME TIME STARTTIME
04kcmCSanj 200811 23.75
04kcmCSanj 200812 0 200812
04kcmCSanj 200901 0 200812
04kcmCSanj 200902 0 200812
04kcmCSanj 200903 30.5
04kcmCSanj 200904 29.54
04kcmCSanj 200905 0 200905
04kcmCSanj 200906 0 200905
04kcmCSanj 200907 12.5
04kcmCSanj 200908 16
04kcmCSanj 200909 0 200909
04kcmCSanj 200910 16
小灰狼W 2009-11-07
  • 打赏
  • 举报
回复
with tt as(select '04kcmCSanj' id, '200811' datetime, 23.75  time from dual union all
select '04kcmCSanj' id, '200812' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200901' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200902' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200903' datetime, 30.50 time from dual union all
select '04kcmCSanj' id, '200904' datetime, 29.54 time from dual union all
select '04kcmCSanj' id, '200905' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200906' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200907' datetime, 12.50 time from dual union all
select '04kcmCSanj' id, '200908' datetime, 16.00 time from dual union all
select '04kcmCSanj' id, '200909' datetime, 0 time from dual union all
select '04kcmCSanj' id, '200910' datetime, 16.00 time from dual)

select id,datetime,time,
case time when 0 then first_value(datetime)over(partition by id,time,rn2-rn1 order by datetime) end starttime


from(select tt.*,row_number()over(partition by id,time order by datetime)rn1,
row_number()over(partition by id order by datetime)rn2
from tt)
order by 1,2

ID DATETIME TIME STARTTIME
04kcmCSanj 200811 23.75
04kcmCSanj 200812 0 200812
04kcmCSanj 200901 0 200812
04kcmCSanj 200902 0 200812
04kcmCSanj 200903 30.5
04kcmCSanj 200904 29.54
04kcmCSanj 200905 0 200905
04kcmCSanj 200906 0 200905
04kcmCSanj 200907 12.5
04kcmCSanj 200908 16
04kcmCSanj 200909 0 200909
04kcmCSanj 200910 16
wenqc 2009-11-07
  • 打赏
  • 举报
回复
非常感谢wildwave兄不断的帮忙!!这么多种方法还是1楼回复那个方法性能最高,决定采纳那个方法了,最近在做一个项目,遇到不懂的还需要wildwave兄和各位高手以后多多帮忙。。再次谢谢啦~~~

3,492

社区成员

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧