Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I wrote up a <a href="http://www.kamsky.org/1/post/2013/10/aggregating-over-time.html" rel="nofollow">complete answer on my MongoDB blog</a> but as a summary, what you have to do is project your actions based on which ones you care about to map values of action field into appropriate key names, group by person aggregating for the three actions when they did them (and optionally how many times) and then project new fields which check if action2 was done after action1, and action3 was done after action2... Last phase just sums up the number of people who did just 1, or 1 and then 2, or 1 and then 2 and then 3. </p> <p>Using a function to generate the aggregation pipeline, it's possible to generate results based on array of actions passed in. </p> <p>In my test case, the entire pipeline ran in under 200ms for a collection of 40,000 documents (this was on my small laptop).</p> <p>As it was correctly pointed out, the general solution I describe assumes that while an actor can take any action multiple times that they can only advance from action1 to action2 but that they cannot skip directly from action1 to action3 (interpreting action order as describing prerequisites where you cannot do action3 until you've done action2).</p> <p>As it turns out, aggregation framework can be used even for sequences of events where the order is completely arbitrary but you still want to know how many people at some point did the sequence action1, action2, action3.</p> <p>The main adjustment to make on the original answer is to add an extra two-stage step in the middle. This step unwinds the collected by person document to re-group it finding the first occurrence of the second action that comes <em>after</em> the first occurrence of the first action.</p> <p>Once we have that the final comparison becomes for action1, followed by earliest occurrence of action2 and compare that to the latest occurrence of action3.</p> <p>It can probably be generalized to handle arbitrary number of events but every additional event past two would add two more stages to the aggregation.</p> <p>Here is <a href="http://www.kamsky.org/1/post/2013/10/more-on-aggregations-over-time.html" rel="nofollow">my write-up of the modification of the pipeline</a> to achieve the answer you are looking for.</p>
    singulars
    1. This table or related slice is empty.
    1. POLinear funnel from a collection of events with MongoDB aggregation, is it possible?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAsya Kamsky
    1. USAsya Kamsky
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COCorrect my if I'm wrong but this solution will only work if the first instances of each event are in the order you want? EG if you were looking for the sequence e1, e2, e3 and had the someone had triggered the following sequence: e1, e3, e2, e3 it would only register as them having got to . If you can make the assumption the e3 cannot be fired until e2 has been this is fine, unfortunately we cannot make that assumption.
      singulars
      1. PO
      1. USmsaspence
    2. COit wasn't clear to me whether each action can be done multiple times and whether you cared about each action being done in strict order - originally I had both first and last times for each action to use in a more complex comparison.
      singulars
      1. PO
      1. USAsya Kamsky
    3. COif you clarify your exact requirements I can explain how you can adjust the algorithm. If you keep first and last times you can compare first time of action to last time of next action - do you consider that something like e2, e1, e3, e2, e1, e2 does *not* satisfy your requirement? and e1, e3, e2, e3, e1, e3, e2, e1, e3 does? The pipeline would be slightly more complicated, but it's still doable (though it would be less generalizable to arbitrary sequences of actions passed in - current variant works for any number of actions greater than or equal to one.
      singulars
      1. PO
      1. USAsya Kamsky
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload