Partager via


MAP_AGG (U-SQL)

Summary

The MAP_AGG aggregator takes a key, value pair and creates a SQL.MAP for all the key/value pairs in the group.

MAP_AGG and EXPLODE are conceptually inverse operations.

The identity value is null.

Syntax

MAP_AGG_Expression :=                                                                                    
     'MAP_AGG' '(' ['DISTINCT'] Key_Expression, Value_Expression ')'.
Key_Expression := expression.
Value_Expression := expression.

Remarks

  • DISTINCT
    Optionally allows to de-duplicate the values returned by the expression inside the group before aggregation.

  • Key_Expression
    The C# expression (including column references) that will define the keys. Its type has to be a type that is acceptable as a SQL.MAP key or an error is raised.

  • Value_Expression
    The C# expression (including column references) that will define the values. Its type has to be a type that is acceptable as a SQL.MAP value or an error is raised.

Return Type

SQL.MAP<K,V> where K is the type of the key expression and V is the type of the value expression.

Usage in Windowing Expression

This aggregator cannot be used in a windowing expression.

Examples

  • The examples can be executed in Visual Studio with the Azure Data Lake Tools plug-in.
  • The scripts can be executed locally. An Azure subscription and Azure Data Lake Analytics account is not needed when executed locally.

MAP_AGG
This example takes a key, value pair (PhoneType and PhoneNumber) and creates a SQL.MAP for all the key/value pairs in the group, EmpName.
This example provides an alternative solution to Using User Defined Aggregator - genericAggregator A.

@employees = 
    SELECT * FROM 
        ( VALUES
        ("Noah",   "cell",   "030-0074321"),
        ("Noah",   "office", "030-0076545"),
        ("Sophia", "cell",   "(5) 555-4729"),
        ("Sophia", "office", "(5) 555-3745"),
        ("Liam",   "cell",   "(5) 555-3932"),
        ("Amy",    "cell",   "(171) 555-7788"),
        ("Amy",    "office", "(171) 555-6750"), 
        ("Amy",    "home",   "(425) 555-6238"),
        ("Justin", "cell",   "0921-12 34 65"),
        ("Justin", "office", "0921-12 34 67"),
        ("Emma",   (string)null, (string)null),
        ("Jacob",  "", ""),
        ("Olivia", "cell",   "88.60.15.31"),
        ("Olivia", "office", "88.60.15.32"),
        ("Mason",  "cell",   "(91) 555 22 82"),
        ("Mason",  "office", "(91) 555 91 99"), 
        ("Mason",  "home",   "(425) 555-2819"),
        ("Ava",    "cell",   "91.24.45.40"),
        ("Ava",    "office", "91.24.45.41"),
        ("Ethan",  "cell",   "(604) 555-4729"),
        ("Ethan",  "office", "(604) 555-3745"),
        ("David",  "cell",   "(171) 555-1212"),
        ("Andrew", "cell",   "(1) 135-5555"),
        ("Andrew", "office", "(1) 135-4892"),
        ("Jennie", "cell",   "(5) 555-3392"),
        ("Jennie", "office", "(5) 555-7293")
        ) AS T(EmpName, PhoneType, PhoneNumber);
        
@result =
    SELECT EmpName,
           String.Join(",", MAP_AGG(PhoneType, PhoneNumber).Select(p => String.Format("{0}:{1}", p.Key, p.Value))).Trim() AS PhoneNumbers
    FROM @employees
    WHERE !string.IsNullOrWhiteSpace(PhoneType)
    GROUP BY EmpName;

OUTPUT @result
TO "/Output/ReferenceGuide/Aggregate/map_agg/exampleA.csv"
USING Outputters.Csv();      

EXPLODE - Bonus Example
This example does not use MAP_AGG; however, it illustrates how to reverse the outcome from the above example using EXPLODE.

@map =
    SELECT EmpName,
           PhoneNumbers == ""? null : new SQL.MAP<string, string>(from p in PhoneNumbers.Split(',') select new KeyValuePair<string, string>(p.Split(':') [0], p.Split(':') [1])) AS PhoneNumberMap
    FROM @result
    WHERE PhoneNumbers != null;

@exploded =
    SELECT EmpName,
           r.key.Trim() AS PhoneType,
           r.value AS PhoneNumber
    FROM @map
    CROSS APPLY
    EXPLODE(PhoneNumberMap) AS r(key, value);

OUTPUT @exploded
TO "/Output/ReferenceGuide/Aggregate/map_agg/exampleB.csv"
USING Outputters.Csv();

MAP_AGG - Additional Example
The query uses MAP_AGG to pivot the sales figures from the @storeSales rowset variable.

@storeSales =
    SELECT * FROM 
        ( VALUES
        (1, "2013", 100),
        (1, "2014", 150),
        (1, "2015", 300),
        (1, "2016", 400),
        (2, "2013", 200),
        (2, "2014", 350),
        (2, "2015", 500),
        (2, "2016", 650),
        (3, "2014", 75),
        (3, "2015", 100),
        (3, "2016", 80)
        ) AS T(StoreID, Year, Sales);

@salesPrePivot =
    SELECT StoreID,
           MAP_AGG(Year, (int?) Sales) AS Data
    FROM @storeSales
    GROUP BY StoreID;

@results =
    SELECT StoreID,
           Data["2013"] AS [2013],
           Data["2014"] AS [2014],
           Data["2015"] AS [2015],
           Data["2016"] AS [2016]
    FROM @salesPrePivot;

OUTPUT @results
TO "/Output/ReferenceGuide/Aggregate/map_agg/exampleC.csv"
USING Outputters.Csv();

PIVOT and MAP_AGG
The examples below repro the first two examples from Using PIVOT and UNPIVOT in SQL Server. To execute the example with the same data set, simply bcp or copy and paste DaysToManufacture and StandardCost from AdventureWorks2014.Production.Product to a local text file. The same data set appears in at least AdventureWorks2016. An alternative solution using PIVOT can be viewed at Basic PIVOT Example.

@products =
     EXTRACT DaysToManufacture  int,
             StandardCost       double
     FROM "/Samples/Data/Product.txt"
     USING Extractors.Tsv();

// standard non-pivoted aggregate result
@results = 
    SELECT DaysToManufacture, AVG(StandardCost) AS AverageCost
    FROM   @products
    GROUP BY DaysToManufacture;

OUTPUT @results
TO "/Output/ReferenceGuide/Aggregate/map_agg/pivot/exampleA1.csv"
USING Outputters.Csv();


// create key, value pair
@prePivot =
    SELECT "AverageCost" AS Cost_Sorted_By_Production_Days,
           MAP_AGG(DaysToManufacture, (double?)AverageCost) AS Data
    FROM @results;

// pivot SQL.MAP results
@resultsPivot =
    SELECT Cost_Sorted_By_Production_Days,
           Data[0] AS [0],
           Data[1] AS [1],
           Data[2] AS [2],
           Data[3] AS [3],
           Data[4] AS [4]
    FROM @prePivot;

OUTPUT @resultsPivot
TO "/Output/ReferenceGuide/Aggregate/map_agg/pivot/exampleA2.csv"
USING Outputters.Csv(outputHeader: true);

PIVOT and MAP_AGG - Additional Example
The example below repros the third example, "Complex PIVOT Example", from Using PIVOT and UNPIVOT. To execute the example with the same data set, simply bcp or copy and paste PurchaseOrderID, EmployeeID, and VendorID from AdventureWorks2014.Purchasing.PurchaseOrderHeader to a local text file. The same data set appears in at least AdventureWorks2016. An alternative solution using PIVOT can be viewed at Complex PIVOT Example.

@purchaseOrder =
     EXTRACT PurchaseOrderID int, 
                EmployeeID int, 
                VendorID   int
     FROM "/Samples/Data/PurchaseOrderHeader.txt"
     USING Extractors.Tsv();

// aggregate data
@results = 
    SELECT EmployeeID, VendorID, COUNT(PurchaseOrderID) AS PurchaseOrderID
    FROM @purchaseOrder
    WHERE EmployeeID IN (250, 251, 256, 257, 260)
    GROUP BY EmployeeID, VendorID;

// create key, value pair
@prePivot =
    SELECT VendorID,
           MAP_AGG(EmployeeID, (int?)PurchaseOrderID) AS Data
    FROM @results
    GROUP BY VendorID;

// pivot SQL.MAP results
@resultsPivot =
    SELECT VendorID,
           Data[250] AS Emp1,
           Data[251] AS Emp2,
           Data[256] AS Emp3,
           Data[257] AS Emp4,
           Data[260] AS Emp5
    FROM @prePivot;

OUTPUT @resultsPivot
TO "/Output/ReferenceGuide/Aggregate/map_agg/pivot/exampleB1.csv"
USING Outputters.Csv(outputHeader: true);

See Also