Sql: Grouping To Have Exact Rows

Let's say there is a schema: |date|value| DBMS is SQLite. I want to get N groups and calculate AVG(value) for each of them. Sample: 2020-01-01 10:00|2.0 2020-01-01 11:00|2.0 2020-

Solution 1:

You can use NTILE() window function to create the groups and aggregate:

SELECT 
  DATETIME(MIN(DATE), ((STRFTIME('%s', MAX(DATE)) - STRFTIME('%s', MIN(DATE))) /2) ||' second') date, 
  ROUND(AVG(value), 2) avg_value
FROM (
  SELECT*, NTILE(3) OVER (ORDERBYdate) grp
  FROM test
) 
GROUPBY grp;

To change the number of rows in each bucket, you must change the number 3 inside the parentheses of NTILE().

See the demo. Results:

|date|avg_value||-------------------|---------||2020-01-01 11:00:00|2.33||2020-01-01 14:00:00|5||2020-01-01 17:00:00|5.33|

Solution 2:

I need to use a windowing function, like NTILE, but it seems NTILE is not usable after GROUP BY. It can create buckets, but then how can I use these buckets for aggregation?

You first use NTILE to assign bucket numbers in a subquery, then group by it in an outer query.

Using sub-query

SELECT bucket
     , AVG(value) AS avg_value
  FROM ( SELECTvalue
              , NTILE(3) OVER ( ORDERBYdate ) AS bucket
           FROM test
       ) x
 GROUPBY bucket
 ORDERBY bucket

Using WITH clause

WITH x AS (
   SELECTdate
        , value
        , NTILE(3) OVER ( ORDERBYdate ) AS bucket
     FROM test
)
SELECT bucket
     , COUNT(*) AS bucket_size
     , MIN(date) AS from_date
     , MAX(date) AS to_date
     , MIN(value) AS min_value
     , AVG(value) AS avg_value
     , MAX(value) AS max_value
     , SUM(value) AS sum_value
  FROM x
 GROUPBY bucket
 ORDERBY bucket