昨天工作：
- inference：在相同规则采样的验证集的效果：在正负样本1:4的验证集上：
acc:0.86 auc:0.84 precision:0.60；在正负样本1:1数据集上：acc:0.84 auc:0.84 precision:0.87；模型表现比较正常；但是在外包标注的数据上的inference表现非常差，大多数样本都被网络认为是负样本； - 实验分析：考虑到网络在两个验证集上的效果差别比较大，在使用相同规则采样得到的验证集中的效果尚可的，说明网络有着比较不错的学习效果，但是在外包标注的数据上的效果不好，可能是训练集构造不合理；
今天计划：
通过词替换的方式重新构造训练集
对比了各种分词方式，使用segment_mainse的方式是最合适的，老爹鞋，T恤女
重新构造训练集
品牌词：search_kg:alinlp_brand_recognition()
常用表：
graph_embedding.jl_jingyan_query_related_top_query
graph_embedding.jl_jingyan_query_related_top_query_detailed
ds=20190819
关键词替换：
graph_embedding.jl_jingyan_query_related_top_query
create table hs_tmp_274 as select * from graph_embedding.jl_jingyan_query_related_top_query where ds = 20190819;
insert overwrite table hs_tmp_275 select query, search_kg:alinlp_segment(query, "MAINSE", "0", "1", " ", "true", ":", "|") as query_seg from hs_tmp_274;
insert overwrite table hs_tmp_276 select query, search_kg:alinlp_brand_recognition(query) as brand from hs_tmp_274;
insert overwrite table hs_tmp_277 select * from hs_tmp_280 where query like '%包包%';
create table hs_tmp_278 as select hs_return_brand(query, query_seg) as (query, brand) from hs_tmp_275;
create table hs_tmp_279 as select brand, count(*) as freq from hs_tmp_278 group by brand order by freq;
create table hs_tmp_280 as
select row_number()over() as index, c.query, c.brand, d.freq from
(select a.query, a.brand from hs_tmp_278 a join (select * from hs_tmp_274 limit 10000)b on a.query == b.query)c join hs_tmp_279 d on c.brand == d.brand;
发现将阈值设置在大于9是合适的，剩下7625条query可用，其余的2k多条数据还是使用之前的方式进行负样本采样
create table hs_tmp_281 as
select *,row_number() over (partition by brand order by cast(freq as bigint) desc) as group_freq from hs_tmp_280;
create table hs_tmp_282 as
select *,row_number() over (partition by brand order by cast(freq as bigint) desc) as group_freq from hs_tmp_278;
query 的 index 从这儿来
create table hs_tmp_283 as
select row_number()over() as index, c.query, c.brand, d.freq from
(select a.query, a.brand from hs_tmp_278 a join (select * from hs_tmp_274)b on a.query == b.query)c join hs_tmp_279 d on c.brand == d.brand;
create table hs_tmp_284 as
select *,row_number() over (partition by brand order by cast(freq as bigint) desc) as group_freq from hs_tmp_283;
create table hs_tmp_285 as
select *, cast(rand() * freq + 1 as bigint) as choose from hs_tmp_284;
create table hs_tmp_286 as
select a.*, b.query as query1 from hs_tmp_285 a join hs_tmp_285 b on a.brand == b.brand and a.group_freq == b.choose1;
insert into table hs_tmp_286 select a.*, b.query as query1 from hs_tmp_285 a join hs_tmp_285 b on a.brand == b.brand and a.group_freq == b.choose3;
create table hs_tmp_287 as
select distinct index, query, brand, query1 as choose_freq from hs_tmp_286;
create table hs_tmp_288 as
select *,row_number() over (partition by index order by query desc) as group_freq from hs_tmp_287;
drop table if exists graph_embedding.hs_tmp_289;
yes
create table if not exists graph_embedding.hs_tmp_289
as select se_keyword, item_id from (
select distinct se_keyword, item_id from (
select bi_udf:bi_split_value(se_keyword, item_list, ",") as (se_keyword, item_id)
from (
select distinct se_keyword, item_list
from graph_embedding.jl_jingyan_query_related_top_query_detailed
where ds=20190819 and page_seq=1
)a
)b
)c;
create table hs_tmp_290 as select b.index, b.query, b.brand, a.item_id from
hs_tmp_289 a join hs_tmp_283 b on a.se_keyword == b.query order by index;
create table hs_tmp_291 as select index, count(*) as freq from hs_tmp_290 group by index order by freq desc;
create table hs_tmp_292 as
select *,row_number() over (partition by index order by query desc) as group_freq from hs_tmp_290;
insert overwrite table hs_tmp_292 select * from hs_tmp_292 where group_freq < 8000;
create table hs_tmp_293 as select b.index, b.query as query_pos, b.brand, b.choose_freq as query_neg, a.index as index_neg from hs_tmp_283 a join hs_tmp_287 b on a.query == b.choose_freq;
create table hs_tmp_294 as select index, query, brand, item_id, 1 as label from hs_tmp_292 where index < 10001;
create table hs_tmp_295 as select a.index, a.query_pos, a.query_neg, a.index_neg, b.item_id from hs_tmp_293 a join hs_tmp_292 b on a.index_neg = b.index;
insert overwrite table hs_tmp_295 select * from hs_tmp_295 where index < 10001;
296可以使用这种方式进行负样本采样，297需要随机采样或者使用之前的采样方式
create table hs_tmp_296 as select distinct query from hs_tmp_280 where freq > 9;
create table hs_tmp_297 as select distinct query from hs_tmp_280 where freq < 10;
create table hs_tmp_298 as select * from hs_tmp_295 a join hs_tmp_296 b on a.query_pos == b.query;
create table hs_tmp_299 as select a.* from hs_tmp_294 a join hs_tmp_296 b on a.query == b.query;
create table hs_tmp_300 as select index, item_id, label from hs_tmp_299;
insert into table hs_tmp_300 select index, item_id, 2 as label from hs_tmp_298 where index == index_neg;
hs_tmp_298 : | index | query_pos | query_neg | index_neg | item_id | query |
hs_tmp_300: | index | item_id | label |
求差集
create table hs_tmp_301 as select c.* from
(select a.*, b.index as indexb, b.item_id as item_idb from hs_tmp_298 a left join hs_tmp_300 b on a.index == b.index and a.item_id == b.item_id)c where c.indexb is NULL and c.item_idb is NULL;
create table hs_tmp_302 as select index, item_id, 0 as label from hs_tmp_301;
insert overwrite table hs_tmp_302 select * from hs_tmp_302 distribute by random();
insert overwrite table hs_tmp_302 select * from hs_tmp_302 distribute by random();
insert overwrite table hs_tmp_302 select * from hs_tmp_302 distribute by random();
insert overwrite table hs_tmp_302 select * from hs_tmp_302 distribute by random();
insert overwrite table hs_tmp_302 select * from hs_tmp_302 distribute by random();
insert overwrite table hs_tmp_302 select * from hs_tmp_302 distribute by random();
insert overwrite table hs_tmp_302 select * from hs_tmp_302 distribute by random();
insert overwrite table hs_tmp_300 select * from hs_tmp_300 where label = 1;
insert into table hs_tmp_300 select * from hs_tmp_302 limit 53504615;
insert overwrite table hs_tmp_300 select * from hs_tmp_300 distribute by random();
insert overwrite table hs_tmp_300 select * from hs_tmp_300 distribute by random();
insert overwrite table hs_tmp_300 select * from hs_tmp_300 distribute by random();
insert overwrite table hs_tmp_300 select * from hs_tmp_300 distribute by random();
insert overwrite table hs_tmp_300 select * from hs_tmp_300 distribute by random();
构造新的query，item数据集
create table hs_tmp_303 as select a.item_id, b.title from
(select distinct item_id from hs_tmp_300) a JOIN
(
select item_id, title from tbcdm.dim_tb_itm
where ds=MAX_PT('tbcdm.dim_tb_itm') and is_online="Y"
)b on a.item_id=b.item_id;
create table hs_tmp_304 as select a.index, b.query from
(select distinct index from hs_tmp_300)a join hs_tmp_283 b on a.index == b.index;
create table hs_tmp_305 as select c., d.title from
(select a., b.query from hs_tmp_300 a join hs_tmp_304 b on a.index == b.index)c join hs_tmp_303 d on c.item_id == d.item_id;
