pid date quantity
s01 200401 100个
s01 200402 80个现在需要求: s01这个产品在200401所消耗的量 是否 比200402消耗的量大10%
如果是则将两条记录都显示
s01 200401 100个
s01 200402 80个现在需要求: s01这个产品在200401所消耗的量 是否 比200402消耗的量大10%
如果是则将两条记录都显示
调试欢乐多
FROM
(SELECT pid,
DATE,
quantity,
LAG(quantity, 1, 0) OVER (PARTITION BY pid ORDER BY DATE) as prev_quantity,
LEAD(quantity, 1, 0) OVER (PARTITION BY pid ORDER BY DATE) as next_quantity,
FROM your_table)
WHERE (prev_quantity != 0
AND prev_quantity / quantity NOT BETWEEN 0.9 AND 1.1 )
OR
(next_quantity != 0
AND next_quantity / quantity NOT BETWEEN 0.9 AND 1.1 )
LAG(quantity, 1, 0) OVER (PARTITION BY pid ORDER BY DATE)
LAG(quantity, 1, 0)就是求当前行的前一行的quantity,没有前一行时返回0。
PARTITION BY pid是以pid分区,类似于group by。
ORDER BY DATE不用解释了吧。
LEAD(quantity, 1, 0) OVER (PARTITION BY pid ORDER BY DATE)
LEAD除了是取当前行的下一行外,和lag一样。where条件基本上是按照你要求的,如果有什么不一致,自己改一下吧。
decode(sign(a.quantity-b.quantity),-1,a.quantity,b.quantity) q //选一个较小值
from tablename a,tablename b where a.pid=b.pid and abs(a.quantity-b.quantity)=q*0.1;
类似分析函数那里可以找?我看的一些oracle书上都没这类东西,能否提供电子书?或者数名?
先送上分,谢谢各位。