I created a fairly simple VDS, using two source tables with an inner join, two SELECT columns, and no aggregations or functions in SELECT.
I created a raw reflection with both fields, and an aggregation reflection with COUNT, SUM, MIN, and MAX checked off for the measure.
My dimension is a date/time field, and I want to get the maximum concurrency by day. So I execute:
select
date_trunc('day',start_time),
max(concurrent_user_count)
from
edo_concurrency
group by
date_trunc('day',start_time)
But this does not use either reflection. If I change the max to sum:
select
date_trunc('day',start_time),
sum(concurrent_user_count)
from
edo_concurrency
group by
date_trunc('day',start_time)
then it does use the aggregation reflection. Also, if I remove the date_trunc function on start_time:
select
start_time,
max(concurrent_user_count)
from
edo_concurrency
group by
start_time
the aggregation reflection is used.
Why am I unable to use max() with date_trunc()?