0

Imagine you have to display information about rainfall based on cities over time.

You have tables the provides the details on how much it rains in a specific city for every hour. There is an endpoint that returns the average amount of rainfall for the timeframe/city requested.

(so imagine a table called rainfall_california, rainfall_texas, etc... I realize this schema isn't ideal for rainfall, but using it for an example.)

So instead of calculating the average on each request, I setup a continuous aggregate to calculate the average into a new view and have a policy to refresh the last hour of data once every hour.

ca_texas_rainfall_1_day ca_texas_rainfall_7_day ca_texas_rainfall_30_day ca_california_rainfall_1_day ca_california_rainfall_7_day ca_california_rainfall_30_day

This works great and is super fast, but I'm a little confused on the best way to set it up. Should I have a different view for each continuous aggregate and each city? Wouldn't that result in a ton of different views? Or should I consolidate the average of each table into a single view?

ysth
  • 96,171
  • 6
  • 121
  • 214
Nicole Staline
  • 557
  • 4
  • 15
  • The continuous aggregates create materialized views for each specific bucket of time. That is my main issue, this schema was purely an example. I guess my real question would be, do I need a different table for each time bucket or can I somehow group them together. – Nicole Staline Jul 31 '21 at 00:44
  • A materialized view is not necessary a terrible idea for creating summarized data, although I also do not see the point. But **only one materialized view**. For a more complete answer post the table definition and materialized view definition and sample test data with desired results, all as text - **no images**. – Belayer Jul 31 '21 at 21:43

0 Answers0