sql - In Oracle how do you perform a calculation depending on the previous calculated result -


i perform simple calculation follows

dataset

sr      value_one      result  1         null          0.99  2          1            0.99*1 = 0.99  3         0.75          0.99*0.75 = 0.7425  4         0.75          0.7425*0.75 = 0.556875  5          1            0.556875*1 = 0.556875  6          1            0.556875*1 = 0.556875  7          1            0.556875*1 = 0.556875  8          1            0.556875*1 = 0.556875  9          1            0.556875*1 = 0.556875  10         1            0.556875*1 = 0.556875

the results dependent on previous 1 sr >= 2 while starts 0.99.

looping while performing calculation. database oracle.

if wanted calculate running sum, easy using analytic functions:

with d (   select 1 sr, cast(null number) value_one dual union    select 2 sr, 1 value_one dual union    select 3 sr, 0.75 value_one dual union    select 4 sr, 0.75 value_one dual union    select 5 sr, 1 value_one dual union    select 6 sr, 1 value_one dual union    select 7 sr, 1 value_one dual union    select 8 sr, 1 value_one dual union    select 9 sr, 1 value_one dual union   select 10 sr, 1 value_one dual  ) select d.*, sum(nvl(value_one, 0.99)) on (order sr) d;

unfortunately, there no "product" aggregate function use here, have make detour using exp , ln (see https://stackoverflow.com/a/3912248/1230592):

with d (   select 1 sr, cast(null number) value_one dual union    select 2 sr, 1 value_one dual union    .. ) select d.*, nvl(exp (sum (ln (value_one)) on (order sr)), 1) * 0.99 d;

this should give desired results


Comments

Post a Comment

Popular posts from this blog

r - Trouble relying on third party package imports in my package -

my package uses ggplot2 extensively make custom plots. import ggplot2 in namespace file. can build, install, , load package r without errors. when call function package, complains can't find function in imported package. function called had absolute nothing plotting , didn't make use of ggplot2 . how can make function calls package work without these types of errors? error in theme_beata() (from plot.r#14) : not find function "theme_classic" the first function erroring is: #' custom ggplot2 theme beata #' @import ggplot2 #' @family themes #' inheritparams ggplot2::theme_classic #' @export theme_beata <- function(base_size = 14, base_family="serif", ticks = true) { ret <- theme_classic() + theme(axis.line = element_line(size=0.6), axis.text = element_text(size = base_size), axis.title = element_text(size= base_size + 2), title = element_text(size=base_size + 4)) if (...
Read more

Payment information shows nothing in one page checkout page magento -

i using magento ver. 1.9.1.0 want remove shipping information , shipping method step 1 page checkout page. changed code in core file like in app\code\core\mage\checkout\block\onepage.php public function getsteps() { $steps = array(); $stepcodes = $this->_getstepcodes(); /* remove shipping address , shipping method */ $excludestep=array_diff($stepcodes,array('shipping','shipping_method')); //$excludestep=$stepcodes; if ($this->iscustomerloggedin()) { $stepcodes = array_diff($excludestep, array('login')); } foreach ($excludestep $step) { $steps[$step] = $this->getcheckout()->getstepdata($step); } return $steps; } and in app\code\core\mage\checkout\controllers\onepagecontroller.php public function savebillingaction() { if ($this->_expireajax()) { return; } if ($this->getrequest()-...
Read more