HLL in inner group by does not get finalized

[vogievetsky]

I try running this query to compute average daily active users on the wikipedia dataset:

SELECT AVG("t_unique") AS "AvgDAU"
FROM (SELECT
      TIME_FLOOR("__time", 'P1D'),
      APPROX_COUNT_DISTINCT("user_unique") AS "t_unique"  
    FROM "wikipedia"
    GROUP BY 1
)

I get the error: Unknown type[class io.druid.hll.HLLCV1] for field probably because not finalizer is added.

Same result if you substitute "user_unique" (an HLL column) with "user" (a string column)

This is the explain plan:

DruidOuterQueryRel(query=[{"queryType":"timeseries","dataSource":{"type":"table","name":"__subquery__"},"intervals":{"type":"intervals","intervals":["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},"descending":false,"virtualColumns":[],"filter":null,"granularity":{"type":"all"},"aggregations":[{"type":"longSum","name":"a0:sum","fieldName":"t_unique","expression":null},{"type":"count","name":"a0:count"}],"postAggregations":[{"type":"arithmetic","name":"a0","fn":"quotient","fields":[{"type":"fieldAccess","name":null,"fieldName":"a0:sum"},{"type":"fieldAccess","name":null,"fieldName":"a0:count"}],"ordering":null}],"context":{"skipEmptyBuckets":true,"timeout":300000}}], signature=[{a0:LONG}])
     DruidQueryRel(query=[{"queryType":"timeseries","dataSource":{"type":"table","name":"wikipedia"},"intervals":{"type":"intervals","intervals":["-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"]},"descending":false,"virtualColumns":[],"filter":null,"granularity":"DAY","aggregations":[{"type":"hyperUnique","name":"a0","fieldName":"user_unique","isInputHyperUnique":false,"round":true}],"postAggregations":[],"context":{"skipEmptyBuckets":true,"timeout":300000}}], signature=[{d0:LONG, a0:LONG}])

Please finalize

[forcepoint-miriam]

Did you try adding a HyperUnique Cardinality post-aggregator? http://druid.io/docs/latest/querying/post-aggregations.html

So adding something like

"postAggregations": [
    {
        "type":"hyperUniqueCardinality",
        "name": a0PostAggregated,
        "fieldName" :a0
    }
]

To the second query.

[vogievetsky]

Unfortunately that does not work in SQL, only in Druid native queries.