sql - Update a column sequentially based on condition -


i'm on oracle 11g , stuck on problem.

my table structure below

  ╔═══════╦══════╦════════╗ ║ tm_id ║ flag ║ countr ║ ╠═══════╬══════╬════════╣ ║ 1     ║ 0    ║ null   ║ ║ 2     ║ 0    ║ null   ║ ║ 3     ║ 1    ║ null   ║ ║ 4     ║ 0    ║ null   ║ ╚═══════╩══════╩════════╝

i want update values of column countr sequential value below

  ╔═══════╦══════╦════════╗ ║ tm_id ║ flag ║ countr ║ ╠═══════╬══════╬════════╣ ║ 1     ║ 0    ║ 1      ║ ║ 2     ║ 0    ║ 2      ║ ║ 3     ║ 1    ║ 2      ║ ║ 4     ║ 0    ║ 3      ║ ╚═══════╩══════╩════════╝

so value countr should increase if flag 0. if 1 shouldn't increase (or should have previous value)

i tried following update statement 

update calendar  set countr = case when flag = 0  tm_id  else  countr-1 end

sql fiddle

oracle 11g r2 schema setup:

create table test ( tm_id, flag, countr )           select 1,0, cast( null number ) dual union select 2,0, cast( null number ) dual union select 3,1, cast( null number ) dual union select 4,0, cast( null number ) dual /  update test t set countr = ( select total                  (                         select tm_id,                                sum( 1 - flag ) on ( order tm_id rows between unbounded preceding , current row ) total                           test                       ) x                 t.tm_id = x.tm_id              ) /

query 1:

select * test

results:

| tm_id | flag | countr | |-------|------|--------| |     1 |    0 |      1 | |     2 |    0 |      2 | |     3 |    1 |      2 | |     4 |    0 |      3 |

edit - explanation

the use of sum here analytic function rather than, typically used, aggregate function.

sum( 1 - flag ) on ( order tm_id rows between unbounded preceding , current row )

from (almost) right left:

  • order tm_id- order rows ascending tm_id
  • then each row:
    • consider rows between unbounded preceding , current row (i.e. rows starting earliest tm_id current row)
    • for rows find sum( 1 - flag ) (i.e. increment counter when flag 0 , not when one).

Comments

Post a Comment

Popular posts from this blog

javascript - Google App Script ContentService downloadAsFile not working -

i have web app developed using google app script htmlservice , html form, populating excel sheet in google drive using spreadsheetapp . , 1 section calling contentservice download data excel file. function doget(e) { // read excel sheet //getappfile(); // render application html template return htmlservice.createtemplatefromfile('index').evaluate() .settitle('go smart') .setsandboxmode(htmlservice.sandboxmode.iframe); } function downloaddoublequatecsvfile() { var sheetid = propertiesservice.getscriptproperties().getproperty('sheetid'); var ss = spreadsheetapp.openbyid(sheetid).getactivesheet(); //var ss = spreadsheetapp.getactivespreadsheet().getactivesheet(); var maxcolumn = ss.getlastcolumn(); var maxrow = ss.getlastrow(); var data = ss.getrange(1, 1, maxrow, maxcolumn).getvalues(); if (data.length > 1) { var csv = ""; (var row = 0; row < data.length; row++) { (var c...
Read more

javascript - Function overwritting -

is there way overwrite function or event handling in function? using addeventlistener, can have many event handlers on element want var test=document.getelementbyid("div"); test.addeventlistener("click",function(){alert("im first");},false); test.addeventlistener("click",function(){alert("im second");},false); // alerts "im first" , after "im second" but if want overwrite function/event example based on client width example this function one() { alert("first"); } function two() { alert("second"); } window.onresize = function() { var test = document.getelementbyid("div"); if (window.innerwidth > 500) { test.onclick = 1 } else { test.onclick = two; } } is possible in javascript? in case use effective little known approach. addeventlistener can accept object property handleevent event handler. in case it's easy overwr...
Read more

php - Find a regex to take part of Email -

i have following emails need extract part from: bestellnummer xxx: 1 von xxx, 1er pack ------------- anfang der nachricht ------------- foo bar baz foo bar baz // <<<<< need text here ------------- ende der nachricht ------------- ------------- anfang der nachricht ------------- foo bar baz foo bar baz ------------- ende der nachricht ------------- there 0 unlimited occurences of ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- and i'm able extract first part regex: $re = "/------------- .*? -------------.?(.*?).?------------- .*? -------------/s"; but, i'm quite new on learning regex, i'm pretty sure there must better regex extract part (foo bar baz foo bar baz) of text between ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- as can in different languages, i'm using .? to match between hyphens. i...
Read more