SQLite Forum

Unique every N seconds. How?
> I believe that would only slice things into predetermined 10 second windows

I have to believe the OP meant this 10-second windowing precisely, well-aware of the fact that two consecutive writes might be milliseconds apart while falling in different 10-second-window-slices.

Boundary-less groupings is not just hard to do in SQLite - It's physically  impossible to do in this Universe within our current understanding of physics.


It is weird to be sure - It's such a normal human thing to imagine simply making little groups of everything that's really close together, be it in terms of colour, time, length, (any metric really). It's almost hard to intuitively know that this is impossible, and almost everyone who tried it in the past, we got burned. (Just take Biological species classification as an example, it's a lot more Rainbow and a lot less Lego than first imagined).

Worse still, it is possible to define boundaries such that the groupings explicitly represents the "worst possible togetherness". Imagine for a moment a consistent timed event that fire exactly at 0.005 milliseconds of every second, and again at 0.995 of every second. If you group by "second" in which it occurs, you will have groupings where every event group has two entries that are 0.99 seconds apart, while in fact if you group every last-event in a second with the first-event in the next second, you will have much better groupings only 0.01 seconds apart. If your events/incidence were consistent in this way, you can preemptively design a "good grouping boundary", but most actual metrics will have a more random dispersion and any assumed group-boundary will probably be worse than good.

There's some fun math to figure out best clumping boundaries on a set, but it's almost always irregular, and always dependent on the specific set and won't be useful in determining a standard for x unknown future sets.