Chapter 7 – Aggregation
by Leona Coffing, Tom Coffing
Tera-Tom Genius Series - Kognitio Architecture and SQL
- Cover
- The Tera-Tom Video Series
- Current Books in the Tera-Tom Genius Series
- Current Books in the Tera-Tom Genius Series
- Our Recommended Book in The Tera-Tom Genius Series
- Tera-Tom- Author of over 75 Books
- The Best Query Tool Works on all Systems
- Trademarks and Copyrights
- About Tom Coffing
- About Leona Coffing
- Contents
- Chapter 1 – Introduction to the Kognitio Architecture
- Chapter 2 – Kognitio Table Structures
- Kognitio has Three Table Distribution Options
- A Table that is distributed via a Round Robin Technique
- Round Robin Technique is the Default
- Random Distribution
- A Table that is distributed by Hash
- Tables that join are excellent candidates for Hashed Tables
- Hash Distribution
- A Table that is distributed by Hash by Multiple Columns
- The Reasons for a Multi-Column HASHED Distribution Key
- Creating a Table that is replicated across all Nodes
- Replicated Distribution
- The Concept is all about the Joins
- Kognitio allows you to create Images
- Creating a Table Image to place a Table in Memory
- Partitioning an Image
- Partitioning an Image View
- CREATE OR REPLACE TABLE IMAGE
- DEFRAG TABLE IMAGE
- Chapter 3 - Nexus
- Nexus is Available on the Cloud
- Nexus Queries Every Major System
- How to Use Nexus
- Why is Nexus Special? Visualization and Automatic SQL
- Why is Nexus Special? Cross-System Joins
- Why is Nexus Special? The Amazing Hub System
- Why is Nexus Special? Save Answer Sets as Tables
- Why is Nexus Special? Automated Data Movement
- Why is Nexus Special? Nexus makes the Servers Talk Directly
- What Makes Nexus Special? The Garden of Analysis
- The Garden of Analysis Grouping Sets Tab
- The Garden of Analysis - Grouping Sets Answer Sets
- The Garden of Analysis – Join Tab (1 of 4)
- The Garden of Analysis – Join Tab (2 of 4)
- The Garden of Analysis – Join Tab (3 of 4)
- The Garden of Analysis – Join Tab (4 of 4)
- The Garden of Analysis – Charts/Graphs Tab (1 of 4)
- The Garden of Analysis – Charts/Graphs Tab (2 of 4)
- The Garden of Analysis – Charts/Graphs Tab (3 of 4)
- The Garden of Analysis – Charts/Graphs Tab (4 of 4)
- The Garden of Analysis – Dynamic Charts Tab (1 of 4)
- The Garden of Analysis – Dynamic Charts Tab (2 of 4)
- The Garden of Analysis – Dynamic Charts Tab (3 of 4)
- The Garden of Analysis – Dynamic Charts Tab (4 of 4)
- The Garden of Analysis – Dashboard Tab (1 of 5)
- The Garden of Analysis – Dynamic Charts Tab (2 of 5)
- The Garden of Analysis – Dynamic Charts Tab (3 of 5)
- The Garden of Analysis – Dynamic Charts Tab (4 of 5)
- The Garden of Analysis – Dynamic Charts Tab (5 of 5)
- Getting to the Super Join Builder
- The Super Join Builder is the First Entry in the Menu
- The Super Join Builder Shows Tables Visually
- Using the Add Join Button
- What to Do When No Tables are Joinable?
- Drag a Joinable Object into the Super Join Builder
- You Will See the Add Custom Join Window
- Defining the Join Columns
- Your Tables Will Appear Together
- Select the Columns You Want on the Report
- Check out the SQL Tab to See the SQL that has been built
- SQL Tab
- Hit Execute to get the Report inside the Super Join Builder
- The Report is delivered inside the Super Join Builder
- Let's Join Two Tables Again (1 of 6)
- Let's Join Two Tables Again (2 of 6)
- Let's Join Two Tables Again (3 of 6)
- Let's Join Two Tables Again (4 of 6)
- Let's Join Two Tables Again (5 of 6)
- Let's Join Two Tables Again (6 of 6)
- The Tabs of the Super Join Builder Philosophy – One Query
- The Tabs of the Super Join Builder – Objects Tab
- The Tabs of the Super Join Builder – Columns Tab)
- The Tabs of the Super Join Builder – Sorting Tab
- The Tabs of the Super Join Builder – Joins Tab
- The Tabs of the Super Join Builder – SQL Tab
- The Tabs of the Super Join Builder – Metadata Tab
- The Tabs of the Super Join Builder – Analytics Tab
- The Tabs of the SJB – Analytics Tab – OLAP Screen
- Getting a Simple CSUM in the Analytics Tab – OLAP
- Getting a Simple CSUM – The SQL Automatically Generated
- The Answer Set of the CSUM
- Getting all of the OLAP functions in the Analytics Tab
- A Five Table Join Using the Menu
- The First Table is placed in the Super Join Builder
- Using the Add Join Cascading Menu
- All Five Tables Are In the Super Join Builder
- A Five Table Join Two Steps (Cube)
- Choose Cube with Columns from the Left Top of the Table
- All Tables are Cubed (Joined Together Instantly)
- Choose Cube and then Choose Your Columns
- Create Cube - Tables Are Joined Without Columns Selected
- Create Cube – Select the Columns You Want on the Report
- How to join Kognitio, Oracle and SQL Server Tables
- The Kognitio Table is now in the Super Join Builder
- Drag the Joining Oracle Table to the Super Join Builder
- Defining the Join Columns
- Choose the Columns You Want on Your Report
- Let's Add a SQL Server Table to our Teradata and Oracle Join
- Defining the Join Columns
- All Three Tables are now in the Super Join Builder
- Change the Hub and Run the Join on Oracle
- Change the Hub and Run the Join on SQL Server
- Simply Amazing - Change the Hub to the Garden of Analysis
- Have the Answer Set Saved Automatically to Any System
- Saving the Answer Set to an Oracle or SQL Server System
- Saving the Answer Set to a Kognitio System
- Saving the Answer Set to a Teradata System
- Chapter 4 – The Basics of SQL
- Introduction
- Setting the Default Schema
- 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 Last in Ascending Mode (Default)
- NULL Values sort First 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 5 – The WHERE Clause
- The WHERE Clause limits Returning Rows
- 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
- Comparisons against a Null Value
- NULL means Unknown Data so Equal (=) won’t return rows
- 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
- The IN List Can Use Character Data
- Using a NOT IN List
- Null Values in a NOT IN List Bring Back No Rows
- A Technique for Handling Nulls with a NOT IN List
- BETWEEN is Inclusive
- NOT BETWEEN is Also Inclusive
- LIKE uses Wildcards Percent ‘%’ and Underscore ‘_’
- LIKE command Underscore is Wildcard for one Character
- The ilike Command
- LIKE Command Works Differently on Char Vs Varchar
- Troubleshooting LIKE Command on Character Data
- Introducing the TRIM Command
- Introducing the RTRIM 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
- Escape Character in the LIKE Command changes Wildcards
- Escape Characters Turn off Wildcards in the LIKE Command
- Quiz – Turn off that Wildcard
- ANSWER – To Find that Wildcard
- Using ILIKE with an AND Clause to Find Multiple Letters
- Using ILIKE with an OR Clause to Find Either Letters
- Keywords
- Chapter 6 – Distinct, Group By and TOP
- Chapter 7 – Aggregation
- Quiz – You calculate the Answer Set in your own Mind
- Answer – You calculate the Answer Set in your own Mind
- 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
- Keyword HAVING tests Aggregates after they are totaled
- Getting the Average Values per Column
- Chapter 8 – 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
- RIGHT OUTER JOIN
- RIGHT OUTER JOIN Example and Results
- FULL OUTER JOIN
- FULL OUTER JOIN Results
- Which Tables are the Left and which Tables are 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?
- Evaluation Order for Outer Queries
- 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 9 – Date Functions
- Current_Date
- Current_Date and Current_Time
- Current_Date and Current_Timestamp
- Current_Timestamp with Milliseconds
- Current_Timestamp with Microseconds
- Current_Timestamp and SYSDATE are Synonyms
- The Now Function
- Adding Days, Weeks and Months to a Date
- Add or Subtract Days from a date
- The EXTRACT Command
- EXTRACT from DATES and TIME
- EXTRACT of the Month on Aggregate Queries
- Deriving a Timestamp from a Date and Time
- Formatting Dates and Dollar Amounts
- TO_CHAR Example that is Impressive
- TO_CHAR Example that is Amazing
- TO_CHAR Example to get Seconds since Midnight
- TO_CHAR Example that is ahead of its Time
- TO_DATE
- TO_TIME
- TO_TIMESTAMP
- Using CASE and Extract to reformat Dates
- Using CAST and SUBSTRING to reformat Dates
- Using the DAYOFWEEK and the DECODE Function
- Intervals
- More Interval Examples
- TO_CHAR Details
- TO_CHAR Details Continued
- TO_CHAR, TO_DATE, TO_TIME and TO_TIMESTAMP
- TO_CHAR, TO_DATE, TO_TIME, TO_TIMESTAMP Cont
- TO_CHAR, TO_DATE, TO_TIME, TO_TIMESTAMP Cont
- TO_CHAR, TO_DATE, TO_TIME, TO_TIMESTAMP Cont
- Chapter 10 – 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 of 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 Extreme Subquery
- Answer to Quiz – Write the Extreme 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 Extreme Correlated Subquery
- Answer To Quiz – Write the Extreme 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
- IN is equivalent to =ANY
- Using a Correlated Exists
- How a Correlated Exists matches up
- The Correlated NOT Exists
- Quiz – How many rows come back from this NOT Exists?
- Answer – How many rows come back from this NOT Exists?
- Chapter 11 – OLAP Functions
- The Row_Number Command
- Using a Derived Table and Row_Number
- Finding the First Occurrence
- Finding the Last Occurrence
- Quiz – How did the Row_Number Reset?
- Answer – How did the Row_Number Reset?
- RANK Defaults to Ascending Order
- Getting RANK to Sort in DESC Order
- RANK OVER and PARTITION BY
- RANK and DENSE RANK
- 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)
- The ANSI CSUM – Getting a Sequential Number
- Troubleshooting the ANSI OLAP on a GROUP BY
- Reset with a PARTITION BY Statement
- PARTITION BY only Resets a Single OLAP not ALL of them
- Moving SUM
- 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
- Moving Average with a Moving Window of 3
- 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
- Moving Difference
- Moving Difference using ANSI Syntax with Partition By
- PERCENT_RANK OVER
- PERCENT_RANK OVER with 14 rows in Calculation
- PERCENT_RANK OVER with 21 rows in Calculation
- COUNT OVER for a Sequential Number
- Troubleshooting COUNT OVER
- 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
- Troubleshooting MAX OVER
- The MIN OVER Command
- Troubleshooting MIN OVER
- Finding a Value of a Column in the Next Row with MIN
- Quiz – Fill in the Blank
- Answer – Fill in the Blank
- Ordered Analytics OVER
- CURRENT ROW AND UNBOUNDED FOLLOWING
- Different Windowing Options
- The CSUM for Each Product_Id and the Next Start Date
- How Ntile Works
- Ntile
- Ntile Continued
- Ntile Percentile
- Another Ntile example
- Using Quantiles (Partitions of Four)
- NTILE Using a Value of 10
- NTILE - Tertiles with a PARTITION BY
- FIRST_VALUE
- FIRST_VALUE after Sorting by the Highest Value
- FIRST_VALUE with Partitioning
- LAST_VALUE
- Using LEAD
- Using LEAD With and Offset of 2
- LEAD
- LEAD With Partitioning
- Using LAG
- Using LAG with an Offset of 2
- LAG
- LAG with Partitioning
- SUM (SUM(n))
- Chapter 12 – Temporary Tables
- There are Two Types of Temporary Tables
- CREATING A Derived Table
- Naming the Derived Table
- Aliasing the Column Names in the Derived Table
- Multiple Ways to Alias the Columns in a Derived Table
- CREATING a Derived Table using the WITH Command
- The Same Derived Query shown Three Different Ways
- Most Derived Tables Are Used To Join To Other Tables
- The Three Components of a Derived Table
- Visualize This Derived Table
- A Derived Table and CAST Statements
- A Derived example Using the WITH Syntax
- Quiz - Answer the Questions
- Answer to Quiz - Answer the Questions
- Clever Tricks on Aliasing Columns in a Derived Table
- An example of Two Derived Tables in a Single Query
- MULTIPLE Derived Tables using the WITH Command
- Finding the First Occurrence
- Finding the Last Occurrence
- Three Steps to Creating a Temporary Table
- Two Versions of Creating a Temporary Table
- ON COMMIT DELETE ROWS is the Kognitio Default
- ON COMMIT DELETE ROWS
- Important Temporary Table Information
- How to Use the ON COMMIT DELETE ROWS Option
- Create Table AS
- Creating a Temporary Table Using a CTAS that Joins Multiple Tables
- Create Table LIKE
- Chapter 13 –Strings
- The LENGTH Command Counts Characters
- The LENGTH Command – Spaces can Count too
- The LENGTH Command Counts Trailing Spaces
- The LENGTH Command and TRIM
- UPPER and LOWER Commands
- Using the LOWER Command
- Using the UPPER Command
- Non-Letters are Unaffected by UPPER and LOWER
- The CHARACTERS Command Counts Characters
- The CHARACTERS Command and Character Data
- The CHARACTERS and TRIM Commands
- LENGTH, CHARACTER_LENGTH and OCTET_LENGTH
- The TRIM Command trims both Leading and Trailing Spaces
- How to TRIM only the Trailing Spaces
- Concatenation
- A Visual of the TRIM Command Using Concatenation
- Trim and Trailing is Case Sensitive
- How to TRIM Trailing Letters
- The SUBSTRING Command
- SUBSTRING and SUBSTR are equal, but use different syntax
- 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
- An example using SUBSTRING, TRIM and CHAR Together
- The POSITION Command finds a Letters Position
- Concatenation
- Concatenation and SUBSTRING
- Four Concatenations Together
- Troubleshooting Concatenation
- Chapter 14 – Interrogating the Data
- Quiz – What would the Answer be?
- Answer to Quiz – What would the Answer be?
- The NULLIF Command
- Quiz – Fill in the Answers for the NULLIF Command
- Answer– Fill in the Answers for the NULLIF Command
- The COALESCE Command – Fill In the Answers
- The COALESCE Answer Set
- The COALESCE Command
- The COALESCE Answer Set
- The COALESCE Quiz
- Answer - The COALESCE Quiz
- The Basics of CAST (Convert and Store)
- A CAST (Convert and Store) example
- Quiz - The Basics of the CASE Statements
- Answer to Quiz - The Basics of the CASE Statements
- Using an ELSE in the Case Statement
- Using an ELSE as a Safety Net
- Rules for a Valued Case Statement
- Rules for a Searched Case Statement
- Valued Case Vs. A Searched Case
- Quiz - Valued Case Statement
- Answer - Valued Case Statement
- Quiz - Searched Case Statement
- Answer - Searched Case Statement
- Answer - When an ELSE is present in CASE Statement
- The CASE Challenge
- The CASE Challenge Answer
- Combining Searched Case and Valued Case
- A Trick for getting a Horizontal Case
- Put a CASE in the ORDER BY
- Nested Case
- Chapter 15 – Set Operators Functions
- Rules of Set Operators
- INTERSECT Explained Logically
- INTERSECT Explained Logically
- UNION Explained Logically
- UNION Explained Logically
- UNION ALL Explained Logically
- UNION ALL Explained Logically
- EXCEPT/MINUS Explained Logically
- EXCEPT Explained Logically
- 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
- Great Trick: Place your Set Operator in a Derived Table
- UNION Vs UNION ALL
- Using UNION ALL and Literals
- A Great example of how EXCEPT works
- Quiz – Build that Query
- Answer To Quiz – Build that Query
- USING Multiple SET Operators in a Single Request
- Changing the Order of Precedence with Parentheses
- Using UNION ALL for speed in Merging Data Sets
- Chapter 16 – View Functions
- The Fundamentals of Views
- Creating a Simple View to Restrict Sensitive Columns
- Creating a Simple View to Restrict Rows
- Basic Rules for Views
- Exception to the ORDER BY Rule inside a View
- Views sometimes CREATED for Formatting
- Creating a View to Join Tables Together
- Another Way to Alias Columns in a View CREATE
- The Standard Way Most Aliasing is done
- What Happens When Both Aliasing Options Are Present
- Resolving Aliasing Problems in a View CREATE
- Answer to Resolving Aliasing Problems in a View CREATE
- Aggregates on View Aggregates
- Chapter 17 – Table Create and Data Types
- Kognitio Has Three Table Distribution Options
- A Table that is distributed via a Round Robin Technique
- Round Robin Technique is the Default
- A Table that is distributed by Hash
- Tables that join are excellent candidates for Hashed Tables
- A Table that is distributed by Hash by Multiple Columns
- The Reasons for a Multi-Column HASHED Distribution Key
- Creating a Table that is replicated across all Nodes
- The Concept is all about the Joins
- Creating a Table with Primary Key
- Creating a Table with a UNIQUE constraint
- How to create tables with Referential Integrity
- Not Null Constraints
- Creating a Table with Default Values
- Creating a Table with a CHECK Constraint
- Creating a Global Temporary Table
- Important Temporary Table Information
- Creating a Table Image to place a Table in Memory
- CREATE OR REPLACE TABLE IMAGE
- DEFRAG TABLE IMAGE
- Not Null Constraints
- Unique Constraints
- Primary Key Constraints
- Check Constraints
- Create Table AS WITH DATA or WITH NO DATA
- Another Version of Create Table AS
- CREATE Table FOR and FROM
- Create Table LIKE
- String Data Types
- Numeric Data Types
- Date, Time and Timestamp Data Types
- Chapter 18 – Data Manipulation Language (DML)
- INSERT Syntax # 1
- INSERT example with Syntax 1
- INSERT Syntax # 2
- INSERT example with Syntax 2
- INSERT example with Syntax 3
- INSERT/SELECT Command
- INSERT/SELECT example using All Columns (*)
- INSERT/SELECT example with Less Columns
- Two UPDATE Examples
- Subquery UPDATE Command Syntax
- Example of Subquery UPDATE Command
- Join UPDATE Command Syntax
- Example of an UPDATE Join Command
- DELETE and TRUNCATE Examples
- To DELETE or to TRUNCATE
- Subquery and Join DELETE Command Syntax
- Example of Subquery DELETE Command
- Chapter 19 – Kognitio Explain
- How to See an EXPLAIN Plan
- Seeing an EXPLAIN Plan with Nexus
- The Eight Rules to Reading an EXPLAIN Plan
- Interpreting Keywords in an EXPLAIN Plan
- Interpreting an EXPLAIN Plan
- A Single Segment Retrieve – The Fastest Query
- EXPLAIN With an ORDER BY Statement
- EXPLAIN ANALYZE
- EXPLAIN With a Range Query on a Table Partitioned By Day
- EXPLAIN That Uses a B-Tree Index Scan
- EXPLAIN That Uses a Bitmap Scan
- EXPLAIN With a Simple Subquery
- EXPLAIN With a Columnar Query
- EXPLAIN With a Clustered Index
- The Most Important Concept for Joins is the Distribution Key
- EXPLAIN With Join that has to Move Data
- EXPLAIN With Join that has to Move Data
- Changing the Join Query Changes the EXPLAIN Plan
- Analyzing the Tables Structures For a 3-Table Join
- An EXPLAIN For a 3-Table Join
- Explain of a Derived Table vs. a Correlated Subquery
- Explain of the Correlated Subquery
- Explain of the Derived Table
- Chapter 20 – Statistical Aggregate Functions
- The Stats Table
- Numeric Manipulation Functions
- Ceiling Gets the Smallest Integer Not Smaller Than X
- Floor Finds the Largest Integer Not Greater Than X
- The Round Function and Precision
- The STDDEV_POP Function
- A STDDEV_POP Example
- The STDDEV_SAMP Function
- A STDDEV_SAMP Example
- The VAR_POP Function
- A VAR_POP Example
- The VAR_SAMP Function
- A VAR_SAMP Example
- The CORR Function
- A CORR Example
- Another CORR Example so you can compare
- The COVAR_POP Function
- A COVAR_POP Example
- Another COVAR_POP Example so you can compare
- The COVAR_SAMP Function
- A COVAR_SAMP Example
- Another COVAR_SAMP Example so you can compare
- The REGR_INTERCEPT Function
- A REGR_INTERCEPT Example
- Another REGR_INTERCEPT Example so you can compare
- The REGR_SLOPE Function
- A REGR_SLOPE Example
- Another REGR_SLOPE Example so you can compare
- The REGR_AVGX Function
- A REGR_AVGX Example
- Another REGR_AVGX Example so you can compare
- The REGR_AVGY Function
- A REGR_AVGY Example
- Another COVAR_POP Example so you can compare
- The REGR_COUNT Function
- A REGR_COUNT Example
- The REGR_R2 Function
- A REGR_R2 Example
- The REGR_SXX Function
- A REGR_SXX Example
- The REGR_SXY Function
- A REGR_SXY Example
- The REGR_SYY Function
- A REGR_SYY Example
- Using GROUP BY
Chapter 7 – Aggregation
“Kognitio climbed Aggregate Mountain and delivered a better way to Sum It.”
- Tera-Tom Coffing
Quiz – You calculate the Answer Set in your own Mind
SELECT Avg(Grade_Pt) AS "AVG"
,Count(Grade_Pt) AS "Count"
,Count(*) AS "Count *"
FROM Student_Table
WHERE Class_Code IS NULL
AVG Count Count *
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(Grade_Pt) AS "AVG"
,Count(Grade_Pt) AS "Count"
,Count(*) AS "Count *"
FROM Student_Table
WHERE Class_Code IS NULL
Here are your answers!
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
The “Group By Dept_No” command allows 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
New Answer Set using the HAVING Statement
The HAVING Clause only works on Aggregate Totals, and in the above example, only Count (*) > 2 can return.
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.
Getting the Average Values per Column
The first query retrieved the average rows per value for the column Product_ID. The example below did the same, but for the column Sale_Date.
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.