Azure / Azure/data-api-builder
[Enhancement]: Let aggregate_records (MCP) compute multiple aggregations in one call (single GROUP BY pass)
- 主要言語
- C#
- スター
- 1.5k
- フォーク
- 370
- 平均マージ
- 3日 22時間
- マージ済み PR(30日)
- 9
説明
### Is your feature request related to a problem?
The MCP `aggregate_records` tool accepts only **one** aggregate per call (`function` + `field`). To retrieve several metrics over the same grouping, a client must issue **N separate calls**, each generating its own query with the same `GROUP BY`:
```sql
SELECT region, SUM(amount) FROM dbo.Sales WHERE ... GROUP BY region
SELECT region, AVG(amount) FROM dbo.Sales WHERE ... GROUP BY region
SELECT region, SUM(cost) FROM dbo.Sales WHERE ... GROUP BY region
```
This scans the same rows and recomputes the same `GROUP BY` **N times**, plus N× query-planning / result-shaping / round-trip overhead. On large tables/views this is a real, avoidable database cost. Issuing the N calls concurrently on the client side only reduces latency — it does not reduce total DB work and can increase peak load/contention.
### Describe the solution you'd like
Allow `aggregate_records` to accept **multiple aggregations in a single call**, producing one `SELECT` with one `GROUP BY` pass, e.g.:
```json
{
"entity": "Sales",
"aggregations": [
{ "function": "sum", "field": "amount", "alias": "sum_amount" },
{ "function": "avg", "field": "amount", "alias": "avg_amount" },
{ "function": "sum", "field": "cost", "alias": "sum_cost" }
],
"groupby": ["region"],
"orderby": ["sum_amount desc"]
}
```
which maps to a single query:
```sql
SELECT region, SUM(amount) AS sum_amount, AVG(amount) AS avg_amount, SUM(cost) AS sum_cost
FROM dbo.Sales
WHERE ...
GROUP BY region
```
`orderby`/`having` should be able to reference the aggregate **aliases** (e.g. `sum_amount`), consistent with how grouped results are projected.
### Why this looks feasible
DAB already builds multi-aggregation queries internally for GraphQL: the shared `SqlQueryStructure.GroupByMetadata.Aggregations` is a `List` and `BaseTSqlQueryBuilder.BuildAggregationColumns(...)` emits all requested aggregate expressions into a single `SELECT`. The MCP `aggregate_records` tool (`AggregateRecordsTool`) currently parses and loads only a single `AggregationOperation`. Extending its argument parsing to accept an array and pushing multiple `AggregationOperation`s onto the existing structure would reuse the machinery GraphQL already relies on.
### Backward compatibility
Keep the current single-metric form (`function` + `field`) working as-is; add the new optional `aggregations` array alongside it. Single-metric callers are unaffected; multi-metric callers opt in.
### Additional context
- Related: #3695 (a separate correctness bug in the aggregate `ORDER BY` for `dwsql`). This enhancement is independent of that fix.
- Benefit is largest for wide analytical tables/views where several measures are aggregated over the same grouping in one logical request.
コントリビューションガイド
調査の方向性
MCP AggregateRecordsTool の引数解析から開始し、既存の GraphQL パスを含めて、GroupByMetadata.Aggregations と BaseTSqlQueryBuilder.BuildAggregationColumns を追跡します。function と field を保持したままオプションの aggregations 配列をサポートし、グループ化された結果が orderby と having で使用可能なエイリアスを持つ 1 つのクエリを生成することを検証します。
索引モデルが issue の本文から書いたものです。
評価
- 技術スタック
- csharp, sql
- 領域
- api, database
- issue の種類
- 機能追加
- 難易度
- 4/5
- 見積もり時間
- 3〜5日
- 活発さ
- 静か
- 明瞭さ
- 明確に書かれている
- 初心者へのやさしさ
- 58/100