在数字化营销领域，广告主每天面临着海量数据带来的分析挑战：从账户整体投放效果，到分渠道、分地域的精细化运营，每一层级的数据洞察都需要灵活高效的查询能力。我们的广告业务动态查询系统，正是为解决这类需求而生；通过层级化数据集设计与实时计算架构，让数据分析师只需简单勾选维度和指标，就能秒级获取可视化分析结果，彻底告别传统 SQL 查询的繁琐流程。

业务痛点：数据层级复杂，实时性要求高，可视化效率，技术门槛

系统核心：数据层级数据集设计，动态查询引擎（“维度+指标+时间+动态条件”）自由搭配组合查询，实时查询排序+秒级响应，动态输出结构

一、数据模型层级设计

1. 数据集层级划分

层级名称	核心维度组合	存储引擎	数据更新频率
账户层级	账户 ID + 日期 + 基础指标	StarRocks	实时 + T+1
账户渠道层级	账户 ID + 渠道 ID + 日期 + 渠道指标	StarRocks	实时 + T+1
账户地域层级	账户 ID + 地域 ID + 日期 + 地域指标	StarRocks	实时 + T+1
广告创意层级	广告 ID + 创意 ID + 日期 + 创意指标	StarRocks	实时 + T+1
原始事件层级	全量曝光 / 点击事件明细	Hive+Iceberg	T+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);
}