amazon redshift - postgresql: merge rows keeping some information, without loops -

-

i have list of calls per every user separated minutes. users can buy in these calls or not. when user makes call within 45 minutes after last call, need consider same call first one.

i need final number of calls ( aggregating calls separated less 45 minutes) , number of calls in bought something, per user.

so example, have list this:

buyer       timestamp              bougth_flag         tom         20150201 9:15                 1 anna        20150201 9:25                 0 tom         20150201 10:15                0 tom         20150201 10:45                1 tom         20150201 10:48                1 anna        20150201 11:50                0 tom         20150201 11:52                0 anna        20150201 11:54                0

the final table be:

buyer       time_started        calls              articles_bought         tom         20150201 9:15          1                        1 anna        20150201 9:25          1                        0 tom         20150201 10:15         3                        2 anna        20150201 10:50         2                        0 tom         20150201 11:52         1                        0

so, need merge rows separated less 45 minutes, , separate still per user. easy loop don't have loops or functions/procedures in postgresql using. ideas how it?

thank you

since not know beforehand how long "call" going (you have call buyer every 30 minutes full day - see comment question), can solve recursive cte. (note changed column 'timestamp' 'ts'. never use keyword table or column name.)

with conversations (   recursive calls (     select buyer, ts, bought_flag, row_number() on (order ts) conversation, 1::int calls     (       select buyer, ts, lag(ts) on (partition buyer order ts) lag, bought_flag       list) sub     lag null or ts - lag > interval '45 minutes'     union     select l.buyer, l.ts, l.bought_flag, c.conversation, c.calls + 1     list l     join calls c on c.buyer = l.buyer , l.ts > c.ts     l.ts - c.ts < interval '45 minutes'   )   select buyer, ts, bought_flag, conversation, max(calls) calls   calls   group buyer, ts, bought_flag, conversation   order conversation, ts ) select buyer, min(ts) time_started, max(calls) calls, sum(bought_flag) articles_bought conversations group buyer, conversation order time_started

a few words of explanation:

  • the starting term of inner recursive cte has sub-query gets basic data table every call, time of previous call. main query in starting term of inner cte keeps rows there no previous call (lag null) or previous call more 45 minutes away. these therefore initial calls in term here "conversation". conversation gets column , id row number query, , column track number of calls in conversation "calls".
  • in recursive term successive calls in same conversation added, "calls" counter incremented.
  • when calls close (such 10:45 , 10:48 after 10:15) later calls may included multiple times, duplicates (10:48) dropped in outer cte selecting earliest call in sequence each conversation.
  • in main query, finally, 'bought_flag' column summed every conversation of every buyer.

Comments

Post a Comment

Popular posts from this blog

python - pip install -U PySide error -

-
does know how avoid following error when running pip install -u pyside per official website instructions here: https://pypi.python.org/pypi/pyside/#installing-pyside-on-a-mac-os-x-system note have done brew install qt successfully. you using pip version 7.0.3, version 7.1.2 available. should consider upgrading via 'pip install --upgrade pip' command. collecting pyside using cached pyside-1.2.4.tar.gz installing collected packages: pyside running setup.py install pyside complete output command /applications/anaconda/bin/python -c "import setuptools, tokenize;__file__='/private/var/folders/dy/ttgqhqqx3g9bsbsnqnxsk2z80000gq/t/pip-build-cpxemt/pyside/setup.py';exec(compile(getattr(tokenize, 'open', open)(__file__).read().replace('\r\n', '\n'), __file__, 'exec'))" install --record /var/folders/dy/ttgqhqqx3g9bsbsnqnxsk2z80000gq/t/pip-0r7hri-record/install-record.txt --single-version-externally-managed --compile: ...
Read more

arrays - C++ error: a brace-enclosed initializer is not allowed here before ‘{’ token -

-
simple question: i'm trying initialize array within c++ class declaration: using namespace std; #include <string> class myclass{ public: string myarray[] = {"a","b","c"}; }; and i'm getting error: error: brace-enclosed initializer not allowed here before ‘{’ token no, without complied c++11 compiler, cannot initialize member array in declaration. have initialize array member in constructor. , don't use open array if know number of elements initialize array.
Read more

cytoscape.js - How to add nodes to Dagre layout with Cytoscape -

-
i trying add nodes dagre graph through cityscape.js. problem nodes not painted. code have: cy.add({ group: "nodes", data: { id:"n0",name:"n0",weight: 75 }, }); $.ajax(url).done(function(jsontree){ var newnodes=[]; for(var i=1;i<6;i++){ //var child = jsontree.result[i]; newnodes.push( { group: "nodes", data: { id: "n"+i, name:"n"+i}}, { group: "edges", data: { id: "e"+i, source: "n0", target: "n"+i } } ); } cy.add(newnodes); any ideas? have used loaded function reload graph not work. not know if have use specific function reload graph. thank in advance. you need add positions new nodes. cytoscape.js docs : it important note positions of newly added nodes must defined when calling cy.add(). nodes can not placed in graph without valid position — otherwise not displayed. yo...
Read more