Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Assuming you know the exact sequence <strong>at the time of writing the query</strong> (either when you're coding it or when you caller code generates it dynamically), you can do this, as long as the sequence is not too long:</p> <pre><code>select * from eventTable1 T1, eventTable1 T2, eventTable1 T3, where t1.theTime between '01/01/2000' and '01/01/2001' and t2.theTime between '01/01/2000' and '01/01/2001' and t3.theTime between '01/01/2000' and '01/01/2001' and t1.theTime &lt;= t2.theTime and t2.theTime &lt;= t3.theTime and t1.eventId = 1 and t2.eventId = 2 and t3.eventId = 3 and t1.userId = t2.userId and t1.userId = t3.userId and t2.userId = t3.userId -- Needed for performance reasons </code></pre> <p>This will work fairly well if you have an index on <code>userId, theTime</code> and the amount of rows is manageable for given time period (e.g. you don't get the full billion rows across a set of users)</p> <p>Please note that the above can (and probably SHOULD) be further optimized depending on your data set and timeframe span, by first selecting ALL records for a given timespan into a temp table and then doing the above join on the temp table. This optimization works best if the amount of rows in a given timespan is manageable (e.g. &lt;100k?) and there is an index on <code>theTime</code></p> <hr> <p>Another approach may be to avoid the <code>JOIN</code> and simply retrieve ALL the sequences combined, per user; and then do the "is this correct sequence" in the caller code:</p> <pre><code>SELECT * FROM eventTable ORDER BY userId, theTime -- works MUCH better if this is an covering index </code></pre> <p>And then in the caller code, you basically do a subset matching on per-user sequences (seems trivial to me but feel free to ask as a separate quetsion on SO if you aren't sure how)</p> <p>Since this is pretty much per-user processing, you can avoid blowing out the memory by selecting chunks of users (approximate # of events per user, then grab as many users as would be safe for your memory - to have that work fast your SQL must support "TOP" or "LIMIT" syntax AND you should have pre-built list of all users in a temp table.</p>
    singulars
    1. This table or related slice is empty.
    1. POSQL - finding Sequence / Path
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USDVK
    1. USDVK
    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. COThe data volume is very big... data transfer over the network would run in gigs.. To avoid this I am currently using CLR stored proc but it is slow as the logic is sequential.. I dont think I can do multithreading in CLR stored proc.. running another server to do multithreading on the same box is not an option..
      singulars
      1. PO
      1. USStackUnderflow
    2. CO@StackUnderflow - that is why I recomend having an indexed list of users (and an index on the user+time on main table), and then chunking by user. This way you can parallelize the app servers doing the data crunching, and have them request the data for the next batch of users when done.
      singulars
      1. PO
      1. USDVK
 

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