py_script/task_tmp/tmp_tv_avg_ratings_fatt0.py

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

"""按月统计电视剧的收视情况

province_input: odl.ad_television
province_output: tmp.month_channel_stat

area_input: odl.area_ad_television
area_output: tmp.area_month_channel_stat
"""
import sys

from fty_util.common import Mysql, Util

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

class channel_history_month_stat():
    
    def province(self):
        # 清空电视台数据统计表
        conn = Mysql.createOfflineConn()
        sql = """
            truncate table tmp.month_channel_stat
        """
        Mysql.execute(sql, conn=conn)
        sql = """
            select distinct month from tmp.ad_television_month order by month asc
        """
        rows = Mysql.getAll(sql, conn=conn)
        for row in rows:
            month = row['month']
            print month
            month_max_date = Util.get_max_date_of_month(month)
            
            # 统计电视台当月电视剧收视数据
            sql = """
                select adt.channel, adt.theater_attribute, adt.tv_name, adt.tv_id,
                min(adt.tv_date) as tv_date, sum(adt.audience_rating) as sum_value, count(adt.id) as count_value from odl.ad_television adt 
                where adt.tv_date >= '%s' and adt.tv_date <= '%s' and adt.audience_rating > 0
                group by adt.channel, adt.theater_attribute, adt.tv_name, adt.tv_id
            """
            sql = sql % (month, month_max_date)
            rows_all = Mysql.getAll(sql, conn=conn)
            data_list = []
            sql_insert = """
                insert into tmp.month_channel_stat (channel, theater_attribute, tv_name, tv_id, tv_date, sum_value, count_value, year, month)
                values (%s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            year = month.year
            for row_all in rows_all:
                channel = row_all['channel']
                theater_attribute = row_all['theater_attribute']
                tv_name = row_all['tv_name']
                tv_id = row_all['tv_id']
                tv_date = row_all['tv_date']
                sum_value = row_all['sum_value']
                count_value = row_all['count_value']
                data_list.append((channel, theater_attribute, tv_name, tv_id, tv_date, sum_value, count_value, year, month))
            Mysql.insertMany(sql_insert, data_list, conn)
        Mysql.close(conn)

    def area(self):
        # 清空电视台数据统计表
        conn = Mysql.createOfflineConn()
        sql = """
            truncate table tmp.month_channel_stat
        """
        Mysql.execute(sql, conn=conn)
        sql = """
            select distinct month from tmp.ad_television_month order by month asc
        """
        rows = Mysql.getAll(sql, conn=conn)
        for row in rows:
            month = row['month']
            print month
            month_max_date = Util.get_max_date_of_month(month)
            
            # 统计电视台当月电视剧收视数据
            sql = """
                select adt.channel, adt.theater_attribute, adt.tv_name, adt.tv_id,
                min(adt.tv_date) as tv_date, sum(adt.audience_rating) as sum_value, count(adt.id) as count_value from odl.area_ad_television adt 
                where adt.tv_date >= '%s' and adt.tv_date <= '%s' and adt.audience_rating > 0
                group by adt.channel, adt.theater_attribute, adt.tv_name, adt.tv_id
            """
            sql = sql % (month, month_max_date)
            rows_all = Mysql.getAll(sql, conn=conn)
            data_list = []
            sql_insert = """
                insert into tmp.area_month_channel_stat (channel, theater_attribute, tv_name, tv_id, tv_date, sum_value, count_value, year, month)
                values (%s, %s, %s, %s, %s, %s, %s, %s, %s)
            """
            year = month.year
            for row_all in rows_all:
                channel = row_all['channel']
                theater_attribute = row_all['theater_attribute']
                tv_name = row_all['tv_name']
                tv_id = row_all['tv_id']
                tv_date = row_all['tv_date']
                sum_value = row_all['sum_value']
                count_value = row_all['count_value']
                data_list.append((channel, theater_attribute, tv_name, tv_id, tv_date, sum_value, count_value, year, month))
            Mysql.insertMany(sql_insert, data_list, conn=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_history_month_stat()
    try:
        getattr(obj, sys.argv[1])()
    except Exception, e:
        print e