Generation of sophisticated reports with BTEQ
Teradata provides the functionality to generate sophisticated reports using BTEQ.
By using the scripting feature of BTEQ, the complex queries can be stored in files rather
than requiring the user to enter them each time. Below are examples of script files
and the output generated by BTEQ.
Application development with OLAP functions, scripts and macros
1. Introduction of OLAP
Teradata RDBMS provides several functions of OLAP (on line analytical processing) as function extensions to SQL to allow you to engage in more sophisticated data mining of your database, which previously might not be done by using standard SQL. The OLAP functions look similar to SQL aggregate functions, because they can operate on grouped rows, and they have a QUALIFY clause analogous to the HAVING clause of ordinary SQL to eliminate unqualified rows based on the OLAP function value. Unlike aggregate functions, which only return one value for all qualified rows involved, the OLAP functions return a new value for each of the qualifying rows participating in the query. OLAP functions are not allowed in sub-queries. OLAP functions operate on all rows, or grouped row sets defined by GROUP BY clause. All column from the FROM clause could appear in select list, even if they are not mentioned in the GROUP BY clause. Although OLAP functions could not be used with aggregate functions together in the same SELECT table expression, you might combine them with derived tables.
2. OLAP functions in Teradata RDBMS
CSUM
It computes the running (or cumulative) sum for a column value, assuming the rows are sorted by the sorted expression list.
Syntax format: CSUM (value expression, sort expression list)
MAVG
It computes the moving average for a column using the current row and the preceding width-1 rows.
Syntax format: MAVG (value expression, sort expression list)
MDIFF
It computes the difference of a column between the current row column value and the preceding nth row column value.
Syntax format: MDIFF (value expression, width, sort expression list)
MLINREG
It returns a predicated value for a column (value expression) based on a least square regression of the previous width-1 (based on sort expression) column values.
Syntax format: MLINREG (value expression, width, sort expression)
MSUM
It computes the moving sum of a column using the current row and the preceding n-1 rows. This functionality is very similar to MAVG function.
Syntax format: MSUM (value expression, width, sort expression list)
QUANTILE
It computes the quantile scores for the values in a column.
Syntax format: QUANTILE (quantile constant, sort expression list)
RANK
It returns the rank of all the rows in the group by the value of sort expression list, with the same sort expression values receiving the same rank. A rank r implies the existence of exactly r -1 rows with sort expression value preceding it.
Syntax format: RANK (sort expression list)