在數字化營銷領域，廣告主每天面臨著海量數據帶來的分析挑戰：從賬戶整體投放效果，到分渠道、分地域的精細化運營，每一層級的數據洞察都需要靈活高效的查詢能力。我們的廣告業務動態查詢系統，正是為解決這類需求而生 ；通過層級化數據集設計與實時計算架構，讓數據分析師只需簡單勾選維度和指標，就能秒級獲取可視化分析結果，徹底告別傳統 SQL 查詢的繁瑣流程。

業務痛點：數據層級復雜，實時性要求高，可視化效率，技術門檻

系統核心：數據層級數據集設計，動態查詢引擎（“維度+指標+時間+動態條件”）自由搭配組合查詢，實時查詢排序+秒級響應，動態輸出結構

一、數據模型層級設計

1. 數據集層級劃分

2. StarRocks 表結構設計（賬戶渠道層級示例）

CREATE TABLE ad_account_channel (-- 維度字段account_id BIGINT NOT NULL,channel_id INT NOT NULL,date DATE NOT NULL,region STRING,-- 基礎指標impressions BIGINT,clicks BIGINT,cost DECIMAL(10,2),ctr DECIMAL(5,4),-- 渠道特有指標channel_ctr DECIMAL(5,4),channel_cvr DECIMAL(5,4),-- 時間維度hour INT,day_of_week INT
) ENGINE=OLAP
AGGREGATE KEY(account_id, channel_id, date, region)
COMMENT "賬戶渠道層級數據集"
DISTRIBUTED BY HASH(account_id) BUCKETS 200
PROPERTIES ("replication_num" = "3","enable_persistent_index" = "true","index_type" = "default"
);

3. 索引與聚合策略

多級索引配置：

-- Bloom Filter索引加速維度過濾
ALTER TABLE ad_account_channel 
ADD BLOOMFILTER INDEX bf_channel(channel_id) 
PROPERTIES("bloom_filter_fpp" = "0.01");-- 物化視圖預聚合高頻查詢
CREATE MATERIALIZED VIEW mv_account_channel_daily 
AS SELECT account_id, channel_id, date, SUM(impressions) AS total_imp, SUM(clicks) AS total_click
FROM ad_account_channel
GROUP BY account_id, channel_id, date;

數據聚合策略：

• 實時聚合：Flink 處理 Kafka 流數據時完成初步聚合
• 離線聚合：Hive T+1 任務生成全量聚合數據
• 自動合并：StarRocks 自動合并實時與離線數據

二、動態查詢引擎架構

1. 核心處理流程

2. SQL 動態生成實現

查詢參數模型：

{"dataset": "account_channel",       // 數據集類型"dimensions": ["account_id", "date", "channel_id"], // 維度"metrics": ["impressions", "clicks", "cost", "ctr"], // 指標"time_range": ["2025-07-01", "2025-07-31"], // 時間范圍"filters": {"region": ["華東", "華南"], "ctr": ">0.01"}, // 過濾條件"sort": {"cost": "desc"}, // 排序"page": {"size": 100, "number": 1} // 分頁
}

SQL 生成模板：

String template = "SELECT ${dimensions}, ${metrics} " +"FROM ${dataset} " +"WHERE ${time_condition} AND ${filter_conditions} " +"GROUP BY ${dimensions} " +"ORDER BY ${sort_condition} " +"LIMIT ${page_size} OFFSET ${page_offset}";// 參數替換示例
Map<String, Object> params = new HashMap<>();
params.put("dimensions", String.join(", ", dimensions));
params.put("metrics", String.join(", ", metrics));
params.put("time_condition", "date BETWEEN '2025-07-01' AND '2025-07-31'");
// 其他參數處理...

3. 緩存優化策略

4. 離線與實時數據同步

INSERT INTO ad_account_channel (
account_id
, channel_id
, date
, impressions
, clicks
, cost
)
SELECT account_id, channel_id, date, SUM(impressions), SUM(clicks), SUM(cost)
FROM hive_ad_click_history
WHERE date = '2025-07-01'
GROUP BY account_id, channel_id, date;

三、可視化與輸出層設計

1. 可視化組件架構

2. 圖表數據格式規范

• 折線圖數據結構：

{"type": "line","title": "賬戶點擊趨勢","xAxis": ["2025-07-01", "2025-07-02", "2025-07-03"],"series": [{"name": "PC端點擊","data": [1200, 1500, 1300]},{"name": "移動端點擊","data": [2500, 2800, 2600]}]
}

• 柱狀圖數據結構：

{"type": "bar","title": "各渠道消耗分布","xAxis": ["渠道A", "渠道B", "渠道C", "渠道D"],"series": [{"name": "消耗(元)","data": [12000, 18000, 9500, 15000]}]
}

3. Excel 導出實現

// 使用Apache POI生成Excel
Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("廣告數據報表");// 創建表頭
Row headerRow = sheet.createRow(0);
for (int i = 0; i < headers.length; i++) {headerRow.createCell(i).setCellValue(headers[i]);
}// 填充數據
for (int i = 0; i < data.size(); i++) {Row dataRow = sheet.createRow(i + 1);for (int j = 0; j < data.get(i).length; j++) {dataRow.createCell(j).setCellValue(data.get(i)[j]);}
}// 自動調整列寬
for (int i = 0; i < headers.length; i++) {sheet.autoSizeColumn(i);
}// 輸出文件
try (FileOutputStream fos = new FileOutputStream("ad_report.xlsx")) {workbook.write(fos);
}

四、整體架構圖示

五、性能優化方案

1. 關鍵性能指標

2. 容量規劃建議

• StarRocks 集群：
  • 每節點配置：96核 CPU+720GB 內存 + 2TB SSD
  • 存儲估算：10億條/天數據 ≈ 500GB / 天（壓縮后）
  • 集群規模：初始 9 節點，支持日均 10 億條數據查詢