3 ms·
You can see the same pattern of two-step aggregation with the HLL_COUNT family of functions in BigQuery: https://cloud.google.com/bigquery/docs/reference/standa
by nlehuen 5y ago
You can see the same pattern of two-step aggregation with the HLL_COUNT family of functions in BigQuery: https://cloud.google.com/bigquery/docs/reference/standard-sql/hll_functions https://cloud.google.com/bigquery/docs/reference/standard-sq...
This is really useful for this kind of metric (distinct count) that can't be trivially aggregated. It allows generating pre-aggregated tables containing the intermediate aggregation state. Now you can compute your DAUs, WAUs and MAUs from the same daily pre-aggregates.
I wish this design was generalized in SQL. For instance, getting the same family of function for variance computation would be super useful for efficient & correct computation of confidence intervals at different levels of aggregation.
- djk447 5y agoAbsolutely! We're actually developing a lot of that: https://github.com/timescale/timescaledb-toolkit/tree/main/docs https://github.com/timescale/timescaledb-toolkit/tree/main/d... A number of the things you're looking for we've done experimentally and we'll be stabilizing over the next few releases. So we'd love some feedback while we're still able to futz with the API without making breaking changes. But the two you're asking about are, I think, going to be covered by hyperloglog (we just reimplemented the internals with HLL++) and stats_agg family of functions, which have both 1D (which will give you avg, stddev, variance, etc) and 2D (co-variance, slope, intercept, x-intercept etc as well as all the 1D functions). Would also love issues if you think we're missing other stuff, going to be generalizing this and want to make it useful for folks. (NB: Post author here.)
- nlehuen 5y agoThe stats_agg + extractor function pattern is exactly what I was thinking about in my last paragraph :-). Very cool!