Source Code Reading: AnalyticsQueryService — 14 Aggregation Implementations

“

Series: S9 Source Code Reading · Article 7 | Level: Advanced | Reading Time: 25 min

#TL;DR

DefaultAnalyticsQueryService is the aggregation analytics service in Data Layer, built on Quarkus 3.x. It uses four independent SQL Builders (AggregateGroupedSqlBuilder, TimeBucketSqlBuilder, TopNSqlBuilder, DistributionSqlBuilder) to convert Proto requests into parameterized Doris SQL, supporting 14 aggregation functions (SUM/COUNT/AVG/MIN/MAX/PERCENTILE etc.), ROLLUP subtotal row detection, Redis 30-second result caching, time-bucket empty bucket filling (BucketFiller), and three distribution bucketing modes. This article analyzes the layered architecture, SQL Builder parameterization, MD5 cache key generation, ROLLUP NULL detection, empty bucket filling, and three distribution bucketing implementations.

#Table of Contents

1. Architecture and Four Query Types
2. Grouped Aggregation: 8-Step Flow
3. SQL Builder Pattern
4. Redis Cache Strategy: MD5 Key with 30s TTL
5. ROLLUP Subtotal Row Detection
6. Time-Bucketed Aggregation and Empty Bucket Filling
7. TopN Query
8. Distribution Query: Three Bucketing Modes
9. QueryExecutionInfo
10. 14 Aggregation Functions Reference
11. Key Takeaways

#1. Architecture and Four Query Types

@ApplicationScoped
public class DefaultAnalyticsQueryService implements AnalyticsQueryService {
    private final DorisClient dorisClient;
    private final RedisDataSource redisDataSource;
    private final ObjectMapper objectMapper;
}

#2. Grouped Aggregation: 8-Step Flow

public AggregateGroupedResponse aggregateGrouped(AggregateGroupedRequest request) {
    // 1. Cache lookup (early return on hit)
    // 2. Build SQL via AggregateGroupedSqlBuilder
    // 3. Execute primary query via DorisClient
    // 4. Map result rows to AggregateRow protos
    // 5. Execute total-row query if include_total=true
    // 6. Build QueryExecutionInfo with timing
    // 7. Assemble response
    // 8. Write to Redis cache if use_cache=true
}

Design highlights: Cache-first (zero SQL overhead on hit), separate total query (no GROUP BY), execution info passthrough (executeMs, totalMs, cacheHit, queryId).

#3. SQL Builder Pattern

SQL Builders convert Proto requests to parameterized SQL with ? placeholders, preventing SQL injection. Each builder handles: aggregate function extraction from AggregateColumn list, GROUP BY from groupByFields, parameterized WHERE from filters, and optional WITH ROLLUP.

AggregateGroupedSqlBuilder builder = new AggregateGroupedSqlBuilder(request);
AggregateGroupedSqlBuilder.BuildResult buildResult = builder.build();
// buildResult.getSql() => "SELECT dept, SUM(revenue) FROM ... WHERE world_id = ? GROUP BY dept"
// buildResult.getParams() => ["world_main"]

#4. Redis Cache Strategy: MD5 Key with 30s TTL

private static final int CACHE_TTL_SECONDS = 30;
private static final String CACHE_KEY_PREFIX = "analytics:grouped:";

30-second TTL rationale: Analytics results typically remain valid within short windows (data writes have latency). 30-second caching balances freshness with repeated query performance. Cache key uses MD5 of worldId + objectType + request.toString() to avoid excessively long key names.

#5. ROLLUP Subtotal Row Detection

When WITH ROLLUP is enabled, Doris returns additional subtotal rows where GROUP BY field values are NULL. A row is marked is_subtotal = true when at least one group-by field has a null value in the result set.

department | region | SUM(revenue)
-----------|--------|-------------
Sales      | East   | 1000         <- regular row
Sales      | NULL   | 3000         <- subtotal (Sales total)
NULL       | NULL   | 5000         <- grand total

#6. Time-Bucketed Aggregation and Empty Bucket Filling

Time-bucket aggregation divides continuous timelines into fixed-size buckets (hour/day/week/month) and aggregates per bucket. BucketFiller fills buckets with no data:

#7. TopN Query

TopN queries return the top N records sorted by a specified measure field, translating to ORDER BY measure DESC LIMIT N SQL. Supports ASC/DESC sort direction and optional filter conditions.

#8. Distribution Query: Three Bucketing Modes

Distribution queries also compute DistributionStats (min, max, mean, median, stddev) for histogram context.

#9. QueryExecutionInfo

QueryExecutionInfo.newBuilder()
    .setExecuteMs(executeMs)     // SQL execution time
    .setTotalMs(totalMs)         // Total processing time
    .setCacheHit(false)          // Cache hit status
    .setQueryId(UUID.randomUUID().toString())  // Trace ID
    .build();

Every analytics response includes execution info for frontend latency display and cache hit monitoring.

#10. 14 Aggregation Functions Reference

#11. Key Takeaways

1. SQL Builder pattern: Each query type has an independent SQL Builder for safe parameterized SQL generation
2. Redis 30s cache: MD5 cache key + 30s TTL balances freshness and performance
3. ROLLUP subtotal detection: NULL values in GROUP BY fields identify subtotal rows
4. Empty bucket filling: BucketFiller supports ZERO/PREVIOUS/NONE fill strategies
5. 14 aggregations: From basic COUNT/SUM to advanced PERCENTILE_APPROX/STDDEV
6. Execution info passthrough: QueryExecutionInfo carries timing and cache status for frontend monitoring

#Next Article

S9-08: SearchService — Unified Abstraction for 6 Search Modes. We will dive into the full-text search service, examining BEST_MATCH and FUZZY modes on Doris OLAP, plus Redis-driven recent and popular search tracking.

Tags: #coomia-dip #source-code-reading #data-Layer #analytics #aggregation #doris #sql-builder #redis-cache #rollup #distribution