当前位置:首页 > 技术文章 > openx > openx改造工程二: pv统计中memcached的运用(2.8新版本)
openx改造工程二: pv统计中memcached的运用(2.8新版本)
文章来源:本站原创  浏览次数:214  发布日期:2013-04-16
主要改动是这两个文件

/plugins/deliveryLog/oxLogImpression/logTmpression.delivery.php

/lib/OX/Dal/Maintenance/Statistics.php

在第一个文件里改为将统计记录写在memcached中,键值中所用时间和维护设置的频率有关系,60分钟就是整小时,也是默认的,当然也可以改,最小是每5分钟执行。

第二个文件,主要做的工作就是在由ox_data_bkt_m表中统计数据到ox_data_intermediate_ad之前从memcached中读出数据,插入到ox_data_bkt_m表中,使流程走通。该计划任务的方法,是由投放广告页面加载广告时触发的,当然要大于设置的频道才会执行。
其后执行的sql方法如下
INSERT INTO
               ox_data_intermediate_ad
               (date_time, ad_id, zone_id, clicks, impressions, requests, operation_interval, operation_interval_id, interval_start, interval_end, creative_id, updated)
           SELECT
               date_time, ad_id, zone_id, SUM(clicks) AS clicks, SUM(impressions) AS impressions, SUM(requests) AS requests, 5 AS operation_interval, 1172 AS operation_interval_id, '2011-11-03 01:40:00' AS interval_start, '2011-11-03 01:44:59' AS interval_end, 0 AS creative_id, '2011-11-03 02:12:06' AS updated
           FROM
               (
               SELECT
                   interval_start AS date_time, creative_id AS ad_id, zone_id AS zone_id, count AS clicks, 0 AS impressions, 0 AS requests
               FROM
                   ox_data_bkt_c
               WHERE
                   interval_start >= '2011-11-03 01:40:00'
                   AND
                   interval_start <= '2011-11-03 01:44:59' UNION ALL
               SELECT
                   interval_start AS date_time, creative_id AS ad_id, zone_id AS zone_id, 0 AS clicks, count AS impressions, 0 AS requests
               FROM
                   ox_data_bkt_m
               WHERE
                   interval_start >= '2011-11-03 01:40:00'
                   AND
                   interval_start <= '2011-11-03 01:44:59' UNION ALL
               SELECT
                   interval_start AS date_time, creative_id AS ad_id, zone_id AS zone_id, 0 AS clicks, 0 AS impressions, count AS requests
               FROM
                   ox_data_bkt_r
               WHERE
                   interval_start >= '2011-11-03 01:40:00'
                   AND
                   interval_start <= '2011-11-03 01:44:59'
               ) AS virtual_table
           GROUP BY
               date_time, ad_id, zone_id

上面的sql是从几个临时表读取数据并整理到ox_data_

intermediate_ad,整理完成之后,就是对临时表的清理

DELETE FROM
                ox_data_bkt_c
            WHERE
                interval_start <= '2011-11-03 11:29:59'
                AND
                interval_start >= '2011-11-03 11:25:00'
 
            DELETE FROM
                ox_data_bkt_m
            WHERE
                interval_start <= '2011-11-03 11:29:59'
                AND
                interval_start >= '2011-11-03 11:25:00'
 
            DELETE FROM
                ox_data_bkt_r
            WHERE
                interval_start <= '2011-11-03 11:29:59'
                AND
                interval_start >= '2011-11-03 11:25:00'

具体细节都已测试,改用memcached统计正常,并且openx对广告banner调用部分,也有相应插件可以启用memcached,也可以确保速度

openx系统默认时间是UTC,在库中记录的也是这个时区的数据,但在广告后台管理中,不同用户可以设置不同的时区进行数据查看,这样子,也就更灵活,更方便了。所以这块儿不用修改


原文来自:openx改造工程二: pv统计中memcached的运用(2.8新版本) http://www.redyun.net/technology/82.html

红云案例Case