123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117 |
- #!/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
|