py_script/task_odl/odl_area_ad_television.py

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

"""ad_television表数据处理

"""

import sys

from fty_util.common import Mysql

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

"""
从yxb.ad_television_tetv
提取数据插入到odl.area_ad_television 表中，作为数据分析来源数据
"""

conn = Mysql.createOfflineConn()

sql = """
    truncate table odl.area_ad_television
"""
Mysql.execute(sql, conn=conn)

m = 0
n = 50000

sql_count = """
    select count(id) from yxb.ad_television_tetv
"""
count = Mysql.getOne(sql_count, conn=conn)[0]
while m <= count + n:
    sql = """
        insert into odl.area_ad_television (television_id, tv_id, tv_name, epi_num, host, channel, tv_date, weekday, start_time, end_time, \
            theater_attribute, property, is_repeat, city, area, audience_num, audience_rating, avg_num, avg_rating, \
            market_rating, avg_fans, avg_view_time) \
        select aty.id, atl.id, aty.tv_name, aty.epi_num, aty.host, aty.channel, aty.tv_date, aty.weekday, aty.start_time, aty.end_time, aty.theater_attribute, \
        aty.property, aty.is_repeat, aty.city, \
        ary.area, ary.audience_num, ary.audience_rating, ary.avg_num, ary.avg_rating, ary.market_rating, ary.avg_fans, ary.avg_view_time \
        from yxb.ad_television_tetv aty \
        left join yxb.ad_rating_tetv ary on ary.tv_id = aty.id and ary.area like 'CSM5%%'
        left join yxb.ad_tv_lib atl on atl.tv_name = aty.tv_name
        limit %s, %s
    """
    sql = sql % (m, n)
    print sql
    Mysql.execute(sql, conn=conn)
    m += n

sql = """
    update odl.area_ad_television
    set theater_attribute = 
    (
    case 
    when start_time >= '00:00:00' and end_time <= '6:00:00' then '凌晨剧场'
    when start_time >= '7:00:00' and end_time < '9:00:00' then '早间剧场'
    when start_time >= '9:00:00' and end_time <= '12:00:00' then '上午剧场'
    when start_time >= '14:00:00' and end_time < '18:00:00' then '下午剧场'
    when start_time >= '19:30:00' and end_time <= '21:30:00' then '黄金剧场'
    when (start_time >= '18:00:00' and end_time < '19:30:00') or (start_time > '21:30:00' and end_time < '24:00:00') then '晚间剧场'
    end
    )
    where theater_attribute is null or theater_attribute = ''
"""

Mysql.execute(sql, conn=conn)

Mysql.close(conn)