Chapter 10 - Aggregation
by Ed Bernier, Tom Coffing
Actian Matrix (Formely ParAccel) - Architecture and SQL
- Cover
- The Tera-Tom Video Series
- The Tera-Tom Genius Series
- Tera-Tom- Author of over 50 Books
- The Best Query Tool Works on all Systems
- Trademarks and Copyrights
- About Tom Coffing
- About Ed Bernier
- Contents
- Chapter 1 – What is Columnar?
- What is Parallel Processing?
- The Basics of a Single Computer
- Data in Memory is fast as Lightning
- Parallel Processing Of Data
- A Table has Columns and Rows
- Rows are Placed Inside a Data Block
- Moving Data Blocks is Like Checking In Luggage
- Facts That Are Disturbing
- Why Columnar?
- Row Based Blocks vs. Columnar Based Blocks
- As Row-Based Tables Get Bigger, the Blocks Split
- Data Blocks Are Processed One at a Time Per Unit
- Columnar Tables Store Each Column in Separate Blocks
- Visualize the Data – Rows vs. Columns
- The Architecture of Actian Matrix
- Matrix has Linear Scalability
- Distribution Styles
- Distribution Key Where the Data is Unique
- Another Way to Create a Table
- Distribution Key Where the Data is Non-Unique
- Even Distribution Key
- Matching Distribution Keys for Co-Location of Joins
- Big Table / Small Table Joins
- Fact and Dimension Table Distribution Key Designs
- Improving Performance By Defining a Sort Key
- Sort Keys Help Group By, Order By and Window Functions
- Each Block Comes With Metadata
- How Data Might Look On A Slice
- Question – How Many Blocks Move Into Memory?
- Answer – How Many Blocks Move Into Memory?
- Quiz – Master that Query With the Metadata
- Answer to Quiz – Master that Query With the Metadata
- The ANALYZE Command Collects Statistics
- Matrix Automatically ANALYZES Some Create Statements
- What is a Vacuum?
- When is a Good Time to Vacuum?
- The VACUUM Command Grooms a Table
- The Matrix database catalog also needs periodic vacuuming and indexing
- Database Limits
- Creating a Database
- Creating a User
- Dropping a User
- Inserting Into a Table
- Renaming a Table or a Column
- Adding and Dropping a Column to a Table
- Chapter 2 – Best Practices for Table Design
- Converting Table Structures to Actian Matrix
- Converting Table Structures to Actian Matrix Finale
- Best Practices for Designing Tables
- Choose the Best Sort Key
- Each Block Comes With Metadata
- Creating a Sort Key
- Sort Keys Help Group By, Order By and Window Functions
- Choose a Great Distribution Key
- Distribution Key Where the Data is Unique
- Matching Distribution Keys for Co-Location of Joins
- Big Table / Small Table Joins
- Define Primary Key and Foreign Key Constraints
- Primary Key and Foreign Key Examples
- Use the Smallest Column Size When Creating Tables
- Use Date/Time Data Types for Date Columns
- Specify Redundant Predicates on the Sort Column
- Setting the statement_timeout to Abort Long Queries
- Chapter 3 – Systems Tables
- Actian Matrix System Tables
- Trouble Shooting Catalog Table pg_table_def
- Seeing the System Tables in your Nexus Tree
- Catalog Table pg_table_def
- Checking Tables for Skew (Poor Distribution)
- Checking All Statements That Used the Analyze Command
- Checking Tables for Skew (Poor Distribution)
- Checking for Details about the Last Copy Operation
- Checking When a Table Has Last Been Analyzed
- Checking For Column Information on a Table
- System tables for troubleshooting data loads
- Determining Whether a Query is Writing to Disk
- Chapter 4 – Compression
- Chapter 5 - Temporary Tables
- Create Table Syntax
- Basic Temporary Table Examples
- More Advanced Temporary Table Examples
- Advanced Temporary Table Examples
- Table Limits and CTAS
- Performing a Deep Copy
- Deep Copy Using the Original DDL
- Deep Copy Using A CTAS
- Deep Copy Using A Create Table LIKE
- Deep Copy by Creating a Temp Table and Truncating Original
- CREATING A Derived Table
- The Three Components of a Derived Table
- Naming the Derived Table
- Aliasing the Column Names in the Derived Table
- Visualize This Derived Table
- Most Derived Tables Are Used To Join To Other Tables
- Multiple Ways to Alias the Columns in a Derived Table
- Our Join Example with a Different Column Aliasing Style
- Column Aliasing Can Default for Normal Columns
- CREATING a Derived Table using the WITH Command
- Our Join Example With the WITH Syntax
- WITH Statement That Uses a SELECT *
- A WITH Clause That Produces Two Tables
- The Same Derived Query shown Three Different Ways
- Quiz - Answer the Questions
- Answer to Quiz - Answer the Questions
- Clever Tricks on Aliasing Columns in a Derived Table
- A Derived Table lives only for the lifetime of a single query
- An Example of Two Derived Tables in a Single Query
- Connecting To Matrix Via Nexus
- Connecting To Matrix Via Nexus
- Connecting To Matrix Via Nexus
- Connecting To Matrix Via Nexus
- Chapter 6 – Explain
- Three Ways to Run an EXPLAIN
- EXPLAIN – Steps, Segments and Streams
- EXPLAIN Terms for Scans and Joins
- EXPLAIN Terms for Aggregation and Sorts
- EXPLAIN Terms for Set Operators and Miscellaneous Terms
- EXPLAIN Terms for Set Operators and Miscellaneous Terms
- EXPLAIN Example and the Cost
- EXPLAIN Example and the Rows
- EXPLAIN Example and the Width
- Simple EXPLAIN Example and the Costs
- EXPLAIN Join Example Using DS_BCAST_INNER
- EXPLAIN Join Example Using DS_DIST_NONE
- EXPLAIN Showing DS_DIST_NONE Visually
- EXPLAIN With a Warning
- EXPLAIN for Ordered Analytics Such as CSUM
- EXPLAIN for Scalar Aggregate Functions
- EXPLAIN for HashAggregate Functions
- EXPLAIN Using Limit, Merge and Sort
- EXPLAIN Using a WHERE Clause Filter
- EXPLAIN Using the Keyword Distinct
- EXPLAIN for Subqueries
- Chapter 7 - Basic SQL Functions
- Finding the Current Schema on the Leader Node
- Getting Things Setup in Your Search Path
- Five Details You Need To Know About the Search_Path
- Introduction
- SELECT * (All Columns) in a Table
- SELECT Specific Columns in a Table
- Commas in the Front or Back?
- Place your Commas in front for better Debugging Capabilities
- Sort the Data with the ORDER BY Keyword
- ORDER BY Defaults to Ascending
- Use the Name or the Number in your ORDER BY Statement
- Two Examples of ORDER BY using Different Techniques
- Changing the ORDER BY to Descending Order
- NULL Values sort First in Ascending Mode (Default)
- NULL Values sort Last in Descending Mode (DESC)
- Major Sort vs. Minor Sorts
- Multiple Sort Keys using Names vs. Numbers
- Sorts are Alphabetical, NOT Logical
- Using A CASE Statement to Sort Logically
- How to ALIAS a Column Name
- A Missing Comma can by Mistake become an Alias
- Comments using Double Dashes are Single Line Comments
- Comments for Multi-Lines
- Comments for Multi-Lines as Double Dashes Per Line
- A Great Technique for Comments to Look for SQL Errors
- Chapter 8 – The WHERE Clause
- Using Limit to bring back a Sample
- Using Limit with an Order By Statement
- The WHERE Clause limits Returning Rows
- Using a Column ALIAS throughout the SQL
- Double Quoted Aliases are for Reserved Words and Spaces
- Character Data needs Single Quotes in the WHERE Clause
- Character Data needs Single Quotes, but Numbers Don’t
- NULL means UNKNOWN DATA so Equal (=) won’t Work
- Use IS NULL or IS NOT NULL when dealing with NULLs
- NULL is UNKNOWN DATA so NOT Equal won’t Work
- Use IS NULL or IS NOT NULL when dealing with NULLs
- Using Greater Than or Equal To (>=)
- AND in the WHERE Clause
- Troubleshooting AND
- OR in the WHERE Clause
- Troubleshooting Or
- Troubleshooting Character Data
- Using Different Columns in an AND Statement
- Quiz – How many rows will return?
- Answer to Quiz – How many rows will return?
- What is the Order of Precedence?
- Using Parentheses to change the Order of Precedence
- Using an IN List in place of OR
- The IN List is an Excellent Technique
- IN List vs. OR brings the same Results
- Using a NOT IN List
- A Technique for Handling Nulls with a NOT IN List
- Another Technique for Handling Nulls with a NOT IN List
- BETWEEN is Inclusive
- NOT BETWEEN is Also Inclusive
- LIKE command Underscore is Wildcard for one Character
- LIKE Command Works Differently on Char Vs Varchar
- The Ilike Command Is NOT Case Sensitive
- Troubleshooting LIKE Command on Character Data
- Introducing the TRIM Command
- Quiz – What Data is Left Justified and what is Right?
- Numbers are Right Justified and Character Data is Left
- Answer – What Data is Left Justified and what is Right?
- An Example of Data with Left and Right Justification
- A Visual of CHARACTER Data vs. VARCHAR Data
- Use the TRIM command to remove spaces on CHAR Data
- Like and Your Escape Character of Choice
- Like and the Default Escape Character
- Similar To Operators
- Similar To Operators
- Similar To Example with Lower Case Letters
- Similar To Example with Lower and Upper Case Letters
- Similar To Example with Multiple Occurrences
- Multiple Occurrences Must Be Consecutive
- Chapter 9 – Distinct Vs Group By AND TOP
- Chapter 10 - Aggregation
- Quiz – You calculate the Answer Set in your own Mind
- Answer – You calculate the Answer Set in your own Mind
- The 3 Rules of Aggregation
- There are Five Aggregates
- Quiz – How many rows come back?
- Answer – How many rows come back?
- Troubleshooting Aggregates
- GROUP BY when Aggregates and Normal Columns Mix
- GROUP BY delivers one row per Group
- GROUP BY Dept_No or GROUP BY 1 the same thing
- Limiting Rows and Improving Performance with WHERE
- WHERE Clause in Aggregation limits unneeded Calculations
- Keyword HAVING tests Aggregates after they are Totaled
- Keyword HAVING is like an Extra WHERE Clause for Totals
- Chapter 11 – Join Functions
- A Two-Table Join Using Traditional Syntax
- A two-table join using Non-ANSI Syntax with Table Alias
- You Can Fully Qualify All Columns
- A two-table join using ANSI Syntax
- Both Queries have the same Results and Performance
- Quiz – Can You Finish the Join Syntax?
- Answer to Quiz – Can You Finish the Join Syntax?
- Quiz – Can You Find the Error?
- Answer to Quiz – Can You Find the Error?
- Super Quiz – Can You Find the Difficult Error?
- Answer to Super Quiz – Can You Find the Difficult Error?
- Quiz – Which rows from both tables won’t Return?
- Answer to Quiz – Which rows from both tables Won’t Return?
- LEFT OUTER JOIN
- LEFT OUTER JOIN Results
- Left Outer Joins Compatible with Oracle
- RIGHT OUTER JOIN
- RIGHT OUTER JOIN Example and Results
- Right Outer Joins Compatible with Oracle
- FULL OUTER JOIN
- FULL OUTER JOIN Results
- Which Tables are the Left and which are the Right?
- Answer - Which Tables are the Left and which are the Right?
- INNER JOIN with Additional AND Clause
- ANSI INNER JOIN with Additional AND Clause
- ANSI INNER JOIN with Additional WHERE Clause
- OUTER JOIN with Additional WHERE Clause
- OUTER JOIN with Additional AND Clause
- OUTER JOIN with Additional AND Clause Results
- Quiz – Why is this considered an INNER JOIN?
- The DREADED Product Join
- The DREADED Product Join Results
- The Horrifying Cartesian Product Join
- The ANSI Cartesian Join will ERROR
- Quiz – Do these Joins Return the Same Answer Set?
- Answer – Do these Joins Return the Same Answer Set?
- The CROSS JOIN
- The CROSS JOIN Answer Set
- The Self Join
- The Self Join with ANSI Syntax
- Quiz – Will both queries bring back the same Answer Set?
- Answer – Will both queries bring back the same Answer Set?
- Quiz – Will both queries bring back the same Answer Set?
- Answer – Will both queries bring back the same Answer Set?
- How would you Join these two tables?
- An Associative Table is a Bridge that Joins Two Tables
- Quiz – Can you Write the 3-Table Join?
- Answer to Quiz – Can you Write the 3-Table Join?
- Quiz – Can you Write the 3-Table Join to ANSI Syntax?
- Answer – Can you Write the 3-Table Join to ANSI Syntax?
- Quiz – Can you Place the ON Clauses at the End?
- Answer – Can you Place the ON Clauses at the End?
- The 5-Table Join – Logical Insurance Model
- Quiz - Write a Five Table Join Using ANSI Syntax
- Answer - Write a Five Table Join Using ANSI Syntax
- Quiz - Write a Five Table Join Using Non-ANSI Syntax
- Answer - Write a Five Table Join Using Non-ANSI Syntax
- Quiz –Re-Write this putting the ON clauses at the END
- Answer –Re-Write this putting the ON clauses at the END
- Chapter 12 – Date Functions
- Current_Date
- TIMEOFDAY()
- SYSDATE Returns a Timestamp with Microseconds
- GETDATE Returns a Timestamp without Microseconds
- Add or Subtract Days from a date
- The ADD_MONTHS Command Returns a Timestamp
- The ADD_MONTHS Command with Trunc Removes Time
- ADD_MONTHS Command to Add 1-Year or 5-Years
- Dateadd Function and Add_Months Function are Different
- The EXTRACT Command
- EXTRACT from DATES and TIME
- EXTRACT with DATE and TIME Literals
- EXTRACT of the Month on Aggregate Queries
- The Datediff command
- The Datediff Function on Column Data
- The Date_Part Function Using a Date
- The Date_Part Function Using a Time
- Date_Part Abbreviations
- The to_char command
- Conversion Functions
- Conversion Function Templates
- Conversion Function Templates Continued
- Formatting a Date
- A Summary of Math Operations on Dates
- Using a Math Operation to find your Age in Years
- Date Related Functions
- A Side Title example with Reserved Words as an Alias
- Implied Extract of Day, Month and Year
- DATE_PART Function
- DATE_PART Function using an ALIAS
- DATE_TRUNC Function
- DATE_TRUNC Function using TIME
- MONTHS_BETWEEN Function
- MONTHS_BETWEEN Function in Action
- ANSI TIME
- ANSI TIMESTAMP
- Matrix TIMESTAMP Function
- Matrix TO_TIMESTAMP Function
- Matrix NOW() Function
- Matrix TIMEOFDAY Function
- Matrix AGE Function
- Time Zones
- Setting Time Zones
- Using Time Zones
- Intervals for Date, Time and Timestamp
- Using Intervals
- Troubleshooting the Basics of a Simple Interval
- Interval Arithmetic Results
- A Date Interval Example
- A Time Interval Example
- A DATE Interval Example
- A Complex Time Interval Example using CAST
- A Complex Time Interval Example using CAST
- The OVERLAPS Command
- An OVERLAPS Example that Returns No Rows
- The OVERLAPS Command using TIME
- The OVERLAPS Command using a NULL Value
- Chapter 13 – OLAP Functions
- CSUM
- CSUM – The Sort Explained
- CSUM – Rows Unbounded Preceding Explained
- CSUM – Making Sense of the Data
- CSUM – Making Even More Sense of the Data
- CSUM – The Major and Minor Sort Key(s)
- Reset with a PARTITION BY Statement
- PARTITION BY only Resets a Single OLAP not ALL of them
- ANSI Moving Window is Current Row and Preceding n Rows
- How ANSI Moving SUM Handles the Sort
- Quiz – How is that Total Calculated?
- Answer to Quiz – How is that Total Calculated?
- Moving SUM every 3-rows Vs a Continuous Average
- Partition by Resets an ANSI OLAP
- Moving Average
- The Moving Window is Current Row and Preceding
- How Moving Average Handles the Sort
- Quiz – How is that Total Calculated?
- Answer to Quiz – How is that Total Calculated?
- Quiz – How is that 4th Row Calculated?
- Answer to Quiz – How is that 4th Row Calculated?
- Moving Average every 3-rows Vs a Continuous Average
- Partition By Resets an ANSI OLAP
- RANK Defaults to Ascending Order
- Getting RANK to Sort in DESC Order
- RANK() OVER and PARTITION BY
- RANK() OVER And LIMIT
- PERCENT_RANK() OVER
- PERCENT_RANK() OVER with 14 rows in Calculation
- PERCENT_RANK() OVER with 21 rows in Calculation
- Quiz – What Causes the Product_ID to Reset?
- Answer to Quiz – What Cause the Product_ID to Reset?
- COUNT OVER for a Sequential Number
- Quiz – What caused the COUNT OVER to Reset?
- Answer to Quiz – What caused the COUNT OVER to Reset?
- The MAX OVER Command
- MAX OVER with PARTITION BY Reset
- The MIN OVER Command
- Quiz – Fill in the Blank
- Answer – Fill in the Blank
- The Row_Number Command
- Quiz – How did the Row_Number Reset?
- Quiz – How did the Row_Number Reset?
- Standard Deviation Functions Using STDDEV / OVER
- Standard Deviation Functions and STDDEV / OVER Syntax
- STDDEV / OVER Example
- VARIANCE / OVER Syntax
- Variance Functions Using VARIANCE / OVER
- Using VARIANCE with PARTITION BY Example
- Using FIRST_VALUE and LAST_VALUE
- Using FIRST_VALUE
- Using LAST_VALUE
- Using LAG and LEAD
- Using LEAD
- Using LEAD With and Offset of 2
- Using LAG
- Using LAG with an Offset of 2
- Chapter 14 – Temporary Tables
- CREATING A Derived Table
- The Three Components of a Derived Table
- Naming the Derived Table
- Aliasing the Column Names in The Derived Table
- Visualize This Derived Table
- Most Derived Tables Are Used To Join To Other Tables
- Multiple Ways to Alias the Columns in a Derived Table
- Our Join Example with a Different Column Aliasing Style
- Column Aliasing Can Default for Normal Columns
- CREATING a Derived Table using the WITH Command
- Our Join Example With the WITH Syntax
- WITH
- A WITH Clause That Produces Two Tables
- The Same Derived Query shown Three Different Ways
- Quiz - Answer the Questions
- Answer to Quiz - Answer the Questions
- Clever Tricks on Aliasing Columns in a Derived Table
- A Derived Table lives only for the lifetime of a single query
- An Example of Two Derived Tables in a Single Query
- Create Table Syntax
- Basic Temporary Table Examples
- More Advanced Temporary Table Examples
- Advanced Temporary Table Examples
- Performing a Deep Copy
- Deep Copy Using the Original DDL
- Deep Copy Using A CTAS
- Deep Copy Using A Create Table LIKE
- Deep Copy by Creating a Temp Table and Truncating Original
- Chapter 15 – Sub-query Functions
- An IN List is much like a Subquery
- An IN List Never has Duplicates – Just like a Subquery
- An IN List Ignores Duplicates
- The Subquery
- The Three Steps of How a Basic Subquery Works
- These are Equivalent Queries
- The Final Answer Set from the Subquery
- Quiz- Answer the Difficult Question
- Answer to Quiz- Answer the Difficult Question
- Should you use a Subquery or a Join?
- Quiz- Write the Subquery
- Answer to Quiz- Write the Subquery
- Quiz- Write the More Difficult Subquery
- Answer to Quiz- Write the More Difficult Subquery
- Quiz- Write the Subquery with an Aggregate
- Answer to Quiz- Write the Subquery with an Aggregate
- Quiz- Write the Correlated Subquery
- Answer to Quiz- Write the Correlated Subquery
- The Basics of a Correlated Subquery
- The Top Query always runs first in a Correlated Subquery
- Correlated Subquery Example vs. a Join with a Derived Table
- Quiz- A Second Chance to Write a Correlated Subquery
- Answer - A Second Chance to Write a Correlated Subquery
- Quiz- A Third Chance to Write a Correlated Subquery
- Answer - A Third Chance to Write a Correlated Subquery
- Quiz- Last Chance to Write a Correlated Subquery
- Answer – Last Chance to Write a Correlated Subquery
- Quiz- Write the NOT Subquery
- Answer to Quiz- Write the NOT Subquery
- Quiz- Write the Subquery using a WHERE Clause
- Answer - Write the Subquery using a WHERE Clause
- Quiz- Write the Subquery with Two Parameters
- Answer to Quiz- Write the Subquery with Two Parameters
- How the Double Parameter Subquery Works
- More on how the Double Parameter Subquery Works
- Quiz – Write the Triple Subquery
- Answer to Quiz – Write the Triple Subquery
- Quiz – How many rows return on a NOT IN with a NULL?
- Answer – How many rows return on a NOT IN with a NULL?
- How to handle a NOT IN with Potential NULL Values
- Using a Correlated Exists
- How a Correlated Exists matches up
- The Correlated NOT Exists
- The Correlated NOT Exists Answer Set
- Quiz – How many rows come back from this NOT Exists?
- Answer – How many rows come back from this NOT Exists?
- Chapter 16 – Substrings and Positioning Functions
- The TRIM Command trims both Leading and Trailing Spaces
- A Visual of the TRIM Command Using Concatenation
- Trim and Trailing is Case Sensitive
- How to TRIM Trailing Letters
- The SUBSTRING Command
- How SUBSTRING Works with NO ENDING POSITION
- Using SUBSTRING to move Backwards
- How SUBSTRING Works with a Starting Position of -1
- How SUBSTRING Works with an Ending Position of 0
- The POSITION Command finds a Letters Position
- Quiz – Find that SUBSTRING Starting Position
- Answer to Quiz – Find that SUBSTRING Starting Position
- Using the SUBSTRING to Find the Second Word On
- Quiz – Why did only one Row Return?
- Answer to Quiz – Why Did only one Row Return?
- Concatenation
- Concatenation and SUBSTRING
- Four Concatenations Together
- Troubleshooting Concatenation
- Declaring a Cursor
- Chapter 17 – Interrogating the Data
- Quiz – What would the Answer be?
- Answer to Quiz – What would the Answer be?
- The NULLIF Command
- Quiz – Fill in the Blank Values in the Answer Set
- Answer to Quiz – Fill in the Blank Values in the Answer Set
- Quiz – Fill in the Answers for the NULLIF Command
- Quiz – Fill in the Answers for the NULLIF Command
- Quiz – Fill in the Answers for the NULLIF Command
- Quiz – Fill in the Answers for the NULLIF Command
- The ISNULL, NVL and COALESCE Commands
- The ISNULL, NVL and COALESCE Commands
- The ISNULL, NVL and COALESCE more examples
- The COALESCE Answer Set
- The Coalesce Quiz
- Answer – The Coalesce Quiz
- The Basics of CAST (Convert And STore)
- Some Great CAST (Convert And STore) Examples
- Some Great CAST (Convert And STore) Examples
- Some Great CAST (Convert And STore) Examples
- The Basics of the CASE Statements
- The Basics of the CASE Statement
- Valued Case Vs. A Searched Case
- Quiz - Valued Case Statement
- Answer - Valued Case Statement
- Quiz - Searched CASE Statement
- Answer - Searched CASE Statement
- Quiz - When NO ELSE is present in CASE Statement
- Answer - When NO ELSE is present in CASE Statement
- When an ELSE is present in CASE Statement
- Answer - When an ELSE is present in CASE Statement
- When an Alias is NOT used in a CASE Statement
- Answer - When an Alias is NOT used in a CASE Statement
- Combining Searched Case and Valued Case
- Nested Case
- Put a CASE in the ORDER BY
- Chapter 18 – View Functions
- Creating a Simple View to Restrict Sensitive Columns
- Creating a Simple View to Restrict Rows
- Creating a View to Join Tables Together
- You Select From a View
- Basic Rules for Views
- An ORDER BY Example Inside of a View
- An ORDER BY Inside of a View that is Queried Differently
- Creating a View with Ordered Analytics
- Creating a View with the TOP Command
- Creating a View with the LIMIT Command
- Altering a Table
- Altering a Table after a View has been Created
- A View that Errors after An ALTER
- Troubleshooting a View
- Updating Data in a Table through a View
- Chapter 19 – Set Operators Functions
- Rules of Set Operators
- INTERSECT Explained Logically
- INTERSECT Explained Logically
- UNION Explained Logically
- UNION Explained Logically
- UNION ALL Explained Logically
- UNION Explained Logically
- EXCEPT Explained Logically
- EXCEPT Explained Logically
- Minus Explained Logically
- Minus Explained Logically
- Testing Your Knowledge
- Answer - Testing Your Knowledge
- Testing Your Knowledge
- Answer - Testing Your Knowledge
- An Equal Amount of Columns in both SELECT List
- Columns in the SELECT list should be from the same Domain
- The Top Query handles all Aliases
- The Bottom Query does the ORDER BY (a Number)
- Great Trick: Place your Set Operator in a Derived Table
- UNION Vs UNION ALL
- A Great Example of how EXCEPT works
- Chapter 20 – Statistical Aggregate Functions
- Chapter 21 – Nexus
- Nexus is Now Available on the Microsoft Azure Cloud
- Nexus Queries Every Major System
- Setup of Nexus is as easy as pie
- Setup of Nexus is a Easy as 1, 2, 3
- Nexus Data Visualization
- Nexus Data Visualization
- Nexus Data Visualization Shows What Tables Can Be Joined
- Nexus is doing a Five-Table Join
- Nexus Generates the SQL Automatically
- Nexus Delivers the Report
- Cross-System Joins from Teradata, Oracle and SQL Server
- The Tab of the Super Join Builder
- The 9 Tabs of the Super Join Builder – Objects Tab 1
- Selecting Columns in the Objects Tab
- The 9 Tabs of the Super Join Builder – Columns Tab 2
- Removing Columns from the Report in the Columns Tab
- The 9 Tabs of the Super Join Builder – Sorting Tab 3
- The 9 Tabs of the Super Join Builder – Joins Tab 4
- The 9 Tabs of the Super Join Builder – Where Tab 5
- Using the WHERE Tab For Additional WHERE or AND
- The 9 Tabs of the Super Join Builder – SQL Tab 6
- The 9 Tabs of the Super Join Builder – Answer Set Tab 7
- The 9 Tabs of the Super Join Builder – Analytics Tab 9
- Analytics Tab
- Analytics Tab – OLAP Example
- Analytics Tab – OLAP Example of SQL Generated
- Analytics Tab – Grouping Sets Example
- Analytics Tab – Grouping Sets Answer Set
- Nexus Data Movement
- Moving a Single Table To a Different System
- The Single Table Data Movement Screen
- Moving an Entire Database To a Different System
- The Database Mover Screen
- The Database Mover Options Tab
- Converting DDL Table Structures
- Converting DDL Table Structures
- Converting DDL Table Structures
- Hound Dog Compression
- Hound Dog Compression on Teradata
- Hound Dog Compression on Teradata
Chapter 10 - Aggregation
“Matrix climbed Aggregate Mountain and delivered a better way to Sum It.”
- Tera-Tom Coffing
Quiz – You calculate the Answer Set in your own Mind
Aggregation_Table
Employee_NoSalary
__________________________
423400100000.00
423401100000.00
423402NULL
SELECT AVG(Salary) as "AVG"
‚Count(Salary) as SalCnt
‚Count(*) as RowCnt
FROM Aggregation_Table ;
What would the result set be from the above query? The next slide shows answers!
Answer – You calculate the Answer Set in your own Mind
SELECT AVG(Salary) as "AVG"
‚Count(Salary) as SalCnt
‚Count(*) as RowCnt
FROM Aggregation_Table ;
Here are your answers!
The 3 Rules of Aggregation
1) Aggregates Ignore Null Values.
2) Aggregates WANT to come back in one row.
3) You CAN’T mix Aggregates with normal columns unless you use a GROUP BY.
There are Five Aggregates
The five aggregates are listed above.
Quiz – How many rows come back?
How many rows will the above query produce in the result set?
Answer – How many rows come back?
How many rows will the above query produce in the result set? The answer is one.
Troubleshooting Aggregates
If you have a normal column (non aggregate) in your query, you must have a corresponding GROUP BY statement.
GROUP BY when Aggregates and Normal Columns Mix
If you have a normal column (non aggregate) in your query, you must have a corresponding GROUP BY statement.
GROUP BY delivers one row per Group
Group By Dept_No command allow for the Aggregates to be calculated per Dept_No. The data has also been sorted with the ORDER BY statement.
GROUP BY Dept_No or GROUP BY 1 the same thing
Both queries above produce the same result. The GROUP BY allows you to either name the column or use the number in the SELECT list just like the ORDER BY.
Limiting Rows and Improving Performance with WHERE
Will Dept_No 300 be calculated? Of course you know it will . . . NOT!
WHERE Clause in Aggregation limits unneeded Calculations
The system eliminates reading any other Dept_No’s other than 200 and 400. This means that only Dept_No’s of 200 and 400 will come off the disk to be calculated.
Keyword HAVING tests Aggregates after they are Totaled
The HAVING Clause only works on Aggregate Totals. The WHERE filters rows to be excluded from calculation, but the HAVING filters the Aggregate totals after the calculations, thus eliminating certain Aggregate totals.
Keyword HAVING is like an Extra WHERE Clause for Totals
The HAVING Clause only works on Aggregate Totals, and in the above example, only Count(*) > 2 can return.
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.