Hi Team, I want to aggregate the count of datapoints on daily basis in druid... But the issue which I am facing is that if there is no data on that day the value should come 0 for that day but instead response doesn't give that day in that case. Can somebody help me out. I am attaching my query and the respective response below

{

"queryType": "groupBy",

"dataSource": "EnergyMeterST501",

"intervals": [

"2023-12-23T00:00+03:00/2024-03-23T23:59:59+03:00"

],

"granularity": {

"type": "period",

"period": "P1D",

"timeZone": "Asia/Riyadh"

},

"dimensions": [

"deviceID"

],

"metric": "count",

"aggregations": [

{

"type": "count",

"name": "count",

"fieldName": "deviceID"

}

],

"having": {

"type": "filter",

"filter": {

"type": "in",

"dimension": "deviceID",

"values": [

"2020070100"

]

}

}

}

Slack Conversation

Hi Uday, In this case you may need to create an "anchor list" of consecutive dates, to which you outer join the data. There are a couple of ways to do this: • time series functions which will fill in missing data points ... this may be only in the Imply version of Druid, but you could check around to see if there is an extension for OS Druid • Manufacturing a sequence and assigning a list of dates to it ... I am not well versed in the Druid native query language, but here's how I generated it in Druid SQL:

replace into test_mo overwrite all
WITH seq10 AS (
  SELECT * FROM TABLE(EXTERN(
    '{"type":"inline","data":"0\n1\n2\n3\n4\n5\n6\n7\n8\n9\n"}',
    '{"type":"csv","findColumnsFromHeader":false,"columns":["val"]}',
    '[{"name":"val","type":"long"}]'
  ))
)
SELECT TIMESTAMPADD(DAY, b.val*10 + c.val, CURRENT_TIMESTAMP) __time, '-' flag from seq10 b CROSS JOIN seq10 c
PARTITIONED BY MONTH