Examples Of Analytical functions present in Teradata.

August 24, 2007 in IT

Let’s work out some examples of OLAP functions that are being discussed in the previous articles. Before that make sure you have a table and there is data present init. Follow the below instruction to create a temporary table and insert data into it. While inserting data you might encounter one of the following errors:

3520:  A constant value in a query is not valid for column sale_month.

3535:  A character string failed conversion to a numeric value.

2665:  Invalid date.

All of these errors are due to the date value that you are inserting. If you encounter any of these errors, try permutation and combination of the date value and see if you can get rid of the error. If you still can not then find out from your sys admin what date format in allowed while you are inserting dates. Here in this example the format is ‘yyyy-mm-dd’. All of the below statements are tested in SQL assistant. Create a Volatile Table “Sales” as follows:

CREATE SET VOLATILE TABLE <username>.sales, NO FALLBACK,CHECKSUM = DEFAULT,LOG               (                “store_no” INTEGER,                “sale_month” DATE,                “prod_code” CHAR (20) CHARACTER SET LATIN NOT CASESPECIFIC,                “projected_sale” INTEGER,                “actual_sale” INTEGER                )PRIMARY INDEX ( “store_no” )ON COMMIT PRESERVE ROWS;

Insert data as follows:

INSERT INTO sales VALUES(10,’2007-01-01′,’Apple’,15000,13500);

INSERT INTO sales VALUES(20,’2007-02-01′,’Banana’,10000,11000);

INSERT INTO sales VALUES(30,’2007-03-01′,’Orange’,11000,10500);

INSERT INTO sales VALUES(40,’2007-04-01′,’Apricot’,12500,12500);

INSERT INTO sales VALUES(50,’2007-05-01′,’Grapes’,15000,12500);

INSERT INTO sales VALUES(60,’2007-06-01′,’Apple’,12000,11500);

INSERT INTO sales VALUES(70,’2007-07-01′,’Banana’,15500,11500);

INSERT INTO sales VALUES(80,’2007-07-01′,’Grapes’,15000,15500);

INSERT INTO sales VALUES(90,’2007-06-01′,’Orange’,16000,15500);

INSERT INTO sales VALUES(10,’2007-05-01′,’Apricot’,14500,13500);

INSERT INTO sales VALUES(20,’2007-04-01′,’Mango’,15500,12500);

INSERT INTO sales VALUES(30,’2007-03-01′,’Mango’,15000,12500);

INSERT INTO sales VALUES(40,’2007-02-01′,’Guava’,14000,11500);

INSERT INTO sales VALUES(50,’2007-01-01′,’Guava’,12000,10500);

INSERT INTO sales VALUES(30,’2007-02-01′,’Strawberry’,12000,10000);

Now take the example of AVG () function. As you know AVG () computes the cumulative or moving average of a column, we will calculate something similar in the following scenario.

Scenario is to find out the actual sales in each store, averaged over the current month and the preceding month. Here preceding month would be understood as the previous month to the current month, now what is current month, it is the first row returned after the result set is ordered after partitioning. Following is the query.

SELECT store_no, sale_month, actual_sale, projected_sale,AVG(actual_sale) OVER (PARTITION BY store_noORDER BY projected_saleROWS 1 PRECEDING)FROM sales;

And it would return the following result set.

store_no     sale_month    actual_sale    projected_sale  Moving Avg(actual_sale)10                5/1/2007       13,500          14,500          13,500.00

10                1/1/2007       13,500          15,000          13,500.00

20                2/1/2007       11,000          10,000          11,000.00

20                4/1/2007       12,500          15,500          11,750.00

30                3/1/2007       10,500          11,000          10,500.00

30                2/1/2007       10,000          12,000          10,250.00

30                3/1/2007       12,500          15,000          11,250.00

40                4/1/2007       12,500          12,500          12,500.00

40                2/1/2007       11,500          14,000          12,000.00

50                1/1/2007       10,500          12,000          10,500.00

50                5/1/2007       12,500          15,000          11,500.00

60                6/1/2007       11,500          12,000          11,500.00

70                7/1/2007       11,500          15,500          11,500.00

80                7/1/2007       15,500          15,000          15,500.00

90                6/1/2007       15,500          16,000          15,500.00

Note that the moving average number is changing every 2^nd row in case there is a difference in between the 1^st and 2^nd row figure within the same store number. Particularly for the store number 30, you can see that there is a slight increase in the moving average in spite of good increase in actual sales. Projected Sales is also added just to have an idea how the actual sale is performing over the project sale.

Advertisement