Description
The SimpleState combinator can be applied to the sum
function to return the sum across all input values. It returns the result with
type SimpleAggregateFunction.
Example usage
Tracking upvotes and downvotes
Let's look at a practical example using a table that tracks votes on posts.
For each post, we want to maintain running totals of upvotes, downvotes, and an
overall score. Using the SimpleAggregateFunction type with sum is suited for
this use case as we only need to store the running totals, not the entire state
of the aggregation. As a result, it will be faster and will not require merging
of partial aggregate states.
First, we create a table for the raw data:
CREATE TABLE raw_votes
(
post_id UInt32,
vote_type Enum8('upvote' = 1, 'downvote' = -1)
)
ENGINE = MergeTree()
ORDER BY post_id;
Next, we create a target table which will store the aggregated data:
CREATE TABLE vote_aggregates
(
post_id UInt32,
upvotes SimpleAggregateFunction(sum, UInt64),
downvotes SimpleAggregateFunction(sum, UInt64),
score SimpleAggregateFunction(sum, Int64)
)
ENGINE = AggregatingMergeTree()
ORDER BY post_id;
We then create a materialized view with SimpleAggregateFunction type columns:
CREATE MATERIALIZED VIEW mv_vote_processor TO vote_aggregates
AS
SELECT
post_id,
-- Initial value for sum state (1 if upvote, 0 otherwise)
toUInt64(vote_type = 'upvote') AS upvotes,
-- Initial value for sum state (1 if downvote, 0 otherwise)
toUInt64(vote_type = 'downvote') AS downvotes,
-- Initial value for sum state (1 for upvote, -1 for downvote)
toInt64(vote_type) AS score
FROM raw_votes;
Insert sample data:
INSERT INTO raw_votes VALUES
(1, 'upvote'),
(1, 'upvote'),
(1, 'downvote'),
(2, 'upvote'),
(2, 'downvote'),
(3, 'downvote');
Query the materialized view using the SimpleState combinator:
SELECT
post_id,
sum(upvotes) AS total_upvotes,
sum(downvotes) AS total_downvotes,
sum(score) AS total_score
FROM vote_aggregates -- Query the target table
GROUP BY post_id
ORDER BY post_id ASC;
┌─post_id─┬─total_upvotes─┬─total_downvotes─┬─total_score─┐
│ 1 │ 2 │ 1 │ 1 │
│ 2 │ 1 │ 1 │ 0 │
│ 3 │ 0 │ 1 │ -1 │
└─────────┴───────────────┴─────────────────┴─────────────┘
See also
