当前在线人数5606
首页 - 分类讨论区 - 电脑网络 - 数据库版 - 同主题阅读文章

此篇文章共收到打赏
0

  • 10
  • 20
  • 50
  • 100
您目前伪币余额:0
未名交友
[更多]
[更多]
Re: 出个更难的sql题
[版面:数据库][首篇作者:TheMatrix] , 2019年11月04日12:15:41 ,1754次阅读,44次回复
来APP回复,赚取更多伪币 关注本站公众号:
[首页][上页] [下页] [末页][分页:1 2 ]
nmamtf
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 21 ]

发信人: nmamtf (nmamtf), 信区: Database
标  题: Re: 出个更难的sql题
发信站: BBS 未名空间站 (Thu Nov  7 16:01:09 2019, 美东)

呵呵, 我的代碼僅僅是說明一個邏輯判斷過程。

對你的數據, inline 就夠了。 對不定數據, 用我上面說的, 簡單分區就可以了,
連 LAG 都不用。 不過, 初學恐怕不能理解。

要是用 Oracle MODEL 和  DIMENSION BY 和 RULES 就更簡單。
不過我對 SQL server 所知甚少, 不知道它有沒有類似的功能。

Hint is here:

P1[ANY] = DECODE(P1[CV(RN)], 0, NVL(P1[CV(RN) - 1], 0), P1[CV(RN)]),

Can you convert above into SQL ? 呵呵

【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 你这样说我觉得不行。decode inline完成。怎么完成?空白的数目不定,这里必须有
: 一个相当于循环的东西。你完成一下看看嘛。
:  0






--
※ 修改:·nmamtf 於 Nov  7 16:53:37 2019 修改本文·[FROM: 152.]
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 152.]

 
TheMatrix
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 22 ]

发信人: TheMatrix (TheMatrix), 信区: Database
标  题: Re: 出个更难的sql题
发信站: BBS 未名空间站 (Thu Nov  7 18:08:29 2019, 美东)

又想了一下,这个问题实际上没有本版前面出现的那个问题难。用两个row_number相减
的技巧当然也可以,但是实际上是over kill。用普通的区间的办法就可以解决。见附
图。

考虑这个问题和本版前面那个问题的区别,这个问题是要解决一个特殊值,就是空白。
而那个问题每个值都可能形成小的区间。所以这个问题实际上没有那个问题难。

这几天在写一个sql,刚好碰到这个问题,row_number相减的技巧实在忍不住不用。不
过我实际问题中比这个复杂,单线索order by还不行,所以我又改了,改成了目前的做
法。

【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 前面版上有一个sql题比较难,是把record有效区间根据内容扩展,这样使碎片的有效
: 区间可以合并成大的有效区间。这个问题还是一个很常见的问题,我后来又遇到过好几
: 次。本版最佳答案是用两个row_number函数相减。这是神来之笔啊。我自己也做了答案
: ,但是比这个神来之笔差不少。现在我自己的答案我已经不记得了,每次都是用这个神
: 来之笔的答案。
: 最近又碰到一个相关的问题。更难。但是有了前面的基础,应该还是能做出来的。出给
: 大家玩一下。先看一下附图中的数据。
: 这个数据中有一个product和seq。seq是序号,也可以换成data effective date,就是
: 一个顺序的标志,不一定连续。p1,p2是数据内容。但是内容中有空白,用0表示。不是
: 0的地方才是真正的数据。
: ...................




--
☆ 发自 iPhone 买买提 1.24.11
--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 2607:fb90:b68d:]


此主题相关图片如下:

[删除]

 
TheMatrix
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 23 ]

发信人: TheMatrix (TheMatrix), 信区: Database
标  题: Re: 出个更难的sql题
发信站: BBS 未名空间站 (Thu Nov  7 18:15:09 2019, 美东)

SQL Server里没有这些。这些不是函数,是关键字,改不了。

【 在 nmamtf (nmamtf) 的大作中提到: 】
: 呵呵, 我的代碼僅僅是說明一個邏輯判斷過程。
: 對你的數據, inline 就夠了。 對不定數據, 用我上面說的, 簡單分區就可以了

: 連 LAG 都不用。 不過, 初學恐怕不能理解。
: 要是用 Oracle MODEL 和  DIMENSION BY 和 RULES 就更簡單。
: 不過我對 SQL server 所知甚少, 不知道它有沒有類似的功能。
: Hint is here:
: P1[ANY] = DECODE(P1[CV(RN)], 0, NVL(P1[CV(RN) - 1], 0), P1[CV(RN)]),
: Can you convert above into SQL ? 呵呵




--
☆ 发自 iPhone 买买提 1.24.11
--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 2607:fb90:b68d:]

 
nmamtf
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 24 ]

发信人: nmamtf (nmamtf), 信区: Database
标  题: Re: 出个更难的sql题
发信站: BBS 未名空间站 (Thu Nov  7 18:39:43 2019, 美东)

ORACLE 很簡單。 一句話就OK 了。 比如, 對於單一產品 A,

你比較一下, 看看那個簡單?


【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: SQL Server里没有这些。这些不是函数,是关键字,改不了。
: ,



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


此主题相关图片如下:

[删除]

此主题相关图片如下:
[删除]

 
TheMatrix
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 25 ]

发信人: TheMatrix (TheMatrix), 信区: Database
标  题: Re: 出个更难的sql题
发信站: BBS 未名空间站 (Thu Nov  7 19:31:21 2019, 美东)

不错。谢谢。

【 在 nmamtf (nmamtf) 的大作中提到: 】
: ORACLE 很簡單。 一句話就OK 了。 比如, 對於單一產品 A,
: 你比較一下, 看看那個簡單?




--
☆ 发自 iPhone 买买提 1.24.11
--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 2607:fb90:b68d:]

 
mario
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 26 ]

发信人: 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.]

 
mario
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 27 ]

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

比较啰嗦,思路还是比较简单的:
1. 用 rows 1 following and 1 following 去取同组的下一个
2. 用 当前p非空,下个p空找到所有准备当 new_p的
3. 想办法凑 seq 的区间
4. join,如果原表seq落在区间内则取代空的

还有一个思路就是用不等于去join,A的seq <= B 的 seq 且 A.p 非空且 B.p 空, 然
后用analytic function取最接近的一个。
--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 216.]

 
TheMatrix
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 28 ]

发信人: TheMatrix (TheMatrix), 信区: Database
标  题: Re: 出个更难的sql题
发信站: BBS 未名空间站 (Fri Nov  8 09:31:05 2019, 美东)

你这个没有处理好区间的结尾。

【 在 mario (Mario) 的大作中提到: 】
: 比较啰嗦,思路还是比较简单的:
: 1. 用 rows 1 following and 1 following 去取同组的下一个
: 2. 用 当前p非空,下个p空找到所有准备当 new_p的
: 3. 想办法凑 seq 的区间
: 4. join,如果原表seq落在区间内则取代空的
: 还有一个思路就是用不等于去join,A的seq <= B 的 seq 且 A.p 非空且 B.p 空, 然
: 后用analytic function取最接近的一个。




--
☆ 发自 iPhone 买买提 1.24.11
--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 2607:fb90:6c2b:]


此主题相关图片如下:

[删除]

 
nmamtf
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 29 ]

发信人: nmamtf (nmamtf), 信区: Database
标  题: Re: 出个更难的sql题
发信站: BBS 未名空间站 (Fri Nov  8 10:09:22 2019, 美东)

給你一個也許 SQL 能做的方法。 假如還是沒有相應的方法, 只能說 SQL server 太
爛。

--剛剛查了一下, SQL server 有這個功能。 也許這是比較簡單的解決方法。


【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 前面版上有一个sql题比较难,是把record有效区间根据内容扩展,这样使碎片的有效
: 区间可以合并成大的有效区间。这个问题还是一个很常见的问题,我后来又遇到过好几
: 次。本版最佳答案是用两个row_number函数相减。这是神来之笔啊。我自己也做了答案
: ,但是比这个神来之笔差不少。现在我自己的答案我已经不记得了,每次都是用这个神
: 来之笔的答案。
: 最近又碰到一个相关的问题。更难。但是有了前面的基础,应该还是能做出来的。出给
: 大家玩一下。先看一下附图中的数据。
: 这个数据中有一个product和seq。seq是序号,也可以换成data effective date,就是
: 一个顺序的标志,不一定连续。p1,p2是数据内容。但是内容中有空白,用0表示。不是
: 0的地方才是真正的数据。
: ...................




--
※ 修改:·nmamtf 於 Nov  8 11:27:23 2019 修改本文·[FROM: 152.]
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 152.]

此主题相关图片如下:

[删除]

此主题相关图片如下:
[删除]

 
TheMatrix
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 30 ]

发信人: TheMatrix (TheMatrix), 信区: Database
标  题: Re: 出个更难的sql题
发信站: BBS 未名空间站 (Fri Nov  8 11:42:29 2019, 美东)


nvl和nullif是函数,可以改成sql server。
SQL Server 没有ignore nulls,这是一个关键词,改不了。

【 在 nmamtf (nmamtf) 的大作中提到: 】
: 給你一個也許 SQL 能做的方法。 假如還是沒有相應的方法, 只能說 SQL server 太
: 爛。
: --剛剛查了一下, SQL server 有這個功能。 也許這是比較簡單的解決方法。




--
☆ 发自 iPhone 买买提 1.24.11
--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 2607:fb90:6c2b:]

 
nmamtf
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 31 ]

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

儘管這裡有一個關於轉化的信息。 但是, 一個簡單的 QUERY 改成那個樣子, 就沒什
麼意義了。

https://it.toolbox.com/blogs/ganotedp/sql-server-2012-equivalent-to-first-
value-ignore-nulls-031317

SQL server 比甲骨文還是差了很多。 甲骨文的模塊query 可以處理非常複雜的
的運算. 相比之下, SQL server 不知道有什麼相應的產品。



【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: nvl和nullif是函数,可以改成sql server。
: SQL Server 没有ignore nulls,这是一个关键词,改不了。





--
※ 修改:·nmamtf 於 Nov  8 13:01:52 2019 修改本文·[FROM: 152.]
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 152.]

 
TheMatrix
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 32 ]

发信人: TheMatrix (TheMatrix), 信区: Database
标  题: Re: 出个更难的sql题
发信站: BBS 未名空间站 (Fri Nov  8 13:42:12 2019, 美东)

本贴是几乎一样的问题。

oracle功能是多一些,但很多不是标准SQL,用了就lock in了。

【 在 nmamtf (nmamtf) 的大作中提到: 】
: 儘管這裡有一個關於轉化的信息。 但是, 一個簡單的 QUERY 改成那個樣子, 就沒什
: 麼意義了。
: https://it.toolbox.com/blogs/ganotedp/sql-server-2012-equivalent-to-first-
: value-ignore-nulls-031317
: SQL server 比甲骨文還是差了很多。 甲骨文的模塊query 可以處理非常複雜的
: 的咚. 相比之下, SQL server 不知道有什麼相應的產品。




--
☆ 发自 iPhone 买买提 1.24.11
--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 2607:fb90:6c2b:]

 
TheMatrix
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 33 ]

发信人: TheMatrix (TheMatrix), 信区: Database
标  题: Re: 出个更难的sql题
发信站: BBS 未名空间站 (Tue Nov 12 10:59:52 2019, 美东)

这个问题很自然的有两个部分:第一部分是本题,也就是把数值延伸到空白的地方去。
第二部分就是本版前面那个问题,把record按照内容合并,这是一个化简table的步骤
,也可以叫normalize。

所以两个问题是相关的。两个row_number相减虽然是神来之笔,但是本着小步走的原则
,拆成两步,第一步把每个row_number命名,第二步把两个相减用作grouping,这样概
念连续,结构更清晰。来看一下代码。

【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 前面版上有一个sql题比较难,是把record有效区间根据内容扩展,这样使碎片的有效
: 区间可以合并成大的有效区间。这个问题还是一个很常见的问题,我后来又遇到过好几
: 次。本版最佳答案是用两个row_number函数相减。这是神来之笔啊。我自己也做了答案
: ,但是比这个神来之笔差不少。现在我自己的答案我已经不记得了,每次都是用这个神
: 来之笔的答案。
: 最近又碰到一个相关的问题。更难。但是有了前面的基础,应该还是能做出来的。出给
: 大家玩一下。先看一下附图中的数据。
: 这个数据中有一个product和seq。seq是序号,也可以换成data effective date,就是
: 一个顺序的标志,不一定连续。p1,p2是数据内容。但是内容中有空白,用0表示。不是
: 0的地方才是真正的数据。
: ...................




--
☆ 发自 iPhone 买买提 1.24.11
--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 2607:fb90:983d:]


此主题相关图片如下:

[删除]

 
smallburrito
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 34 ]

发信人: smallburrito (smallburrito), 信区: Database
标  题: Re: 出个更难的sql题
发信站: BBS 未名空间站 (Fri Nov 15 17:24:33 2019, 美东)

new_p1好像是跟上一个new_p1和P1有没有值有关系
--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 4.]

 
TheMatrix
进入未名形象秀
我的博客
[回复] [回信给作者] [本篇全文] [本讨论区] [修改] [删除] [转寄] [转贴] [收藏] [举报] [ 35 ]

发信人: TheMatrix (TheMatrix), 信区: Database
标  题: Re: 出个更难的sql题
发信站: BBS 未名空间站 (Fri Nov 15 22:41:21 2019, 美东)

new_p1是p1把空白填上之后的值:如果p1是0(表示空白)那么new_p1等于前面最近的
一个p1非零p1值。

【 在 smallburrito (smallburrito) 的大作中提到: 】
: new_p1好像是跟上一个new_p1和P1有没有值有关系




--
☆ 发自 iPhone 买买提 1.24.11
--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 50.]

[首页][上页] [下页] [末页][分页:1 2 ]
[快速返回] [ 进入数据库讨论区] [返回顶部]
回复文章
标题:
内 容:

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

友情链接


 

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

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