ClickStat业务

clickstat的源头：

#######################################
#       cn clickstat colletction      #
#######################################
     cnstatweb1 cnstatweb2
     cnstatweb3 cnstatweb4
     dw-uslogin

 

clickstat的文件存储：

for day in 19 20 21 22
do
    mkdir /home/admin/xuyuqin/1688_click/2011-08-$day
    cp /mnt/nas1/cn-cookie/2011-08-$day/cnstat* /home/admin/xuyuqin/1688_click/2011-08-$day/
done

 

日志举例：

http://stat.china.alibaba.com/bt/1688_click.html?page=7&objectId=587742604&recId=1011&alg=010801&objectType=offer&st_page_id=127001128252932963411282530204216586914395&ver=30&time=1282531436425

 

clickstat日志底层逻辑：

SQL>

SELECT DISTINCT owner, table_name FROM ALL_TABLES WHERE table_name LIKE UPPER('%clickstat%') and owner = 'CNLOG';

OWNER                          TABLE_NAME
------------------------------ ------------------------------
CNLOG                          CLICKSTAT_URL_DIMT0
CNLOG                          CLICKSTAT_PROJECT_DIMT0
CNLOG                          CLICKSTAT_QUERY_DIMT0
CNLOG                          CLICKSTAT_FATDT0

 

col PROJECT_NAME for a20
col CLICKSTAT_URL for a80
col CLICKSTAT_QUERY_NAME for a20
col CLICKSTAT_QUERY_DESC for a30
SELECT /*+ PARALLEL(a,4) */
    a.PROJECT_NAME,
    b.CLICKSTAT_URL,
    c.CLICKSTAT_QUERY_NAME,
    c.CLICKSTAT_QUERY_DESC,
    c.IS_USE
FROM cnlog.CLICKSTAT_PROJECT_DIMT0 a,
    cnlog.CLICKSTAT_URL_DIMT0 b,
    cnlog.CLICKSTAT_QUERY_DIMT0 c
where a.PROJECT_ID = b.PROJECT_ID
  and b.CLICKSTAT_URL_ID = c.CLICKSTAT_URL_ID
  and b.CLICKSTAT_URL like '%1688%';

 

clickstat的上层汇总逻辑：

方法一：在开发环境下grep

PT_dev_DB_Client 10.20.151.1

dwapp@pttest1:/home/dwapp>

grep "c02_clickstat_fatdt0" /home/dwapp/ccbu_rebuild/cn_product_task/bdl/*.pl

/home/dwapp/ccbu_rebuild/cn_product_task/bdl/exp_cn_click_attr.pl:      FROM    odl.c02_clickstat_fatdt0
/home/dwapp/ccbu_rebuild/cn_product_task/bdl/exp_cn_page_click.pl:              FROM    odl.c02_clickstat_fatdt0
/home/dwapp/ccbu_rebuild/cn_product_task/bdl/exp_cn_se_basic.pl:        FROM    odl.c02_clickstat_fatdt0 a
/home/dwapp/ccbu_rebuild/cn_product_task/bdl/exp_cn_se_filt_tmp.pl:     FROM    odl.c02_clickstat_fatdt0 
/home/dwapp/ccbu_rebuild/cn_product_task/bdl/exp_cn_se_filt_tmp.pl:     FROM    odl.c02_clickstat_fatdt0 
/home/dwapp/ccbu_rebuild/cn_product_task/bdl/exp_cn_se_filt_tmp.pl:     FROM    odl.c02_clickstat_fatdt0 
/home/dwapp/ccbu_rebuild/cn_product_task/bdl/exp_cn_se_filt_tmp.pl:     FROM    odl.c02_clickstat_fatdt0 
/home/dwapp/ccbu_rebuild/cn_product_task/bdl/exp_cn_se_filt_tmp.pl:     FROM    odl.c02_clickstat_fatdt0 

 

方法二：在windows下将SVN代码update后，搜索框：c02_clickstat_fatdt0 *.pl

查看后具体关注以下两个存储过程：

bdl.exp_cn_page_click.pl —— 将公共字段：stat_date ,click_id ,click_page_id ,cookie_id ,clickstat_url_id ,click_time ,refer_info ,dw_ins_date整合成为横表

bdl.exp_cn_click_attr.pl    ——各业务依然是纵表存放

odl.c02_clickstat_fatdt1    ——将日志的查询querystring单独存放，不用进行纵转横，方便处理

例如：select count(1) from odl.c02_clickstat_fatdt1 where yyyymmdd >= cast('20110817' as date) and yyyymmdd < cast('20110818' as date) and query_string like '%page=7%recId=1011%'

 

个性化推荐字段逻辑：

URL包含：

1688_click

page=7
recId=1011

转载于:https://www.cnblogs.com/include/archive/2011/08/23/2151043.html