sql - PostgreSQL - rank over rows listed in blocks of 0 and 1 -


i have table looks like:

id     code      date1         date2         block -------------------------------------------------- 20     1234      2017-07-01    2017-07-31    1 15     1234      2017-06-01    2017-06-30    1 13     1234      2017-05-01    2017-05-31    0 11     1234      2017-03-01    2017-03-31    0 9      1234      2017-02-01    2017-02-28    1 8      1234      2017-01-01    2017-01-31    0 7      1234      2016-11-01    2016-11-31    0 6      1234      2016-10-01    2016-10-31    1 2      1234      2016-09-01    2016-09-31    1

i need rank rows according blocks of 0's , 1's, like:

id     code      date1         date2         block     desired_rank ------------------------------------------------------------------- 20     1234      2017-07-01    2017-07-31    1         1 15     1234      2017-06-01    2017-06-30    1         1 13     1234      2017-05-01    2017-05-31    0         2 11     1234      2017-03-01    2017-03-31    0         2 9      1234      2017-02-01    2017-02-28    1         3 8      1234      2017-01-01    2017-01-31    0         4 7      1234      2016-11-01    2016-11-31    0         4 6      1234      2016-10-01    2016-10-31    1         5 2      1234      2016-09-01    2016-09-31    1         5

i've tried use rank() , dense_rank(), result end is:

id     code      date1         date2         block     dense_rank() ------------------------------------------------------------------- 20     1234      2017-07-01    2017-07-31    1         1 15     1234      2017-06-01    2017-06-30    1         2 13     1234      2017-05-01    2017-05-31    0         1 11     1234      2017-03-01    2017-03-31    0         2 9      1234      2017-02-01    2017-02-28    1         3 8      1234      2017-01-01    2017-01-31    0         3 7      1234      2016-11-01    2016-11-31    0         4 6      1234      2016-10-01    2016-10-31    1         4 2      1234      2016-09-01    2016-09-31    1         5

in last table, rank doesn't care rows, takes 1's , 0's unit , sets ascending count starting @ first 1 , 0. query goes this:

create temp table data (id integer,code text, date1 date, date2 date, block integer);  insert data values (20,'1234', '2017-07-01','2017-07-31',1), (15,'1234', '2017-06-01','2017-06-30',1), (13,'1234', '2017-05-01','2017-05-31',0), (11,'1234', '2017-03-01','2017-03-31',0), (9, '1234', '2017-02-01','2017-02-28',1), (8, '1234', '2017-01-01','2017-01-31',0), (7, '1234', '2016-11-01','2016-11-30',0), (6, '1234', '2016-10-01','2016-10-31',1), (2, '1234', '2016-09-01','2016-09-30',1);  select *,dense_rank() on (partition code,block order date2 desc) data  order date2 desc;

by way, database in postgresql.

i hope there's workaround... :)

edit: note blocks of 0's , 1's aren't equal.

there's no way result using single window function:

select *,    sum(flag) -- sum 0/1 create "rank"    on (partition code          order date2 desc)  (    select *,       case          when lag(block) -- check if 1st row of new block               on (partition code                     order date2 desc) = block           0           else 1        end flag    data  ) dt

Comments

Post a Comment

Popular posts from this blog

php - Vagrant up error - Uncaught Reflection Exception: Class DOMDocument does not exist -

i've set bento/ubuntu-16.04 vagrant box using php 5.6, , getting following error quite few times when try , run shell script: "uncaught reflection exception: class domdocument not exist". i've tried adding following top of script (not together), each no success: "sudo apt-get install php5.6-xml", "sudo apt-get install php5-xml", "sudo apt-get install php-dom" i've tried resetting server "sudo service apache2 restart". does have suggestions?
Read more

vue.js - Create hooks for automated testing -

Image
qa guys complained can't automate frontend testing. because our html looks same outside. lazy hack did this <template> <div :role="$options.name"> ... </div> </template> <script> export default { name: 'vmcomponentname' } </script> which takes name script , applies html. in browser get: <div role="vmcomponentname"> ... </div> line :role="$options.name" goes in every component. is there dry er solutions? please share. additional details i'll explain in details looking for. imagine have vmusercreate form creates user. test case can create user . to automatically test without role, have use following selector: .wrapper > .wrapper > .wrapper > .submit-button the test extremely brittle (will break lot). if use roles can use [role="vmusercreateform"] .submit-button selector. many folds less brittle. so basically, i'm ...
Read more

Add new key value to json node in java -

i have json file : { "id":1, "name":"abc", "addressdetails": { "city":"newyork" } } i wanted add 1 more key-value ("pincode" : "414141") node 'addressdetails'. i tried using : objectmapper mapper = new objectmapper(); jsonnode root = mapper.readtree(new file("d://test.json")); objectnode node = mapper.createobjectnode(); node.with("addressdetails").put("pincode", "414141"); but it's not getting added, there way can ? you can try you should modify file path objectmapper mapper = new objectmapper(); objectnode nodes = mapper.readvalue(new file("d:\\test.txt"), objectnode.class); nodes.with("addressdetails").put("pincode", "414141"); mapper.writer().writevalue(new file("d:\\test.txt"), nodes);
Read more