This message was deleted.

Based on a hint here - https://apachedruidworkspace.slack.com/archives/C0303FDCZEZ/p1674083039747199?thread_ts=1674028769.660779&cid=C0303FDCZEZ I updated the example to be:

{
  "queryType": "groupBy",
  "dataSource": "wikipedia",
  "granularity": "all",
  "dimensions": [],
  "aggregations": [
    {
      "type": "count",
      "name": "my_count_sketch",
      "aggregator": {
        "type": "thetaSketch",
        "name": "mycount",
        "fieldName": "added"
      }
    },
    {
      "type": "arrayOfDoublesSketch",
      "name": "my_array_of_double_sketch",
      "fieldName": "added",
      "metricColumns": [
        "added"
      ]
    },
    {
      "type": "longSum",
      "name": "deleted_sum",
      "fieldName": "deleted"
    },
    {
      "type": "count",
      "name": "total_count"
    }
  ],
  "postAggregations": [
    {
      "type": "arithmetic",
      "name": "average_deleted",
      "fn": "/",
      "fields": [
        {
          "type": "fieldAccess",
          "fieldName": "deleted_sum"
        },
        {
          "type": "fieldAccess",
          "fieldName": "total_count"
        }
      ]
    },
    {
      "type": "fieldAccess",
      "name": "my_array_of_double_sketch_post_aggregator",
      "fieldName": "my_array_of_double_sketch"
    },
    {
      "type": "arrayOfDoublesSketchToVariances",
      "name": "my_variance_sketch",
      "field": "my_array_of_double_sketch_post_aggregator"
    }
  ],
  "intervals": [
    "2014-01-15T00:00:00.000/2023-08-15T02:00:00.000"
  ]
}

Getting now: Error: Unknown exception Missing type id when trying to resolve subtype of [simple type, class org.apache.druid.query.aggregation.PostAggregator]: missing type id property 'type' (for POJO property 'field') at [Source: (org.eclipse.jetty.server.HttpInputOverHTTP); line: 1, column: 807] (through reference chain: org.apache.druid.query.groupby.GroupByQuery["postAggregations"]->java.util.ArrayList[2]->org.apache.druid.query.aggregation.datasketches.tuple.ArrayOfDoublesSketchToVariancesPostAggregator["field"]) com.fasterxml.jackson.databind.exc.InvalidTypeIdException

In the original query, can you check if you are getting the desired result by replacing the

"field": "my_array_of_double_sketch_post_aggregator"

with

"field": {"type": "fieldAccess", "fieldName": "my_array_of_double_sketch_post_aggregator"}

this works for me on 25.0

{
    "dataSource": "wikipedia",
    "queryType": "groupBy",
    "intervals": [
        "2000-06-27T00:00:00.000Z/2040-06-27T23:00:00.000Z"
    ],
    "granularity": "all",
    "filter": {
        "type": "true"
    },
    "aggregations": [
        {
            "type": "arrayOfDoublesSketch",
            "name": "sketch",
            "fieldName": "user",
            "metricColumns" : ["added"]
            "nominalEntries": 16384
        }
    ]
        ,
        "postAggregations": [
        {
  "type"  : "arrayOfDoublesSketchToVariances",
  "name": "countsketch",
  "field"  : {"type": "fieldAccess",
                    "fieldName": "sketch"}
}],
    "dimensions": [
        {
            "type": "default",
            "dimension": "countryName",
            "outputName": "countryName"
        }
    ]
}

Thank you! This works indeed! So the main thing I missed was to use the field and then inside it use the fieldAccess to reference the sketch. Do you happen to know / confirm there is no such variance approximation in the sql dialect?

VARIANCE(expr) is available in sql along with VAR_POP and VAR_SAMP. Tuple sketch is specifically useful if you need to calculate a number of metrics…var,mean,count etc

In my case I thought I want the tuple sketch for variance because i'm planning on having the variance calculated "incrementally" - variance of day 1 (sketch), variance of day 2 (sketch), variance of day 3 (sketch) and then I want the variance of days 1 - 3. Otherrwise I didn't see another option for calculating variance of days 1 - 3 and still being incremental in calculations (calculating on daily data rather than data for days 1-3)

how about

select TIME_FLOOR(__time,'PT3H') 
,TIME_FLOOR(__time,'PT1H') hour1,variance(added) from "wikipedia" 
group by grouping sets ((TIME_FLOOR(__time,'PT3H'),TIME_FLOOR(__time,'PT1H')),TIME_FLOOR(__time,'PT3H'))