Example - Analytics Database - Teradata Vantage

SQL Functions, Expressions, and Predicates

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-01-12
dita:mapPath
obm1628111499646.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
kby1472250656485
lifecycle
latest
Product Category
Teradata Vantageā„¢

The following example shows how the SQL query to calculate a frequency of gender to marital status would appear using Teradata Warehouse Miner.

SELECT gender, marital_status, xcnt,xpct
   ,CSUM(xcnt, xcnt DESC, gender, marital_status) AS xcum_cnt
   ,CSUM(xpct, xcnt DESC, gender, marital_status) AS xcum_pct
   ,RANK(xcnt DESC, gender ASC, marital_status ASC) AS xrank
FROM  
   (SELECT gender, marital_status, COUNT(*) AS xcnt
      ,100.000 * xcnt / xall (FORMAT 'ZZ9.99') AS xpct
   FROM customer_table A,
      (SELECT COUNT(*) AS xall
      FROM customer_table) B
GROUP BY gender, marital_status, xall
HAVING xpct >= 1) T1
QUALIFY xrank <= 8
ORDER BY xcnt DESC, gender, marital_status

The result for this query looks like the following table.

gender marital_status xcnt xpct xcum_cnt xcum_pct xrank
F Married 3910093 36.71 3910093 36.71 1
M Married 2419511 22.71 6329604 59.42 2
F Divorced 1612130 15.13 7941734 74.55 3
M Divorced 1412624 3.26 9354358 87.81 4
F Single 491224 4.61 9845582 92.42 5
F Widowed 319881 3.01 10165463 95.43 6
M Single 319794 3.00 10485257 98.43 7
M Widowed 197131 1.57 10652388 100.00 8