How to Query Metrics?

A metric represents the result of a group by operation that involves grouping data based on all dimensions. The resulting output includes all dimensions and the corresponding measures. In Accio, metric can be treated as a regular table, allowing us to perform aggregations or apply WHERE conditions.

SELECT count(*) FROM Revenue

Example

Metric Revenue @model(Orders) {
  totalrevenue: INTEGER @measure(sum(totalprice))
  orderstatus: OrderStatus @dim
  custkey: INTEGER @dim
  orderdate: date @time_grain(orderdate, [DAY, MONTH])
}

Query metric

Query all content in Revenue.

SELECT * FROM Revenue;

 custkey | orderstatus |    totalrevenue
---------+-------------+--------------------
     251 | F           | 1369253.1199999999
    1202 | F           | 1699875.2499999998
    1384 | F           | 1795731.6300000001
...

Find orderstatus is O and custkey is 100 in Revenue

SELECT * FROM Revenue 
WHERE custkey = 100 
AND orderstatus = 'O';

 custkey | orderstatus |    totalrevenue
---------+-------------+--------------------
     100 | O           | 1630118.7299999997

Aggregate totalrevenue by custkey

SELECT custkey, sum(totalrevenue) AS totalrevenue 
FROM Revenue 
GROUP BY custkey;

 custkey |    totalrevenue
---------+--------------------
     251 | 1480785.3399999999
    1202 | 3085561.0599999996
    1384 | 3217696.1999999997
     377 |          803271.94
...

Query metric with roll_up function

Metric won’t show up time_grain column.and neither does it aggregate data by time_grain column unless we use roll_up function. roll_up is for metric specifically, and its purpose is to aggregate metric with time grain column by date part (i.e. YEAR, MONTH, DAY). Other accio data objects like model, view can’t be used in roll_up function.

Query metric aggregate by YEAR part in time grain column (i.e. orderdate)

SELECT * FROM roll_up(Revenue, orderdate, YEAR)

 orderdate  | custkey |     totalrevenue
------------+---------+--------------------
 1992-01-01 |     251 |          213843.68
 1992-01-01 |    1202 | 1015090.2500000001
 1993-01-01 |    1384 |  527967.1799999999
 1993-01-01 |     377 |            97846.7
 1993-01-01 |     491 | 284295.41000000003
 1993-01-01 |     527 |          290068.09
 1993-01-01 |    1480 |          714318.12
...

Query metric aggregate by DAY part in time grain column (i.e. orderdate)

SELECT * FROM roll_up(Revenue, orderdate, DAY);

 orderdate  | custkey | orderstatus |    totalrevenue
------------+---------+-------------+--------------------
 1992-06-06 |     251 | F           |           44320.73
 1992-06-06 |    1202 | F           |          356711.63
 1993-02-17 |    1384 | F           |          222389.91
 1993-02-17 |     377 | F           |           46484.63
 1993-02-17 |     491 | F           |            68929.8
 1993-10-31 |     527 | F           |           72709.83
...

Query metric only aggregate by YEAR part in time grain column (i.e. orderdate)

SELECT orderdate, sum(totalrevenue) AS totalrevenue 
FROM roll_up(Revenue, orderdate, DAY) 
GROUP BY orderdate;

 orderdate  |    totalrevenue
------------+--------------------
 1992-06-06 |          871174.53
 1993-02-17 |          624004.33
 1993-10-31 | 1100586.5899999999
 1994-07-14 |  624606.3200000001
 1995-03-27 |         1135738.11
 1995-12-08 | 1289773.6400000001
 1996-08-20 |         1571461.49
...

Query metric in 1992-06-06

SELECT * FROM roll_up(Revenue, orderdate, DAY) 
WHERE orderdate = '1992-06-06'

 orderdate  | custkey | orderstatus | totalrevenue
------------+---------+-------------+--------------
 1992-06-06 |     251 | F           |     44320.73
 1992-06-06 |    1202 | F           |    356711.63
 1992-06-06 |     287 | F           |    274018.73
 1992-06-06 |    1472 | F           |    196123.44
(4 rows)