skydb/sky — pull request #122 by benbjohnson (2013-08-25 18:54:54+00:00)
Title: Add temporal loops.

## Temporal Loops

### Overview

This pull request adds support for looping over discrete units of time. It's useful for creating a "day zero" based on an event timestamp. It's also useful for iterating over periods of time where events don't exist but you still want to perform an action.

Temporal loops maintain an iterator variable to keep track of the number of time the loop has been performed. Also, temporal loops don't move the cursor forward so that needs to be done manually using an event loop.

There are basically two flavors of temporal loops: Bounded duration and unbounded duration. Bounded duration loops would be phrased as "for each day over the next 30 days". Unbounded duration loops would be phrased as "for each day until the end of the timeline". Bounded duration loops will continue iterating over step periods until the upper bound is reached -- even after there are no more events.

### Syntax

The syntax of a bounded loop is like this:

```
WHEN action == "signup" THEN
FOR i EVERY 1 DAY WITHIN 30 DAYS
FOR EACH EVENT
WHEN action == "Send Friend Request" THEN
SELECT count() AS friend_request_count GROUP BY i
END
END
END
END
```

That will show how many friend requests users send by day after they've signed up. That's a totally contrived example but you might also want to sum purchase amounts by month after initial purchase or you can combine this with the FSM from the last pull request and calculate movement of states by day after initial visit.

Unbounded loops are the same syntax except you just take out the `WITHIN` clause:

```
FOR i EVERY 1 DAY
...statements...
END
```

### Other Notes

There are a couple things that aren't implemented with this first iteration of temporal loops. You can't currently do temporal loops inside other temporal loops. I haven't tried to get sessionizing working within temporal loops. Also, you can't key off a specific day for the start of the temporal loop (e.g. loop over Aug 1 - Aug 30).

As far as time periods go, you can use `SECONDS`, `MINUTES`, `HOURS`, `DAYS`, `WEEKS` and `YEARS`.

This is meant to be a first cut at it. Let me know your thoughts on it and other use cases you can think of.

/cc: @boourns @orenmazor @snormore @emiddleton