1.1 - 8.10 - TFIDF Example: Tokenized Training Document Set - Teradata Vantage

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Release Date
October 2019
Content Type
Programming Reference
Publication ID
B700-4003-079K
Language
English (United States)

This example uses the NGramSplitter_MLE function to tokenize a training document set, from which it creates the input table for the TFIDF function.

NGramSplitter_MLE Input: tfidf_train

docid content
1 Chennai floods have battered the capital city of Tamil Nadu and its adjoining areas. Normal life came to a standstill when roads were submerged in water and all modes of transport were severely affected. In the past, Chennai has had tsunamis and earthquakes
2 Roger Federer born on 8 August 1981, is a greatest tennis player, who has been continuously ranked inside the top 10 since October 2002 and has won Wimbledon, USOpen, Australian and FrenchOpen titles mutiple times
3 The Federer Nadal rivalry, known by many as Fedal, is between two professional tennis players, Roger Federer of Switzerland and Rafael Nadal of Spain. They are currently engaged in a storied rivalry, which many consider to be the greatest in tennis history. They have played 34 times, most recently in the 2015 Swiss Indoors final, and Nadal leads their eleven-year-old rivalry with an overall record of 23–11
4 The India Pakistan cricket rivalry is one of the most intense sports rivalries in the world. An India-Pakistan cricket match has been estimated to attract up to one billion viewers, according to TV ratings firms and various other reports. The 2011 World Cup semifinal between the two teams attracted around 988 million television viewers
5 An Ashes series is traditionally of five Tests, hosted in turn by England and Australia at least once every four years. As of August 2015, England hold the ashes, having won three of the five Tests in the 2015 Ashes series. Overall, Australia has won 32 series, England 32 and five series have been drawn.

NGramSplitter_MLE SQL Call

This call creates a table of tokenized input, tfidf_token1, from tfidf_train.

CREATE MULTISET TABLE tfidf_token1 AS (
  SELECT * FROM NGramSplitter_MLE (
    ON tfidf_train
    USING
    TextColumn ('content')
    Delimiter (' ')
    Grams ('1')
    Overlapping ('false')
    ConvertToLowerCase ('true')
    Punctuation ('\[.,?\!\]')
    Reset ('\[.,?\!\]')
    OutputTotalGramCount ('false')
    Accumulate ('docid')
  ) AS dt
) WITH DATA;

SQL Call to Create TFIDF Input Table tfidf_input1

The TFIDF input table must have the tokenized input in the column term.

CREATE MULTISET TABLE tfidf_input1 AS (
  SELECT docid, ngram AS term, frequency AS "count"
  FROM tfidf_token1 AS dt
) WITH DATA;

This query returns the following table:

SELECT * FROM tfidf_input1 ORDER BY 1, 3, 2;
 docid term            count 
 ----- --------------- ----- 
     1 a                   1
     1 adjoining           1
     1 affected            1
     1 all                 1
     1 areas               1
     1 battered            1
     1 came                1
     1 capital             1
     1 city                1
     1 earthquakes         1
     1 floods              1
     1 had                 1
     1 has                 1
     1 have                1
     1 its                 1
     1 life                1
     1 modes               1
     1 nadu                1
     1 normal              1
     1 past                1
     1 roads               1
     1 severely            1
     1 standstill          1
     1 submerged           1
     1 tamil               1
     1 to                  1
     1 transport           1
     1 tsunamis            1
     1 water               1
     1 when                1
     1 chennai             2
     1 in                  2
     1 of                  2
     1 the                 2
     1 were                2
     1 and                 3
     2 10                  1
     2 1981                1
     2 2002                1
     2 8                   1
     2 a                   1
     2 august              1
     2 australian          1
     2 been                1
     2 born                1
     2 continuously        1
     2 federer             1
     2 frenchopen          1
     2 greatest            1
     2 inside              1
     2 is                  1
     2 mutiple             1
     2 october             1
     2 on                  1
     2 player              1
     2 ranked              1
     2 roger               1
     2 since               1
     2 tennis              1
     2 the                 1
     2 times               1
     2 titles              1
     2 top                 1
     2 usopen              1
     2 who                 1
     2 wimbledon           1
     2 won                 1
     2 and                 2
     2 has                 2
     3 2015                1
     3 23â??11             1
     3 34                  1
     3 a                   1
     3 an                  1
     3 are                 1
     3 as                  1
     3 be                  1
     3 between             1
     3 by                  1
     3 consider            1
     3 currently           1
     3 eleven-year-old     1
     3 engaged             1
     3 fedal               1
     3 final               1
     3 greatest            1
     3 have                1
     3 history             1
     3 indoors             1
     3 is                  1
     3 known               1
     3 leads               1
     3 most                1
     3 overall             1
     3 played              1
     3 players             1
     3 professional        1
     3 rafael              1
     3 recently            1
     3 record              1
     3 roger               1
     3 spain               1
     3 storied             1
     3 swiss               1
     3 switzerland         1
     3 their               1
     3 times               1
     3 to                  1
     3 two                 1
     3 which               1
     3 with                1
     3 and                 2
     3 federer             2
     3 many                2
     3 tennis              2
     3 they                2
     3 in                  3
     3 nadal               3
     3 of                  3
     3 rivalry             3
     3 the                 3
     4 2011                1
     4 988                 1
     4 according           1
     4 an                  1
     4 and                 1
     4 around              1
     4 attract             1
     4 attracted           1
     4 been                1
     4 between             1
     4 billion             1
     4 cup                 1
     4 estimated           1
     4 firms               1
     4 has                 1
     4 in                  1
     4 india               1
     4 india-pakistan      1
     4 intense             1
     4 is                  1
     4 match               1
     4 million             1
     4 most                1
     4 of                  1
     4 other               1
     4 pakistan            1
     4 ratings             1
     4 reports             1
     4 rivalries           1
     4 rivalry             1
     4 semifinal           1
     4 sports              1
     4 teams               1
     4 television          1
     4 tv                  1
     4 two                 1
     4 up                  1
     4 various             1
     4 cricket             2
     4 one                 2
     4 viewers             2
     4 world               2
     4 to                  3
     4 the                 5
     5 an                  1
     5 as                  1
     5 at                  1
     5 august              1
     5 been                1
     5 by                  1
     5 drawn               1
     5 every               1
     5 four                1
     5 has                 1
     5 have                1
     5 having              1
     5 hold                1
     5 hosted              1
     5 is                  1
     5 least               1
     5 once                1
     5 overall             1
     5 three               1
     5 traditionally       1
     5 turn                1
     5 years               1
     5 2015                2
     5 32                  2
     5 and                 2
     5 australia           2
     5 in                  2
     5 tests               2
     5 won                 2
     5 ashes               3
     5 england             3
     5 five                3
     5 of                  3
     5 the                 3
     5 series              4

SQL Call to Create TFIDF Input Table tf1

CREATE MULTISET TABLE tf1 AS (
  SELECT * FROM tf (
    ON tfidf_input1 PARTITION BY docid
  ) AS dt1
) WITH DATA;

TFIDF SQL Call

CREATE MULTISET TABLE tfidf_output1 AS (
  SELECT * FROM TFIDF (
    ON tf1 AS TF PARTITION BY term
    ON (
      SELECT CAST(COUNT(DISTINCT(docid)) AS integer) AS "count"
      FROM tfidf_input1
    ) AS DocCount DIMENSION
  ) AS dt
) WITH DATA;

TFIDF Output

This query returns the following table:

SELECT * FROM tfidf_output1 ORDER BY tfidf DESC;
 docid term            tf                   idf                 tf_idf                
 ----- --------------- -------------------- ------------------- --------------------- 
     5 series           0.07272727272727272  1.6094379124341003   0.11705002999520729
     5 ashes            0.05454545454545454  1.6094379124341003   0.08778752249640547
     5 england          0.05454545454545454  1.6094379124341003   0.08778752249640547
     5 five             0.05454545454545454  1.6094379124341003   0.08778752249640547
     1 chennai         0.046511627906976744  1.6094379124341003   0.07485757732251629
     1 were            0.046511627906976744  1.6094379124341003   0.07485757732251629
     3 nadal            0.04477611940298507  1.6094379124341003   0.07206438413884031
     4 one             0.037037037037037035  1.6094379124341003   0.05960881157163334
     4 cricket         0.037037037037037035  1.6094379124341003   0.05960881157163334
     4 viewers         0.037037037037037035  1.6094379124341003   0.05960881157163334
     4 world           0.037037037037037035  1.6094379124341003   0.05960881157163334
     5 tests            0.03636363636363636  1.6094379124341003  0.058525014997603646
     5 australia        0.03636363636363636  1.6094379124341003  0.058525014997603646
     5 32               0.03636363636363636  1.6094379124341003  0.058525014997603646
     3 many            0.029850746268656716  1.6094379124341003   0.04804292275922687
     3 they            0.029850746268656716  1.6094379124341003   0.04804292275922687
     2 mutiple          0.02857142857142857  1.6094379124341003   0.04598394035526001
     2 10               0.02857142857142857  1.6094379124341003   0.04598394035526001
     2 inside           0.02857142857142857  1.6094379124341003   0.04598394035526001
     2 on               0.02857142857142857  1.6094379124341003   0.04598394035526001
     2 player           0.02857142857142857  1.6094379124341003   0.04598394035526001
     2 october          0.02857142857142857  1.6094379124341003   0.04598394035526001
     2 frenchopen       0.02857142857142857  1.6094379124341003   0.04598394035526001
     2 since            0.02857142857142857  1.6094379124341003   0.04598394035526001
     2 titles           0.02857142857142857  1.6094379124341003   0.04598394035526001
     2 2002             0.02857142857142857  1.6094379124341003   0.04598394035526001
     2 continuously     0.02857142857142857  1.6094379124341003   0.04598394035526001
     2 australian       0.02857142857142857  1.6094379124341003   0.04598394035526001
     2 usopen           0.02857142857142857  1.6094379124341003   0.04598394035526001
     2 wimbledon        0.02857142857142857  1.6094379124341003   0.04598394035526001
     2 1981             0.02857142857142857  1.6094379124341003   0.04598394035526001
     2 who              0.02857142857142857  1.6094379124341003   0.04598394035526001
     2 ranked           0.02857142857142857  1.6094379124341003   0.04598394035526001
     2 born             0.02857142857142857  1.6094379124341003   0.04598394035526001
     2 8                0.02857142857142857  1.6094379124341003   0.04598394035526001
     2 top              0.02857142857142857  1.6094379124341003   0.04598394035526001
     3 rivalry          0.04477611940298507  0.9162907318741551   0.04102794321824575
     1 earthquakes     0.023255813953488372  1.6094379124341003   0.03742878866125814
     1 submerged       0.023255813953488372  1.6094379124341003   0.03742878866125814
     1 past            0.023255813953488372  1.6094379124341003   0.03742878866125814
     1 transport       0.023255813953488372  1.6094379124341003   0.03742878866125814
     1 capital         0.023255813953488372  1.6094379124341003   0.03742878866125814
     1 city            0.023255813953488372  1.6094379124341003   0.03742878866125814
     1 battered        0.023255813953488372  1.6094379124341003   0.03742878866125814
     1 roads           0.023255813953488372  1.6094379124341003   0.03742878866125814
     1 areas           0.023255813953488372  1.6094379124341003   0.03742878866125814
     1 tamil           0.023255813953488372  1.6094379124341003   0.03742878866125814
     1 standstill      0.023255813953488372  1.6094379124341003   0.03742878866125814
     1 nadu            0.023255813953488372  1.6094379124341003   0.03742878866125814
     1 life            0.023255813953488372  1.6094379124341003   0.03742878866125814
     1 severely        0.023255813953488372  1.6094379124341003   0.03742878866125814
     1 adjoining       0.023255813953488372  1.6094379124341003   0.03742878866125814
     1 all             0.023255813953488372  1.6094379124341003   0.03742878866125814
     1 had             0.023255813953488372  1.6094379124341003   0.03742878866125814
     1 came            0.023255813953488372  1.6094379124341003   0.03742878866125814
     1 modes           0.023255813953488372  1.6094379124341003   0.03742878866125814
     1 its             0.023255813953488372  1.6094379124341003   0.03742878866125814
     1 affected        0.023255813953488372  1.6094379124341003   0.03742878866125814
     1 tsunamis        0.023255813953488372  1.6094379124341003   0.03742878866125814
     1 when            0.023255813953488372  1.6094379124341003   0.03742878866125814
     1 floods          0.023255813953488372  1.6094379124341003   0.03742878866125814
     1 normal          0.023255813953488372  1.6094379124341003   0.03742878866125814
     1 water           0.023255813953488372  1.6094379124341003   0.03742878866125814
     5 2015             0.03636363636363636  0.9162907318741551     0.033319662977242
     5 won              0.03636363636363636  0.9162907318741551     0.033319662977242
     4 around          0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 teams           0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 india           0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 television      0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 tv              0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 estimated       0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 other           0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 india-pakistan  0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 various         0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 cup             0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 ratings         0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 988             0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 attracted       0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 up              0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 rivalries       0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 reports         0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 billion         0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 attract         0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 match           0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 million         0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 sports          0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 pakistan        0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 semifinal       0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 intense         0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 firms           0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 2011            0.018518518518518517  1.6094379124341003   0.02980440578581667
     4 according       0.018518518518518517  1.6094379124341003   0.02980440578581667
     5 having           0.01818181818181818  1.6094379124341003  0.029262507498801823
     5 turn             0.01818181818181818  1.6094379124341003  0.029262507498801823
     5 drawn            0.01818181818181818  1.6094379124341003  0.029262507498801823
     5 four             0.01818181818181818  1.6094379124341003  0.029262507498801823
     5 traditionally    0.01818181818181818  1.6094379124341003  0.029262507498801823
     5 years            0.01818181818181818  1.6094379124341003  0.029262507498801823
     5 hosted           0.01818181818181818  1.6094379124341003  0.029262507498801823
     5 at               0.01818181818181818  1.6094379124341003  0.029262507498801823
     5 hold             0.01818181818181818  1.6094379124341003  0.029262507498801823
     5 every            0.01818181818181818  1.6094379124341003  0.029262507498801823
     5 once             0.01818181818181818  1.6094379124341003  0.029262507498801823
     5 least            0.01818181818181818  1.6094379124341003  0.029262507498801823
     5 three            0.01818181818181818  1.6094379124341003  0.029262507498801823
     4 to               0.05555555555555555  0.5108256237659907  0.028379201320332816
     3 tennis          0.029850746268656716  0.9162907318741551  0.027351962145497167
     3 federer         0.029850746268656716  0.9162907318741551  0.027351962145497167
     2 won              0.02857142857142857  0.9162907318741551   0.02617973519640443
     2 roger            0.02857142857142857  0.9162907318741551   0.02617973519640443
     2 federer          0.02857142857142857  0.9162907318741551   0.02617973519640443
     2 greatest         0.02857142857142857  0.9162907318741551   0.02617973519640443
     2 times            0.02857142857142857  0.9162907318741551   0.02617973519640443
     2 tennis           0.02857142857142857  0.9162907318741551   0.02617973519640443
     2 august           0.02857142857142857  0.9162907318741551   0.02617973519640443
     3 played          0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 spain           0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 swiss           0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 leads           0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 are             0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 with            0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 34              0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 recently        0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 history         0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 indoors         0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 storied         0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 23â??11         0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 engaged         0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 switzerland     0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 consider        0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 currently       0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 record          0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 which           0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 fedal           0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 their           0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 rafael          0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 be              0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 players         0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 known           0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 eleven-year-old 0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 final           0.014925373134328358  1.6094379124341003  0.024021461379613435
     3 professional    0.014925373134328358  1.6094379124341003  0.024021461379613435
     4 between         0.018518518518518517  0.9162907318741551  0.016968346886558426
     4 two             0.018518518518518517  0.9162907318741551  0.016968346886558426
     4 rivalry         0.018518518518518517  0.9162907318741551  0.016968346886558426
     4 most            0.018518518518518517  0.9162907318741551  0.016968346886558426
     5 overall          0.01818181818181818  0.9162907318741551     0.016659831488621
     5 by               0.01818181818181818  0.9162907318741551     0.016659831488621
     5 as               0.01818181818181818  0.9162907318741551     0.016659831488621
     5 august           0.01818181818181818  0.9162907318741551     0.016659831488621
     2 been             0.02857142857142857  0.5108256237659907  0.014595017821885449
     2 a                0.02857142857142857  0.5108256237659907  0.014595017821885449
     3 greatest        0.014925373134328358  0.9162907318741551  0.013675981072748583
     3 roger           0.014925373134328358  0.9162907318741551  0.013675981072748583
     3 as              0.014925373134328358  0.9162907318741551  0.013675981072748583
     3 overall         0.014925373134328358  0.9162907318741551  0.013675981072748583
     3 most            0.014925373134328358  0.9162907318741551  0.013675981072748583
     3 2015            0.014925373134328358  0.9162907318741551  0.013675981072748583
     3 times           0.014925373134328358  0.9162907318741551  0.013675981072748583
     3 between         0.014925373134328358  0.9162907318741551  0.013675981072748583
     3 two             0.014925373134328358  0.9162907318741551  0.013675981072748583
     3 by              0.014925373134328358  0.9162907318741551  0.013675981072748583
     2 has              0.05714285714285714 0.22314355131420976    0.0127510600750977
     5 of               0.05454545454545454 0.22314355131420976  0.012171466435320532
     1 a               0.023255813953488372  0.5108256237659907  0.011879665668976528
     1 have            0.023255813953488372  0.5108256237659907  0.011879665668976528
     1 to              0.023255813953488372  0.5108256237659907  0.011879665668976528
     1 in              0.046511627906976744 0.22314355131420976  0.010378769828567896
     1 of              0.046511627906976744 0.22314355131420976  0.010378769828567896
     3 in               0.04477611940298507 0.22314355131420976  0.009991502297651183
     3 of               0.04477611940298507 0.22314355131420976  0.009991502297651183
     4 been            0.018518518518518517  0.5108256237659907  0.009459733773444272
     4 an              0.018518518518518517  0.5108256237659907  0.009459733773444272
     5 been             0.01818181818181818  0.5108256237659907  0.009287738613927104
     5 have             0.01818181818181818  0.5108256237659907  0.009287738613927104
     5 an               0.01818181818181818  0.5108256237659907  0.009287738613927104
     5 in               0.03636363636363636 0.22314355131420976  0.008114310956880354
     3 have            0.014925373134328358  0.5108256237659907  0.007624263041283444
     3 to              0.014925373134328358  0.5108256237659907  0.007624263041283444
     3 an              0.014925373134328358  0.5108256237659907  0.007624263041283444
     3 a               0.014925373134328358  0.5108256237659907  0.007624263041283444
     2 is               0.02857142857142857 0.22314355131420976   0.00637553003754885
     1 has             0.023255813953488372 0.22314355131420976  0.005189384914283948
     4 in              0.018518518518518517 0.22314355131420976  0.004132287987300181
     4 has             0.018518518518518517 0.22314355131420976  0.004132287987300181
     4 is              0.018518518518518517 0.22314355131420976  0.004132287987300181
     4 of              0.018518518518518517 0.22314355131420976  0.004132287987300181
     5 has              0.01818181818181818 0.22314355131420976  0.004057155478440177
     5 is               0.01818181818181818 0.22314355131420976  0.004057155478440177
     3 is              0.014925373134328358 0.22314355131420976 0.0033305007658837277
     4 the              0.09259259259259259                 0.0                   0.0
     4 and             0.018518518518518517                 0.0                   0.0
     2 the              0.02857142857142857                 0.0                   0.0
     2 and              0.05714285714285714                 0.0                   0.0
     3 and             0.029850746268656716                 0.0                   0.0
     3 the              0.04477611940298507                 0.0                   0.0
     5 the              0.05454545454545454                 0.0                   0.0
     5 and              0.03636363636363636                 0.0                   0.0
     1 the             0.046511627906976744                 0.0                   0.0
     1 and              0.06976744186046512                 0.0                   0.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.