如何针对ADX中的重复数据与汇总查询进行优化

我们将设备读数定期摄入Azure Data Explorer（ADX），其中0.28%的读数后续会更新数值。目前已通过arg_max结合物化视图处理重复数据，但按日/月/年的汇总查询性能不佳。尝试过两种优化方式均失败：

• 无法在现有物化视图之上再创建汇总物化视图（ADX限制仅允许基于含any()/take_any()类聚合的物化视图创建子视图）
• 单个物化视图中无法包含多个summarize子句

同时需了解如何删除被更新的旧读数，以下是可行优化方案：

方案1：预计算时间维度，优化基础物化视图 #

先修正基础物化视图的分组逻辑（原分组键错误，会导致同一时间点的多版本读数被错误保留），并提前计算时区转换后的日/月/年维度字段，避免实时计算开销。

修正后的物化视图创建语句 #

.create materialized-view with (backfill=true, DocString="Latest readings with precomputed time dimensions", 
        effectiveDateTime=datetime(2019-01-01), 
        MaxSourceRecordsForSingleIngest=10000000, 
        Concurrency=5 
) ReadingsLatestWithDimensions on table Readings { 
    Readings 
    // 正确分组：按设备+时间戳，保留该组合下的最新读数
    | summarize arg_max(IngestTime, *) by DeviceName, Timestamp  
    | extend 
        LocalTimestamp = datetime_utc_to_local(Timestamp, 'Europe/Oslo'),
        Day = startofday(LocalTimestamp),
        Month = startofmonth(LocalTimestamp),
        Year = startofyear(LocalTimestamp)
}

优化后的日汇总查询 #

ReadingsLatestWithDimensions
| summarize Reading_Day = sum(Reading) by Day, DeviceName

预计算字段后，查询无需实时进行时区转换和日期截断，性能会显著提升。

方案2：用更新策略实现二级汇总 #

通过更新策略将基础物化视图的数据同步到专门的汇总表，实现类似二级物化视图的效果，查询时直接读取汇总表即可获得最优性能。

步骤1：创建日汇总表 #

.create table ReadingsDailySummary (Day:datetime, DeviceName:string, Reading_Day:decimal)

步骤2：配置更新策略 #

.alter table ReadingsDailySummary policy update 
@'[{
    "Source": "ReadingsLatestWithDimensions",
    "Query": "ReadingsLatestWithDimensions | summarize Reading_Day = sum(Reading) by Day, DeviceName",
    "IsEnabled": true
}]'

每当基础物化视图有新数据（或旧数据更新）时，更新策略会自动触发，同步更新汇总表数据。

方案3：删除被更新的旧读数 #

硬删除（直接清理旧记录） #

ADX支持硬删除符合条件的记录，但操作不可逆，需谨慎执行（需管理员权限）：

// 批量删除所有设备的非最新读数
let latest_records = Readings | summarize max(IngestTime) by DeviceName, Timestamp;
Readings
| join kind=inner latest_records on DeviceName, Timestamp
| where IngestTime != max_IngestTime
| delete

软删除（标记无效记录） #

若需保留历史数据，可添加字段标记有效记录，查询时过滤：

1. 添加状态字段：

.alter table Readings add column IsActive:bool
.update table Readings set IsActive = true where IsActive is null

2. 更新时标记旧记录为无效：

// 示例：标记指定设备时间点的旧记录
let target_device = "EX";
let target_timestamp = datetime(2022-10-31 22:00:00.0000000);
let latest_ingest_time = toscalar(Readings | summarize max(IngestTime) by DeviceName, Timestamp | where DeviceName == target_device and Timestamp == target_timestamp | project max_IngestTime);
Readings
| where DeviceName == target_device
| where Timestamp == target_timestamp
| where IngestTime != latest_ingest_time
| update set IsActive = false

3. 查询时过滤有效记录：

Readings
| where IsActive == true
| summarize arg_max(IngestTime, *) by DeviceName, Timestamp

方案4：用函数封装查询逻辑 #

若上述方案无法满足需求，可创建查询函数封装去重与汇总逻辑，简化日常查询操作：

.create function ReadingsDailySummaryFunc() {
    Readings
    | summarize arg_max(IngestTime, *) by DeviceName, Timestamp
    | extend LocalTimestamp = datetime_utc_to_local(Timestamp, 'Europe/Oslo')
    | summarize Reading_Day = sum(Reading) by Day = startofday(LocalTimestamp), DeviceName
}

调用方式：ReadingsDailySummaryFunc()

内容的提问来源于stack exchange，提问作者Kjetil Hamre