15.00 - Prejoin With Aggregation - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Prejoin With Aggregation

The following example creates a prejoin view with aggregation. Note that you can create a functionally identical object as a join index.

    REPLACE VIEW LargeTableSpaceTotal
     (DBname,Acctname,Tabname,CurrentPermSum,PeakPermSum,   NumVprocs)
    AS SELECT DatabaseName,AccountName,TableName,
    SUM (CurrentPerm)(FORMAT '---,---,---,--9'), 
    SUM (PeakPerm)(FORMAT '---,---,---,--9'),
    COUNT(*)(FORMAT 'ZZ9')
    FROM DBC.TablesizeV
    GROUP BY 1, 2, 3
    HAVING SUM (currentperm) > 10E9;
 
    SELECT DatabaseName (CHAR(10), TITLE 'DbName'),
     AccountName (CHAR(10),TITLE 'AcctName'),
     TableName (CHAR(16),TITLE 'TableName'), Vproc,
     CurrentPerm (FORMAT '---,---,---,--9'), 
     CurrentPerm * 100.0 / CurrentpermSum (AS PctDist, TITLE '  %  //
     Distrib',FORMAT         'ZZ9.999'),PctDist * NumVprocs 
    (AS PctofAvg,TITLE '%         of//AVG ', FORMAT 'ZZ9.9')
    FROM LargeTableSpaceTotal, DBC.TablesizeV
    WHERE DBname   = TablesizeV.DatabaseName
    AND   AcctName = TablesizeV.AccountName
    AND   TabName  = TablesizeV.TableName
    AND   PctofAvg > 125.0
    ORDER BY 1, 2, 3, 4;