{"id":670,"date":"2016-08-03T07:11:08","date_gmt":"2016-08-03T06:11:08","guid":{"rendered":"http:\/\/blog.tiran.info\/?p=670"},"modified":"2017-12-22T14:44:05","modified_gmt":"2017-12-22T13:44:05","slug":"visualisation-en-nuage-de-mots-avec-oracle","status":"publish","type":"post","link":"https:\/\/blog.tiran.stream\/?p=670","title":{"rendered":"Visualisation en nuage de mots avec Oracle"},"content":{"rendered":"

La repr\u00e9sentation en nuage de mots-cl\u00e9s<\/a>\u00a0est tr\u00e8s r\u00e9pandue pour donner une impression de l’importance relative – en terme de fr\u00e9quence d’apparition – des termes au sein de donn\u00e9es textuelles.\u00a0Ici, j’exploite les informations du mod\u00e8le de classification Bay\u00e9sienne construit dans le billet \u00ab\u00a0Classification Bay\u00e9sienne Na\u00efve avec Oracle<\/a>\u00a0\u00bb\u00a0pour extraire les mots les plus utilis\u00e9s par cat\u00e9gorie.<\/p>\n

Le d\u00e9tail du mod\u00e8le est accessible via la fonction DBMS_DATA_MINING.GET_MODEL_DETAILS_NB<\/a>. Il s’agit d’une fonction PIPELINE<\/a> dont le champ (nested table) CONDITIONALS permet d’obtenir les probabilit\u00e9s conditionnelles<\/a> des diff\u00e9rents termes. Cette information peut ensuite \u00eatre utilis\u00e9e comme indicateur de popularit\u00e9 des mots au sein d’une cat\u00e9gorie:<\/p>\n

C’est le principe des la vue suivante qui extrait les 40 mots les plus \u00ab\u00a0populaires\u00a0\u00bb de la cat\u00e9gorie Polars:<\/p>\n

SQL> CREATE OR REPLACE VIEW mots_freq_polar\r\n  2  AS\r\n  3          SELECT attribute_subname mot,\r\n  4                 ROUND (100 * conditional_probability, 1) freq\r\n  5            FROM TABLE (DBMS_DATA_MINING.GET_MODEL_DETAILS_NB ('BOOKS_NB')) a,\r\n  6                 TABLE (a.conditionals) b\r\n  7           WHERE     INSTR (attribute_str_value, '[') = 0\r\n  8                 AND target_attribute_str_value = 'polar'\r\n  9        ORDER BY conditional_probability DESC\r\n 10     FETCH FIRST 40 ROWS ONLY;\r\n\r\nView created.\r\n\r\nSQL> \r\nSQL> column mot format a30\r\nSQL> SELECT *\r\n  2    FROM mots_freq_polar\r\n  3  FETCH FIRST 10 ROWS ONLY;\r\n\r\nMOT                                  FREQ\r\n------------------------------ ----------\r\nENQU\u00caTE                              24.3\r\nBIEN                                 21.9\r\nFEMME                                21.4\r\nVIE                                  18.8\r\nMORT                                 18.1\r\nANS                                    18\r\nDEUX                                 15.9\r\nROMAN                                15.8\r\nJEUNE                                14.6\r\nPOLICE                               14.5\r\n\r\n10 rows selected.\r\n\r\nSQL>\r\n<\/pre>\n
 <\/p>\n
Pour g\u00e9n\u00e9rer le graphique, j’utilise ici Oracle Data Visualisation Desktop<\/a> qui dispose d’une visualisation \u00ab\u00a0Nuage de mots-cl\u00e9s\u00a0\u00bb:<\/p>\n
\"NuageMotsDVD\"<\/a><\/p>\n
 <\/p>\n
Dans un cadre plus g\u00e9n\u00e9ral, il est possible d’obtenir un d\u00e9compte des termes \u00e0 partir d’une source textuelle \u00e0 l’aide de la proc\u00e9dure CTX_DOC.TOKENS<\/a> appliqu\u00e9e \u00e0 un index textuel<\/a> (ctxsys.context).<\/p>\n
Exemple:<\/p>\n
SQL> CREATE TABLE polar_tab (book_id PRIMARY KEY, extrait)\r\n  2  AS\r\n  3     SELECT ROWNUM book_id, a.extrait\r\n  4       FROM train_set_books a\r\n  5      WHERE categorie = 'polar';\r\n\r\nTable created.\r\n\r\nSQL>\r\nSQL> CREATE INDEX POLAR_IDXTXT\r\n  2     ON polar_tab (extrait)\r\n  3     INDEXTYPE IS ctxsys.context\r\n  4        PARAMETERS ('LEXER BOOKS_NB_LEXER WORDLIST BOOKS_NB_WORDLIST STOPLIST BOOKS_STOPLIST SYNC(MANUAL)');\r\n\r\nIndex created.\r\n\r\nSQL>\r\nSQL> CREATE TABLE polar_tokens\r\n  2  (\r\n  3     QUERY_ID   NUMBER,\r\n  4     TOKEN      VARCHAR2 (64),\r\n  5     OFFSET     NUMBER,\r\n  6     LENGTH     NUMBER\r\n  7  );\r\n\r\nTable created.\r\n\r\nSQL>\r\nSQL> BEGIN\r\n  2     FOR rec IN (SELECT *\r\n  3                   FROM polar_tab)\r\n  4     LOOP\r\n  5        ctx_doc.tokens ('POLAR_IDXTXT',\r\n  6                        rec.book_id,\r\n  7                        'POLAR_TOKENS',\r\n  8                        rec.book_id);\r\n  9     END LOOP;\r\n 10  END;\r\n 11  \/\r\n\r\nPL\/SQL procedure successfully completed.\r\n\r\nSQL>\r\nSQL> COMMIT;\r\n\r\nCommit complete.\r\n\r\nSQL>\r\nSQL> set pages 30\r\nSQL>      SELECT token, COUNT (*) cnt\r\n  2         FROM polar_tokens\r\n  3     GROUP BY token\r\n  4     ORDER BY 2 DESC\r\n  5  FETCH FIRST 15 ROWS ONLY;\r\n\r\nTOKEN                                                                   CNT\r\n---------------------------------------------------------------- ----------\r\nBIEN                                                                   1765\r\nJEUNE                                                                  1744\r\nENQU\u00caTE                                                                1715\r\nDEUX                                                                   1710\r\nFEMME                                                                  1637\r\nVIE                                                                    1587\r\nHOMME                                                                  1537\r\nANS                                                                    1404\r\nMORT                                                                   1318\r\nROMAN                                                                  1200\r\nMONDE                                                                  1088\r\nPOLICE                                                                 1004\r\nVILLE                                                                   930\r\nAFFAIRE                                                                 914\r\nJOUR                                                                    908\r\n\r\n15 rows selected.\r\n\r\nSQL>\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
La repr\u00e9sentation en nuage de mots-cl\u00e9s\u00a0est tr\u00e8s r\u00e9pandue pour donner une impression de l’importance relative – en terme de fr\u00e9quence<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"colormag_page_container_layout":"default_layout","colormag_page_sidebar_layout":"default_layout","footnotes":""},"categories":[7,8,15,19,17],"tags":[],"class_list":["post-670","post","type-post","status-publish","format-standard","hentry","category-oracle-advanced-analytics","category-oracle-data-visualisation","category-statistique-exploratoire","category-donnees-non-structurees","category-visualisation"],"_links":{"self":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts\/670","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=670"}],"version-history":[{"count":1,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts\/670\/revisions"}],"predecessor-version":[{"id":1164,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=\/wp\/v2\/posts\/670\/revisions\/1164"}],"wp:attachment":[{"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=670"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=670"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.tiran.stream\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=670"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}