Find last occurring value within record in PostgreSQL -

-

i'm not new sql, new postgresql , struggling adapt current knowledge in different environment.

i trying create variable captures whether or not stays active, skips, or churns within 0/1 time series variable. example, in data below, dataset include variables id,time, , voted, , create variable "skipped":

id     time     voted     skipped 1      1        1         active 1      2        0         skipped 1      3        1         active 2      1        1         active 2      2        0         churned 2      3        0         churned 3      1        1         active 3      2        1         active 3      3        0         churned

the rule coding "skipped" pretty simple: if 1 last record, person "active" , zeroes count "skipped", if 0 last record, person "churned".

the record id = 1 skip because id non-zero @ time 3 after being 0 @ time 2. other 2 cases, 0 final value "churned". can help? i've been noodling on day, , hitting wall.

this isn't particularly elegant, should meet needs:

with votes (   select     id, time, voted,     max(time) on (partition id) max_time   voter_data ) select   v1.id, v1.time, v1.voted,   case     when v1.voted = 1 'active'     when v2.voted = 1 'skipped'     else 'churned'   end skipped   votes v1   join votes v2 on     v1.id = v2.id ,     v1.max_time = v2.time

in nutshell, first figure out last record each voter id, , self-join on resulting table isolate last id.

there chance produce multiple results -- if it's possible have same id vote twice @ same time. if that's case, want row_number() instead of max().

results on data:

1   1   1   'active' 1   2   0   'skipped' 1   3   1   'active' 2   1   1   'active' 2   2   0   'churned' 2   3   0   'churned' 3   1   1   'active' 3   2   1   'active' 3   3   0   'churned'

Comments

Post a Comment

Popular posts from this blog

SVG stroke-linecap doesn't work for circles in Firefox? -

-
i've got problem svg stroke-linecap attribute. i've got circular progress bar in angularjs , set outer circle (blue one) have rounded "ends". @ fiddle . <svg ... height="130" width="130"> <!-- ngif: background --> <circle ... ng-if="background" fill="#fff" class="ng-scope" stroke-width="13" stroke="#cc3399" r="57.5" cy="65" cx="65" stroke-linecap="round" /> <!-- end ngif: background --> <circle ... fill="none" stroke-dashoffset="36.12831551628261" stroke-dasharray="361.28315516282623" stroke-width="13" stroke="#432db3" stroke-linecap="round" r="57.5" cy="65" cx="65" transform="rotate(-89.9, 65, 65)" /> </svg> how can that? ...
Read more

routes - Laravel 4 Wildcard Routing to Different Controllers -

-
realizing i'm going take beating this, have several questions "best practices" when constructing laravel route. do laravel routes run "first come first serve" top bottom? if route found, pass control controller (or perform action) , terminate? i have part of application acts url shortner. so, route looks this: route::get('/{id}', array('as' => 'home.text', 'uses' => 'homecontroller@text'), function($id){ //make sure id exists before pushing controller. if (storage::where('shortcode','=', $id)->count() > 0) { // route found } }); how can improved? one, i'm not sure how pass processing on controller conditionally.. sort of controller::make() ..? 3) there better way in laravel check if record exists versus doing mode::where('column','=','value')->count() 4) have legacy urls http://url.com/?...
Read more

cross browser - XSLT namespace-alias Not Working in Firefox or Chrome -

-
i transforming xhtml xhtml, need xslt stylesheet part of resulting document (the stylesheet contained in <script type"text/template"> element. using the xsl:namespace-alias instruction, works fine in ie, fails in both chrome , firefox. here relevant code: <xsl:output doctype-system="about:legacy-compat" omit-xml-declaration="yes" indent="yes" method="html" media-type="application/xhml" encoding="utf-8"/> <xsl:namespace-alias stylesheet-prefix="wxsl" result-prefix="xsl" /> <xsl:template match="head"> <!-- code omitted clarity --> <script type="text/template"> <wxsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/xsl/transform" xmlns="http://www.w3.org/1999/xhtml" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:eli="local"> ...
Read more