Zero filling in SQL

Hi,

Can someone tell me how to enable zero filling in sql queries? (I am currently on 0.16.0)

For example: I am running a simple query:

{

“query”:“SELECT __time, sum(metric) as metric\nFROM ffs_metrics WHERE __time BETWEEN TIMESTAMP ‘2018-01-01 00:00:00’ AND TIMESTAMP ‘2018-01-31 00:00:00’ AND (groupid = ‘149’) \nAND metricname = ‘BY_LINK_INTERNAL’\nGROUP BY __time\n\n”

}

``

The above sql gives me this result:

[

{

“__time”: “2018-01-04T00:00:00.000Z”,

“metric”: 0.0

},

{

“__time”: “2018-01-05T00:00:00.000Z”,

“metric”: 0.0

},

{

“__time”: “2018-01-08T00:00:00.000Z”,

“metric”: -139.34

},

{

“__time”: “2018-01-09T00:00:00.000Z”,

“metric”: 0.0

},

{

“__time”: “2018-01-10T00:00:00.000Z”,

“metric”: 0.0

}

]

``

The same query on time series JSON query gives me the missing dates filled with zeros. Here is the response from JSON just for completeness.

[

{

“timestamp”: “2018-01-04T00:00:00.000Z”,

“result”: {

“numerator”: 0.0

}

},

{

“timestamp”: “2018-01-05T00:00:00.000Z”,

“result”: {

“numerator”: 0.0

}

},

{

“timestamp”: “2018-01-06T00:00:00.000Z”,

“result”: {

“numerator”: 0.0

}

},

{

“timestamp”: “2018-01-07T00:00:00.000Z”,

“result”: {

“numerator”: 0.0

}

},

{

“timestamp”: “2018-01-08T00:00:00.000Z”,

“result”: {

“numerator”: -139.34

}

},

{

“timestamp”: “2018-01-09T00:00:00.000Z”,

“result”: {

“numerator”: 0.0

}

},

{

“timestamp”: “2018-01-10T00:00:00.000Z”,

“result”: {

“numerator”: 0.0

}

}

]

``

Any help?

Interesting question, unfortunately I also do not have the answer but would also like to know how to accomplish this.

In ‘normal’ SQL you would solve it by using a cross join I believe, but with Druid joins are not really supprted:
https://druid.apache.org/docs/latest/querying/joins.html

06 and 07 data is not getting passed to broker as data is getting filtered in historical.

I suggest you change from sum(metric) to

Sum( case when condition then metric else 0)

Move condition from where to select.

I don’t think it will have any performance impact.

Regards.

That did the trick. So I changed that and tried filter too, like this -

sum(metric) filter (where metricname = ‘BY_LINK_INTERNAL’)

``

Works in both cases. The query still registers as a TopN (from plan) but returns with zeros filled in. Much thanks.