[Perceptron]

model building

drop table news20b_perceptron_model1;
create table news20b_perceptron_model1 as
select 
 feature,
 voted_avg(weight) as weight
from 
 (select 
     train_perceptron(add_bias(features),label) as (feature,weight)
  from 
     news20b_train_x3
 ) t 
group by feature;

prediction

create or replace view news20b_perceptron_predict1 
as
select
  t.rowid, 
  sum(m.weight * t.value) as total_weight,
  case when sum(m.weight * t.value) > 0.0 then 1 else -1 end as label
from 
  news20b_test_exploded t LEFT OUTER JOIN
  news20b_perceptron_model1 m ON (t.feature = m.feature)
group by
  t.rowid;

evaluation

create or replace view news20b_perceptron_submit1 as
select 
  t.label as actual, 
  p.label as predicted
from 
  news20b_test t JOIN news20b_perceptron_predict1 p
    on (t.rowid = p.rowid);

select 
  sum(if(actual = predicted, 1, 0)) / count(1) as accuracy
from
  news20b_perceptron_submit1;

0.9459567654123299

[Passive Aggressive]

model building

drop table news20b_pa_model1;
create table news20b_pa_model1 as
select 
 feature,
 voted_avg(weight) as weight
from 
 (select 
     train_pa(add_bias(features),label) as (feature,weight)
  from 
     news20b_train_x3
 ) t 
group by feature;

prediction

create or replace view news20b_pa_predict1 
as
select
  t.rowid, 
  sum(m.weight * t.value) as total_weight,
  case when sum(m.weight * t.value) > 0.0 then 1 else -1 end as label
from 
  news20b_test_exploded t LEFT OUTER JOIN
  news20b_pa_model1 m ON (t.feature = m.feature)
group by
  t.rowid;

evaluation

create or replace view news20b_pa_submit1 as
select 
  t.label as actual, 
  pd.label as predicted
from 
  news20b_test t JOIN news20b_pa_predict1 pd 
    on (t.rowid = pd.rowid);

select 
  sum(if(actual = predicted, 1, 0)) / count(1) as accuracy
from
  news20b_pa_submit1;

0.9603682946357086

[Passive Aggressive (PA1)]

model building

drop table news20b_pa1_model1;
create table news20b_pa1_model1 as
select 
 feature,
 voted_avg(weight) as weight
from 
 (select 
     train_pa1(add_bias(features),label) as (feature,weight)
  from 
     news20b_train_x3
 ) t 
group by feature;

prediction

create or replace view news20b_pa1_predict1 
as
select
  t.rowid, 
  sum(m.weight * t.value) as total_weight,
  case when sum(m.weight * t.value) > 0.0 then 1 else -1 end as label
from 
  news20b_test_exploded t 
  LEFT OUTER JOIN news20b_pa1_model1 m 
    ON (t.feature = m.feature)
group by
  t.rowid;

evaluation

create or replace view news20b_pa1_submit1 as
select 
  t.label as actual, 
  p.label as predicted
from 
  news20b_test t 
  JOIN news20b_pa1_predict1 p 
    on (t.rowid = p.rowid);

select 
  sum(if(actual = predicted, 1, 0)) / count(1) as accuracy
from 
  news20b_pa1_submit1;

0.9601681345076061

[Passive Aggressive (PA2)]

model building

drop table news20b_pa2_model1;
create table news20b_pa2_model1 as
select 
 feature,
 voted_avg(weight) as weight
from 
 (select 
     train_pa2(add_bias(features),label) as (feature,weight)
  from 
     news20b_train_x3
 ) t 
group by feature;

prediction

create or replace view news20b_pa2_predict1 
as
select
  t.rowid, 
  sum(m.weight * t.value) as total_weight,
  case when sum(m.weight * t.value) > 0.0 then 1 else -1 end as label
from 
  news20b_test_exploded t LEFT OUTER JOIN
  news20b_pa2_model1 m ON (t.feature = m.feature)
group by
  t.rowid;

evaluation

create or replace view news20b_pa2_submit1 as
select 
  t.label as actual, 
  pd.label as predicted
from 
  news20b_test t JOIN news20b_pa2_predict1 pd 
    on (t.rowid = pd.rowid);

select 
  sum(if(actual = predicted, 1, 0)) / count(1) as accuracy
from 
  news20b_pa2_submit1;

0.9597678142514011