I thinking about this variants:
- I need to replace this part with something like variables.
In MySQL it can be something like this: (in reality can't because of the problem)
SELECT @day := day as day, wau.visitors
(SELECT visitors FROM ...impossible wau query... WHERE date=@day) as wau FROM (SELECT day FROM ...crazy query for calendar range... )
In ElasticSearch maybe it is possible with scripting? But how?
... WAU aggregation inside scripted loop through a calendar executed for each day:
"lte": "{{ CALENDAR_DAY_VARIABLE }}",
"gte": "{{ CALENDAR_DAY_VARIABLE }}-6d/d"
- I need to use pipeline aggregations. It is should be something like a "LEFT JOIN".
- On the left side – a calendar – date histogram, but without using any index data.
- On the right side – a WAU aggregation that should be called for each day. Some variable is needed again here.
- I need to use pipeline aggregations again. In the nested aggregation level I need to make a calendar and get any dates from them. In the top aggregations level I need to get WAU aggregation for any date from nested level, using nested.day instead of "now" in "lte", "gte" and/or "interval" params.
Any ideas about this? Maybe someone already makes this charts with ES?
P.S.: I thinking about cardinally different variant then I can call the simple DAU / WAU / ... queries for every day on application level and cache the result inside a different ES index.
It should be much more faster of course and then I'll can use this index inside exists Kibana visualisations (especially inside Timelion).