"You can make more friends in two months by becoming interested in other people, than you will in two years by trying to get other people interested in you."
- Dale Carnegie
Derived Table
Exists only within a query
Materialized by a SELECT Statement inside a query
Space comes from the User’s Spool space
Deleted when the query ends
Temporary Table
Created by the User and materialized with an INSERT/SELECT
Space comes from the User’s space
Table and Data are deleted only after a User Logs off the session
The two types of Temporary tables are derived and temporary. You might be asking, "Where are the Global and Local table options for temporary tables"? Global and local temporary tables do not exist in Kognitio. The Kognitio Database will accept the GLOBAL and LOCAL keywords in a temporary table declaration, but they have no effect. They are used for compatibility reasons.
•Exists only within a query
•Materialized by a SELECT Statement inside a query
•Space comes from the User’s space
•Deleted when the query ends
The SELECT Statement that creates and populates the Derived table is always inside Parentheses.
In the example above, TeraTom is the name we gave the Derived Table. It is mandatory that you always name the table or it errors.
AVGSAL is the name we gave to the column in our Derived Table that we call TeraTom. Our SELECT (which builds the columns) shows we are only going to have one column in our derived table, and we have named that column AVGSAL.
AVGSAL is the name we gave to the column in our Derived Table that we call TeraTom. Our SELECT (which builds the columns) shows we are only going to have one column in our derived table. We have aliased the column AVGSAL, but we did so inside the SELECT statement.
When using the WITH Command, we can CREATE our Derived table before running the main query. There will always be two SELECT statements. The first SELECT materializes the derived table and the second SELECT is required to query the derived table.
The first five columns in the Answer Set came from the Employee_Table. AVGSAL came from the derived table named TeraTom. This is how derived tables are normally used. They join with other tables.
A derived table will always have a SELECT query to materialize the derived table with data. The SELECT query always starts with an open parenthesis and ends with a close parenthesis. |
|
The derived table must be given a name. Above we called our derived table TeraTom. |
|
You will need to define (alias) the columns in the derived table. Above we allowed Dept_No to default to Dept_No, but we had to specifically alias AVG(Salary) as AVGSAL. |
Every derived table must have the three components listed above.
Our example above shows the data in the derived table named TeraTom. This query allows us to see each employee and the plus or minus avg of their salary compared to the other workers in their department.
SELECT E.Dept_No, E.Last_Name, E.Salary
,CAST(AVGSAL as Decimal (8,2)) as AVGSAL
,CAST(Salary - AVGSAL as Decimal (8,2)) as PlusMinAvg
FROM Employee_Table as E INNER JOIN
(SELECT Dept_No, AVG(salary) as AVGSAL
FROM Employee_Table
GROUP BY Dept_No)AS TeraTom
ON E.Dept_No = TeraTom.Dept_No
ORDER BY E.Dept_No ;
Our example above shows the data in the derived table named TeraTom. We have used CAST statements to better format the data in our SELECT list. This query shows employees, the AVG (Salary) by dept_no and compares salary to the average.
Most derived tables involve calculations, aggregations or ordered analytics. This allows tables and derived columns to mix well on the final report. Above, we are finding all employees who make a salary that is greater than the average salary within their own department. We created a derived table that holds all departments and the average salary within the department. We then join the derived table (named TeraTom) to the employee_table where we can check the salary vs. the avg (salary).
SELECT Dept_No, First_Name, Last_Name, AVGSAL
FROM Employee_Table
INNER JOIN
(SELECT Dept_No, AVG(Salary)
FROMEmployee_Table
GROUP BY Dept_No) as TeraTom (Depty, AVGSAL)
ON Dept_No = Depty ;
1) What is the name of the derived table? __________
2) How many columns are in the derived table? _______
3) What is the name of the derived table columns? ______
4) Is there more than one row in the derived table? _______
5) What common keys join the Employee and Derived? _______
6) Why were the join keys named differently? ______________
SELECT Dept_No, First_Name, Last_Name, AVGSAL
FROM Employee_Table
INNER JOIN
(SELECT Dept_No, AVG(Salary)
FROMEmployee_Table
GROUP BY Dept_No) as TeraTom (Depty, AVGSAL)
ON Dept_No = Depty ;
1) What is the name of the derived table? TeraTom
2) How many columns are in the derived table? 2
3) What’s the name of the derived columns? Depty and AVGSAL
4) Is their more than one row in the derived table? Yes
5) What keys join the tables? Dept_No and Depty
6) Why were the join keys named differently? If both were named Dept_No, we would error unless we full qualified.
WITH T (Dept_No, AVGSAL) AS
(SELECT Dept_No, AVG(Salary) FROM Employee_Table
GROUP BY Dept_No)
SELECT T.Dept_No, First_Name, Last_Name,
AVGSAL, Counter
FROM Employee_Table as E
INNER JOIN
T
ON E.Dept_No = T.Dept_No
INNER JOIN
(SELECT Employee_No, SUM(1) OVER(PARTITION BY Dept_No
ORDER BY Dept_No, Last_Name Rows Unbounded Preceding)
FROM Employee_Table) as S (Employee_No, Counter)
ON E.Employee_No = S.Employee_No
ORDER BY T.Dept_No;
We have two derived tables in our example. One is used in a WITH statement and the other is a derived table within the query itself.
Using the WITH Command, we can CREATE multiple Derived tables that can be referenced elsewhere in the query.
Using the Row_Number ordered analytic and by partitioning of Product_ID and the sorting by Sale_Date ASC we are bringing back only the first occurrence of a row based on the earliest Sale_Date. This can be done because we are placing our query in a derived table and then selecting from that derived table using a WHERE clause.
Using the Row_Number ordered analytic and by partitioning of Product_ID and the sorting by Sale_Date DESC we are bringing back only the first occurrence of a row based on the latest Sale_Date. This can be done because we are placing our query in a derived table and then selecting from that derived table using a WHERE clause.
Query the table all session long
When you use the phrase ON COMMIT PRESERVE ROWS the data will stay in the table all session long. The normal ANSI default is ON COMMIT DELETE ROWS, which will delete the rows after a single transaction.
CREATE Temporary TABLE Dept_Agg1
( Dept_no Integer
,Sum_SalaryDecimal(10,2)
) ON COMMIT PRESERVE ROWS ;
CREATE Temporary TABLE Dept_Agg2
( Dept_no Integer
,Sum_SalaryDecimal(10,2)
) ON COMMIT DELETE ROWS ;
I will explain how to use these different techniques in the next few pages.
CREATE Temporary TABLE Dept_Agg5
( Dept_no Integer
,Sum_SalaryDecimal(10,2)
) ON COMMIT PRESERVE ROWS ;
ANSI defaults to ON COMMIT DELETE ROWS. Don't forget you ON COMMIT PRESERVE ROWS or the rows will be deleted after the INSERT/SELECT statement.
CREATE Temporary TABLE Dept_Agg8
( Dept_no Integer
,Sum_SalaryDecimal(10,2)
) ON COMMIT DELETE ROWS ;
INSERT INTO Dept_Agg8
SELECT Dept_No, SUM(Salary)
FROM Employee_Table
GROUP BY 1;
SELECT *
FROM Dept_Agg8
Order by 1;
ON COMMIT DELETE ROWS allows the user a single transaction after creating the table before it deletes the contents. After the INSERT/SELECT the table's rows were deleted. This seems stupid at first, but it is actually smart. The next page will show you how to take advantage of this and why it is used.
CREATE Temporary TABLE Dept_Ag1
( Dept_no Integer
,Sum_SalaryDecimal(10,2)
) ON COMMIT PRESERVE ROWS ;
The default value for ON COMMIT is different if you use the word TEMPORARY vs TEMP. CREATE TEMPORARY TABLE deletes the contents when the current transaction completes, whereas CREATE TEMP TABLE preserves them.
CREATE Temporary TABLE Dept_Agg7
( Dept_no Integer
,Sum_SalaryDecimal(10,2)
) ON COMMIT DELETE ROWS ;
The ON COMMIT DELETE ROWS option allow you only one transaction after creating the temporary table, but you can embed the INSERT/SELECT and the SELECT to get the report you need inside a SET MODE TRANSACTION statement. This option should be used if you only need the temporary table to produce a single report.
This table is exactly like the Order_Table
CREATE TABLE New_Order
AS SELECT *
FROM Order_Table
This table uses only some columns
CREATE TABLE New_Employee
AS SELECT First_Name
,Last_Name
,Salary
FROM Employee_Table
This table is a temporary table
CREATE Temporary TABLE temp_order
AS SELECT *
FROM Order_Table
ON COMMIT PRESERVE ROWS
Above are some great example to quickly CREATE a Table from another table.
You can create a temporary table using a CTAS (Create Table AS) statement, as in the above example.
This example uses an INSERT/SELECT
CREATE TABLE Sales3 (LIKE Sales_Table);
INSERT INTO Sales3
SELECT *
FROM Sales_Table;
SELECT *
FROM Sales3;
This example creates a temporary table
CREATE Temporary TABLE Sales4 (LIKE Sales_Table) ON COMMIT PRESERVE ROWS ;
INSERT INTO Sales4
SELECT *
FROM Sales_Table;
SELECT *
FROM Sales4;
The example above creates at table using the LIKE statement. It then loads the data with an INSERT/SELECT. You are now ready to query the new table. Notice that you can do the same technique to create a temporary table.