Search code examples
distinctinfluxdbdistinct-values

How to use Distinct function in influxDB

I am using influx DB and issuing command,

SELECT * FROM interface

Below is the out put-

interface 
time                              element                path                                       value
2016-08-24T21:22:16.7080877Z    "link-layer-address0"   "key:/arp-information/link-layer-address0"  "3c:61:04:48:df:91"
2016-08-24T21:22:17.9090527Z    "link-layer-address0"   "key:/arp-information/link-layer-address0"  "3c:61:04:48:df:92"
2016-08-24T21:22:19.8584133Z    "link-layer-address1"   "key:/arp-information/link-layer-address1"  "3c:61:04:48:df:97"
2016-08-24T21:22:20.3377847Z    "link-layer-address2"   "key:/arp-information/link-layer-address2"  "3c:61:04:48:df:90"

When issue command it works fine. 

SELECT distinct(value) FROM interface

But When issue command for path column there is no out put. Wondering what i am missing?

SELECT distinct(path) FROM interface
Solution

  • Thanks for the extra info @Ammad.

    Short Answer

    Try GROUP BY with tags. DISTINCT() only works with fields.

    Long Answer

    distinct() works on fields, not tags. See here:

    https://docs.influxdata.com/influxdb/v1.0/query_language/functions/#distinct

    DISTINCT() returns the unique values of a single field.

    The field values are meant to be the actual data you're interested in. Tag values are metadata: data about the data. Most functions in database systems operate on the data or the metadata, but rarely on both.

    Here's a toy example on v0.13, showing that distinct() really doesn't work with tags:

    insert foo,tag1=asdf field1="some text"
insert foo,tag1=asdf field1="some text"
insert foo,tag1=asdfg field1="some text"
insert foo,tag1=asdfg field1="some text"
insert foo,tag1=asdfg field1="some more text"
insert foo,tag1=asdfg field1="some more text"

    Now some queries:

    select * from foo

name: foo
time                            field1          tag1
2016-09-12T05:19:53.563221799Z  some text       asdf
2016-09-12T05:20:03.027652248Z  some text       asdf
2016-09-12T05:20:10.04939971Z   some text       asdfg
2016-09-12T05:20:11.235525548Z  some text       asdfg
2016-09-12T05:20:17.418920163Z  some more text  asdfg
2016-09-12T05:20:19.354742922Z  some more text  asdfg

    Now let's try distinct()

    select distinct(tag1) from foo

    Results in no output at all. 

    select distinct(field1) from foo

name: foo
time                    distinct
1970-01-01T00:00:00Z    some text
1970-01-01T00:00:00Z    some more text

    You might be able to get what you want by using GROUP BY. Like this:

    select distinct(field1) from foo group by tag1

    Which gives:

    name: foo
tags: tag1=asdf
time                    distinct
1970-01-01T00:00:00Z    some text

name: foo
tags: tag1=asdfg
time                    distinct
1970-01-01T00:00:00Z    some text
1970-01-01T00:00:00Z    some more text

    This shows each value of tag1, and the values of field1 associated with that tag1 value.

    Hope that helps.