New pivots.aggregates.aggregate option for nested aggregates

[ldhasson]

When doing pivots, it's possible to want two nested levels of aggregates. For example, consider daily weather data with multiple readings per day per zip codes. You want a pivoted view that collects daily averages per zip-code over the past 30 days. The first level would define daily averages, while the final level would create an array of the average values for the last 30 days for each measure, i.e., temperature, aqi, rainfall...

Currently, this is not possible as the aggregate in the inner level is replicated for the outer level. It was meant to calculate simple values typical in most pivoting logic.

 { "name":"Weather_PivotView"
  ,"description":"An aggregate of weather daya for the past 30 days",
   "columns":[
      { "sameas":"weather.zipCode" }
     ,{ "sameas":"weather.taken"  , "expression":"?::DATE", "type":"DATE" }
     ,{ "sameas":"weather.value"  , "aggregate":"AVG"  }
    ]
  ,"subWhere":"weather.taken >= now() - interval '30 days'"
  ,"pivots":[
      { "on": "weather.name"
       ,"aggregates":[
           { "name":"value", "suffix":"_values" }
         ]
       ,"interleave":true
       ,"globals":false
       ,"values":[ { "value":"TEMP"    , "description":"Temperature"        }
                  ,{ "value":"AQI"     , "description":"Air Quality Index." }
                  ,{ "value":"RAINFALL", "description":"Rain Fall"          }
                 ]
      }
    ]
 }

The aggregate definition is supplied only once for the source columns, and the aggregate is replicated in the final pivot logic

with T as (
-- 'An aggregate of weather daya for the past 30 days'
select CODESMASTER.Weather."zipCode" as "zipCode" -- FK to zip code
     , CODESMASTER.Weather."taken"::DATE as "taken" -- Start date for the value
     , CODESMASTER.Weather."name" as "name" -- Name
     , avg(CODESMASTER.Weather."value") as "value" -- value.
  from CODESMASTER.Weather
 where (CODESMASTER.Weather."taken" >= now() - interval '30 days')
   and ( CODESMASTER.Weather."name" in ('TEMP', 'AQI', 'RAINFALL')
       )
 group by 1, 2, 3
) select "zipCode" 
       , "taken" 
     , AVG("value") filter (where "name"= 'TEMP')  as "TEMP_values"
     , AVG("value") filter (where "name"= 'AQI')  as "AQI_values"
     , AVG("value") filter (where "name"= 'RAINFALL')  as "RAINFALL_values"
from T
     group by 1, 2

Not only is this more verbose than needed, it's also somewhat counter intuitive to have AVG twice when the second level AVG in this particular case is effectively aggregating only a single value.

We propose to augment the pivots.aggregates elements with the ability to define its own "aggregate" and "orderBy" and allow pivoted column sources to not be aggregates themselves. This provides for a more natural expression of the aggregation logic needed for pivoting as well as the ability to mix different types of aggregation at each level so as to achieve maybe a better representation of all the data points for the past 30 days in an array.

This will allow something like this:

 { "name":"Weather_PivotView"
  ,"description":"An aggregate of weather daya for the past 30 days",
   "columns":[
      { "sameas":"weather.zipCode" }
     ,{ "sameas":"weather.taken"  , "expression":"?::DATE", "type":"DATE" }
     ,{ "sameas":"weather.value"  , "aggregate":"AVG"  }
    ]
  ,"subWhere":"weather.taken >= now() - interval '30 days'"
  ,"pivots":[
      { "on": "weather.name"
       ,"aggregates":[
            { "name":"taken", "suffix":"_takens", "aggregate":"ARRAY", "orderBy":["taken"] }
           ,{ "name":"value", "suffix":"_values", "aggregate":"ARRAY", "orderBy":["taken"] }
         ]
       ,"interleave":true
       ,"globals":false
       ,"values":[ { "value":"TEMP"    , "description":"Temperature"        }
                  ,{ "value":"AQI"     , "description":"Air Quality Index." }
                  ,{ "value":"RAINFALL", "description":"Rain Fall"          }
                 ]
      }
    ]
 }

The following is then generated with the aggregates at both levels being different.

with T as (
-- 'An aggregate of weather daya for the past 30 days'
select CODESMASTER.Weather."zipCode" as "zipCode" -- FK to zip code
     , CODESMASTER.Weather."taken"::DATE as "taken" -- Start date for the value
     , CODESMASTER.Weather."name" as "name" -- Name
     , avg(CODESMASTER.Weather."value") as "value" -- value.
  from CODESMASTER.Weather
 where (CODESMASTER.Weather."taken" >= now() - interval '30 days')
   and ( CODESMASTER.Weather."name" in ('TEMP', 'AQI', 'RAINFALL')
       )
 group by 1, 2, 3
) select "zipCode" 
     , array_agg("taken" order by T."taken" ASC) filter (where "name"= 'TEMP')  as "TEMP_takens"
     , array_agg("value" order by T."taken" ASC) filter (where "name"= 'TEMP')  as "TEMP_values"
     , array_agg("taken" order by T."taken" ASC) filter (where "name"= 'AQI')  as "AQI_takens"
     , array_agg("value" order by T."taken" ASC) filter (where "name"= 'AQI')  as "AQI_values"
     , array_agg("taken" order by T."taken" ASC) filter (where "name"= 'RAINFALL')  as "RAINFALL_takens"
     , array_agg("value" order by T."taken" ASC) filter (where "name"= 'RAINFALL')  as "RAINFALL_values"
from T
     group by 1