py_script/task_tmp/tmp_year_channel_avg_ratings_stat_by_tv.py

#!/usr/bin/env python
#coding=utf-8

"""电视台近一年平均收视率

计算方法：取出电视台的一年收视数据，求平均数
avg(audience_rating) group by channel, theater_attribute

province_input: odl.ad_television
province_output: tmp.channel_avg_ratings

area_input: odl.area_ad_television
area_output: odl.area_channel_avg_ratings
"""
import sys

from fty_util.common import Mysql, Util

reload(sys)
sys.setdefaultencoding('utf8')

class channel_avg_ratings():
    
    # 央卫视频道
    def province(self):
        conn = Mysql.createOfflineConn()
        first_day = Util.get_first_date_of_yesterday()
        one_year_ago = Util.get_max_date_of_one_year_ago(first_day)
        # 计算最近一年电视台的平均收视率
        station_dict = {}
        sql = """
            select channel, theater_attribute, avg(audience_rating) as avg_rate from odl.ad_television
            where tv_date > '%s' and tv_date <= '%s'
            group by channel, theater_attribute
        """
        sql = sql % (one_year_ago, first_day)
        rows = Mysql.getAll(sql, conn=conn)
        data_list = []
        sql = """
            insert into tmp.channel_avg_ratings (channel, theater_attribute, value) values (%s, %s, %s)
        """
        for row in rows:
            channel = row['channel']
            theater_attribute = row['theater_attribute']
            avg_rate = row['avg_rate']
            data_list.append((channel, theater_attribute, avg_rate))
        Mysql.insertMany(sql, data_list, conn)
        Mysql.close(conn)

    # 省级地面频道
    def area(self):
        conn = Mysql.createOfflineConn()
        first_day = Util.get_first_date_of_yesterday()
        one_year_ago = Util.get_max_date_of_one_year_ago(first_day)
        # 计算最近一年电视台的平均收视率
        station_dict = {}
        sql = """
            select channel, theater_attribute, avg(audience_rating) as avg_rate from odl.area_ad_television
            where tv_date > '%s' and tv_date <= '%s'
            group by channel, theater_attribute
        """
        sql = sql % (one_year_ago, first_day)
        rows = Mysql.getAll(sql, conn=conn)
        data_list = []
        sql = """
            insert into tmp.area_channel_avg_ratings (channel, theater_attribute, value) values (%s, %s, %s)
        """
        for row in rows:
            channel = row['channel']
            theater_attribute = row['theater_attribute']
            avg_rate = row['avg_rate']
            data_list.append((channel, theater_attribute, avg_rate))
        Mysql.insertMany(sql, data_list, conn)
        Mysql.close(conn)

if __name__ == '__main__':
    if len(sys.argv) != 2:
        print '没有输入参数，退出'
        sys.exit(0)
    print 'method name is ' + sys.argv[1]
    obj = channel_avg_ratings()
    try:
        getattr(obj, sys.argv[1])()
    except Exception, e:
        print e