Agregace dat pomocí GraphQL

Nástroj Data API Builder podporuje agregaci GraphQL pro entity řady SQL Server a pro entity ve vyhrazeném fondu SQL služby Azure Synapse Analytics. Pomocí pole groupBy v dotazu kolekce můžete vypočítat hodnoty sum, avg, min, max a count.

Příklady v tomto článku používají entity SQL Server a GraphQL s oprávněním ke čtení. Tvůrce rozhraní DATA API generuje SQL stejně jako příkazy zobrazené v jednotlivých scénářích. Hodnoty parametrů se zobrazují jako parametry dotazu za běhu. Funkce sum, avg, min a max platí pro číselná pole. Funkce count funguje na libovolném poli.

Important

Agregace není dostupná pro Azure Cosmos DB pro NoSQL, PostgreSQL nebo MySQL.

Agregace je ve výchozím nastavení povolená. Chcete-li jej vypnout, nastavte v konfiguračním souboru v části runtime.graphql hodnotu enable-aggregation na false. Agregační dotazy vracejí jednu stránku skupin, ve výchozím nastavení 100. Pomocí argumentu first v dotazu kolekce změňte maximum, například books(first: 500). Nastavte výchozí hodnotu pomocí runtime.pagination.default-page-size.

Přidání schématu GraphQL

Když povolíte agregaci, Tvůrce rozhraní Data API přidá do každé podporované kolekce GraphQL agregační pole a vygenerované typy. Přesné vygenerované názvy typů jsou specifické pro entitu a viditelné prostřednictvím introspekce GraphQL, ale syntaxe dotazu je konzistentní napříč entitami.

Následující fragmenty kódu zobrazují formáty syntaxe, ne kompletní dotazy GraphQL.

groupBy

Vrátí seskupené řádky pro kolekci. Místo agregačního dotazu vyberte tohoto člena items .

<collection> { groupBy { ... } }

groupBy(fields: [...])

Zobrazí seznam polí entity, podle které se mají seskupit. Vynechte fields, aby se všechny řádky agregovaly do jedné skupiny.

<collection> { groupBy(fields: [<field>, ...]) { ... } }

fields

Vrátí seskupené hodnoty polí pro každý řádek ve výsledku agregace.

groupBy(fields: [<field>]) { fields { <field> } }

aggregations

Obsahuje výběr agregační funkce pro každou skupinu.

groupBy { aggregations { <alias>: <function>(field: <field>) } }

sum, avg, min a max

Agregovaná číselná pole

aggregations { <alias>: sum(field: <numeric-field>) }
aggregations { <alias>: avg(field: <numeric-field>) }
aggregations { <alias>: min(field: <numeric-field>) }
aggregations { <alias>: max(field: <numeric-field>) }

count

Spočítá hodnoty pro pole.

aggregations { <alias>: count(field: <field>) }

field

Identifikuje pole entity, které se má agregovat. Názvy polí jsou výčtové hodnoty, nikoli řetězce.

<function>(field: <field>)

having

Filtruje skupiny poté, co nástroj Data API builder vypočítá agregovanou hodnotu.

aggregations { <alias>: <function>(field: <field>, having: { <operator>: <value> }) }

distinct

Počítá jedinečné hodnoty při použití s count.

aggregations { <alias>: count(field: <field>, distinct: true) }

Tvůrce rozhraní Data API generuje pro tyto členy typy GraphQL specifické pro jednotlivé entity, včetně typů řádků skupin, seskupených typů polí, typů pro výběr agregací, výčtů polí a vstupních typů having. Tyto vygenerované typy nemusíte v dotazu pojmenovat.

Samples

Následující ukázky ukazují tabulku SQL, dotaz GraphQL, výsledný sql a výsledný výstup pro běžné agregační vzory.

Agregace všech řádků v tabulce

Tento vzor použijte, pokud chcete mít jeden souhrnný řádek pro celou entitu.

Tabulka SQL

CREATE TABLE dbo.Books (
    id INT NOT NULL PRIMARY KEY,
    title NVARCHAR(200) NOT NULL,
    [year] INT NOT NULL,
    pages INT NOT NULL
);

INSERT INTO dbo.Books (id, title, [year], pages) VALUES
    (1, N'GraphQL Basics', 2023, 120),
    (2, N'Advanced APIs', 2023, 450),
    (3, N'Data Patterns', 2023, 390),
    (4, N'Cloud APIs', 2024, 140),
    (5, N'Runtime Internals', 2024, 510),
    (6, N'Query Tuning', 2024, 250);
id title rok stránky
1 Základy GraphQL 2023 120
2 Pokročilá rozhraní API 2023 450
3 Vzory dat 2023 390
4 Cloudová rozhraní API 2024 140
5 Interní informace o modulu runtime 2024 510
6 Ladění dotazů 2024 250

Dotaz GraphQL

{
  books {
    groupBy {
      aggregations {
        totalPages: sum(field: pages)
        averagePages: avg(field: pages)
        shortestBook: min(field: pages)
        longestBook: max(field: pages)
        bookCount: count(field: id)
      }
    }
  }
}

Výsledný SQL

SELECT TOP 100
    SUM([table0].[pages]) AS [totalPages],
    AVG([table0].[pages]) AS [averagePages],
    MIN([table0].[pages]) AS [shortestBook],
    MAX([table0].[pages]) AS [longestBook],
    COUNT([table0].[id]) AS [bookCount]
FROM [dbo].[Books] AS [table0]
WHERE 1 = 1
FOR JSON PATH, INCLUDE_NULL_VALUES;

Výsledný výstup

{
  "data": {
    "books": {
      "groupBy": [
        {
          "aggregations": {
            "totalPages": 1860,
            "averagePages": 310,
            "shortestBook": 120,
            "longestBook": 510,
            "bookCount": 6
          }
        }
      ]
    }
  }
}
Celkový počet stránek průměrný počet stránek nejkratší kniha Nejdelší kniha bookCount
1860 310 120 510 6

Seskupení řádků podle jednoho pole

Pomocí groupBy(fields: [...]) vrátíte jeden agregovaný řádek pro každou hodnotu pole. Názvy polí jsou hodnoty výčtu GraphQL, nikoli řetězce.

Tabulka SQL

CREATE TABLE dbo.Books (
  id INT NOT NULL PRIMARY KEY,
  title NVARCHAR(200) NOT NULL,
  [year] INT NOT NULL,
  pages INT NOT NULL
);

INSERT INTO dbo.Books (id, title, [year], pages) VALUES
  (1, N'GraphQL Basics', 2023, 120),
  (2, N'Advanced APIs', 2023, 450),
  (3, N'Data Patterns', 2023, 390),
  (4, N'Cloud APIs', 2024, 140),
  (5, N'Runtime Internals', 2024, 510),
  (6, N'Query Tuning', 2024, 250);
id title rok stránky
1 Základy GraphQL 2023 120
2 Pokročilá rozhraní API 2023 450
3 Vzory dat 2023 390
4 Cloudová rozhraní API 2024 140
5 Interní informace o modulu runtime 2024 510
6 Ladění dotazů 2024 250

Dotaz GraphQL

{
  books(orderBy: { year: ASC }) {
    groupBy(fields: [year]) {
      fields { year }
      aggregations {
        totalPages: sum(field: pages)
        averagePages: avg(field: pages)
      }
    }
  }
}

Výsledný příkaz SQL

SELECT TOP 100
    [table0].[year] AS [year],
    SUM([table0].[pages]) AS [totalPages],
    AVG([table0].[pages]) AS [averagePages]
FROM [dbo].[Books] AS [table0]
WHERE 1 = 1
GROUP BY [table0].[year]
ORDER BY [table0].[year] ASC
FOR JSON PATH, INCLUDE_NULL_VALUES;

Výsledný výstup

{
  "data": {
    "books": {
      "groupBy": [
        {
          "fields": {
            "year": 2023
          },
          "aggregations": {
            "totalPages": 960,
            "averagePages": 320
          }
        },
        {
          "fields": {
            "year": 2024
          },
          "aggregations": {
            "totalPages": 900,
            "averagePages": 300
          }
        }
      ]
    }
  }
}
rok celkový počet stran průměrný počet stránek
2023 960 320
2024 900 300

Seskupit řádky v zobrazení

Agregace také funguje pro entity založené na zobrazení. Nakonfigurujte pole klíče pro zobrazení, aby ho tvůrce rozhraní Data API mohl vystavit jako entitu.

Zobrazení SQL

CREATE TABLE dbo.Employees (
  id INT NOT NULL PRIMARY KEY,
  name NVARCHAR(100) NOT NULL,
  department NVARCHAR(50) NOT NULL,
  title NVARCHAR(100) NOT NULL,
  age INT NOT NULL
);

INSERT INTO dbo.Employees (id, name, department, title, age) VALUES
  (1, N'Ada', N'Engineering', N'Developer', 29),
  (2, N'Ben', N'Engineering', N'Architect', 41),
  (3, N'Cora', N'Sales', N'Account manager', 34),
  (4, N'Diego', N'Sales', N'Sales lead', 52),
  (5, N'Ema', N'Support', N'Support engineer', 25),
  (6, N'Finn', N'Support', N'Support lead', 38),
  (7, N'Gia', N'Engineering', N'Engineering manager', 45);

CREATE VIEW dbo.EmployeeAgeReport
AS
SELECT id, department, age
FROM dbo.Employees;
id oddělení věk
1 Inženýrství 29
2 Inženýrství 41
3 Sales 34
4 Sales 52
5 Support 25
6 Support 38
7 Inženýrství 45

Nakonfigurujte zobrazení s id jako klíčovým polem:

dab add EmployeeAgeReport --source dbo.EmployeeAgeReport --source.type view --source.key-fields id --permissions "anonymous:read"

Dotaz GraphQL

{
  employeeAgeReports(orderBy: { department: ASC }) {
    groupBy(fields: [department]) {
      fields { department }
      aggregations {
        youngest: min(field: age)
        oldest: max(field: age)
        employeeCount: count(field: id)
      }
    }
  }
}

Výsledný dotaz SQL

SELECT TOP 100
  [table0].[department] AS [department],
  MIN([table0].[age]) AS [youngest],
  MAX([table0].[age]) AS [oldest],
  COUNT([table0].[id]) AS [employeeCount]
FROM [dbo].[EmployeeAgeReport] AS [table0]
WHERE 1 = 1
GROUP BY [table0].[department]
ORDER BY [table0].[department] ASC
FOR JSON PATH, INCLUDE_NULL_VALUES;

Výsledný výstup

{
  "data": {
    "employeeAgeReports": {
      "groupBy": [
        {
          "fields": {
            "department": "Engineering"
          },
          "aggregations": {
            "youngest": 29,
            "oldest": 45,
            "employeeCount": 3
          }
        },
        {
          "fields": {
            "department": "Sales"
          },
          "aggregations": {
            "youngest": 34,
            "oldest": 52,
            "employeeCount": 2
          }
        },
        {
          "fields": {
            "department": "Support"
          },
          "aggregations": {
            "youngest": 25,
            "oldest": 38,
            "employeeCount": 2
          }
        }
      ]
    }
  }
}
oddělení Nejmladší nejstarší počet zaměstnanců
Inženýrství 29 45 3
Sales 34 52 2
Support 25 38 2

Filtrování řádků před agregací

Pomocí filter u dotazu na kolekci můžete omezit zdrojové řádky předtím, než je Data API builder seskupí a agreguje.

Zobrazení SQL

CREATE TABLE dbo.Employees (
  id INT NOT NULL PRIMARY KEY,
  name NVARCHAR(100) NOT NULL,
  department NVARCHAR(50) NOT NULL,
  title NVARCHAR(100) NOT NULL,
  age INT NOT NULL
);

INSERT INTO dbo.Employees (id, name, department, title, age) VALUES
  (1, N'Ada', N'Engineering', N'Developer', 29),
  (2, N'Ben', N'Engineering', N'Architect', 41),
  (3, N'Cora', N'Sales', N'Account manager', 34),
  (4, N'Diego', N'Sales', N'Sales lead', 52),
  (5, N'Ema', N'Support', N'Support engineer', 25),
  (6, N'Finn', N'Support', N'Support lead', 38),
  (7, N'Gia', N'Engineering', N'Engineering manager', 45);

CREATE VIEW dbo.EmployeeAgeReport
AS
SELECT id, department, age
FROM dbo.Employees;
id oddělení věk
1 Inženýrství 29
2 Inženýrství 41
3 Sales 34
4 Sales 52
5 Support 25
6 Support 38
7 Inženýrství 45

Dotaz GraphQL

{
  employeeAgeReports(filter: { age: { gt: 30 } }, orderBy: { department: ASC }) {
    groupBy(fields: [department]) {
      fields { department }
      aggregations {
        youngest: min(field: age)
        oldest: max(field: age)
        employeeCount: count(field: id)
      }
    }
  }
}

Výsledný příkaz SQL

SELECT TOP 100
  [table0].[department] AS [department],
  MIN([table0].[age]) AS [youngest],
  MAX([table0].[age]) AS [oldest],
  COUNT([table0].[id]) AS [employeeCount]
FROM [dbo].[EmployeeAgeReport] AS [table0]
WHERE [table0].[age] > @param1
GROUP BY [table0].[department]
ORDER BY [table0].[department] ASC
FOR JSON PATH, INCLUDE_NULL_VALUES;

Pro tento dotaz platí, že @param1 je 30.

Výsledný výstup

{
  "data": {
    "employeeAgeReports": {
      "groupBy": [
        {
          "fields": {
            "department": "Engineering"
          },
          "aggregations": {
            "youngest": 41,
            "oldest": 45,
            "employeeCount": 2
          }
        },
        {
          "fields": {
            "department": "Sales"
          },
          "aggregations": {
            "youngest": 34,
            "oldest": 52,
            "employeeCount": 2
          }
        },
        {
          "fields": {
            "department": "Support"
          },
          "aggregations": {
            "youngest": 38,
            "oldest": 38,
            "employeeCount": 1
          }
        }
      ]
    }
  }
}
oddělení nejmladší Nejstarší počet zaměstnanců
Inženýrství 41 45 2
Sales 34 52 2
Support 38 38 1

Filtrování skupin pomocí having

Používá se having u agregační funkce k filtrování skupin po agregaci. Tento vzor se mapuje na klauzuli SQL HAVING .

Tabulka SQL

CREATE TABLE dbo.Products (
    id INT NOT NULL PRIMARY KEY,
    category NVARCHAR(50) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

INSERT INTO dbo.Products (id, category, price) VALUES
    (1, N'Electronics', 5000.00),
    (2, N'Electronics', 10000.00),
    (3, N'Furniture', 4000.00),
    (4, N'Furniture', 8000.00),
    (5, N'Books', 100.00),
    (6, N'Books', 200.00);
id kategorie cena
1 Elektronika 5000.00
2 Elektronika 10000.00
3 Nábytek 4000.00
4 Nábytek 8000.00
5 Knihy 100.00
6 Knihy 200.00

Dotaz GraphQL

{
  products(orderBy: { category: ASC }) {
    groupBy(fields: [category]) {
      fields { category }
      aggregations {
        totalValue: sum(field: price, having: { gt: 10000 })
        averagePrice: avg(field: price)
      }
    }
  }
}

Výsledný dotaz SQL

SELECT TOP 100
    [table0].[category] AS [category],
    SUM([table0].[price]) AS [totalValue],
    AVG([table0].[price]) AS [averagePrice]
FROM [dbo].[Products] AS [table0]
WHERE 1 = 1
GROUP BY [table0].[category]
HAVING SUM([table0].[price]) > @param1
ORDER BY [table0].[category] ASC
FOR JSON PATH, INCLUDE_NULL_VALUES;

Pro tento dotaz je @param110000.

Výsledný výstup

{
  "data": {
    "products": {
      "groupBy": [
        {
          "fields": {
            "category": "Electronics"
          },
          "aggregations": {
            "totalValue": 15000,
            "averagePrice": 7500
          }
        },
        {
          "fields": {
            "category": "Furniture"
          },
          "aggregations": {
            "totalValue": 12000,
            "averagePrice": 6000
          }
        }
      ]
    }
  }
}
kategorie celková hodnota průměrná cena
Elektronika 15000 7500
Nábytek 12000 6000

Počet jedinečných hodnot

Použijte distinct: true spolu s count k počítání unikátních hodnot v každé skupině.

Tabulka SQL

CREATE TABLE dbo.Orders (
    id INT NOT NULL PRIMARY KEY,
    customer_id INT NOT NULL,
    product_id INT NOT NULL
);

INSERT INTO dbo.Orders (id, customer_id, product_id) VALUES
    (1, 101, 1),
    (2, 101, 2),
    (3, 101, 2),
    (4, 101, 3),
    (5, 101, 4),
    (6, 101, 5),
    (7, 102, 1),
    (8, 102, 1),
    (9, 102, 2),
    (10, 102, 3);
id customer_id product_id
1 101 1
2 101 2
3 101 2
4 101 3
5 101 4
6 101 5
7 102 1
8 102 1
9 102 2
10 102 3

Dotaz GraphQL

{
  orders(orderBy: { customer_id: ASC }) {
    groupBy(fields: [customer_id]) {
      fields { customer_id }
      aggregations {
        uniqueProducts: count(field: product_id, distinct: true)
        totalOrders: count(field: id)
      }
    }
  }
}

Výsledný příkaz SQL

SELECT TOP 100
    [table0].[customer_id] AS [customer_id],
    COUNT(DISTINCT ([table0].[product_id])) AS [uniqueProducts],
    COUNT([table0].[id]) AS [totalOrders]
FROM [dbo].[Orders] AS [table0]
WHERE 1 = 1
GROUP BY [table0].[customer_id]
ORDER BY [table0].[customer_id] ASC
FOR JSON PATH, INCLUDE_NULL_VALUES;

Výsledný výstup

{
  "data": {
    "orders": {
      "groupBy": [
        {
          "fields": {
            "customer_id": 101
          },
          "aggregations": {
            "uniqueProducts": 5,
            "totalOrders": 6
          }
        },
        {
          "fields": {
            "customer_id": 102
          },
          "aggregations": {
            "uniqueProducts": 3,
            "totalOrders": 4
          }
        }
      ]
    }
  }
}
customer_id uniqueProducts Celkový počet objednávek
101 5 6
102 3 4

Běžné chyby

  • Vyberte groupBy v poli kolekce. Nepředávejte groupBy jako argument kolekce.
  • Použít aggregations, ne aggregates.
  • Použít avg, ne average.
  • Použijte hodnoty výčtu polí, například fields: [year]. Názvy polí neuvádějte v uvozovkách.
  • Nevybírejte items a groupBy ve stejném dotazu kolekce.
  • Při seskupení podle polí vyberte pouze stejná pole uvnitř objektu fields .

Související obsah

  • Last updated on