Teradata - word frequency in a column

Question

Say I have the following column in a teradata table:

Red ball
Purple ball
Orange ball

I want my output to be

Word    Count
Red     1
Ball    3
Purple  1
Orange  1

Thanks.

This is the client version, what is returned by `select * from dbc.dbcinfoV;*? — dnoeth, Oct 14 '14 at 16:07

score 4 · Accepted Answer · answered Oct 14 '14 at 16:27

In TD14 there's a STRTOK_SPLIT_TO_TABLE function:

SELECT token, COUNT(*)
FROM TABLE (STRTOK_SPLIT_TO_TABLE(1 -- this is just a dummy, usually the PK column when you need to join
                                 ,table.stringcolumn
                                 ,' ') -- simply add other separating characters
     RETURNS (outkey INTEGER,
              tokennum INTEGER,
              token VARCHAR(100) CHARACTER SET UNICODE
             )
           ) AS d
GROUP BY 1

Andrew · Answer 2 · 2014-10-14T16:20:48.713

1

Here's how I would handle something like this:

  WITH RECURSIVE CTE  (POS, NEW_STRING, REAL_STRING) AS
(
SELECT
0, CAST('' AS VARCHAR(100)),TRIM(word)
FROM wordcount
UNION ALL
SELECT
CASE WHEN POSITION(' ' IN REAL_STRING) > 0
THEN POSITION(' ' IN REAL_STRING)
ELSE CHARACTER_LENGTH(REAL_STRING)
END DPOS,
TRIM(BOTH  ' ' FROM SUBSTR(REAL_STRING, 0, DPOS+1)),
TRIM(SUBSTR(REAL_STRING, DPOS+1))
FROM CTE
WHERE DPOS > 0
)

SELECT TRIM(NEW_STRING) as word,
count (*)
FROM CTE
group by word
WHERE pos > 0;

Which will return:

    word    Count(*)
    orange  1
    purple  1
    red 1
    ball    3

There may be an easier way with regex in 14, but I haven't messed with it yet.

EDIT: Removed some unneeded columns from the query.

edited Oct 14 '14 at 16:20

answered Oct 14 '14 at 16:07

Andrew

8,445
3
28
46

I somehow get a different answer when I do a count like this: Select sum(case when colour like ‘%purple%’ then 1 else 0 end) from wordcount Not on this example table, but on my entire table. Any idea why? – kittymad Oct 14 '14 at 16:26
No clue, to be honest. Try dnoeth's answer above mine. It's certainly a lot cleaner. – Andrew Oct 14 '14 at 16:32
They both give me the same result. I accepted dnoeth's answer because it is indeed neater. Thanks a lot, really appreciate your help. – kittymad Oct 14 '14 at 23:41

score 0 · Answer 3 · edited Oct 14 '14 at 16:17

0

Change your table with this

name  |name2
_______________
red    |  ball
purple |  ball
orange |  ball
_______________

And then run the following query:

select name, count(name)as name1_count from table_test
group by name
union all
select name2,count(name2)as name2_count from table_test
group by name2;

edited Oct 14 '14 at 16:17

Patrice

4,641
9
33
43

answered Oct 14 '14 at 15:55

Diego Montes Aragones

1
1

Unfortunately no, that is a single column I am talking about, and the column can have up to 10-15 words. – kittymad Oct 14 '14 at 16:01

Teradata - word frequency in a column

3 Answers3