sql - How to finish this LAG calculation in Oracle -


i have month , value columns in table,like

month              value        market 2010/01            100            1   2010/02            200            1 2010/03            300            1 2010/04            400            1 2010/05            500            1 2010/01            100            2   2010/02            200            2 2010/03            300            2 2010/04            400            2 2010/05            500            2

what want new month , value combinations using (value in month(n-1)+value in month(n))/2=value in month n, calculation based on market column, group market number. so, above example, new month , value combination should 

month                value         market 2010/01            null               1 2010/02            (100+200)/2        1 2010/03            (200+300)/2        1 2010/04            (300+400)/2        1 2010/05            (400+500)/2        1 2010/01            null               2 2010/02            (100+200)/2        2 2010/03            (200+300)/2        2 2010/04            (300+400)/2        2 2010/05            (400+500)/2        2

do know how achieve in oracle? thank you!

if there no gap in data, can use lag:

sql> data (   2     select date '2010-01-01' mon, 100 val dual union   3     select date '2010-02-01' mon, 200 val dual union   4     select date '2010-03-01' mon, 300 val dual union   5     select date '2010-04-01' mon, 400 val dual union   6     select date '2010-05-01' mon, 500 val dual   7  )   8  select mon, (lag(val) on (order mon) + val) / 2 avg_val data;  mon            avg_val ----------- ---------- 01/01/2010   01/02/2010         150 01/03/2010         250 01/04/2010         350 01/05/2010         450

however, if there gap result might not expect. in case, can either use self-join or narrow windowing clause:

sql> data (   2     select date '2010-01-01' mon, 100 val dual union   3     select date '2010-02-01' mon, 200 val dual union   4     select date '2010-03-01' mon, 300 val dual union   5     /* gap ! */   6     select date '2010-05-01' mon, 400 val dual union   7     select date '2010-06-01' mon, 500 val dual   8  )   9  select mon, (first_value(val)  10                 on (order mon  11                       range between interval '1' month preceding  12                                 , interval '1' month preceding)  13               + val) / 2 avg_val  14    data;  mon            avg_val ----------- ---------- 01/01/2010   01/02/2010         150 01/03/2010         250 01/05/2010   01/06/2010         450

Comments

Post a Comment

Popular posts from this blog

android - Get AccessToken using signpost OAuth without opening a browser (Two legged Oauth) -

i using signpost oauth access data magento server. have read various tutorials on same , reach point open browser user can enter credentials. however, per requirement have automate part. hence, user should not browser page. have set-up done on server side ( magento ), hit url , returned call page. same through program in android. i have tried below, consumer = new commonshttpoauthconsumer(key, secret); provider = new commonshttpoauthprovider(oauth_init_url,access_token_url, authorize_url); try { provider.retrieverequesttoken(consumer, oauth.out_of_band); log.d("tokens" , consumer.gettoken() + " -- " + consumer.gettokensecret()); } and request tokens. dont know how bypass next step. tried directly accessing accesstoken (stupid of me) provider.retrieveaccesstoken(consumer, "mycallback://callback"); no luck, ends in oauth.signpost.exception.oauthnotauthorizedexception: authorization failed (server replied 401). ...
Read more

org.mockito.exceptions.misusing.InvalidUseOfMatchersException: mockito -

getting error org.mockito.exceptions.misusing.invaliduseofmatchersexception: invalid use of argument matchers! 1 matchers expected, 2 recorded. exception may occur if matchers combined raw values: //incorrect: somemethod(anyobject(), "raw string"); when using matchers, arguments have provided matchers. example: //correct: somemethod(anyobject(), eq("string matcher")); at when( getprogramservice .callservice(any(getprogramtype.class))) .thenreturn(jaxbresponse); please explain error , possible resolution the code posted looks fine; don't see problem it. code before or above causing exception. key here: invalid use of argument matchers! 1 matchers expected, 2 recorded. any , used above, doesn't return "special kind of null" or "special kind of getprogramtype"; don't exist. instead, returns null , side effect puts matcher on stack. whe...
Read more

google shop client API returns 400 bad request error while adding an item -

i'm working on solution add items in google shopping market java application. following tutorial real mess because of missing classes of laste release, after setting 1.5-beta maven managed make base example typecheck correctly classes. still can make simple task of adding 1 item. instead of posting whole code i'm posting part creates product feed. private product createproduct(string id) { product product = new product(); product.title = "red wool sweater"; product.content = new content( "text", "comfortable , soft, sweater keep warm on " + "cold winter nights. red , blue stripes."); product.appcontrol = new appcontrol(); product.appcontrol.addrequireddestination("productads"); product.externalid = id; product.lang = "it"; product.country = "it"; product.condition = "nuovo"; product.price = new price("eu...
Read more