'Where' clause with graphite queries

I hold a lot of time-series metric data in graphite. Let's assume I have 2 different metrics X and Y that represent the prices of two different products.

I would now like to query the data from an app and do something like this (of course, that's a pseudo-sql):

Select all points of metric X where value is smaller than value of metric Y during a time frame

I couldn't find any reasonable way, without writing my own script or some map-reduce job, to do it.

I could, for example, plot the graphs and try to understand it visually pretty easily. But it won't be usable for an app to use.

I also thought about using some functions like currentBelow and currentAbove but it doesn't look like I can provide two different series to compare but only one specific integer per the entire period of time.

Solution

Disclaimer: I hope there is some better solution ;).

The solution is:

Metrics:

product.X.price
product.Y.price

create a filter mask - values 0 is not greater, 1 is greater
1. diffSeries - to set bound 0 - above points we want to include, below to exclude.
```
diffSeries(product.X.price, product.Y.price)
```
2. removeBelowValue - to remove all excluded - below 0,
```
removeBelowValue(diffSeries(product.X.price, product.Y.price), 0)
```
3. divideSeries - divide above series by itself to create a mask of 0 and 1 - fortunately in graphite's functions 0/0 = 0 (sic!),
```
divideSeries(removeBelowValue(diffSeries(product.X.price, product.Y.price), 0), removeBelowValue(diffSeries(product.X.price, product.Y.price), 0))
```

filter out using prepared mask with multiplySeries, since

0 * datapoint = 0

1 * datapoint = datapoint

multiplySeries(product.X.price, divideSeries(removeBelowValue(diffSeries(product.X.price, product.Y.price), 0), removeBelowValue(diffSeries(product.X.price, product.Y.price), 0)))