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

此篇文章共收到打赏
0

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

发信人: TheMatrix (TheMatrix), 信区: Database
标  题: 一个sql题
发信站: BBS 未名空间站 (Fri Jul 21 23:58:32 2017, 美东)

最近做了一个sql问题,我觉得有点难度,给大家试试:

有一个table,有4个column:
item, effective_date, expiration_date, price

有如下records:
A, 2017-01-10, 2017-01-20, 12.50
A, 2017-01-20, 2017-02-10, 12.50
A, 2017-02-10, 2017-03-01, 13.25
A, 2017-03-01, 2017-04-01, 12.50
A, 2017-04-01, 2017-04-21, 12.50
B, 2017-01-10, 2017-01-20, 12.50
B, 2017-01-20, 2017-02-10, 12.50
B, 2017-02-10, 2017-03-01, 13.25
B, 2017-03-01, 2017-04-01, 12.50
B, 2017-04-01, 2017-04-21, 12.50

table的primary key是item, effective_date。
可以假定一个item的所有records其effective_date和expiration_date是首尾相连的。
要求写一个sql,把同一个item同一个price的effective period merge起来。

结果应该是:
A, 2017-01-10, 2017-02-10, 12.50
A, 2017-02-10, 2017-03-01, 13.25
A, 2017-03-01, 2017-04-21, 12.50
B, 2017-01-10, 2017-02-10, 12.50
B, 2017-02-10, 2017-03-01, 13.25
B, 2017-03-01, 2017-04-21, 12.50


--
※ 修改:·TheMatrix 於 Jul 22 00:50:47 2017 修改本文·[FROM: 50.]
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 50.]

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

发信人: didadida (滴滴嗒嗒), 信区: Database
标  题: 一个sql题
发信站: BBS 未名空间站 (Sat Jul 22 07:06:37 2017, 美东)

select item, min(effective),max(expiration),price
From tableName
Group by item,price


手机输入
大致如此

过于简单,难道我理解错了?
--
※ 修改:·didadida 於 Jul 22 07:12:51 2017 修改本文·[FROM: 71.]
※ 来源:· 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 71.]

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

发信人: TheMatrix (TheMatrix), 信区: Database
标  题: Re: 一个sql题
发信站: BBS 未名空间站 (Sat Jul 22 09:08:12 2017, 美东)

你这样得到的结果是:
A, 2017-01-10, 2017-04-21, 12.50
A, 2017-02-10, 2017-03-01, 13.25
B, 2017-01-10, 2017-04-21, 12.50
B, 2017-02-10, 2017-03-01, 13.25

这样不对。问题的难点在于price可以在不同的时间段重复出现。

【 在 didadida (滴滴嗒嗒) 的大作中提到: 】
: select item, min(effective),max(expiration),price
: From tableName
: Group by item,price
: 手机输入
: 大致如此
: 过于简单,难道我理解错了?



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

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

发信人: didadida (滴滴嗒嗒), 信区: Database
标  题: Re: 一个sql题
发信站: BBS 未名空间站 (Sat Jul 22 21:22:41 2017, 美东)

买买提不让我贴SQL
只好贴图了


【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 你这样得到的结果是:
: A, 2017-01-10, 2017-04-21, 12.50
: A, 2017-02-10, 2017-03-01, 13.25
: B, 2017-01-10, 2017-04-21, 12.50
: B, 2017-02-10, 2017-03-01, 13.25
: 这样不对。问题的难点在于price可以在不同的时间段重复出现。




--
※ 修改:·didadida 於 Jul 22 21:26:53 2017 修改本文·[FROM: 71.]
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 71.]

此主题相关图片如下:

[删除]

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

发信人: didadida (滴滴嗒嗒), 信区: Database
标  题: Re: 一个sql题
发信站: BBS 未名空间站 (Sat Jul 22 21:25:13 2017, 美东)

添加了个auto numbered 字段做PK
其实不加也无所谓

【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 你这样得到的结果是:
: A, 2017-01-10, 2017-04-21, 12.50
: A, 2017-02-10, 2017-03-01, 13.25
: B, 2017-01-10, 2017-04-21, 12.50
: B, 2017-02-10, 2017-03-01, 13.25
: 这样不对。问题的难点在于price可以在不同的时间段重复出现。



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


此主题相关图片如下:

[删除]

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

发信人: TheMatrix (TheMatrix), 信区: Database
标  题: Re: 一个sql题
发信站: BBS 未名空间站 (Sat Jul 22 23:49:40 2017, 美东)

嗯。逻辑上可以。

你这个recursive cte性能上应该不行。
我从来没用过recursive cte。
我的版本是用analytic functions.

再看看大家还有没有什么版本。

【 在 didadida (滴滴嗒嗒) 的大作中提到: 】
: 买买提不让我贴SQL
: 只好贴图了



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

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

发信人: didadida (滴滴嗒嗒), 信区: Database
标  题: Re: 一个sql题
发信站: BBS 未名空间站 (Sun Jul 23 09:05:00 2017, 美东)

你能不能贴一下你的版本,学习一下

【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 嗯。逻辑上可以。
: 你这个recursive cte性能上应该不行。
: 我从来没用过recursive cte。
: 我的版本是用analytic functions.
: 再看看大家还有没有什么版本。



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

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

发信人: TheMatrix (TheMatrix), 信区: Database
标  题: Re: 一个sql题
发信站: BBS 未名空间站 (Sun Jul 23 09:26:50 2017, 美东)

这个网站确实不能贴sql啊。以前还真不知道。
我看看有什么办法,不行我也贴图。

【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 嗯。逻辑上可以。
: 你这个recursive cte性能上应该不行。
: 我从来没用过recursive cte。
: 我的版本是用analytic functions.
: 再看看大家还有没有什么版本。



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

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

发信人: TheMatrix (TheMatrix), 信区: Database
标  题: Re: 一个sql题
发信站: BBS 未名空间站 (Sun Jul 23 09:28:56 2017, 美东)

______with
______prev_price as (
______select *
______, lag(price) over (partition by item order by effective_date) prev_
price
______from Product
______),
______change_price as (
______select *
______, case when price = prev_price then 0 else 1 end change
______from prev_price
______),
______grouping_price as (
______select *
______, sum(change) over (partition by item order by effective_date)
grouping
______from change_price
______)
______select item
______, min(effective_date) effective_date
______, max(expiration_date) expiration_date
______, price
______from grouping_price
______group by item, price, grouping
______order by item, effective_date;

【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 这个网站确实不能贴sql啊。以前还真不知道。
: 我看看有什么办法,不行我也贴图。




--
※ 修改:·TheMatrix 於 Jul 23 09:31:32 2017 修改本文·[FROM: 50.]
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 50.]

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

发信人: TheMatrix (TheMatrix), 信区: Database
标  题: Re: 一个sql题
发信站: BBS 未名空间站 (Sun Jul 23 09:39:19 2017, 美东)

table: Product
item 20
effective_date date
expiration_date date
price decimal(10, 2)

000000insert into Product values ('A', '2017-01-10', '2017-01-20', 12.50);
000000insert into Product values ('A', '2017-01-20', '2017-02-10', 12.50);
000000insert into Product values ('A', '2017-02-10', '2017-03-01', 13.25);
000000insert into Product values ('A', '2017-03-01', '2017-04-01', 12.50);
000000insert into Product values ('A', '2017-04-01', '2017-04-21', 12.50);
000000insert into Product values ('B', '2017-01-10', '2017-01-20', 12.50);
000000insert into Product values ('B', '2017-01-20', '2017-02-10', 12.50);
000000insert into Product values ('B', '2017-02-10', '2017-03-01', 13.25);
000000insert into Product values ('B', '2017-03-01', '2017-04-01', 12.50);
000000insert into Product values ('B', '2017-04-01', '2017-04-21', 12.50);

【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: ______with
: ______prev_price as (
: ______select *
: ______, lag(price) over (partition by item order by effective_date) prev_
: price
: ______from Product
: ______),
: ______change_price as (
: ______select *
: ______, case when price = prev_price then 0 else 1 end change
: ...................




--
※ 修改:·TheMatrix 於 Jul 23 09:44:58 2017 修改本文·[FROM: 50.]
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 50.]

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

发信人: TheMatrix (TheMatrix), 信区: Database
标  题: Re: 一个sql题
发信站: BBS 未名空间站 (Sun Jul 23 19:53:26 2017, 美东)

贴一个图。

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


此主题相关图片如下:

[删除]

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

发信人: AlexanderZ (), 信区: Database
标  题: Re: 一个sql题
发信站: BBS 未名空间站 (Thu Jul 27 17:59:40 2017, 美东)

请问楼主花了多少时间做了出来,不计中间被工作打断的时间,本人未看答案花了大概
3个小时才写出类似的方案,求差距。
--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 206.]

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

发信人: AlexanderZ (), 信区: Database
标  题: Re: 一个sql题
发信站: BBS 未名空间站 (Thu Jul 27 18:21:36 2017, 美东)

一开始把问题想简单了,中间又走了不少弯路,后来又想通过简单标准sql(即不用lag
/lead)来实现,浪费了不少时间,想想如果是面试,必挂:-(
--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 206.]

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

发信人: lemondream (lemon), 信区: Database
标  题: Re: 一个sql题
发信站: BBS 未名空间站 (Thu Jul 27 21:34:23 2017, 美东)

幼稚。groupby item, price。sql 有什么难题
--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 74.]

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

发信人: TheMatrix (TheMatrix), 信区: Database
标  题: Re: 一个sql题
发信站: BBS 未名空间站 (Thu Jul 27 21:47:23 2017, 美东)

我大概也花了3个小时的时间。我这个是工作中遇到的问题。开始我也是group by item
, price,看了结果发现不对,再改,也用了不少时间。如果是面试题肯定做不出来。

【 在 AlexanderZ () 的大作中提到: 】
: 请问楼主花了多少时间做了出来,不计中间被工作打断的时间,本人未看答案花了大概
: 3个小时才写出类似的方案,求差距。



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

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

发信人: TheMatrix (TheMatrix), 信区: Database
标  题: Re: 一个sql题
发信站: BBS 未名空间站 (Thu Jul 27 21:59:42 2017, 美东)

标准sql相当于record list/set processing,但是它缺乏通用程序语言的map/reduce
所需要的function,也就是缺乏普通function和aggregate function。所以应该有很多
问题单纯用sql做不了。

【 在 lemondream (lemon) 的大作中提到: 】
: 幼稚。groupby item, price。sql 有什么难题



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

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

发信人: wyr (遗忘小资--其实我是挖坑的), 信区: Database
标  题: Re: 一个sql题
发信站: BBS 未名空间站 (Fri Jul 28 01:26:21 2017, 美东)

这题我5分钟就做完了………………小菜
还有lead/lag这个function好些SQL engine还不支持,直接上ANSI window function


with t as(
SELECT
A.*,
case when AVG(PRICE) over (PARTITION BY PRODUCT order by effective_date rows
between 1 preceding and 1 preceding) = price then 0 else 1 end as flg
FROM P A) ,
t1 as (
select
a.*,
sum(flg) over (partition by product order by effective_date rows between
unbounded preceding and current row) as flg2
from t a
)
select product , price, flg2, min(effective_date), max(expiration_date) from
t1
group by product, price, flg2
--
※ 来源:·WWW 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 70.]

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

发信人: TheMatrix (TheMatrix), 信区: Database
标  题: Re: 一个sql题
发信站: BBS 未名空间站 (Fri Jul 28 08:42:03 2017, 美东)

这个问题不用SQL window funtion 做还真是不好做,好像只有didadida的recursive
cte的方法了。


【 在 wyr(遗忘小资--其实我是挖坑的) 的大作中提到: 】
<br>: 这题我5分钟就做完了………………小菜
<br>: 还有lead/lag这个function好些SQL engine还不支持,直接上ANSI window
function
<br>: with t as(
<br>: SELECT
<br>: A.*,
<br>: case when AVG(PRICE) over (PARTITION BY PRODUCT order by effective_
date rows
<br>:  between 1 preceding and 1 preceding) = price then 0 else 1 end as flg
<br>: FROM P A) ,
<br>:  t1 as (
<br>: select
: ...................
<br>
--
※ 来源:· 未名空间站 网址:mitbbs.com 移动:在应用商店搜索未名空间·[FROM: 2607:fb90:2cf:9]

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

发信人: AlexanderZ (), 信区: Database
标  题: Re: 一个sql题
发信站: BBS 未名空间站 (Fri Jul 28 10:40:11 2017, 美东)

神速,佩服。
【 在 wyr (遗忘小资--其实我是挖坑的) 的大作中提到: 】
: 这题我5分钟就做完了………………小菜
: 还有lead/lag这个function好些SQL engine还不支持,直接上ANSI window function
: with t as(
: SELECT
: A.*,
: case when AVG(PRICE) over (PARTITION BY PRODUCT order by effective_date
rows
:  between 1 preceding and 1 preceding) = price then 0 else 1 end as flg
: FROM P A) ,
:  t1 as (
: select
: ...................



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

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

发信人: lemondream (lemon), 信区: Database
标  题: Re: 一个sql题
发信站: BBS 未名空间站 (Fri Jul 28 11:37:49 2017, 美东)

我怎么没看懂这个 output 为什么不对呀? 您能不能把正确的output贴出来。
这个难道不是你要的?
【 在 TheMatrix (TheMatrix) 的大作中提到: 】
: 你这样得到的结果是:
: A, 2017-01-10, 2017-04-21, 12.50
: A, 2017-02-10, 2017-03-01, 13.25
: B, 2017-01-10, 2017-04-21, 12.50
: B, 2017-02-10, 2017-03-01, 13.25
: 这样不对。问题的难点在于price可以在不同的时间段重复出现。



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

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

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

友情链接


 

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

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