sql - How can one assign a rank that increases--rather than the same value as rank() and dense_rank() does--to later group of values previously encountered? - 


date        id          b   bc      x 2017-06-01  a35b3y26f   3   0.19    1 2017-06-02  a35b3y26f   3   0.19    1 2017-06-03  a35b3y26f   3   0.23    2 2017-06-04  a35b3y26f   3   0.12    3 2017-06-05  a35b3y26f   3   0.21    4 2017-06-06  a35b3y26f   3   0.19    5 2017-06-07  a35b3y26f   3   0.28    6 2017-06-08  a35b3y26f   3   0       7 2017-06-09  a35b3y26f   3   0       7 2017-06-10  a35b3y26f   3   0.15    8 2017-06-11  a35b3y26f   3   0.3     9 2017-06-12  a35b3y26f   3   0.17    10 2017-06-13  a35b3y26f   3   0.27    11 2017-06-14  a35b3y26f   3   0.28    12 2017-06-15  a35b3y26f   3   0.18    13 2017-06-16  a35b3y26f   3   0       14 2017-06-17  a35b3y26f   3   0.2     15 2017-06-18  a35b3y26f   3   0       16 2017-06-19  a35b3y26f   3   0.28    17 2017-06-20  a35b3y26f   3   0.25    18 2017-06-21  a35b3y26f   3   0.19    19 2017-06-22  a35b3y26f   3   0.23    20 2017-06-23  a35b3y26f   3   0       21 2017-06-24  a35b3y26f   3   0       21 2017-06-25  a35b3y26f   3   0.13    22

above, column x represents values wish have outputted in result-set.

is there way using existing windowing functions provided postgresql can obtain outcome?

one way use sum , lag functions:

select "date", "id", "b", "bc", "x",         sum( xxxxx ) on (order "date") x (    select *,           case "bc"            when lag( "bc" ) on (order "date")           0 else 1            end xxxxx    table1 ) x

demo: http://sqlfiddle.com/#!17/8dab6/4

|                 date |        id | b |   bc |  x |  x | |----------------------|-----------|---|------|----|----| | 2017-06-01t00:00:00z | a35b3y26f | 3 | 0.19 |  1 |  1 | | 2017-06-02t00:00:00z | a35b3y26f | 3 | 0.19 |  1 |  1 | | 2017-06-03t00:00:00z | a35b3y26f | 3 | 0.23 |  2 |  2 | | 2017-06-04t00:00:00z | a35b3y26f | 3 | 0.12 |  3 |  3 | | 2017-06-05t00:00:00z | a35b3y26f | 3 | 0.21 |  4 |  4 | | 2017-06-06t00:00:00z | a35b3y26f | 3 | 0.19 |  5 |  5 | | 2017-06-07t00:00:00z | a35b3y26f | 3 | 0.28 |  6 |  6 | | 2017-06-08t00:00:00z | a35b3y26f | 3 |    0 |  7 |  7 | | 2017-06-09t00:00:00z | a35b3y26f | 3 |    0 |  7 |  7 | | 2017-06-10t00:00:00z | a35b3y26f | 3 | 0.15 |  8 |  8 | | 2017-06-11t00:00:00z | a35b3y26f | 3 |  0.3 |  9 |  9 | | 2017-06-12t00:00:00z | a35b3y26f | 3 | 0.17 | 10 | 10 | | 2017-06-13t00:00:00z | a35b3y26f | 3 | 0.27 | 11 | 11 | | 2017-06-14t00:00:00z | a35b3y26f | 3 | 0.28 | 12 | 12 | | 2017-06-15t00:00:00z | a35b3y26f | 3 | 0.18 | 13 | 13 | | 2017-06-16t00:00:00z | a35b3y26f | 3 |    0 | 14 | 14 | | 2017-06-17t00:00:00z | a35b3y26f | 3 |  0.2 | 15 | 15 | | 2017-06-18t00:00:00z | a35b3y26f | 3 |    0 | 16 | 16 | | 2017-06-19t00:00:00z | a35b3y26f | 3 | 0.28 | 17 | 17 | | 2017-06-20t00:00:00z | a35b3y26f | 3 | 0.25 | 18 | 18 | | 2017-06-21t00:00:00z | a35b3y26f | 3 | 0.19 | 19 | 19 | | 2017-06-22t00:00:00z | a35b3y26f | 3 | 0.23 | 20 | 20 | | 2017-06-23t00:00:00z | a35b3y26f | 3 |    0 | 21 | 21 | | 2017-06-24t00:00:00z | a35b3y26f | 3 |    0 | 21 | 21 | | 2017-06-25t00:00:00z | a35b3y26f | 3 | 0.13 | 22 | 22 |

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