Trying to make a Influx Continuous Query where certain fields are restricted by a CASE-when. This doesn’t seem to be supported.
Is there a way (i.e.: using joins or something) to get a similar result?
Specifically, this is the query I want to get to work (ohclv based on trade-data):
SELECT
min(price) AS low,
max(price) AS high,
first(price) AS open,
last(price) AS close,
sum(amount) AS volume,
sum(CASE WHEN side="sell" THEN amount ELSE 0 END) AS volume_sell,
count(price) AS num_trades,
count(CASE WHEN side="sell" THEN 1 ELSE 0 END) AS num_trades_sell
FROM db..trades
As far as I know, there is no equivalent of the “CASE” statement.
A possible workaround is to use different queries to write in the same measurement, in fact, points that share the same Time and Tags values (also called series) will be merged once written in the database.
Therefore a possible solution will be to use the following queries, note that this might or might not be a viable option depending on the detail of the result. (ie: if you group by the field you also use in the where the points will never be merged)
# Turn those in a continuous query, with the same frequency, time window and tag set
# Query for total values
SELECT
min(price) AS low,
max(price) AS high,
first(price) AS open,
last(price) AS close,
sum(amount) AS volume,
count(price) AS num_trades
FROM __db__.__measurement__
GROUP BY __tags__
# Query for specific values
SELECT
sum(amount) AS volume_sell,
count(__Field__) AS num_trades_sell
-- or count(distinct(__Field__)) AS num_trades_sell
-- or some math workaround to always return 1 like the ones below
--1. CEIL(count(__Field__)/10000) AS num_trades_sell
--2. ROUND(count(__Field__)/count(__Field__)) AS num_trades_sell
FROM __db__.__measurement__
WHERE "side" ='sell'
GROUP BY __tags__
Also note that is not possible to pass fixed values in the query (ie: 1 as num_trades_sell) so you will need to use a function (smaples in the query above)
