SQL - 计数有序列表中的更改数 [英] SQL - Count number of changes in an ordered list
本文介绍了SQL - 计数有序列表中的更改数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
说我有一个有两列(日期和价格)的表格。如果我选择了一个日期范围,那么有没有办法计算一段时间内价格变化的数量?
例如:
Date |价格
22-Oct-11 | 3.20
23-Oct-11 | 3.40
24-Oct-11 | 3.40
25-Oct-11 | 3.50
26-Oct-11 | 3.40
27-Oct-11 | 3.20
28-Oct-11 | 3.20
在这种情况下,我希望它返回4个价格变化的计数。 >
提前感谢。
解决方案
您可以使用分析函数 LEAD
和 LAG
来访问结果集的前一行和下一行,然后使用它来查看是否有更改。 p>
SQL> ed
写入文件afiedt.buf
1 t为(
2选择日期'2011-10-22'dt,3.2从双联合起的价格全部
3选择日期'2011-10-23',3.4从双联全部
4选择日期'2011-10-24',3.4从双联全部
5选择日期'2011-10-25' 3.5 from dual union all
6选择日期'2011-10-26',3.4从双联全部
7选择日期'2011-10-27',3.2从双联全部
8选择日期'2011-10-28',3.2从双
9)
10 select sum(is_change)
11从(
12 select dt,
13 price ,
14(由dt)先行价格,
15(当价格超过(order by dt)!)=价格
16然后1
17 else 0
18 end)is_change
19 * from t)
SQL> /
SUM(IS_CHANGE)
--------------
4
Say I've got a table with two columns (date and price). If I select over a range of dates, then is there a way to count the number of price changes over time?
For instance:
Date | Price
22-Oct-11 | 3.20
23-Oct-11 | 3.40
24-Oct-11 | 3.40
25-Oct-11 | 3.50
26-Oct-11 | 3.40
27-Oct-11 | 3.20
28-Oct-11 | 3.20
In this case, I would like it to return a count of 4 price changes.
Thanks in advance.
解决方案
You can use the analytic functions LEAD
and LAG
to access to prior and next row of a result set and then use that to see if there are changes.
SQL> ed
Wrote file afiedt.buf
1 with t as (
2 select date '2011-10-22' dt, 3.2 price from dual union all
3 select date '2011-10-23', 3.4 from dual union all
4 select date '2011-10-24', 3.4 from dual union all
5 select date '2011-10-25', 3.5 from dual union all
6 select date '2011-10-26', 3.4 from dual union all
7 select date '2011-10-27', 3.2 from dual union all
8 select date '2011-10-28', 3.2 from dual
9 )
10 select sum(is_change)
11 from (
12 select dt,
13 price,
14 lag(price) over (order by dt) prior_price,
15 (case when lag(price) over (order by dt) != price
16 then 1
17 else 0
18 end) is_change
19* from t)
SQL> /
SUM(IS_CHANGE)
--------------
4
这篇关于SQL - 计数有序列表中的更改数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文