TD_CategoricalSummary

Written by James Armitage

TD_CategoricalSummary analyzes categorical columns and returns the distinct values within each column along with their frequency counts. It provides a quick way to understand the distribution of categorical data across one or more fields.

A categorical summary groups data into discrete categories and reports how many records belong to each category. This is particularly useful for analyzing nominal data such as customer demographics, survey responses, product types, account statuses, or geographic regions.

The function helps users quickly identify:

  • The unique values present in a column

  • The frequency of each value

  • Dominant categories and rare occurrences

  • Potential data quality issues, such as unexpected values or imbalanced distributions

Results can be visualized using tables, bar charts, histograms, or pie charts to support exploratory data analysis and business reporting.

Syntax and link to documentation:

TD_CategoricalSummary (

  ON { table | view | (query) } AS InputTable

  USING

  TargetColumns
  ({ 'target_column' | target_column_range }[,...])

)

TD_CategoricalSummary • Database Engine 20 In-Database Analytic Functions • Teradata Online Documentation | Quick access to technical manuals

1. Create a Sample Banking Dataset

This example creates a simple banking transaction table containing several categorical columns that can be profiled using TD_CategoricalSummary.

-- Create sample banking transaction table

CREATE VOLATILE MULTISET TABLE
banking_categorical_summary_input
(
    account_type VARCHAR(12),
    txn_type     VARCHAR(20),
    channel      VARCHAR(20)
)
PRIMARY INDEX (account_type)
ON COMMIT PRESERVE ROWS;

2. Insert Sample Categorical Data

The dataset contains a mixture of account types, transaction types, and banking channels to demonstrate category frequency analysis.

-- Insert sample banking transactions

INSERT INTO banking_categorical_summary_input
VALUES ('checking', 'CARD_PAYMENT', 'POS');

INSERT INTO banking_categorical_summary_input
VALUES ('checking', 'CARD_PAYMENT', 'ONLINE');

INSERT INTO banking_categorical_summary_input
VALUES ('savings', 'TRANSFER', 'MOBILE');

INSERT INTO banking_categorical_summary_input
VALUES ('checking', 'SALARY', 'BACS');

INSERT INTO banking_categorical_summary_input
VALUES ('savings', 'TRANSFER', 'ONLINE');

INSERT INTO banking_categorical_summary_input
VALUES ('checking', 'CARD_PAYMENT', 'POS');

3. Generate the Categorical Summary

TD_CategoricalSummary examines the selected categorical columns and returns the distinct values along with their occurrence counts.

-- Analyze categorical column distributions

SELECT
    ColumnName,
    CAST(DistinctValue AS VARCHAR(20)) AS DistinctValue,
    DistinctValueCount

FROM TD_CategoricalSummary (
    ON banking_categorical_summary_input AS InputTable

    USING
        TargetColumns(
            'account_type',
            'txn_type',
            'channel'
        )
) AS dt

ORDER BY 1,2,3;

4. Example Output

Column Name Distinct Value Count
account_type checking 4
account_type savings 2
txn_type CARD_PAYMENT 3
channel POS 2

5. Why Use TD_CategoricalSummary?

Benefit Description
Data Profiling Understand the distribution of categorical values.
Feature Selection Identify low-value or redundant columns.
Model Preparation Validate categorical features before training ML models.
Data Quality Checks Detect unexpected values and skewed distributions.

6. Clean Up

-- Remove demo table

DROP TABLE banking_categorical_summary_input;

TD_CategoricalSummary.sql

James Armitage
Next

TD_SimpleImputeTransform