在sql下計算tfidf

aloneme發表於2017-12-05
SQL

吐槽

由於工作中要直接在sql中寫tfidf，對tfidf又有了新的理解，以及又讓我厭惡了sql。。。因為在R或者python中寫起tfidf來還是很方便的，直接調個包，或者直接寫都很快，但在sql中寫起來有點慢，比較冗長，也有可能我寫的不多。。下面分tfidf的

  1. 理論部分
  2. sql操作

來介紹本文：

tfidf給我直觀的感覺就是我們資料探勘競賽中說的規則。簡單有效

tfidf理論

tfidf分兩部分，分別為tf與idf

1.tf(詞頻)：

$$
TF(詞頻) = 某個詞在一個文件中出現的次數
$$

或者進行標準化

$$
TF(詞頻) = frac{某個詞在一個文件中出現的次數}{文件的總詞數}
$$

或者另一個標準化公式

$$
TF(詞頻) = frac{某個詞在一個文件中出現的次數}{文件中出現次數最多的詞的出現次數}
$$

2.idf(逆文件頻率)：

$$
IDF(逆文件頻率) = log(frac{總文件數}{(包含該詞的文件數+1)} )
$$

3.tfidf:

$$
TDIDF = TF *IDF
$$

實際含義：如果某個詞比較少見，但是它在這篇文章中多次出現，那麼它很可能就反映了這篇文章的特性，正是我們所需要的關鍵詞。

實際操作

我們可以把一個使用者的一些文字，或者說一些類別特徵，進行分詞(類別特徵已經直接可以看做分好的詞)

  • 把一個使用者的所有分詞結果作為一個文件
  • 總文件數則為所有使用者的文件加起來
  • 包含該詞的文件則可以根據group by uid,tag之後資料，在對tag計數 (tag指分詞後的詞語，或者類別)

假設我們的資料是下面這樣的：表名為table1，庫名為dw

 uid     hotelid    tags
125554    428365    美食預訂,返現,優惠券,禮,休閒度假,親子酒店
125554    456909    休閒度假,商務出行,健身室,免費WiFi
125554    662503    優惠券,親子酒店,閃住,空氣清新房
125554    7904714    返現,,健身室,免費WiFi
125554    3893971    返現,優惠券,禮,商務出行,空氣清新房
125554    5445544    優惠券,禮,商務出行
125554    6395558    美食預訂,返現,優惠券,禮,親子酒店
125554    662485    美食預訂,返現,優惠券,禮,休閒度假,親子酒店
125554    429724    美食預訂,返現,優惠券,禮,休閒度假,親子酒店
125554    428827    返現,優惠券,禮,休閒度假

在sql中用split來拆固定字串，再用explode把它炸開

select uid,tag,total_doc
        ,count(*)over(partition by tag) as words_in_doc -- 包含該詞的文件數
        ,n --詞頻
        ,tf --歸一化詞頻
        ,log(total_doc / (count(*)over(partition by tag) +1)) idf --得到idf
        ,tf * log(total_doc / (count(*)over(partition by tag) +1)) as tfidf --得到tfidf
from(
    select a.uid,a.tag
        ,count(*) n --得到詞頻
        ,count(*)  /(max(count(*)) over(partition by uid)) as tf --得到歸一化詞頻
        ,dense_rank() over (order by uid) + dense_rank() over (order by uid  desc) - 1 as total_doc 
    from
    (select uid,tag
    from (select uid,tag1
          from dw.table1 LATERAL VIEW explode(split(tags,`,`)) a as tag1) b
    LATERAL VIEW explode(split(tag1,`，`)) a as tag     
    where tag not in (``) 
     )a
    group by uid,tag
    )a

得到資料結構：
uid            tag        total_doc    words_in_doc    n    tf    idf    tfidf
00399066    麻辣火鍋    58    1    1    1.0    3.367295829986474    3.367295829986474
02932115    首住特惠    58    1    3    0.034482758620689655    3.367295829986474    0.11611364930987841
02769693    閃住    58    45    1    1.0    0.23180161405732438    0.23180161405732438
02689299    閃住    58    45    18    0.42857142857142855    0.23180161405732438    0.09934354888171044
02589732    閃住    58    45    2    0.2    0.23180161405732438    0.04636032281146488
00087790    閃住    58    45    3    0.6    0.23180161405732438    0.13908096843439463
03373990    閃住    58    45    14    0.4666666666666667    0.23180161405732438    0.10817408656008472

這邊是因為為了偷懶，所以直接寫在一個查詢了，如果分多次子表，會跟容易理解.

下面為分多次查詢：

--寫著一大段就是為了得到uid數來作為總文件數
use dw;
set hive.mapred.mode=nonstrict;
drop table if exists dw.table2;
create table dw.table2 as 
select uid,tag,total_doc --為了得到uid數來作為文件數
from
    (
    select uid,tag
    from (select uid,tag1
          from dw.table1 LATERAL VIEW explode(split(tags,`,`)) a as tag1) b
    LATERAL VIEW explode(split(tag1,`，`)) a as tag     
    where tag not in (``) 
    )a
left join (select count(distinct uid) as total_doc from dw.table1 )b
on 1=1;

得到資料結構：

uid            tag            total_doc
02932115    首住特惠      58
02932115    首住特惠      58
02932115    首住特惠      58
06275610    閃住            58
06100328    閃住            58
06100328    閃住            58

所以只要分詞到上面這樣的資料結構就能直接算tfidf了

再全部算出來：

select uid,tag,total_doc
        ,count(*)over(partition by tag) as words_in_doc -- 包含該詞的文件數
        ,n -- 得到詞頻
        ,tf --得到歸一化詞頻
        ,log(total_doc / (count(*)over(partition by tag) +1)) idf --得到idf(因為已經去重，每個uid對應唯一tag，故 by tag 就能計算文件在總文件中出現的次數)
        ,tf * log(total_doc / (count(*)over(partition by tag) +1)) as tfidf --得到tfidf
from (
    select uid,tag,total_doc
    ,count(*) n
    ,count(*)  /(max(count(*)) over(partition by uid)) as tf --得到歸一化詞頻
    from dw.table2
    group by uid,tag,total_doc
    )a

得到資料結構：跟上面一模一樣

uid    tag    total_doc    words_in_doc    n    tf    idf    tfidf
00399066    麻辣火鍋    58    1    1    1.0    3.367295829986474    3.367295829986474
02932115    首住特惠    58    1    3    0.034482758620689655    3.367295829986474    0.11611364930987841
02769693    閃住    58    45    1    1.0    0.23180161405732438    0.23180161405732438
06100328    閃住    58    45    5    0.25    0.23180161405732438    0.057950403514331096
00258565    閃住    58    45    3    0.42857142857142855    0.23180161405732438    0.09934354888171044
01001760    閃住    58    45    4    0.4444444444444444    0.23180161405732438    0.10302293958103305
03018508    閃住    58    45    15    0.9375    0.23180161405732438    0.2173140131787416

*總結：tfidf是一個強有力的規則，不僅能對文字做，其實任何帶有實際意義的分類資料都能使用tfidf，而不是隻統計個分類資料的眾數就沒有然後了。。
關鍵函式：在sql中用split來拆固定字串，再用explode把它炸開
*

本文理論部分參考:
阮一峰的網路日誌

相關文章