Loading lesson...
Aggregate into arrays, maps, and beyond
Aggregate into arrays, maps, and beyond
Topics covered: ARRAY_AGG for collecting values, MAP_AGG for key-value pairs, Aggregate modifiers (ORDER BY), COUNT_IF conditional counting, CASE WHEN in aggregates
ARRAY_AGG Fundamentals Basic Syntax For each customer, this collects all their order IDs into a single array. The result is an array type that can be processed by other functions. Practical Example Consider an orders table: Each customer appears once with all their products collected in an array. The array preserves each individual value. Arrays vs Strings Arrays provide significant advantages over comma-separated strings for storing collected values. Why Arrays Beat Strings Arrays preserve stru
MAP_AGG Fundamentals Basic Syntax Practical Example Consider a product configuration table: Each product now has a single row with all its settings in a map. Downstream code can access settings.color or settings.size directly without parsing. ARRAY_AGG vs MAP_AGG Choose the right aggregation based on your access pattern: Accessing Map Values If duplicate keys exist within a group, the last value wins. Ensure your data has unique keys per group or use deduplication first.
Controlling Order By default, aggregated values have no guaranteed order. Modifiers give you precise control over sequencing. ORDER BY in Aggregates Time-Ordered Arrays The array preserves the sequence with most recent activity first. Downstream code can iterate through these events in order. Removing Duplicates DISTINCT in Aggregates ORDER BY + DISTINCT You can use both modifiers together: This gives you unique categories in alphabetical order for each customer.
Basic COUNT_IF COUNT_IF Syntax Calculating Percentages Conditional counts become even more powerful when converted to percentages. This reveals patterns that raw counts might hide. South has the highest completion rate (76.6%) while East has the lowest (70.8%). Raw counts alone wouldn't reveal this because South has fewer total orders. Multiple Conditions This counts only positive reviews (4+ stars) from verified purchases, giving you a more trustworthy quality metric. COUNT_IF Patterns COUNT_IF
Conditional Aggregation Conditional SUM and AVG NULL Handling Performance Tips Advanced aggregates can be computationally expensive. Understanding their behavior helps you write efficient queries at scale. These guidelines help you write efficient and predictable advanced aggregation queries. These advanced aggregation patterns unlock powerful data transformations that go beyond simple summaries. Put them to the test with hands-on challenges.