5.Analytical Function.pdf

data

1. Analytical Functions
After completing this module, you will be able to:
•Enable and Expedite the processing OLAP queries
•Describe the Derivation of Ordered Analytical Functions

2. Analytical functions „
Support common operation in analytical and data mining
environment
„
Includes ANSI SQL-99 compliant window functions as well as
Teradata-specific functions
„
It used in Expression list of SELECT statement

3. Aggregate function Lists „
Window aggregate functions
„
AVG
„
COUNT
„
MAX
„
MIN
„
SUM
„
Rank function
„
RANK
„
Distribution function
„
PERCENT_RANK
„
Row number function
„
ROW_NUMBER
„
Teradata-specific functions
„
CSUM
„
MAVG
„
MDIFF
„
MLINREG
„
MSUM
„
QUANTILE
„
RANK

4. Windows Aggregate Functions Window Feature
ƒ
Provides dynamic subset data definition
ƒ
ANSI SQL-99 Compliant
Window Clauses
Entire partition following the current row UNBOUNDED
FOLLOWING
Entire partition preceding the current row UNBOUNDED
PRECEDING
starting point for the aggregation group within the
Partition.
ROWS
Grouping of rows based on column specified PARTITION BY
arguments that define how values are grouped, ordered. OVER
Description Syntax Element

5. Computation types
ROWS BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING
Group„
ROWS UNBOUNDED PRECEDING
„
ROWS BETWEEN UNBOUNDED PRECEDING AND value
PRECEDING
„
ROWS BETWEEN UNBOUNDED PRECEDING AND
CURRENT ROW
„
ROWS BETWEEN UNBOUNDED PRECEDING AND
value FOLLOWING
Cumulative
Syntax options Computation

6. Computation types contd..
„
ROWS BETWEEN value PRECEDING AND UNBOUNDED
FOLLOWING
„
ROWS BETWEEN CURRENT ROW AND UNBOUNDED
FOLLOWING
„
ROWS BETWEEN value FOLLOWING AND UNBOUNDED
FOLLOWING
Remaining
„
ROWS value PRECEDING
„
ROWS CURRENT ROW
„
ROWS BETWEEN value PRECEDING AND value PRECEDING
„
ROWS BETWEEN value PRECEDING AND CURRENT ROW
„
ROWS BETWEEN value PRECEDING AND value FOLLOWING
„
ROWS BETWEEN CURRENT ROW AND CURRENT ROW
„
ROWS BETWEEN CURRENT ROW AND value FOLLOWING
„
ROWS BETWEEN value FOLLOWING AND value FOLLOWING
Moving
Syntax options Computation

7. Analytical functions
select state, zip_code ,
Balance_Forward,
SUM(Balance_Forward) OVER( PARTITION BY Zip_Code
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING)
FROM
AU.accounts;
•SUM
•Result
State Zip_Code Balance_
F
Grouped Sum(Balance_Forwar
d
CA 90020 5003.8 16950545.29
CA 90020 2582.3 16950545.29
CA 90066 1849.26 14484617.91
CA 90066 8392.2 14484617.91
CA 90066 9948.5 14484617.91

8. Analytical functions
select state, zip_code ,
Balance_Forward,
SUM(Balance_Forward) OVER( PARTITION BY State order by Zip_Code
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING)
FROM
AU.accounts;
•AVG
•Result
State Zip_Code Balance_Forward Moving Avg(Balance_Forward)
CA 90020 18419.5 18420
CA 90020 7571.97 12996
CA 90020 2265.76 9419
CA 90020 2707.29 4182
CA 90020 17572.4 7515
CA 90020 5160.96 8480

9. Analytical functions
select Account_number, Trans_number ,
Trans_amount,
MIN(Trans_Amount) OVER( PARTITION BY Account_number
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING)
FROM
AU.Trans
•MIN
•Result
Account_Number Trans_Number Trans_amount Cumulative Max(Trans_Amount
)
20025236 1235 94.42 94.42
20025236 11235 102 94.42
20025237 1236 -94.5 -94.5
20025237 21455 106 -94.5
20025237 22458 252 -94.5
20025238 1237 -94.57 -94.57
20025239 1238 -94.65 -94.65
20025239 12238 103 -94.65

10. Analytical functions
select Account_number, Trans_number ,
Trans_amount,
MAX(Trans_Amount) OVER( PARTITION BY Account_number
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED
FOLLOWING)
FROM
AU.Trans
•MAX
•Result
Account_Number Trans_Number Trans_amount Cumulative Max(Trans_Amount
)
20025236 1235 94.42 102
20025236 11235 102 102
20025237 1236 -94.5 252
20025237 21455 106 252
20025237 22458 252 252
20025238 1237 -94.57 -94.57
20025239 1238 -94.65 103
20025239 12238 103 103

11. RANK Function
„
Returns the rank (1 … n) of all the rows in th e group by the value of
sort_expression_list, with the same sort_expression values receiving the same
rank.
„
It is Teradata Extension to ANSI
„
Window Aggregate need to use for ANSI SQL-99 select
Account_number,
Trans_Amount,
RANK(Trans_Amount)
from
AU.Trans
select Account_number,
Trans_Amount,
RANK() OVER(ORDER BY
TRANS_AMOUNT DESC)
from AU.Trans
•Teradata Extn.
•ANSI SQL-99
Account_Number Trans_Amount Rank(Trans_Amount)
20033997 2044 1
20033987 2042 2
20033977 2040 3
20033967 2038 4
20033957 2036 5

12. PERCENT RANK Function „
Returns the relative rank of rows for a value_expression.
„
SQL-99 window function.
Select
Account_number,
Trans_Amount,
PERCENT_RANK()
OVER(ORDER BY TRANS_AMOUNT DESC)
from
AU.Trans
Account_Number Trans_Amount Percent Rank(Trans_Amount)
20033997 2044 0.000000
20033987 2042 0.000067
20033977 2040 0.000133
20033967 2038 0.000200
20033957 2036 0.000267

13. ROW NUMBER Function
„
Returns the sequential row number, wher e the first row is number one, of the
row within its window partition according to the window ordering of the window.
„
SQL-99 window function.
Account_Number Trans_Amount Row Number()
20033997 2044 1
20033987 2042 2
20033977 2040 3
20033967 2038 4
20033957 2036 5
select Account_number, Trans_Amount,
ROW_NUMBER() OVER(ORDER BY TRANS_AMOUNT DESC)
from AU.Trans

14. CSUM Function „
Returns the cumulative (or running) sum of a column expression,
assuming the rows are sorted by sort_expression_list.
„
Teradata-specific function.
select
Account_number,
Trans_Amount,
CSUM(Trans_Amount, Account_number desc)
from
au.Trans Account_Number Trans_Amount CSum(Trans_Amount,1)
20034000 -764.45 -764.45
20033999 -764.37 -1528.82
20033999 833 -695.82
20033998 -764.3 -1460.12
20033997 -764.22 -2224.34
20033997 2044 -180.34

15. MAVG Function „
Returns the moving difference between the current row-column value and
the preceding nth row-column value.
„
Teradata-specific function.
select
Account_number,
Trans_Amount,
MAVG(Trans_Amount, 2, Account_number desc)
from
au.Trans Account_Number Trans_Amount mount,2,Account_Number DESC)
20034000 -764.45 -764
20033999 -764.37 -764
20033999 833 34
20033998 -764.3 34
20033997 -764.22 -764
20033997 2044 640

16. MDIFF Function „
Computes the moving average of a column using the current row and the
preceding width-1 rows.
„
Teradata-specific function.
select
Account_number,
Trans_Amount,
MDIFF(Trans_Amount, 1, Account_number desc)
from
au.Trans Account_Number Trans_Amount mount,1,Account_Number DESC)
20034000 -764.45 ?
20033999 -764.37 0.08
20033999 833 1597.37
20033998 -764.3 -1597.30
20033997 -764.22 0.08
20033997 2044 2808.22

17. MSUM Function „
Computes the moving sum of a column using the current row and the
preceding n-1 rows. This function is very similar to the MAVG function.
„
Teradata-specific function.
select
Account_number,
Trans_Amount,
MSUM(Trans_Amount, 1, Account_number desc)
from
au.Trans Account_Number Trans_Amount mount,1,Account_Number DESC)
20034000 -764.45 -764.45
20033999 -764.37 -764.37
20033999 833 833.00
20033998 -764.3 -764.30
20033997 -764.22 -764.22
20033997 2044 2044.00

18. QUANTILE Function „
Computes the moving sum of a column using the current row and the
preceding n-1 rows. This function is very similar to the MAVG function.
„
Teradata-specific function.
select
Account_number,
Trans_Amount,
QUANTILE(1000, Trans_Amount ASC)
from
au.Trans Account_Number Trans_Amount
Q
uantile(1000,Trans_Amount ASC)
20033985 -763.3 999
20033984 -763.23 999
20033983 -763.15 999
20033997 -764.22 999
20033975 -762.54 998
20033982 -763.07 998
20033980 -762.92 998