T*******x
发帖数: 8565 | 1 最近做了一个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 | d******a
发帖数: 32122 | 2 select item, min(effective),max(expiration),price
From tableName
Group by item,price
手机输入
大致如此
过于简单，难道我理解错了？ | T*******x
发帖数: 8565 | 3 你这样得到的结果是：
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可以在不同的时间段重复出现。
【在 d******a 的大作中提到】
: select item, min(effective),max(expiration),price
: From tableName
: Group by item,price
: 手机输入
: 大致如此
: 过于简单，难道我理解错了？
| d******a
发帖数: 32122 | 4 买买提不让我贴SQL
只好贴图了
【在 T*******x 的大作中提到】
: 你这样得到的结果是：
: 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可以在不同的时间段重复出现。
| d******a
发帖数: 32122 | 5 添加了个auto numbered 字段做PK
其实不加也无所谓
【在 T*******x 的大作中提到】
: 你这样得到的结果是：
: 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可以在不同的时间段重复出现。
| T*******x
发帖数: 8565 | 6 嗯。逻辑上可以。
你这个recursive cte性能上应该不行。
我从来没用过recursive cte。
我的版本是用analytic functions.
再看看大家还有没有什么版本。
【在 d******a 的大作中提到】
: 买买提不让我贴SQL
: 只好贴图了
| d******a
发帖数: 32122 | 7 你能不能贴一下你的版本，学习一下
【在 T*******x 的大作中提到】
: 嗯。逻辑上可以。
: 你这个recursive cte性能上应该不行。
: 我从来没用过recursive cte。
: 我的版本是用analytic functions.
: 再看看大家还有没有什么版本。
| T*******x
发帖数: 8565 | 8 这个网站确实不能贴sql啊。以前还真不知道。
我看看有什么办法，不行我也贴图。
【在 T*******x 的大作中提到】
: 嗯。逻辑上可以。
: 你这个recursive cte性能上应该不行。
: 我从来没用过recursive cte。
: 我的版本是用analytic functions.
: 再看看大家还有没有什么版本。
| T*******x
发帖数: 8565 | 9 ______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;
【在 T*******x 的大作中提到】
: 这个网站确实不能贴sql啊。以前还真不知道。
: 我看看有什么办法，不行我也贴图。
| T*******x
发帖数: 8565 | 10 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);
【在 T*******x 的大作中提到】
: ______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
| | | T*******x
发帖数: 8565 | | A********Z
发帖数: 18 | 12 请问楼主花了多少时间做了出来，不计中间被工作打断的时间，本人未看答案花了大概
3个小时才写出类似的方案，求差距。 | A********Z
发帖数: 18 | 13 一开始把问题想简单了，中间又走了不少弯路，后来又想通过简单标准sql（即不用lag
/lead）来实现,浪费了不少时间，想想如果是面试，必挂:-( | l********m
发帖数: 284 | 14 幼稚。groupby item， price。sql 有什么难题 | T*******x
发帖数: 8565 | 15 我大概也花了3个小时的时间。我这个是工作中遇到的问题。开始我也是group by item
, price，看了结果发现不对，再改，也用了不少时间。如果是面试题肯定做不出来。
【在 A********Z 的大作中提到】
: 请问楼主花了多少时间做了出来，不计中间被工作打断的时间，本人未看答案花了大概
: 3个小时才写出类似的方案，求差距。
| T*******x
发帖数: 8565 | 16 标准sql相当于record list/set processing，但是它缺乏通用程序语言的map/reduce
所需要的function，也就是缺乏普通function和aggregate function。所以应该有很多
问题单纯用sql做不了。
【在 l********m 的大作中提到】
: 幼稚。groupby item， price。sql 有什么难题
| w*r
发帖数: 2421 | 17 这题我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 | T*******x
发帖数: 8565 | 18 这个问题不用SQL window funtion 做还真是不好做，好像只有didadida的recursive
cte的方法了。
: 这题我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
【在 w*r 的大作中提到】
: 这题我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********Z
发帖数: 18 | 19 神速，佩服。
rows
【在 w*r 的大作中提到】
: 这题我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
| l********m
发帖数: 284 | 20 我怎么没看懂这个 output 为什么不对呀？ 您能不能把正确的output贴出来。
这个难道不是你要的？
【在 T*******x 的大作中提到】
: 你这样得到的结果是：
: 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可以在不同的时间段重复出现。
| | | l********m
发帖数: 284 | 21 把effective date merge 起来是什么意思？sql merge function？ | l********m
发帖数: 284 | 22 把effective date merge 起来是什么意思？sql merge function？ | A********Z
发帖数: 18 | 23 意思是如果连续时间段price未变的话，则将此段时间的记录合并为一条。我也是花了
点时间才弄懂其需求。所以很佩服 wyr 兄弟 5分钟就能搞，我五分钟还没搞清楚需求
，还没建好case呢。
【在 l********m 的大作中提到】
: 把effective date merge 起来是什么意思？sql merge function？
| A********Z
发帖数: 18 | 24 楼主贴中有的。。。
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
【在 l********m 的大作中提到】
: 我怎么没看懂这个 output 为什么不对呀？ 您能不能把正确的output贴出来。
: 这个难道不是你要的？
| T*******x
发帖数: 8565 | 25 对是这么回事。
我觉得能做出来就挺好，慢点不要紧。
【在 A********Z 的大作中提到】
: 意思是如果连续时间段price未变的话，则将此段时间的记录合并为一条。我也是花了
: 点时间才弄懂其需求。所以很佩服 wyr 兄弟 5分钟就能搞，我五分钟还没搞清楚需求
: ，还没建好case呢。
| A********Z
发帖数: 18 | 26 这类贴近实践的问题 怎么也比哪些不切实际的面试题要好 :-)
item
【在 T*******x 的大作中提到】
: 我大概也花了3个小时的时间。我这个是工作中遇到的问题。开始我也是group by item
: , price，看了结果发现不对，再改，也用了不少时间。如果是面试题肯定做不出来。
| q*********e
发帖数: 12 | 27 我这次就不写CODE了，点破中心点即可： 为了简化问题，建立两个COMPOSITE KEY:
Key1=item+effective_date, Key2=item+ expiration_date.
问题一下子就简化成了 Key1 Join Key2 的recursive CTE (with clause）, 就和
employee to Manager 在一个表中的 从属关系分析一模一样了。 | l********m
发帖数: 284 | 28 多谢多谢。这就make sense了。
【在 A********Z 的大作中提到】
: 意思是如果连续时间段price未变的话，则将此段时间的记录合并为一条。我也是花了
: 点时间才弄懂其需求。所以很佩服 wyr 兄弟 5分钟就能搞，我五分钟还没搞清楚需求
: ，还没建好case呢。
| T*******x
发帖数: 8565 | 29 这两天又思考了一下SQL window funtction，因为平时我也不是很常用window
function，只是用一些基本用法，lead lag很少用。这次这个问题这几天我也思考能不
能不要window function做。我的结论基本上是，不要window function就只能用
recursive cte了，也就是didadida的方法。
另外我觉得window function是对group by的增强：所有用group by 能做的都能用
window function做，也就是partition by解决，而反之group by 不能解决window
function能解决的所有问题，最简单的比如row number function不用window function
就没法解决。
【在 T*******x 的大作中提到】
: 这个问题不用SQL window funtion 做还真是不好做，好像只有didadida的recursive
: cte的方法了。
:
:
: 这题我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_
| T*******x
发帖数: 8565 | 30 employee和manager是怎么个情况？
【在 q*********e 的大作中提到】
: 我这次就不写CODE了，点破中心点即可： 为了简化问题，建立两个COMPOSITE KEY:
: Key1=item+effective_date, Key2=item+ expiration_date.
: 问题一下子就简化成了 Key1 Join Key2 的recursive CTE (with clause）, 就和
: employee to Manager 在一个表中的 从属关系分析一模一样了。
| | | A********Z
发帖数: 18 | 31 不怀疑 递归CTE 可以解决这个问题，事实上，这也是我的一个思路，但是很快放弃，
原因有三
1. 性能堪忧
2. OPTION MAXMAXRECURSION 怎么看都别扭
3. 以前留下的阴影，可称之为个人偏见
windows function应该是主流选择吧。
就这个case而言，不用window function, 不用递归cte, 应该也是可以搞定的，以下是
我中间的一个思路，颜值低，谨供参考。
function
【在 T*******x 的大作中提到】
: 这两天又思考了一下SQL window funtction，因为平时我也不是很常用window
: function，只是用一些基本用法，lead lag很少用。这次这个问题这几天我也思考能不
: 能不要window function做。我的结论基本上是，不要window function就只能用
: recursive cte了，也就是didadida的方法。
: 另外我觉得window function是对group by的增强：所有用group by 能做的都能用
: window function做，也就是partition by解决，而反之group by 不能解决window
: function能解决的所有问题，最简单的比如row number function不用window function
: 就没法解决。
| T*******x
发帖数: 8565 | 32 这个好啊。
我不知道select里面还可以用另一个select。今天又学到了东西。
这样的话应该可以用普通的SQL实现一些window function的功能了。
比如row_number() over (partition by ... order by ...).
【在 A********Z 的大作中提到】
: 不怀疑 递归CTE 可以解决这个问题，事实上，这也是我的一个思路，但是很快放弃，
: 原因有三
: 1. 性能堪忧
: 2. OPTION MAXMAXRECURSION 怎么看都别扭
: 3. 以前留下的阴影，可称之为个人偏见
: windows function应该是主流选择吧。
: 就这个case而言，不用window function, 不用递归cte, 应该也是可以搞定的，以下是
: 我中间的一个思路，颜值低，谨供参考。
:
: function
| T*******x
发帖数: 8565 | 33 这个问题最开始是没有expiration_date的：table只有三个columns：item, effective
_date, price。expiration_date是我用lead function导出来的。一个item最后一个
record的expiration_date可以用NULL，也可以用9999-12-31:
item, effective_date, price
A, 2017-01-10, 12.50
A, 2017-01-20, 12.50
A, 2017-02-10, 13.25
A, 2017-03-01, 12.50
A, 2017-04-01, 12.50
B, 2017-01-10, 12.50
B, 2017-01-20, 12.50
B, 2017-02-10, 13.25
B, 2017-03-01, 12.50
B, 2017-04-01, 12.50
【在 A********Z 的大作中提到】
: 这类贴近实践的问题 怎么也比哪些不切实际的面试题要好 :-)
:
: item
| T*******x
发帖数: 8565 | 34 确实，有了这个“select statement in select list”结构，row_number, lead, lag
, sum等window function都可以用这个结构实现。查了一下，sql server, mysql,
oracle都可以这样写。以前还真没有这样写过。学了一招。
【在 T*******x 的大作中提到】
: 这个好啊。
: 我不知道select里面还可以用另一个select。今天又学到了东西。
: 这样的话应该可以用普通的SQL实现一些window function的功能了。
: 比如row_number() over (partition by ... order by ...).
| A********Z
发帖数: 18 | 35 可是可以，就是不太帅，也不像window function 那样性能可期。如果不是为了兼容性
，相信用这个的少 ...
lag
【在 T*******x 的大作中提到】
: 确实，有了这个“select statement in select list”结构，row_number, lead, lag
: , sum等window function都可以用这个结构实现。查了一下，sql server, mysql,
: oracle都可以这样写。以前还真没有这样写过。学了一招。
| T*******x
发帖数: 8565 | 36 刚才往前翻了一下旧贴，发现这个问题前面有人提过了，而且答案更好：
http://www.mitbbs.com/article/Database/31190747_0.html
对于这个问题就是这样：
with g as (
select *
, row_number() over (partition by item order by effective_date) -
row_number() over (partition by item, price order by effective_date) as
grouping
)
select item
, min(effective_date) effective_date
, max(expiration_date) expiration_date
, price
from g
group by item, price, grouping
order by item, effective_date;
古之人诚不我欺啊。
【在 A********Z 的大作中提到】
: 可是可以，就是不太帅，也不像window function 那样性能可期。如果不是为了兼容性
: ，相信用这个的少 ...
:
: lag
| T*******x
发帖数: 8565 | 37 row_number用select in select结构实现就是：假定item和effective_date是
composite key：select *, (select count(*) from Product b where a.item = b.
item and b.effective_date <= a.effective_date) rowNumber from Product a
lag
【在 T*******x 的大作中提到】
: 确实，有了这个“select statement in select list”结构，row_number, lead, lag
: , sum等window function都可以用这个结构实现。查了一下，sql server, mysql,
: oracle都可以这样写。以前还真没有这样写过。学了一招。
| T*******x
发帖数: 8565 | 38 row_number也可以用join来实现：还是以product table为例：select a.item, a.
effective_date, a.expiration_date, a.price, count(*) rowNumber from Product
a join Product b on a.item = b.item where a.effective_date >= b.effective_
date group by a.item, a.effective_date, a.expiration_date, a.price
join几乎可以做所有的事情，这个我不应该忘了，不过效率肯定没有window function
高，应该和select in select结构效率差不多，或者稍低。
【在 T*******x 的大作中提到】
: row_number用select in select结构实现就是：假定item和effective_date是
: composite key：select *, (select count(*) from Product b where a.item = b.
: item and b.effective_date <= a.effective_date) rowNumber from Product a
:
: lag
| T*******x
发帖数: 8565 | 39 row_number-row_number的思路很好，用select in select结构实现就是：select *, (
select count(*) from Product b where a.item = b.item and b.effective_date <=
a.effective_date and a.price <> b.price) grouping from Product a
所以原问题根据上面的实现就变成：select item
, min(effective_date) effective_date
, max(expiration_date) expiration_date
, price
from g group by item, price, grouping order by item, effective_date
【在 T*******x 的大作中提到】
: row_number也可以用join来实现：还是以product table为例：select a.item, a.
: effective_date, a.expiration_date, a.price, count(*) rowNumber from Product
: a join Product b on a.item = b.item where a.effective_date >= b.effective_
: date group by a.item, a.effective_date, a.expiration_date, a.price
: join几乎可以做所有的事情，这个我不应该忘了，不过效率肯定没有window function
: 高，应该和select in select结构效率差不多，或者稍低。
| T*******x
发帖数: 8565 | 40 用join实现row_number-row_number作为grouping的逻辑有两种方法，一种用left join
with inequality join condition，还有一种是inner join加where condition。
第一种 select a.item, a.effective_date, a.expiration_date, a.price, sum(case
when b.item is null then 0 else 1 end) grouping from Product a left join
Product b on a.item = b.item and a.effective_date >= b.effective_date and a.
price <> b.price group by a.item, a.effective_date, a.expiration_date, a.
price
第二种：select a.item, a.effective_date, a.expiration_date, a.price, sum(
case when a.price = b.price then 0 else 1 end) grouping from Product a join
Product b on a.item = b.item where a.effective_date >= b.effective_date
group by a.item, a.effective_date, a.expiration_date, a.price
在此基础上原问题的解法就变成： with g as (as in above) select item, min(
effective_date) effective_date, max(expiration_date) expiration_date, price
from g group by item, price, grouping order by item, effective_date
这是一个完全用普通SQL写的，没有recursive cte，没有window function，没有
select in select结构的SQL。
哈哈。
古之人诚不我欺，我亦不欺古人也！
: row_number-row_number的思路很好，用select in select结构实现就是：
select *, (
: select count(*) from Product b where a.item = b.item and b.effective_
date
: a.effective_date and a.price b.price) grouping from Product a
: 所以原问题根据上面的实现就变成：select item
: , min(effective_date) effective_date
: , max(expiration_date) expiration_date
: , price
: from g group by item, price, grouping order by item, effective_date
【在 T*******x 的大作中提到】
: row_number-row_number的思路很好，用select in select结构实现就是：select *, (
: select count(*) from Product b where a.item = b.item and b.effective_date <=
: a.effective_date and a.price <> b.price) grouping from Product a
: 所以原问题根据上面的实现就变成：select item
: , min(effective_date) effective_date
: , max(expiration_date) expiration_date
: , price
: from g group by item, price, grouping order by item, effective_date
| | | s********y
发帖数: 122 | 41 It took me five minutes. No fancy functions...just the most basic SQL. I
created two temp tables, 'cause I need to utilize identity column. Tested
on Sybase and works as expected.
Looks like some of the solutions posted before may have a problem. It won't
work if a item+price+time_range group has more than two records, for
example:
'A', '2017-01-10', '2017-01-15', 12.50
'A', '2017-01-15', '2017-01-20', 12.50
'A', '2017-01-20', '2017-02-10', 12.50
// find the starting record for each item+price+time_range group
SELECT a.*, groupid = IDENTITY(1)
INTO #p1
FROM Product a
INNER JOIN Product b ON a.item = b.item AND a.price = b.price
AND NOT EXISTS
(
SELECT 1 FROM Product c
WHERE a.item = c.item
AND a.price = c.price
AND a.effective_date = c.expiration_date
)
GROUP BY a.item, a.effective_date
ORDER BY a.item, a.effective_date
// find the ending record for each item+price+time_range group
SELECT a.*, groupid = IDENTITY(1)
INTO #p2
FROM Product a
INNER JOIN Product b ON a.item = b.item AND a.price = b.price
AND NOT EXISTS
(
SELECT 1 FROM Product c
WHERE a.item = c.item
AND a.price = c.price
AND a.expiration_date = c.effective_date
)
GROUP BY a.item, a.effective_date
ORDER BY a.item, a.effective_date
// since both temp tables are sorted the same way, the new identity column
groupid can be used to join
SELECT #p1.item, #p1.effective_date, #p2.expiration_date, #p2.price
FROM #p1, #p2
WHERE #p1.groupid = #p2.groupid | T*******x
发帖数: 8565 | 42 你这个是建立在一个item一个price的effective range的头序列和尾序列是一一顺序对
应的。这个在逻辑上是成立的。
有几个不足之处：
1. identity函数不是ansi SQL，每种数据库的用法都不同。
2. identity函数只能select into，不能写成一个SQL。
3. 你group by item，effective_date，但是select a.*，这不行吧？
4. 你join b没有必要啊，你也没有用到b。你可以写成：SELECT a.*, groupid =
IDENTITY(1) INTO #p1 FROM Product a where NOT EXISTS
(
SELECT 1 FROM Product c
WHERE a.item = c.item
AND a.price = c.price
AND a.effective_date = c.expiration_date
)
ORDER BY a.item, a.effective_date
't
【在 s********y 的大作中提到】
: It took me five minutes. No fancy functions...just the most basic SQL. I
: created two temp tables, 'cause I need to utilize identity column. Tested
: on Sybase and works as expected.
: Looks like some of the solutions posted before may have a problem. It won't
: work if a item+price+time_range group has more than two records, for
: example:
: 'A', '2017-01-10', '2017-01-15', 12.50
: 'A', '2017-01-15', '2017-01-20', 12.50
: 'A', '2017-01-20', '2017-02-10', 12.50
: // find the starting record for each item+price+time_range group
| T*******x
发帖数: 8565 | 43 你这个可以这样写：
with
head_seq as (
select a.*
, row_number() over (order by a.item, a.effective_date) rn
from Product a where not exists (
select * from Product c where a.item = c.item and a.price = c.price and a.
effective_date = c.expiration_date
)
),
tail_seq as (
select a.*
, row_number() over (order by a.item, a.effective_date) rn
from Product a where not exists (
select * from Product c where a.item = c.item and a.price = c.price and c.
effective_date = a.expiration_date
)
)
select h.item, h.effective_date, t.expiration_date, h.price
from head_seq h join tail_seq t on h.rn = t.rn
't
【在 s********y 的大作中提到】
: It took me five minutes. No fancy functions...just the most basic SQL. I
: created two temp tables, 'cause I need to utilize identity column. Tested
: on Sybase and works as expected.
: Looks like some of the solutions posted before may have a problem. It won't
: work if a item+price+time_range group has more than two records, for
: example:
: 'A', '2017-01-10', '2017-01-15', 12.50
: 'A', '2017-01-15', '2017-01-20', 12.50
: 'A', '2017-01-20', '2017-02-10', 12.50
: // find the starting record for each item+price+time_range group
| o*******8
发帖数: 249 | 44 哈哈，对的，这个题跟下面这道题是一样的。我已经成功地用在我的项目里了。
【在 T*******x 的大作中提到】
: 刚才往前翻了一下旧贴，发现这个问题前面有人提过了，而且答案更好：
: http://www.mitbbs.com/article/Database/31190747_0.html
: 对于这个问题就是这样：
: with g as (
: select *
: , row_number() over (partition by item order by effective_date) -
: row_number() over (partition by item, price order by effective_date) as
: grouping
: )
: select item
| T*******x
发帖数: 8565 | 45 哈哈。我也已经用在我的项目里了！
【在 o*******8 的大作中提到】
: 哈哈，对的，这个题跟下面这道题是一样的。我已经成功地用在我的项目里了。
| A********Z
发帖数: 18 | 46 这个的确帅。 要鸡蛋里挑骨头的话，就是 ROW_NUMBER-ROW_NUMBER 那块可读性差
点，新手可能不容易理解:-)
【在 T*******x 的大作中提到】
: 刚才往前翻了一下旧贴，发现这个问题前面有人提过了，而且答案更好：
: http://www.mitbbs.com/article/Database/31190747_0.html
: 对于这个问题就是这样：
: with g as (
: select *
: , row_number() over (partition by item order by effective_date) -
: row_number() over (partition by item, price order by effective_date) as
: grouping
: )
: select item
| m**b
发帖数: 4 | 47 大致可行。问题是如果同一产品同一价格但中间月份价格不一样就不对了， e.g,
A, d1, d2, 10.0
A, d2, d3, 10.0
A, d3, d4, 10.1
A, d4, d5, 10.0
结果会变成，
A, d1, d5, 10.0
A, d3, d4, 10.1
正确答案应该是，
A, d1, d3, 10.0
A, d3, d4, 10.1
A, d4, d5, 10.0
【在 T*******x 的大作中提到】
: 刚才往前翻了一下旧贴，发现这个问题前面有人提过了，而且答案更好：
: http://www.mitbbs.com/article/Database/31190747_0.html
: 对于这个问题就是这样：
: with g as (
: select *
: , row_number() over (partition by item order by effective_date) -
: row_number() over (partition by item, price order by effective_date) as
: grouping
: )
: select item
| T*******x
发帖数: 8565 | 48 不像你说的那样。row_number-row_number的方法正是可以避免你说的那种结果：因为
第一个row_number是partition by item order by effective-date，第二个row_
number是partition by item, price order by effective-date。相同item相同price
但是不连续的两个区段得到不同的row_number-row_number。
【在 m**b 的大作中提到】
: 大致可行。问题是如果同一产品同一价格但中间月份价格不一样就不对了， e.g,
: A, d1, d2, 10.0
: A, d2, d3, 10.0
: A, d3, d4, 10.1
: A, d4, d5, 10.0
: 结果会变成，
: A, d1, d5, 10.0
: A, d3, d4, 10.1
: 正确答案应该是，
: A, d1, d3, 10.0
| m**b
发帖数: 4 | 49 对头。没有看清group by里有grouping。高手！
price
【在 T*******x 的大作中提到】
: 不像你说的那样。row_number-row_number的方法正是可以避免你说的那种结果：因为
: 第一个row_number是partition by item order by effective-date，第二个row_
: number是partition by item, price order by effective-date。相同item相同price
: 但是不连续的两个区段得到不同的row_number-row_number。
| T*******x
发帖数: 8565 | 50 这个row_number-row_number的方法确实是神来之笔，有点奥数的意思。
【在 m**b 的大作中提到】
: 对头。没有看清group by里有grouping。高手！
:
: price
| | | s*******9
发帖数: 1 | 51 请问楼主花了多少时间做了出来，不计中间被工作打断的时间，本人未看答案花了大概
3个小时才写出类似的方案，求差距。 | T*******x
发帖数: 8565 | 52 前面有个人问过这个问题。是不就是你？我也用了大概三小时。没遇到过的问题想几个
小时正常。
【在 s*******9 的大作中提到】
: 请问楼主花了多少时间做了出来，不计中间被工作打断的时间，本人未看答案花了大概
: 3个小时才写出类似的方案，求差距。
|
|
