how to have a item represented as value in time series grafana?-CodePudding

So I have this table:

ID  sale_date   item    price
-------------------------------
1   2022-11-01  apple   2.11
2   2022-11-02  apple   2.61
3   2022-11-03  apple   2.91
4   2022-11-04  apple   2.51
5   2022-11-01  orange  3.23
6   2022-11-02  orange  2.75
7   2022-11-03  orange  3.01
8   2022-11-04  orange  3.51
9   2022-11-01  banana  1.93
10  2022-11-02  banana  2.22
11  2022-11-03  bananna 2.13
12  2022-11-04  banana  1.53

I am trying to create a query that will display in a Grafana time series graph measuring the price for apple, orange and bananas overtime.

my query:

SELECT
  $__time(sale_date),
  item,
  price
from mytable
group by sale_date, item, price

I am trying to achieve a time series similar to this:

any help is appreciated :)

CodePudding user response：

This will be good start - aggregated avg price per day:

SELECT
  $__timeGroupAlias(sale_date, '1d'),
  item as "metric",
  AVG(price) AS "value"
FROM mytable
WHERE
  $__timeFilter(sale_date)
GROUP BY 1, 2
ORDER BY 1

You may still need to tweak it, because your sale_date datatype is unknown. Eventually, you may need different macro.