Database Reference
In-Depth Information
Recording aggregate analytics
observations
When we explore precomputed aggregate data storage, let's focus on the first question
posed above: how many total views did status updates receive on each day of September?
More generally, we want to store the daily overall view counts in a structure that allows us
to easily retrieve the counts for a given range of time. We don't need to store discrete in-
formation about every view event that happened; simply knowing how many views oc-
curred per day is sufficient.
Let's create a new
daily_status_update_views
table that aggregates our analytics
observations at just the right granularity:
CREATE TABLE "daily_status_update_views" (
"year" int,
"date" timestamp,
"total_views" counter,
"web_views" counter,
"mobile_views" counter,
"api_views" counter,
PRIMARY KEY (("year"), "date")
);
Of course, the most striking thing about this table definition is the introduction of the
counter
column type; we'll dive into this a little later in this chapter. First, let's take a
close look at the structure of the primary key and the data columns.
