Correlation Example: Include PARTITION BY - Teradata Vantage

This example uses the PARTITION BY clause to group the data by state. The function calculates the correlations between columns separately for California (CA) and Texas (TX) data.

Input

The input table, corr_input, is sample macroeconomic data for the states of California and Texas over a period of 16 years (1947-1962). The GDP (gross domestic product) numbers are in millions of dollars ($M). GDPdeflator is GDP data normalized to the year 1954 that is, GDP is 100 for 1954). The other columns represent the number of people (in thousands) who were employed, unemployed, or in the armed forces.

SQL Call

The function calculates the correlation between each pair of columns in the TargetColumns syntax element. This example compares GDP to GDPdeflator, the employed population to GDP, the number of people unemployed, and the number of people in the armed forces.

SELECT * FROM CorrelationReduce (
  ON CorrelationMap (
    ON corr_input PARTITION BY state
    USING
    TargetColumns ('[2:3]','employed:gdp','employed:unemployed','employed:armedforces')
    KeyName ('keys')
    GroupByColumns('state')
  ) PARTITION BY keys,state
) AS dt;

Output

Because GDP and GDPdeflator represent the same data but with different scaling, their correlation is 1. The correlation coefficients for all column pairs are shown below.

state corr_item1  corr_item2  value_col
----- ----------- ----------- --------------------
ca    gdpdeflator gdp         1.0
ca    armedforces employed    0.9528257846832275
ca    unemployed  employed    -0.43761759996414185
tx    gdpdeflator gdp         0.9999999403953552
tx    armedforces employed    -0.4519846439361572
tx    unemployed  employed    0.3207692801952362
ca    gdp         employed    0.967695415019989
tx    gdp         employed    0.9127569198608398
ca    gdp         gdp         1.0
tx    gdp         gdp         1.0
ca    gdpdeflator gdpdeflator 1.0
tx    gdpdeflator gdpdeflator 1.0

Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.