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

.htaccess - ERR_TOO_MANY_REDIRECTS htaccess -

could can explain me .htaccess code doing? rewriterule ([0-9]+)/([^t]{1}[a-za-z0-9_-]{0,})\.([^s]{1}[a-za-z0-9]+)$ ../items/go.php?u=$1/$2.$3 i have folder : mywebsite.com/download/folder1/ .htaccess file in uplevel mywebsite.com/download/ folder contains code above. when access file directly path mywebsite.com/download/folder1/file.mp4 returns error : mywebsite.com redirected many times. try clearing cookies. err_too_many_redirects i tried clear browser cookies issue persist. issue solved when commented out htaccess line above. this rule appears looking specific pattern consisting of: <numbers>/<alphanumeric string not starting t , other conditions applied>.<alphanumeric string not starting s> then redirecting user's browser go.php parsed filename passed file arguments. err_too_many_redirects due infinite redirect loop. without knowing other rewriterule statements present difficult narrow down exact cause. can narrow down
Read more