Showing posts with label SQL decode. Show all posts
Showing posts with label SQL decode. Show all posts

Friday, December 25, 2015

Convert Values Using Decode Function

We use decode() function to convert a value to other values. We have the following table.

SQL> select * from tbl_test order by id;

     ID MESSAGE
------- --------------------------------
      1 A
      2 B
      3 C
      4 D
The following query transforms characters values into numbers or weights.
SQL> select a.*, decode(message, 'A', 0.3, 'B',0.4, 0.35) weight from tbl_test a order by id;

     ID MESSAGE                              WEIGHT
------- -------------------------------- ----------
      1 A                                        .3
      2 B                                        .4
      3 C                                       .35
      4 D                                       .35
In the above query, A is mapped to 0.3, B to 0.4. All other values are mapped to the default value of 0.35. If the default value is not defined, other characters where the mapping is not defined will be given a NULL value.
SQL> select a.*, decode(message, 'A', 0.3, 'B',0.4) weight from tbl_test a order by id;

     ID MESSAGE                              WEIGHT
------- -------------------------------- ----------
      1 A                                        .3
      2 B                                        .4
      3 C
      4 D
I have used decode function to convert categorical values into weights that are derived from a logistic regression model. For example, I used the following query to convert transaction code into weights. Before decode function is applied, CARD_TXN_CDE is cleaned up using trim, substr and nvl functions. The trim function removes leading and trailing blanks. The substr function extracts the first 18 characters of the string. The nvl function converts NULL values to blank.
decode(nvl(substr( trim(CARD_TXN_CDE),1,18),' '),
' ',-.660841,
'01',-.518927,
'1',-.076546,
'10',-.294631,
'12',.077699,
'14',-.709884,
'25',-.30619,
'40',.021855,
'51',-.004593,
'52',-.069521,
'53',-.000344,
'57',.570421,
'59',.858444,
'67',1.481654,
'81',.29988,
'91',-.004755,
'96',.02628,
'AD',-.001036,
'AK',-.150162,
'CB',-.001588,
'D5',.364975,
'G',-.015795,
'H',-.274374,
'I',-1.065177,
'J',-.027991,
'N',.157622,
'NK',.763406,
'NR',.080558,
'P',-.961133,
'PE',-.19558,
'PH',.479081,
'PR',.134741,
'S',-.239287,
'SV',.475934,
'T1',.241061,
'T2',-.277572,
'T3',.901487,
'T4',-.0137,
'TS',.01362,
'U2',-.186914,
'X',1.301152,
'XX',-.11462,
'Z0',-.7141,
'Z4',-.004642,
'Z6',-.014541,
0)

Sunday, April 07, 2013

Logistic Regression Model Implemented in SQL

In a project, we need to deploy a logistic regression model into a production system that only takes SQL scripts as its input. Two functions come in handy, decode() and nvl(). Decode() converts categorical value into a weight and nvl() conveniently replaces null with a desired value. The following SQL scripts is similar to what we delivered.



select transaction_id,
(1/
(1+
exp(-(
nvl(AMT1*.000019199,0)+
nvl(AMT3*(-.00002155),0)+
decode(nvl(substr((TXN_CODE1),1,18),' '),
'XX',-.070935,
'57',-.192319,
'1',-.053794,
'81',-.010813,
'NR',-.079628,
'PD',-.102987,
'P',-1.388433,
'Z6',-.106081,
'01',-1.1528,
'Z4',-.004237,
'T1',.697737,
'AK',-.490381,
'U2',.063712,
'NK',.054354,
'PR',.205336,
'51',-.286213,
'N',.075582,
' ',-.330585,
0)+
decode(nvl(substr( trim(TXN_CODE2),1,18),' '),
'U',-.11176,
0)+
decode(nvl(substr( trim(TXN_CODE3),1,18),' '),
'1',-.642605,
0)+
decode(nvl(substr( trim(TXN_CODE4),1,18),' '),
'00',-.084517,
'10',.057248,
0)
-6.8190776
)
)
)
) as score from tbl_data;