17.10 - NaiveBayesTextClassifierPredictの例: TopKを指定 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Advanced SQL Engine分析関数

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
2021年7月
Content Type
プログラミング リファレンス
Publication ID
B035-1206-171K-JPN
Language
日本語 (日本)

入力

  • PredictorValues: 次のように、自動車のクレームのログであるcomplaints_testテーブルにML Engine TextTokenizer関数を適用して作成されたcomplaints_test_tokenized。
    CREATE MULTISET TABLE complaints_test_tokenized AS (
      SELECT doc_id, doc_name, lower(cast(token AS VARCHAR(20))) AS token
        FROM TextTokenizer (
          ON complaints_test PARTITION BY ANY
          USING
          TextColumn ('text_data')
          OutputByWord ('true')
          Accumulate ('doc_id', doc_name)
        ) AS dt
    ) WITH DATA;
  • モデル: 次のクエリーを使用してcomplaints_tokens_modelを作成します。
    CREATE TABLE complaints_tokens_model(
     token VARCHAR(100),
     category VARCHAR(100),
     prob DOUBLE PRECISION
    );
complaints_test
doc_id doc_name text_data
1 A ELECTRICAL CONTROL MODULE IS SHORTENING OUT, CAUSING THE VEHICLE TO STALL. ENGINE WILL BECOME TOTALLY INOPERATIVE. CONSUMER HAD TO CHANGE ALTERNATOR/ BATTERY AND STARTER, AND MODULE REPLACED 4 TIMES, BUT DEFECT STILL OCCURRING CANNOT DETERMINE WHAT IS CAUSING THE PROBLEM。
2 B ABS BRAKES FAIL TO OPERATE PROPERLY, AND AIR BAGS FAILED TO DEPLOY DURING A CRASH AT APPROX. 28 MPH IMPACT. MANUFACTURER NOTIFIED。
3 C WHILE DRIVING AT 60 MPH GAS PEDAL GOT STUCK DUE TO THE RUBBER THAT IS AROUND THE GAS PEDAL。
4 D THERE IS A KNOCKING NOISE COMING FROM THE CATALYITC CONVERTER ,AND THE VEHICLE IS STALLING. ALSO, HAS PROBLEM WITH THE STEERING。
5 E CONSUMER WAS MAKING A TURN ,DRIVING AT APPROX 5- 10 MPH WHEN CONSUMER HIT ANOTHER VEHICLE. UPON IMPACT, DUAL AIRBAGS DID NOT DEPLOY . ALL DAMAGE WAS DONE FROM ENGINE TO TRANSMISSION,TO THE FRONT OF VEHICLE, AND THE VEHICLE CONSIDERED A TOTAL LOSS。
6 F WHEEL BEARING AND HUBS CRACKED, CAUSING THE METAL TO GRIND WHEN MAKING A RIGHT TURN. ALSO WHEN APPLYING THE BRAKES, PEDAL GOES TO THE FLOOR, CAUSE UNKNOWN. WAS ADVISED BY MIDAS NOT TO DRIVE VEHICLE- WHEELE COULD COME OFF。
7 G DRIVING ABOUT 5-10 MPH, THE VEHICLE HAD A LOW FRONTAL IMPACT IN WHICH THE OTHER VEHICLE HAD NO DAMAGES. UPON IMPACT, DRIVER'S AND THE PASSENGER'S AIR BAGS DID NOT DEPLOY, RESULTING IN INJURIES. PLEASE PROVIDE FURTHER INFORMATION AND VIN#。
8 H THE AIR BAG WARNING LIGHT HAS COME ON. INDICATING AIRBAGS ARE INOPERATIVE.THEY WERE FIXED ONE AT THE TIME, BUT PROBLEM HAS REOCCURRED。
9 I CONSUMER WAS DRIVING WEST WHEN THE OTHER CAR WAS GOING EAST. THE OTHER CAR TURNED IN FRONT OF CONSUMER'S VEHICLE, CONSUMER HIT OTHER VEHICLE AND STARTED TO SPIN AROUND ,COULDN'T STOP, RESULTING IN A CRASH. UPON IMPACT, AIRBAGS DIDN'T DEPLOY。
10 J WHILE DRIVING ABOUT 65 MPH AND THE TRANSMISISON MADE A STRANGE NOISE, AND THE LEFT FRONT AXLE LOCKED UP. THE DEALER HAS REPAIRED THE VEHICLE。

SQL呼び出し

SELECT * FROM NaiveBayesTextClassifierPredict (
  ON complaints_test_tokenized AS PredictorValues PARTITION BY doc_id
  ON complaints_tokens_model AS Model DIMENSION
  USING
  ModelType ('Bernoulli')
  InputTokenColumn ('token')
  DocIDColumns ('doc_id')
  OutputProb ('true')
  Accumulate ('doc_name')
  TopK ('2')
) AS dt ORDER BY doc_id;

出力

doc_id  prediction                  loglik                    prob  doc_name
------  ----------  ----------------------  ----------------------  --------
     1  crash       -1.38044220625651E 002   1.41243173571687E-009  A
     1  no_crash    -1.17666267644292E 002   9.99999998587568E-001  A
     2  crash       -1.04652470718918E 002   1.70704288519507E-003  B
     2  no_crash    -9.82811865081127E 001   9.98292957114805E-001  B
     3  crash       -1.03026451289745E 002   2.26862573862878E-012  C
     3  no_crash    -7.62146044204976E 001   9.99999999997731E-001  C
     4  crash       -1.10830711173169E 002   1.42026355157382E-011  D
     4  no_crash    -8.58531176043404E 001   9.99999999985797E-001  D
     5  no_crash    -1.23936921216052E 002   3.43620138383542E-002  E
     5  crash       -1.20601083912966E 002   9.65637986161646E-001  E
     6  crash       -1.30310015371040E 002   2.61074198636704E-006  F
     6  no_crash    -1.17454141890718E 002   9.99997389258014E-001  F
     7  no_crash    -1.23123774759574E 002   4.23603936872661E-002  G
     7  crash       -1.20005517060745E 002   9.57639606312734E-001  G
     8  crash       -1.08617321658980E 002   8.92398441816595E-009  H
     8  no_crash    -9.00827983614664E 001   9.99999991076016E-001  H
     9  crash       -1.19919230739025E 002   2.24857954852037E-002  I
     9  no_crash    -1.16147101713878E 002   9.77514204514796E-001  I
    10  crash       -1.06104244132225E 002   7.57068462691010E-008  J
    10  no_crash    -8.97078469668254E 001   9.99999924293154E-001  J