This message was deleted.

Hi @Tomer Ben David thank you for your question.

APPROX_COUNT_DISTINCT_DS_THETA(expr, [size])

or

DS_THETA(expr, [size])

https://druid.apache.org/docs/latest/querying/sql-aggregations.html#sketch-functions

Isn't APPROX_COUNT_DISTINCT_DS_THETA returning the “Counts distinct values of" – I was looking for a query to return the variance approximation based on sketches …

I don't see that under "All Druid SQL functions". (Checked all the DS_ functions.) Closest is a variance aggregation on values, but that isn't what you're looking for. Could be a good feature request jira.

Thank you, if there is any idea or tips on how to make this basic example (which i'm trying to come up work) would be great - https://apachedruidworkspace.slack.com/archives/C0309C9L90D/p1674058136023399

I think my_array_of_doubles_sketch should be a post-aggregation, since it's built on an aggregation? (But then I'm a little surprised that it works w/o my_variance_sketch in there.)

hmm good point as arrayOfDoublesSketch is from documentation an aggregation I added a fieldAccess post aggregation and referencing this but still having issue but I think this is a step forward:

{
  "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"
  ]
}

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

I think you don't need the fieldAccess, and the field should be the earlier aggregation, "my_array_of_double_sketch". Does this work: