Joins and Aggregates on a View - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

Create a report for a set of times and for each destination that includes an average and a maximum value of the count and sums. The purpose of the report is to determine potential loss of revenue by destination.

Create the view loss_summary_view.

     CREATE VIEW loss_summary_view (week, from_code, to_code, count_a,
                                    sum_x, sum_y, sum_z)
     AS SELECT c.week, h.from_code, h.to_code, COUNT(h.a),SUM(h.x),                SUM(h.y), SUM(h.z)
        FROM history AS h, calendar AS c
        WHERE c.month = 100610
        AND   c.day = h.day
        GROUP BY 1, 2, 3 ;

The SELECT request that uses loss_summary_view to create the report looks like this.

     SELECT lsv.week, ld.to_location, AVG(lsv.count_a),             MAX(lsv.count_a),AVG(lsv.sum_x), MAX(lsv.sum_x),             AVG(lsv.sum_y), MAX(lsv.sum_y), AVG(lsv.sum_z),
            MAX(lsv.sum_z)
     FROM  loss_summary_view AS lsv, location_description AS ld
     WHERE lsv.to_code = ld.to_code
     GROUP BY 1, 2;

This example joins the cust_file table with the aggregate view cust_prod_sales to determine which companies purchased more than $10,000 worth of item 123:

     CREATE VIEW cust_prod_sales (custno, pcode, sales)
     AS SELECT custno, pcode, SUM(sales)
        FROM sales_hist
        GROUP BY custno, pcode;

The SELECT request that uses cust_prod_sales to create the report looks like this.

     SELECT company_name, sales
     FROM cust_prod_sales AS a, cust_file AS b
     WHERE a.custno = b.custno
     AND   a.pcode = 123
     AND   a.sales > 10000;