Downsampling and Retention: With Tags and Fields

1

I am working with Downsampling and Retention for InfluxDB and using the example shown on web-site. I modified the existing measurement to include a single tag (my dataset has multiple tags with few having very high cardinality)

I am trying to define a CQ but influx does not support mixing of tags and fields. Below are first 10 records.

select * from orders limit 10
name: orders
time guid phone website

2017-10-24T18:10:26Z g1 11 31
2017-10-24T18:10:27Z g0 12 32
2017-10-24T18:10:28Z g1 13 33
2017-10-24T18:10:29Z g0 14 34
2017-10-24T18:10:30Z g1 15 35
2017-10-24T18:10:31Z g0 16 36
2017-10-24T18:10:32Z g1 17 37
2017-10-24T18:10:33Z g0 18 38
2017-10-24T18:10:34Z g1 19 39
2017-10-24T18:10:35Z g0 20 40

The idea is to downsample 10-second interval data to 1-minute aggregates retaining tag values. Before I create CQ am trying to get it working and below is my query and is not working for obvious reasons

SELECT guid,mean(website) AS mean_website, mean(phone) AS mean_phone FROM food_data.one_hour.orders where time >= ‘2017-10-24T18:10:26Z’ AND time < ‘2017-10-24T18:11:26Z’ GROUP BY time(1m)
ERR: error parsing query: mixing aggregate and non-aggregate queries is not supported
SELECT guid, mean(website) AS mean_website, mean(phone) AS mean_phone INTO food_data.two_hours.downsampled_orders FROM food_data.one_hour.orders WHERE time >= ‘2017-10-24T16:51:00Z’ AND time < ‘2017-10-24T16:52:00Z’ GROUP BY time(1m), guid
ERR: error parsing query: mixing aggregate and non-aggregate queries is not supported

show series
key

downsampled_orders
orders,guid=g0
orders,guid=g1

My dataset contains multiple series and I want to down-sample dataset from 10 seconds to 1 minute along with the tags.
Is it possible to do it ?

2

Solution

drop database food_data
create database food_data
use food_data
show RETENTION policies
CREATE RETENTION POLICY “one_hour” ON “food_data” DURATION 1h REPLICATION 1 DEFAULT
CREATE RETENTION POLICY “two_hours” ON “food_data” DURATION 2h REPLICATION 1
show RETENTION policies
show CONTINUOUS QUERIES
CREATE CONTINUOUS QUERY “cq_1m” ON “food_data” BEGIN SELECT mean(website) AS mean_website, mean(phone) AS mean_phone INTO food_data.two_hours.downsampled_orders FROM food_data.one_hour.orders GROUP BY time(1m), guid, host END
show CONTINUOUS QUERIES
show measurements
show series

select * from two_hours.downsampled_orders limit 10
select * from one_hour.orders limit 10