AggregatingMergeTree not aggregating inserts properly

Question

I have a table that aggregates the number of sales across various products by minute/hour/day and computes various metrics.

The table below has 1 minute increment calculations that compute off core_product_tbl. After the computations are in product_agg_tbl, other tables compute by hour, day, week etc off of product_agg_tbl.

CREATE TABLE product_agg_tbl (
  product String,
  minute DateTime,
  high Nullable(Float32),
  low Nullable(Float32),
  average AggregateFunction(avg, Nullable(Float32)),
  first Nullable(Float32),
  last Nullable(Float32),
  total_sales Nullable(UInt64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(minute)
ORDER BY (product, minute);

CREATE MATERIALIZED VIEW product_agg_mv TO product_agg_tbl AS
SELECT 
  product,
  minute,
  max(price) AS high,
  min(price) AS low,
  avgState(price) AS average,
  argMin(price, sales_timestamp) AS first,
  argMax(price, sales_timestamp) AS last,
  sum(batch_size) as total_sales
FROM  core_product_tbl
WHERE minute >= today()
GROUP BY product, toStartOfMinute(sales_timestamp) AS minute;

CREATE VIEW product_agg_1w AS
SELECT
    product,
    toStartOfHour(minute) AS minute,
    max(high) AS high,
    min(low) AS low,
    avgMerge(average) AS average_price,
    argMin(first, minute) AS first,
    argMax(last, minute) AS last,
    sum(total_sales) as total_sales
FROM product_agg_tbl
WHERE minute >= date_sub(today(), interval 7 + 7 day)
GROUP BY  product, minute;

The issue I have is that when I run the query below straight off of core_product_tbl, I get much different numbers than product_agg_1w. What could be going on?

SELECT 
  product,
  toStartOfHour(minute) AS minute,
  max(price) AS high,
  min(price) AS low,
  avgState(price) AS average,
  argMin(price, sales_timestamp) AS first,
  argMax(price, sales_timestamp) AS last,
  sum(batch_size) as total_sales
FROM  core_product_tbl
WHERE minute >= today()
GROUP BY product, toStartOfMinute(sales_timestamp) AS minute;

user_11644308 · Answer

You should use SimpleAggregateFunction or AggregateFunction in the table AggregatingMergeTree.

AggregatingMergeTree knows nothing about Materialized View and about select in the Materialized View. https://den-crane.github.io/Everything_you_should_know_about_materialized_views_commented.pdf

CREATE TABLE product_agg_tbl (
  product String,
  minute DateTime,
  high SimpleAggregateFunction(max, Nullable(Float32)),
  low SimpleAggregateFunction(min, Nullable(Float32)),
  average AggregateFunction(avg, Nullable(Float32), DateTime),
  first AggregateFunction(argMin, Nullable(Float32), DateTime),
  last AggregateFunction(argMax, Nullable(Float32),DateTime),
  total_sales SimpleAggregateFunction(sum,Nullable(UInt64))
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(minute)
ORDER BY (product, minute);

CREATE MATERIALIZED VIEW product_agg_mv TO product_agg_tbl AS
SELECT 
  product,
  minute,
  max(price) AS high,
  min(price) AS low,
  avgState(price) AS average,
  argMinState(price, sales_timestamp) AS first,
  argMaxState(price, sales_timestamp) AS last,
  sum(batch_size) as total_sales
FROM  core_product_tbl
WHERE minute >= today()
GROUP BY product, toStartOfMinute(sales_timestamp) AS minute;

CREATE VIEW product_agg_1w AS
SELECT
    product,
    toStartOfHour(minute) AS minute,
    max(high) AS high,
    min(low) AS low,
    avgMerge(average) AS average_price,
    argMinMerge(first, minute) AS first,
    argMaxMerge(last, minute) AS last,
    sum(total_sales) as total_sales
FROM product_agg_tbl
WHERE minute >= date_sub(today(), interval 7 + 7 day)
GROUP BY  product, minute;

Don't use view (product_agg_1w) because it's counterproductive for performance. It reads excessive data. Use select directly to product_agg_tbl.

AggregatingMergeTree not aggregating inserts properly

1 Answers