SAP HANA里的情感分析实现的SQLScript代码

create procedure pr_sent as var_num integer

;

var_time timestamp

;

i_longth integer

;

var_id NVARCHAR (32)

;

var_sterm varchar (100)

;

var_match varchar (200)

;

var_date date

;

I_EXIST_CAMPAIGN INTEGER

;

I_EXIST_SERVICE INTEGER

;

CURSOR C_values for select

      distinct SocialDataUUID,

      SocialPostSearchTermText ,

      SOCIALPOSTCREATIONDATE

from VOICEOFCUSTOMER

where match_flag='0'

;

 

BEGIN ----PART1 VOICEOFCUSTOMERDETAIL

insert

into VOICEOFCUSTOMERDETAIL (SocialDataUUID ,

      RULE,

      COUNTER,

      VOICEOFCUSTOMERTYPE ,

      VOICEOFCUSTOMERTEXT ) select

      T1.SocialDataUUID,

      T2.TA_RULE,

      T2.TA_COUNTER,

      T2.TA_TYPE,

      T2.TA_TOKEN

from "SAP_SOMI"."sap.hba.somi.db::SOCIALDATA" t1,

      "SAP_SOMI"."$TA_INDEXVOICEOFCUST" t2

where t1.SocialDataUUID=t2.SocialDataUUID

and NOT EXISTS (SELECT

      1

      FROM VOICEOFCUSTOMERDETAIL a4

      WHERE t2.SocialDataUUID=a4.SocialDataUUID )

and (upper (t2.TA_TYPE) = 'TOPIC'

      or t2.TA_TYPE ='PRODUCT'

      OR UPPER (t2.TA_TYPE) LIKE '%REQUEST'

      OR (UPPER (t2.TA_TYPE) LIKE '%SENTIMENT'

             AND UPPER (t2.TA_TYPE) <> 'SENTIMENT' )

      OR UPPER (t2.TA_TYPE) LIKE '%PROBLEM'

      OR UPPER (t2.TA_TYPE) LIKE '%SERVICE' )

;

---VOICEOFCUSTOMER

insert

into VOICEOFCUSTOMER ("SOCIALDATAUUID" ,

      SocialPostSearchTermText ,

      "VOICEOFCUSTOMERAVERAGEOPINION",

      SOCIALPOSTCREATIONDATETIME ,

      SOCIALPOSTCreationDateYear ,

      SOCIALPOSTCreationDateMonth ,

      SOCIALPOSTCREATIONDATE ,

      "SENTIMENT_AVG" ,

      "MATCH_FLAG" ) select

      DISTINCT SocialDataUUID,

      SocialPostSearchTermText,

      case when type_num = 0

or type_num is null

then 'Neutral' when type_num >0

AND type_num <= 1

then 'Weak Positive' when type_num>1

AND type_num <=2

then 'Strong Positive' when type_num>= -1

AND type_num <0

then 'Weak Negative' when type_num>= -2

AND type_num <-1

then 'Strong Negative'

else 'No Sentiment'

end as VoiceOfCustomerSubType,

      to_timestamp(CreationDateTime),

      SUBSTR (CreationDateTime,

      1,

      4 ) AS CreationYear ,

      SUBSTR (CreationDateTime,

      1,

      7 ) AS CreationMonth ,

      TO_DATE (CreationDateTime) AS CreationDate,

      map (type_num,

      null,

      0,

      type_num) AS SENTIMENT_AVG,

      '0' as match_flag

from (select

      t1.SocialDataUUID,

      t1.SocialPostSearchTermText,

      substr_before (to_char(TO_timestamp (TRIM (SUBSTR (T1.CreationDateTime,

      INSTR (T1.CreationDateTime,

      ',')+1,

      INSTR (T1.CreationDateTime,

      '+')-INSTR (T1.CreationDateTime,

      ',')-1 ) ),

      'DD MON YYYY HH24:MI:SS')),

      '.' ) as CreationDateTime,

      t2.TA_TYPE,

      T2.TA_COUNTER,

      T2.TA_TOKEN ,

      t4.type_num

      from "SAP_SOMI"."sap.hba.somi.db::SOCIALDATA" t1

      LEFT JOIN (select

      sum (case when TA_TYPE ='NeutralSentiment'

                    then 0 when TA_TYPE ='StrongNegativeSentiment'

                    then -2 when TA_TYPE ='StrongPositiveSentiment'

                    then 2 when TA_TYPE ='WeakNegativeSentiment'

                    then -1 when TA_TYPE ='WeakPositiveSentiment'

                    then 1

                    else 0

                    end ) / count (1) as type_num,

      SocialDataUUID

             from (select

      a2.SocialDataUUID,

      a2.TA_TYPE

                    from "SAP_SOMI"."$TA_INDEXVOICEOFCUST" a2

                    where NOT EXISTS (SELECT

      1

                          FROM VOICEOFCUSTOMER a4

                          WHERE a2.SocialDataUUID=a4.SocialDataUUID )

                    and ( UPPER (a2.TA_TYPE) LIKE '%SENTIMENT'

                          AND UPPER (a2.TA_TYPE) <> 'SENTIMENT' ) )

             group by SocialDataUUID ) t4 ON t1.SocialDataUUID=t4.SocialDataUUID ,

      "SAP_SOMI"."$TA_INDEXVOICEOFCUST" t2

      where t1.SocialDataUUID=t2.SocialDataUUID

      and NOT EXISTS (SELECT

      1

             FROM VOICEOFCUSTOMER a4

             WHERE t2.SocialDataUUID=a4.SocialDataUUID )

      and (upper (t2.TA_TYPE) = 'TOPIC'

             or t2.TA_TYPE ='PRODUCT'

             OR UPPER (t2.TA_TYPE) LIKE '%REQUEST'

             OR (UPPER (t2.TA_TYPE) LIKE '%SENTIMENT'

                    AND UPPER (t2.TA_TYPE) <> 'SENTIMENT' )

             OR UPPER (t2.TA_TYPE) LIKE '%PROBLEM'

             OR UPPER (t2.TA_TYPE) LIKE '%SERVICE' ) )T

;

-----PART2 MATCH_CAMP

for i_val as c_values do var_id := i_val.SocialDataUUID

;

var_sterm := i_val.SocialPostSearchTermText

;

var_date:=i_val.SOCIALPOSTCREATIONDATE

;

--INSERT INTO VOICEOFCUSTCAMP

SELECT

      COUNT (1)

INTO I_EXIST_CAMPAIGN

FROM VIEWS

WHERE VIEW_NAME='sap.crm.sentiment/AT_CAMP'

AND SCHEMA_NAME='_SYS_BIC'

;

 

IF I_EXIST_CAMPAIGN=1

THEN insert

into VOICEOFCUSTCAMP select

      var_id,

      var_sterm,

      guid,

      CAMP_TEXT,

      PRODUCT_TEXT,

      SNIPPETS (CAMP_TEXT),

      SNIPPETS (PRODUCT_TEXT)

from "_SYS_BIC"."sap.crm.sentiment/AT_CAMP" t1

where contains ( (CAMP_TEXT,

      PRODUCT_TEXT),

      var_sterm ,

      FUZZY (0.9) )

AND PLANSTARTDATE < var_date

;

 

END

IF

;

--INSERT INTO VOICEOFCUSTSERVICE

SELECT

      COUNT (1)

INTO I_EXIST_SERVICE

FROM VIEWS

WHERE VIEW_NAME='sap.crm.sentiment/AT_SERV'

AND SCHEMA_NAME='_SYS_BIC'

;

 

IF I_EXIST_SERVICE=1

THEN insert

into VOICEOFCUSTSERV select

      var_id,

      var_sterm,

      guid,

      DESCRIPTION,

      SHORT_TEXT,

      SNIPPETS (DESCRIPTION),

      SNIPPETS (SHORT_TEXT)

from "_SYS_BIC"."sap.crm.sentiment/AT_SERV" t1

where contains ( (DESCRIPTION,

      SHORT_TEXT),

      var_sterm ,

      FUZZY (0.9) )

;

 

END

IF

;

update VOICEOFCUSTOMER

set match_flag='1'

where match_flag='0'

and SocialDataUUID=var_id

;

 

end for

;

 

END

;