12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667 |
- #!/usr/bin/env python
- #coding=utf-8
- """ad_television表数据增量更新
- """
- import datetime
- import sys
- from fty_util.common import Mysql
- reload(sys)
- sys.setdefaultencoding('utf8')
- conn = Mysql.createOfflineConn()
- sql = """
- select max(tv_date) as max_date from odl.area_ad_television
- """
- row = Mysql.getOne(sql, conn=conn)
- max_date = row[0]
- # year = datetime.datetime.strptime(max_date, '%Y-%m-%d').year
- 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
- where aty.tv_date > '%s'
- """
- sql = sql % (max_date)
- Mysql.execute(sql, conn=conn)
- """
- 凌晨剧场 0:00 - 6:00
- 早间剧场:7:00-9:00
- 上午剧场 9:00-12:00
- 下午剧场 14:00-18:00
- 晚间剧场 18:00-24:00
- 黄金剧场:19:30-21:30
- """
- 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 tv_date > '%s' and (theater_attribute is null or theater_attribute = '')
- """
- sql = sql % (max_date)
- Mysql.execute(sql, conn=conn)
- Mysql.close(conn)
|