我们产品执行需要一个定时的报表数据,因此我们需要编写一个数据库脚本给DBA,每个月初去执行然后导出相关的数据提交给相关方。这里主要练习了输出文件和时间函数:
SELECT
ci.instance_id AS creative_instance_id,
ci.instance_name AS creative_instance_name,
at.task_id AS task_id,
at.task_name AS task_name,
atci.geshu_monitor_url AS geshu_monitor_url,
atci.geshu_click_url AS geshu_click_url,
atci.monitor_url AS monitor_url,
atci.click_url AS click_url,
at.start_time AS task_start_time,
at.end_time AS task_end_time,
at.create_time AS task_create_time,
ca.campaign_id AS campaign_id,
ca.campaign_name AS campaign_name,
ad.advertiser_id AS advertiser_id,
ad.advertiser_name AS advertiser_name,
me.media_code AS media_code,
me.media_name AS media_name
FROM
creative_instance ci,
ad_task_creative_instance atci,
ad_task at,
campaign ca,
advertiser ad,
media me
WHERE ci.instance_id = atci.instance_id
AND atci.task_id = at.task_id
AND at.campaign_id = ca.campaign_id
AND ci.media_id = me.id
AND ca.advertiser_id = ad.advertiser_id
AND ci.is_delete = 0
AND DATE_FORMAT(at.create_time, '%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m')
ORDER BY task_create_time ASC
INTO OUTFILE 'GPMP_BI_Month_report.txt' FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n' ;