Compatibility: Teradata Extension
A moving average incorporates the same window of rows and addition as seen in the MSUM. However, the aspect of the average incorporates a count of all the values involved and then divides the sum by the count to obtain the average.
The Moving Average (MAVG) function provides a moving average on a column's value, based on a defined number of rows also known as the query width. Like the MSUM, the MAVG defaults to ascending order for the sort. So, once you learn the MSUM, the MAVG is easier to learn because of the similarities.
If the number of rows is less than the width defined then the calculation will be based on the rows present.
The syntax for MAVG is:
The MAVG command uses the first parameter as the column containing a numeric value to average.
The second parameter used by the MAVG is the width number. It represents the number of rows included in the summation. Valid values are from 1 to 4096. If the number of rows is less than the width defined then the calculation will be based on the rows present. Although 1 is an acceptable value, it does not make sense to use it. This would mean that every average was the value for that one row.
The third parameter is entered as a major sort key that sequences the output rows. This column will be sorted to determine the sequence of the detail row data along with the MAVG. By default, the sort sequence is ascending (ASC). The DESC can be specified to request a descending (highest to lowest) sequence. Optionally, additional sort keys can be entered to request one or more minor sort sequences.
The following SELECT shows a 5-row (day) moving average for product 1000 from the Sales table using MAVG:
The above output averages 5 rows (5 days) all the way through the end of the returned rows. As the sixth row comes into the average operation, the first row drops out. This continues through the end of the rows.
To make the sequence more refined, the next SELECT uses an additional sort column based on the product number:
Now, the major sequence is the product ID with it being sorted by date within the ID.
Moving Average with Reset Capabilities
The moving sum operation can also be written to provide the equivalence of a sub- average. When the GROUP BY designation is added to the query it indicates which column or columns to monitor for a change to occur. It also indicates the order of the sort to perform. When the data value in the column changes, the GROUP BY causes the accumulation value to be reset back to zero. Hence a sub-average is provided within a moving window.
Using MAVG and GROUP BY
The next SELECT shows a 5-row (day) moving average for products 1000 and 2000 from the Sales table using MAVG with a break on the year and month portion of the sale date in the GROUP BY:
In the above output, the biggest change occurs on October 1, for product 1000. There, the average is the same as the sales for that day when the reset occurred as a result of finishing all rows for September.
Teradata Related Interview Questions
|Microstrategy Interview Questions||Informatica Interview Questions|
|MySQL Interview Questions||Oracle 11g Interview Questions|
|Hadoop Interview Questions||TeraData DBA Interview Questions|
|MYSQL DBA Interview Questions||Database Administration Interview Questions|
|DB2 SQL Programming Interview Questions||Hadoop Administration Interview Questions|
|Java Hadoop Developer Interview Questions||Informatica MDM Interview Questions|
|Informatica Admin Interview Questions||Hadoop Testing Interview Questions|
Teradata Related Practice Tests
|Microstrategy Practice Tests||Informatica Practice Tests|
|MySQL Practice Tests||Oracle 11g Practice Tests|
|Hadoop Practice Tests||TeraData DBA Practice Tests|
|MYSQL DBA Practice Tests||Database Administration Practice Tests|
|DB2 SQL Programming Practice Tests||Hadoop Administration Practice Tests|
Teradata Parallel Architecture
Fundamental Sql Using Select
On-line Help And Show Commands
Date And Time Processing
Character String Processing
Reporting Totals And Subtotals
Data Definition Language
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.