ADX/KQL：如何避免evaluate pivot操作中的列自动重排？

你的核心需求是解决evaluate pivot操作导致列自动重排的问题，且因列会动态变化，无法使用固定列名的project-reorder。除了你提到的两个方案，还有以下两种可行思路：

方案一：基于原始表列顺序的动态重排 #

利用原始数据表的列顺序，通过动态生成列列表实现重排，自动跳过不存在的列：

1. 先通过getschema()获取原始表的列名顺序
2. 用array_intersect将原始列名列表与pivot后的表列名列表取交集，得到存在且符合原始顺序的列列表
3. 用动态project语句重排列

修改后的代码示例：

//let numberFiltersAvailable = 1;
let numberFiltersAvailable = 2;
let dataTable = datatable(['Start Date']:string, ['R Ident']:string, ['R Name']:string, ['J Ident']:string, KPI1:string, KPI2:string, KPI3:string, KPI4:string, KPI5:string, ['Sender']:string, ['Receiver']:string, ['J Weight']:string, Duration:string)
[
    "23-04-28 [22:00]", "R1", "", "001900", "46.6 %", "46.16 %", "100.0 %", "73.36 %", "63.52 %", "S1", "Re1", "2 tons", "53min", 
    "23-04-29 [05:24]", "R2", "", "001898", "59.96 %", "59.96 %", "98.19 %", "85.55 %", "71.38 %", "S1", "Re1", "1 tons", "04h, 36min", 
    "23-04-29 [10:00]", "R3", "", "001901", "57.62 %", "57.62 %", "97.89 %", "92.44 %", "63.68 %", "S1", "Re1", "6 tons", "01h, 59min", 
    "23-04-29 [11:59]", "R3", "", "001902", "50.72 %", "50.72 %", "100.0 %", "80.91 %", "62.69 %", "S1", "Re1", "6 tons", "02h, 09min", 
    "23-04-29 [14:09]", "R4", "", "001903", "0.0 %", "0.0 %", "100.0 %", "84.39 %", "0.0 %", "S1", "Re1", "5 tons", "01h, 46min", 
    "23-04-29 [15:55]", "R2", "", "001904", "68.95 %", "68.95 %", "93.96 %", "93.26 %", "78.69 %", "S1", "Re1", "1 tons", "06h, 04min", 
];
let empty_calculationValue = view(){
    dataTable
    | take 1
    | project Value = "Please select a filter",
              Validation = case(numberFiltersAvailable > 1, 1, 0)
    };
// 获取原始表的列名顺序
let originalColumns = dataTable | getschema | project ColumnName | summarize make_list(ColumnName);
// 执行原有逻辑得到pivot后的表
let pivotedTable = dataTable
    | extend Validation = case(numberFiltersAvailable > 1, 0, 1)
    | as t1
    | union kind=outer (empty_calculationValue)
    | where Validation == 1
    | project-away Validation 
    | evaluate narrow()
    | where isnotempty(Value)
    | extend Column = replace_string(replace_string(replace_string(Column, "[", ""), "'", ""), "]", "")
    | evaluate pivot(Column, take_any(Value), Row)
    | project-away Row;
// 动态生成符合原始顺序的列列表并重排
pivotedTable
| evaluate project_columns(array_intersect(originalColumns, todynamic(pivotedTable | getschema | project ColumnName | summarize make_list(ColumnName))))

方案二：在narrow阶段保留列顺序索引，pivot后按索引排序 #

通过给原始列添加顺序索引，在pivot后根据索引重新排列列：

1. 在narrow之前，给原始表的列按顺序添加索引列
2. narrow后保留列名和对应的索引
3. pivot后将列名和索引映射，按索引排序后重排列

修改后的代码示例：

//let numberFiltersAvailable = 1;
let numberFiltersAvailable = 2;
let dataTable = datatable(['Start Date']:string, ['R Ident']:string, ['R Name']:string, ['J Ident']:string, KPI1:string, KPI2:string, KPI3:string, KPI4:string, KPI5:string, ['Sender']:string, ['Receiver']:string, ['J Weight']:string, Duration:string)
[
    "23-04-28 [22:00]", "R1", "", "001900", "46.6 %", "46.16 %", "100.0 %", "73.36 %", "63.52 %", "S1", "Re1", "2 tons", "53min", 
    "23-04-29 [05:24]", "R2", "", "001898", "59.96 %", "59.96 %", "98.19 %", "85.55 %", "71.38 %", "S1", "Re1", "1 tons", "04h, 36min", 
    "23-04-29 [10:00]", "R3", "", "001901", "57.62 %", "57.62 %", "97.89 %", "92.44 %", "63.68 %", "S1", "Re1", "6 tons", "01h, 59min", 
    "23-04-29 [11:59]", "R3", "", "001902", "50.72 %", "50.72 %", "100.0 %", "80.91 %", "62.69 %", "S1", "Re1", "6 tons", "02h, 09min", 
    "23-04-29 [14:09]", "R4", "", "001903", "0.0 %", "0.0 %", "100.0 %", "84.39 %", "0.0 %", "S1", "Re1", "5 tons", "01h, 46min", 
    "23-04-29 [15:55]", "R2", "", "001904", "68.95 %", "68.95 %", "93.96 %", "93.26 %", "78.69 %", "S1", "Re1", "1 tons", "06h, 04min", 
];
let empty_calculationValue = view(){
    dataTable
    | take 1
    | project Value = "Please select a filter",
              Validation = case(numberFiltersAvailable > 1, 1, 0)
    };
// 获取原始列的顺序索引映射
let columnOrderMap = dataTable 
    | getschema 
    | project ColumnName, ColumnIndex = row_number()
    | extend ColumnNameClean = replace_string(replace_string(replace_string(ColumnName, "[", ""), "'", ""), "]", "")
    | summarize make_dictionary(ColumnNameClean, ColumnIndex);
// 执行原有逻辑并保留列索引
let intermediateTable = dataTable
    | extend Validation = case(numberFiltersAvailable > 1, 0, 1)
    | as t1
    | union kind=outer (empty_calculationValue)
    | where Validation == 1
    | project-away Validation 
    | evaluate narrow()
    | where isnotempty(Value)
    | extend Column = replace_string(replace_string(replace_string(Column, "[", ""), "'", ""), "]", "")
    | extend ColumnIndex = columnOrderMap[Column];
// pivot后按索引排序列并重排
intermediateTable
| evaluate pivot(Column, take_any(Value), Row)
| project-away Row
| evaluate project_columns(
    intermediateTable 
    | summarize sortedColumns = sort_array(make_set(Column), (x) => columnOrderMap[x])
    | mv-expand sortedColumns to typeof(string)
    | summarize make_list(sortedColumns)
)

这两种方案都能动态适配列的变化，同时保持原始的列顺序。

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