Support group by scenarios for the auto interval

[ptomasroos]

Feature Request

Is your feature request related to a problem? Please describe.
The new introduced auto interval works great for plain queries, but in the case of a group by where you return multiple metrics it causes problems since the LIMIT clause won't be per group, rather for the complete set, which is undesired. #220 #225

Describe the solution you'd like

A query google give's me a set of example on how to set limit per group by in BQ. (From stackoverflow)

select t.*
from (select t.*,
             row_number() over (partition by group order by age desc) as seqnum
      from t
     ) t
where seqnum <= 2;

or

#standardSQL
SELECT
  `Group`,
  ARRAY_AGG(Age
            ORDER BY Age DESC LIMIT 2) AS oldest_people
FROM People
GROUP BY `Group`;

At this moment, when using $__timeGroup(ts, auto), the LIMIT would still be appended to the end
LIMIT 1742 for example.

bigquery-grafana/src/datasource.ts

Line 531 in b8a2a84

q += " LIMIT " + options.maxDataPoints;

So we either need to find a way to expose $__maxDataPoints to the manual SQL builder?

select t.*
from (select t.*,
             row_number() over (partition by group order by age desc) as seqnum
      from t
     ) t
where seqnum <= $__maxDataPoints;

or we could use a $__limitMarker to insert where the LIMIT should be placed in the above query, aka make sure we can place the appropriate calculated limit into a sub query and not just at the end.

#standardSQL
SELECT
  `Group`,
  ARRAY_AGG(Age
            ORDER BY Age DESC $__limitMarker) AS oldest_people
FROM People
GROUP BY `Group`;

I prefer to use this later method. Using a $__limitMarker, would be very flexible IMHO and would work with arbitrary subqueries

[ghost]

Essentially, it would be nice if the query parser, could break out each sub query into a "query" and add the limit that way.

--- Query 2
select t.*
from (
     -- Query 1
      select t.*, row_number() over (partition by group order by age desc) as seqnum
      from t
      Limit 2
     ) t 
Limit 1

Is this what you are after?