当前在线人数15855
首页 - 分类讨论区 - 电脑网络 - 数据库版 -阅读文章
未名交友
[更多]
[更多]
文章阅读:Re: 出个更难的sql题
[同主题阅读] [版面: 数据库] [作者:mario] , 2019年11月08日00:55:57
mario
进入未名形象秀
我的博客
[上篇] [下篇] [同主题上篇] [同主题下篇]

发信人: mario (Mario), 信区: Database
标  题: Re: 出个更难的sql题
发信站: BBS 未名空间站 (Fri Nov  8 00:56:08 2019, 美东)

with data(product, seq, p1, p2) as (
select 'A', 1, 0, 5
union all select 'A', 2, 0, 0
union all select 'A', 3, 3, 0
union all select 'A', 4, 3, 0
union all select 'A', 5, 0, 0
union all select 'A', 6, 0, 6
union all select 'A', 7, 0, 0
union all select 'A', 8, 4, 0
union all select 'B', 1, 0, 5
union all select 'B', 2, 0, 0
union all select 'B', 3, 3, 0
union all select 'B', 4, 3, 0
union all select 'B', 5, 0, 0
union all select 'B', 6, 0, 6
union all select 'B', 7, 0, 0
union all select 'B', 8, 4, 0
),
-- get p1, p2 on next row --
t1 as
(
select product, seq,
       p1, p2,
       min(p1) over (partition by product order by seq rows between 1
following and 1 following) as next_p1,
       min(p2) over (partition by product order by seq rows between 1
following and 1 following) as next_p2,
       count(*) over (partition by product) as product_cnt
  from data
),
-- for p1, select all rows where current_p1 is not 0 and next_p1 is 0 --
t2_p1 as
(
select product, seq, p1, next_p1, product_cnt
  from t1
where p1 <> 0 and coalesce(next_p1, 0) = 0
),
-- get the seq and next seq --
t3_p1 as
(
select product, seq as seq_begin,
       min(seq) over (partition by product order by seq rows between 1
following and 1 following) - 1 as seq_end,
       p1, next_p1, product_cnt
  from t2_p1
),
-- handle the last record in a product group --
t4_p1 as
(
select product, seq_begin,
       coalesce(seq_end, product_cnt) as seq_end,
       p1, next_p1
  from t3_p1
),
-- repeat the same for p2 --
t2_p2 as
(
select product, seq, p2, next_p2, product_cnt
  from t1
where p2 <> 0 and coalesce(next_p2, 0) = 0
),
t3_p2 as
(
select product, seq as seq_begin,
       min(seq) over (partition by product order by seq rows between 1
following and 1 following) - 1 as seq_end,
       p2, next_p2, product_cnt
  from t2_p2
),
t4_p2 as
(
select product, seq_begin,
       coalesce(seq_end, product_cnt) as seq_end,
       p2, next_p2
  from t3_p2
)
select a.product, a.seq, a.p1, a.p2,
       coalesce(b.p1, a.p1) as new_p1,
       coalesce(c.p2, a.p2) as new_p2
  from data a
       left join t4_p1 b
       on a.product = b.product and a.seq between b.seq_begin and b.seq_end
       left join t4_p2 c
       on a.product = c.product and a.seq between c.seq_begin and c.seq_end



【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 不错。谢谢。



--

※ 来源:·BBS 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 216.]

[上篇] [下篇] [同主题上篇] [同主题下篇]
[转寄] [转贴] [回信给作者] [修改文章] [删除文章] [同主题阅读] [从此处展开] [返回版面] [快速返回] [收藏] [举报]
 
回复文章
标题:
内 容:

未名交友
将您的链接放在这儿

友情链接


 

Site Map - Contact Us - Terms and Conditions - Privacy Policy

版权所有,未名空间(mitbbs.com),since 1996