バグ #90: DB負荷調査 - i-generation - Redmine

バグ #90

DB負荷調査

山本義治が10年以上前に追加. ほぼ10年前に更新.

ステータス:

新規

開始日:

2014/09/04

優先度:

通常

期日:

担当者:

進捗 %:

カテゴリ:

作業時間の記録:

1.00時間

対象バージョン:

説明

断続的にDB負荷がかかっている。
クエリー最適化が必要

mysqlスレーブエラー
↓
mysqlCPU負荷10%超え

履歴

#1 山本義治が10年以上前に更新

#各種ログテーブルindex最適化
click_log_YYYYMM
action_log_YYYYMM
access_action_log_YYYYMM
stat_log_***_YYYYMM

#2 山本義治が10年以上前に更新

#コンバージョン処理
　→クリックログ参照期間を直近３ヶ月に

[root@web1 ac]# diff action.php /www/aff/ad/www/ac/
186,187c186
<     //for($stat_month=date("Ym");$stat_month>=$START_MONTH;$stat_month=date("Ym",mktime(0,0,0,substr($stat_month,4,2)-1,1,substr($stat_month,0,4)))){
<     for($stat_month=date("Ym");$stat_month>=date("Ym",strtotime("-90 day"));$stat_month=date("Ym",mktime(0,0,0,substr($stat_month,4,2)-1,1,substr($stat_month,0,4)))){
---
>     for($stat_month=date("Ym");$stat_month>=$START_MONTH;$stat_month=date("Ym",mktime(0,0,0,substr($stat_month,4,2)-1,1,substr($stat_month,0,4)))){

→商用リリース済み

#3 山本義治が10年以上前に更新

#退会処理
　→取得件数制限

[root@web1 ac]# diff resign.php /www/aff/ad/www/ac/
77c77
<         $sql = "select * from action_log_".$i." where c_id = '$c_id' and user_id = '$user_id' and status != 4 and status != 2 limit 1";
---
>         $sql = "select * from action_log_".$i." where c_id = '$c_id' and user_id = '$user_id' and status != 4 and status != 2";
79c79
<         $sql = "select * from action_log_".$i." where c_id = '$c_id' and subscriber = '$sub_id' and status != 4 and status != 2 limit 1";
---
>         $sql = "select * from action_log_".$i." where c_id = '$c_id' and subscriber = '$sub_id' and status != 4 and status != 2";
81c81
<         $sql = "select * from action_log_".$i." where c_id = '$c_id' and aff_id = '$afid' and status != 4 and status != 2 limit 1";
---
>         $sql = "select * from action_log_".$i." where c_id = '$c_id' and aff_id = '$afid' and status != 4 and status != 2";
119c119
<     $sql = "select * from click_log_".$stat_month." where aff_id = '$aff_id' limit 1";
---
>     $sql = "select * from click_log_".$stat_month." where aff_id = '$aff_id'";

→商用リリース済み

#4 山本義治が10年以上前に更新

#退会時キックバック
　→対象メディアのみクリックログを参照するように変更

[root@web1 ac]# diff resign.php /www/aff/ad/www/ac/
117a118,127
> for($stat_month=date("Ym");$stat_month>=$START_MONTH;$stat_month=date("Ym",mktime(0,0,0,substr($stat_month,4,2)-1,1,substr($stat_month,0,4)))){
>     $sql = "select * from click_log_".$stat_month." where aff_id = '$aff_id' limit 1";
>     $res = __mysql_query_ex($sql);
>     $numrows = mysql_num_rows($res);
>     if($numrows){
>         $row = mysql_fetch_array($res);
>         $media_data    = $row["media_data"];
>         break;
>     }
> }
135,144d144
<             for($stat_month=date("Ym");$stat_month>=$START_MONTH;$stat_month=date("Ym",mktime(0,0,0,substr($stat_month,4,2)-1,1,substr($stat_month,0,4)))){
<                 $sql = "select * from click_log_".$stat_month." where aff_id = '$aff_id' limit 1";
<                 $res = __mysql_query_ex($sql);
<                 $numrows = mysql_num_rows($res);
<                 if($numrows){
<                     $row = mysql_fetch_array($res);
<                     $media_data    = $row["media_data"];
<                     break;
<                 }
<             }

→商用リリース済み

#5 山本義治が10年以上前に更新

#高負荷クエリー

運営者管理画面＞登録ログ詳細
/www/aff/admin/www/stat/action_log_detail.php

select * from action_log_201409 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201408 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201407 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201406 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201405 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201404 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201403 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201402 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201401 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201312 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201311 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201310 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201309 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201308 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201307 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201306 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201305 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201304 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201303 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201302 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201301 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201212 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201211 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201210 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201209 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201208 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201207 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201206 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201205 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201204 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201203 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201202 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201201 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201112 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201111 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201110 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' union all select * from action_log_201109 where order_date >= '2011-09-04 00:00:00' and order_date <= '2014-09-04 23:59:59' order by order_date desc limit 0,30;

集計プログラム
/www/aff/stat/*.php

SELECT TB1.m_owner_id, TB1.m_id, TB1.asp_id, TB1.listing_id, TB1.t_id FROM (SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201409 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201408 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201407 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201406 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201405 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201404 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201403 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201402 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201401 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201312 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201311 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201310 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201309 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201308 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201307 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201306 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201305 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201304 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201303 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201302 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201301 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201212 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201211 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201210 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201209 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201208 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201207 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201206 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201205 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201204 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201203 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201202 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201201 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201112 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201111 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201110 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201109 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201108 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201107 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201106 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201105 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201104 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201103 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0' UNION ALL SELECT m_owner_id, m_id, asp_id, listing_id, t_id FROM click_log_201102 WHERE c_id = '235' AND subscriber = '07012330584060_nz.ezweb.ne.jp' AND status = '0') AS TB1 GROUP BY m_id,asp_id,listing_id,t_id

#6 山本義治が10年以上前に更新

#重いselect文でテーブルロックしている場合にもinsertできるようにする

コンカレントインサートオプション
https://blogs.oracle.com/takemura/entry/myisam%E3%82%B9%E3%83%88%E3%83%AC%E3%83%BC%E3%82%B8%E3%82%A8%E3%83%B3%E3%82%B8%E3%83%B3%E3%81%AE%E3%82%B3%E3%83%B3%E3%82%AB%E3%83%AC%E3%83%B3%E3%83%88%E3%82%A4%E3%83%B3%E3%82%B5%E3%83%BC%E3%83%88%E6%A9%9F%E8%83%BD%E3%81%AB%E3%81%A4%E3%81%84%E3%81%A6
http://dev.mysql.com/doc/refman/5.1-olh/ja/concurrent-inserts.html

#7 山本義治が10年以上前に更新

#退会処理スレーブ参照

[admin@web1 ac]$ diff resign.php /www/aff/ad/www/ac/
69d68
<     $mysql_id2 = __mysql_con_ex2();
83c82
<     $res = __mysql_query_ex2($sql);
---
>     $res = __mysql_query_ex($sql);

→商用リリース済み

#8 山本義治が10年以上前に更新

#集計プログラム
#ポイントバック再通知プログラム
　→スレーブ参照するように変更

/www/aff/stat/stat*.php
/www/aff/admin/cron/point_back_retry.php

→商用リリース済み

#9 山本義治が10年以上前に更新

#広告詳細レポート参照期間変更

[admin@web1 content]$ diff report.php /www/aff/admin/www/stat/content/
35c35
< $sql = "select content_name,status,click_cost_cl,entry_date,
---
> $sql = "select content_name,status,click_cost_cl,
48c48
<     list($content_name,$status,$click_cost_db,$entry_date_db,
---
>     list($content_name,$status,$click_cost_db,
162c162
< $year_age_month = substr($entry_date_db,0,4).substr($entry_date_db,5,2);
---
> $year_age_month = $START_MONTH;

#10 山本義治が10年以上前に更新

#集計プログラム
　→参照期間変更

/www/aff/stat/stat.php
/www/aff/stat/stat_asp.php
/www/aff/stat/stat_listing.php

[root@web1 stat]# diff stat.php /www/aff/stat/
10c10
< require("/www/aff_test_new/ad/const.inc");
---
> require("/www/aff/ad/const.inc");
181c181
<                     $sql = "select c_owner_id,content_name,click_cost,click_cost_cl,action_cost,action_cost_cl,action_ratio,action_ratio_cl,action_check,entry_date from content where c_id = '$c_id'";
---
>                     $sql = "select c_owner_id,content_name,click_cost,click_cost_cl,action_cost,action_cost_cl,action_ratio,action_ratio_cl,action_check from content where c_id = '$c_id'";
186c186
<                         list($c_owner_id[$c_id],$content_name[$c_id],$banner_click_cost[$c_id],$banner_click_cost_cl[$c_id],$action_cost[$c_id],$action_cost_cl[$c_id],$action_ratio[$c_id],$action_ratio_cl[$c_id],$action_check[$c_id],$entry_date[$c_id]) = mysql_fetch_array($res);
---
>                         list($c_owner_id[$c_id],$content_name[$c_id],$banner_click_cost[$c_id],$banner_click_cost_cl[$c_id],$action_cost[$c_id],$action_cost_cl[$c_id],$action_ratio[$c_id],$action_ratio_cl[$c_id],$action_check[$c_id]) = mysql_fetch_array($res);
339c339
<                     $sql = "select c_owner_id,content_name,click_cost,click_cost_cl,entry_date,
---
>                     $sql = "select c_owner_id,content_name,click_cost,click_cost_cl,
361c361
<                         list($c_owner_id[$c_id],$content_name[$c_id],$banner_click_cost[$c_id],$banner_click_cost_cl[$c_id],$entry_date[$c_id],
---
>                         list($c_owner_id[$c_id],$content_name[$c_id],$banner_click_cost[$c_id],$banner_click_cost_cl[$c_id],
617,618c617
<                         $stat_start_month = substr($entry_date[$c_id],0,4).substr($entry_date[$c_id],5,2);
<                         for($stat_month=date("Ym");$stat_month>=$stat_start_month;$stat_month=date("Ym",mktime(0,0,0,substr($stat_month,4,2)-1,1,substr($stat_month,0,4)))){
---
>                         for($stat_month=date("Ym");$stat_month>=$START_MONTH;$stat_month=date("Ym",mktime(0,0,0,substr($stat_month,4,2)-1,1,substr($stat_month,0,4)))){

→商用リリース済み

#11 山本義治が10年以上前に更新

#action_log_merge削除

[admin@web1 admit]$ diff admit_ok.php /www/aff/user/www/merchant/admit/
69a70,76
> 
> $sql = "select count(*) from action_log_merge $query";
> //print("$sql<br>");
> $res = __mysql_query_ex( $sql );
> list($total_numrows) = mysql_fetch_array($res);

→商用リリース済み

#12 山本義治が10年以上前に更新

#新クライアント管理画面トップページ
　→アクションログテーブル参照期間変更

[admin@web1 report]$ diff top.php /www/aff/user/www/merchant/report
169,172c169,171
<     if($stat_month >= $searchStartYM)
<         $resign_sql = $resign_sql."SELECT tb.m_owner_id, tb.asp_id, tb.listing_id, DATE_FORMAT(tb.resign_date,'%Y%m') as resign_month from action_log_".$stat_month." as tb
<             LEFT JOIN media_owner ON (media_owner.m_owner_id = tb.m_owner_id) LEFT JOIN media_group ON (media_owner.g_id = media_group.g_id) LEFT JOIN asp ON (asp.asp_id = tb.asp_id)
<             LEFT JOIN listing ON (listing.listing_id = tb.listing_id) WHERE tb.status=4".$where_state." UNION ALL \n";
---
>     $resign_sql = $resign_sql."SELECT tb.m_owner_id, tb.asp_id, tb.listing_id, DATE_FORMAT(tb.resign_date,'%Y%m') as resign_month from action_log_".$stat_month." as tb
>         LEFT JOIN media_owner ON (media_owner.m_owner_id = tb.m_owner_id) LEFT JOIN media_group ON (media_owner.g_id = media_group.g_id) LEFT JOIN asp ON (asp.asp_id = tb.asp_id)
>         LEFT JOIN listing ON (listing.listing_id = tb.listing_id) WHERE tb.status=4".$where_state." UNION ALL \n";

→商用リリース

#13 山本義治がほぼ10年前に更新

#高負荷クエリー

クライアント管理画面月別レポート

SELECT tb.YEARMONTH
    ,tb.click_count
    ,tb.click_unique_count
    ,tb.action_count
    ,CASE WHEN tb.click_count = 0 THEN 0 ELSE truncate(100*tb.action_count/tb.click_count+0.005,2) END as cvr
    ,tb.total_cost
    ,CASE WHEN tb.action_count = 0 THEN 0 ELSE truncate(tb.total_cost/tb.action_count+0.005,2) END as cpa
    ,tb.indirect_effect
    ,CASE WHEN tb_r.resign_count IS NULL THEN 0 ELSE tb_r.resign_count END as resign_count
    ,CASE WHEN tb.action_count = 0 OR tb_r.resign_count IS NULL THEN 0 ELSE truncate(100*tb_r.resign_count/tb.action_count+0.005,2) END as resign_percent
    FROM (SELECT TB1.YEARMONTH, sum(TB1.click_count) AS click_count, sum(TB1.click_unique_count) AS click_unique_count, sum(TB1.action_count) AS action_count, sum(TB1.total_cost) as total_cost, sum(TB1.indirect_effect) AS indirect_effect
        FROM(
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_201501 LEFT JOIN media_owner on (stat_log_201501.m_owner_id=media_owner.m_owner_id) LEFT JOIN media_group on (media_owner.g_id=media_group.g_id)  WHERE c_owner_id=298 AND c_id=1424
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_asp_201501 LEFT JOIN asp on (stat_log_asp_201501.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND (asp.asp_media_flg is null OR asp.asp_media_flg = 0)
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH,sum(click_count) as click_count,sum(click_count) as click_unique_count,sum(action_count) as action_count,sum(click_cost_cl)+sum(action_cost_cl) as total_cost,0 as indirect_effect
FROM stat_log_asp_media_201501 LEFT JOIN asp on (stat_log_asp_media_201501.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND asp.asp_media_flg = 1
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_listing_201501 LEFT JOIN listing on (stat_log_listing_201501.listing_id=listing.listing_id)  WHERE c_owner_id=298 AND c_id=1424
        ) AS TB1
        WHERE TB1.YEARMONTH is not null
        GROUP BY TB1.YEARMONTH UNION ALL 
SELECT TB1.YEARMONTH, sum(TB1.click_count) AS click_count, sum(TB1.click_unique_count) AS click_unique_count, sum(TB1.action_count) AS action_count, sum(TB1.total_cost) as total_cost, sum(TB1.indirect_effect) AS indirect_effect
        FROM(
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_201412 LEFT JOIN media_owner on (stat_log_201412.m_owner_id=media_owner.m_owner_id) LEFT JOIN media_group on (media_owner.g_id=media_group.g_id)  WHERE c_owner_id=298 AND c_id=1424
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_asp_201412 LEFT JOIN asp on (stat_log_asp_201412.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND (asp.asp_media_flg is null OR asp.asp_media_flg = 0)
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH,sum(click_count) as click_count,sum(click_count) as click_unique_count,sum(action_count) as action_count,sum(click_cost_cl)+sum(action_cost_cl) as total_cost,0 as indirect_effect
FROM stat_log_asp_media_201412 LEFT JOIN asp on (stat_log_asp_media_201412.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND asp.asp_media_flg = 1
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_listing_201412 LEFT JOIN listing on (stat_log_listing_201412.listing_id=listing.listing_id)  WHERE c_owner_id=298 AND c_id=1424
        ) AS TB1
        WHERE TB1.YEARMONTH is not null
        GROUP BY TB1.YEARMONTH UNION ALL 
SELECT TB1.YEARMONTH, sum(TB1.click_count) AS click_count, sum(TB1.click_unique_count) AS click_unique_count, sum(TB1.action_count) AS action_count, sum(TB1.total_cost) as total_cost, sum(TB1.indirect_effect) AS indirect_effect
        FROM(
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_201411 LEFT JOIN media_owner on (stat_log_201411.m_owner_id=media_owner.m_owner_id) LEFT JOIN media_group on (media_owner.g_id=media_group.g_id)  WHERE c_owner_id=298 AND c_id=1424
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_asp_201411 LEFT JOIN asp on (stat_log_asp_201411.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND (asp.asp_media_flg is null OR asp.asp_media_flg = 0)
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH,sum(click_count) as click_count,sum(click_count) as click_unique_count,sum(action_count) as action_count,sum(click_cost_cl)+sum(action_cost_cl) as total_cost,0 as indirect_effect
FROM stat_log_asp_media_201411 LEFT JOIN asp on (stat_log_asp_media_201411.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND asp.asp_media_flg = 1
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_listing_201411 LEFT JOIN listing on (stat_log_listing_201411.listing_id=listing.listing_id)  WHERE c_owner_id=298 AND c_id=1424
        ) AS TB1
        WHERE TB1.YEARMONTH is not null
        GROUP BY TB1.YEARMONTH UNION ALL 
SELECT TB1.YEARMONTH, sum(TB1.click_count) AS click_count, sum(TB1.click_unique_count) AS click_unique_count, sum(TB1.action_count) AS action_count, sum(TB1.total_cost) as total_cost, sum(TB1.indirect_effect) AS indirect_effect
        FROM(
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_201410 LEFT JOIN media_owner on (stat_log_201410.m_owner_id=media_owner.m_owner_id) LEFT JOIN media_group on (media_owner.g_id=media_group.g_id)  WHERE c_owner_id=298 AND c_id=1424
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_asp_201410 LEFT JOIN asp on (stat_log_asp_201410.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND (asp.asp_media_flg is null OR asp.asp_media_flg = 0)
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH,sum(click_count) as click_count,sum(click_count) as click_unique_count,sum(action_count) as action_count,sum(click_cost_cl)+sum(action_cost_cl) as total_cost,0 as indirect_effect
FROM stat_log_asp_media_201410 LEFT JOIN asp on (stat_log_asp_media_201410.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND asp.asp_media_flg = 1
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_listing_201410 LEFT JOIN listing on (stat_log_listing_201410.listing_id=listing.listing_id)  WHERE c_owner_id=298 AND c_id=1424
        ) AS TB1
        WHERE TB1.YEARMONTH is not null
        GROUP BY TB1.YEARMONTH UNION ALL 
SELECT TB1.YEARMONTH, sum(TB1.click_count) AS click_count, sum(TB1.click_unique_count) AS click_unique_count, sum(TB1.action_count) AS action_count, sum(TB1.total_cost) as total_cost, sum(TB1.indirect_effect) AS indirect_effect
        FROM(
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_201409 LEFT JOIN media_owner on (stat_log_201409.m_owner_id=media_owner.m_owner_id) LEFT JOIN media_group on (media_owner.g_id=media_group.g_id)  WHERE c_owner_id=298 AND c_id=1424
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_asp_201409 LEFT JOIN asp on (stat_log_asp_201409.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND (asp.asp_media_flg is null OR asp.asp_media_flg = 0)
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH,sum(click_count) as click_count,sum(click_count) as click_unique_count,sum(action_count) as action_count,sum(click_cost_cl)+sum(action_cost_cl) as total_cost,0 as indirect_effect
FROM stat_log_asp_media_201409 LEFT JOIN asp on (stat_log_asp_media_201409.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND asp.asp_media_flg = 1
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_listing_201409 LEFT JOIN listing on (stat_log_listing_201409.listing_id=listing.listing_id)  WHERE c_owner_id=298 AND c_id=1424
        ) AS TB1
        WHERE TB1.YEARMONTH is not null
        GROUP BY TB1.YEARMONTH UNION ALL 
SELECT TB1.YEARMONTH, sum(TB1.click_count) AS click_count, sum(TB1.click_unique_count) AS click_unique_count, sum(TB1.action_count) AS action_count, sum(TB1.total_cost) as total_cost, sum(TB1.indirect_effect) AS indirect_effect
        FROM(
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_201408 LEFT JOIN media_owner on (stat_log_201408.m_owner_id=media_owner.m_owner_id) LEFT JOIN media_group on (media_owner.g_id=media_group.g_id)  WHERE c_owner_id=298 AND c_id=1424
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_asp_201408 LEFT JOIN asp on (stat_log_asp_201408.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND (asp.asp_media_flg is null OR asp.asp_media_flg = 0)
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH,sum(click_count) as click_count,sum(click_count) as click_unique_count,sum(action_count) as action_count,sum(click_cost_cl)+sum(action_cost_cl) as total_cost,0 as indirect_effect
FROM stat_log_asp_media_201408 LEFT JOIN asp on (stat_log_asp_media_201408.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND asp.asp_media_flg = 1
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_listing_201408 LEFT JOIN listing on (stat_log_listing_201408.listing_id=listing.listing_id)  WHERE c_owner_id=298 AND c_id=1424
        ) AS TB1
        WHERE TB1.YEARMONTH is not null
        GROUP BY TB1.YEARMONTH UNION ALL 
SELECT TB1.YEARMONTH, sum(TB1.click_count) AS click_count, sum(TB1.click_unique_count) AS click_unique_count, sum(TB1.action_count) AS action_count, sum(TB1.total_cost) as total_cost, sum(TB1.indirect_effect) AS indirect_effect
        FROM(
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_201407 LEFT JOIN media_owner on (stat_log_201407.m_owner_id=media_owner.m_owner_id) LEFT JOIN media_group on (media_owner.g_id=media_group.g_id)  WHERE c_owner_id=298 AND c_id=1424
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_asp_201407 LEFT JOIN asp on (stat_log_asp_201407.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND (asp.asp_media_flg is null OR asp.asp_media_flg = 0)
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH,sum(click_count) as click_count,sum(click_count) as click_unique_count,sum(action_count) as action_count,sum(click_cost_cl)+sum(action_cost_cl) as total_cost,0 as indirect_effect
FROM stat_log_asp_media_201407 LEFT JOIN asp on (stat_log_asp_media_201407.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND asp.asp_media_flg = 1
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_listing_201407 LEFT JOIN listing on (stat_log_listing_201407.listing_id=listing.listing_id)  WHERE c_owner_id=298 AND c_id=1424
        ) AS TB1
        WHERE TB1.YEARMONTH is not null
        GROUP BY TB1.YEARMONTH UNION ALL 
SELECT TB1.YEARMONTH, sum(TB1.click_count) AS click_count, sum(TB1.click_unique_count) AS click_unique_count, sum(TB1.action_count) AS action_count, sum(TB1.total_cost) as total_cost, sum(TB1.indirect_effect) AS indirect_effect
        FROM(
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_201406 LEFT JOIN media_owner on (stat_log_201406.m_owner_id=media_owner.m_owner_id) LEFT JOIN media_group on (media_owner.g_id=media_group.g_id)  WHERE c_owner_id=298 AND c_id=1424
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_asp_201406 LEFT JOIN asp on (stat_log_asp_201406.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND (asp.asp_media_flg is null OR asp.asp_media_flg = 0)
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH,sum(click_count) as click_count,sum(click_count) as click_unique_count,sum(action_count) as action_count,sum(click_cost_cl)+sum(action_cost_cl) as total_cost,0 as indirect_effect
FROM stat_log_asp_media_201406 LEFT JOIN asp on (stat_log_asp_media_201406.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND asp.asp_media_flg = 1
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_listing_201406 LEFT JOIN listing on (stat_log_listing_201406.listing_id=listing.listing_id)  WHERE c_owner_id=298 AND c_id=1424
        ) AS TB1
        WHERE TB1.YEARMONTH is not null
        GROUP BY TB1.YEARMONTH UNION ALL 
SELECT TB1.YEARMONTH, sum(TB1.click_count) AS click_count, sum(TB1.click_unique_count) AS click_unique_count, sum(TB1.action_count) AS action_count, sum(TB1.total_cost) as total_cost, sum(TB1.indirect_effect) AS indirect_effect
        FROM(
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_201405 LEFT JOIN media_owner on (stat_log_201405.m_owner_id=media_owner.m_owner_id) LEFT JOIN media_group on (media_owner.g_id=media_group.g_id)  WHERE c_owner_id=298 AND c_id=1424
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_asp_201405 LEFT JOIN asp on (stat_log_asp_201405.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND (asp.asp_media_flg is null OR asp.asp_media_flg = 0)
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH,sum(click_count) as click_count,sum(click_count) as click_unique_count,sum(action_count) as action_count,sum(click_cost_cl)+sum(action_cost_cl) as total_cost,0 as indirect_effect
FROM stat_log_asp_media_201405 LEFT JOIN asp on (stat_log_asp_media_201405.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND asp.asp_media_flg = 1
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_listing_201405 LEFT JOIN listing on (stat_log_listing_201405.listing_id=listing.listing_id)  WHERE c_owner_id=298 AND c_id=1424
        ) AS TB1
        WHERE TB1.YEARMONTH is not null
        GROUP BY TB1.YEARMONTH UNION ALL 
SELECT TB1.YEARMONTH, sum(TB1.click_count) AS click_count, sum(TB1.click_unique_count) AS click_unique_count, sum(TB1.action_count) AS action_count, sum(TB1.total_cost) as total_cost, sum(TB1.indirect_effect) AS indirect_effect
        FROM(
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_201404 LEFT JOIN media_owner on (stat_log_201404.m_owner_id=media_owner.m_owner_id) LEFT JOIN media_group on (media_owner.g_id=media_group.g_id)  WHERE c_owner_id=298 AND c_id=1424
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_asp_201404 LEFT JOIN asp on (stat_log_asp_201404.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND (asp.asp_media_flg is null OR asp.asp_media_flg = 0)
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH,sum(click_count) as click_count,sum(click_count) as click_unique_count,sum(action_count) as action_count,sum(click_cost_cl)+sum(action_cost_cl) as total_cost,0 as indirect_effect
FROM stat_log_asp_media_201404 LEFT JOIN asp on (stat_log_asp_media_201404.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND asp.asp_media_flg = 1
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_listing_201404 LEFT JOIN listing on (stat_log_listing_201404.listing_id=listing.listing_id)  WHERE c_owner_id=298 AND c_id=1424
        ) AS TB1
        WHERE TB1.YEARMONTH is not null
        GROUP BY TB1.YEARMONTH UNION ALL 
SELECT TB1.YEARMONTH, sum(TB1.click_count) AS click_count, sum(TB1.click_unique_count) AS click_unique_count, sum(TB1.action_count) AS action_count, sum(TB1.total_cost) as total_cost, sum(TB1.indirect_effect) AS indirect_effect
        FROM(
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_201403 LEFT JOIN media_owner on (stat_log_201403.m_owner_id=media_owner.m_owner_id) LEFT JOIN media_group on (media_owner.g_id=media_group.g_id)  WHERE c_owner_id=298 AND c_id=1424
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_asp_201403 LEFT JOIN asp on (stat_log_asp_201403.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND (asp.asp_media_flg is null OR asp.asp_media_flg = 0)
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH,sum(click_count) as click_count,sum(click_count) as click_unique_count,sum(action_count) as action_count,sum(click_cost_cl)+sum(action_cost_cl) as total_cost,0 as indirect_effect
FROM stat_log_asp_media_201403 LEFT JOIN asp on (stat_log_asp_media_201403.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND asp.asp_media_flg = 1
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_listing_201403 LEFT JOIN listing on (stat_log_listing_201403.listing_id=listing.listing_id)  WHERE c_owner_id=298 AND c_id=1424
        ) AS TB1
        WHERE TB1.YEARMONTH is not null
        GROUP BY TB1.YEARMONTH UNION ALL 
SELECT TB1.YEARMONTH, sum(TB1.click_count) AS click_count, sum(TB1.click_unique_count) AS click_unique_count, sum(TB1.action_count) AS action_count, sum(TB1.total_cost) as total_cost, sum(TB1.indirect_effect) AS indirect_effect
        FROM(
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_201402 LEFT JOIN media_owner on (stat_log_201402.m_owner_id=media_owner.m_owner_id) LEFT JOIN media_group on (media_owner.g_id=media_group.g_id)  WHERE c_owner_id=298 AND c_id=1424
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_asp_201402 LEFT JOIN asp on (stat_log_asp_201402.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND (asp.asp_media_flg is null OR asp.asp_media_flg = 0)
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH,sum(click_count) as click_count,sum(click_count) as click_unique_count,sum(action_count) as action_count,sum(click_cost_cl)+sum(action_cost_cl) as total_cost,0 as indirect_effect
FROM stat_log_asp_media_201402 LEFT JOIN asp on (stat_log_asp_media_201402.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND asp.asp_media_flg = 1
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_listing_201402 LEFT JOIN listing on (stat_log_listing_201402.listing_id=listing.listing_id)  WHERE c_owner_id=298 AND c_id=1424
        ) AS TB1
        WHERE TB1.YEARMONTH is not null
        GROUP BY TB1.YEARMONTH UNION ALL 
SELECT TB1.YEARMONTH, sum(TB1.click_count) AS click_count, sum(TB1.click_unique_count) AS click_unique_count, sum(TB1.action_count) AS action_count, sum(TB1.total_cost) as total_cost, sum(TB1.indirect_effect) AS indirect_effect
        FROM(
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_201401 LEFT JOIN media_owner on (stat_log_201401.m_owner_id=media_owner.m_owner_id) LEFT JOIN media_group on (media_owner.g_id=media_group.g_id)  WHERE c_owner_id=298 AND c_id=1424
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_asp_201401 LEFT JOIN asp on (stat_log_asp_201401.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND (asp.asp_media_flg is null OR asp.asp_media_flg = 0)
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH,sum(click_count) as click_count,sum(click_count) as click_unique_count,sum(action_count) as action_count,sum(click_cost_cl)+sum(action_cost_cl) as total_cost,0 as indirect_effect
FROM stat_log_asp_media_201401 LEFT JOIN asp on (stat_log_asp_media_201401.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND asp.asp_media_flg = 1
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_listing_201401 LEFT JOIN listing on (stat_log_listing_201401.listing_id=listing.listing_id)  WHERE c_owner_id=298 AND c_id=1424
        ) AS TB1
        WHERE TB1.YEARMONTH is not null
        GROUP BY TB1.YEARMONTH UNION ALL 
SELECT TB1.YEARMONTH, sum(TB1.click_count) AS click_count, sum(TB1.click_unique_count) AS click_unique_count, sum(TB1.action_count) AS action_count, sum(TB1.total_cost) as total_cost, sum(TB1.indirect_effect) AS indirect_effect
        FROM(
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_201312 LEFT JOIN media_owner on (stat_log_201312.m_owner_id=media_owner.m_owner_id) LEFT JOIN media_group on (media_owner.g_id=media_group.g_id)  WHERE c_owner_id=298 AND c_id=1424
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_asp_201312 LEFT JOIN asp on (stat_log_asp_201312.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND (asp.asp_media_flg is null OR asp.asp_media_flg = 0)
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH,sum(click_count) as click_count,sum(click_count) as click_unique_count,sum(action_count) as action_count,sum(click_cost_cl)+sum(action_cost_cl) as total_cost,0 as indirect_effect
FROM stat_log_asp_media_201312 LEFT JOIN asp on (stat_log_asp_media_201312.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND asp.asp_media_flg = 1
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_listing_201312 LEFT JOIN listing on (stat_log_listing_201312.listing_id=listing.listing_id)  WHERE c_owner_id=298 AND c_id=1424
        ) AS TB1
        WHERE TB1.YEARMONTH is not null
        GROUP BY TB1.YEARMONTH UNION ALL 
SELECT TB1.YEARMONTH, sum(TB1.click_count) AS click_count, sum(TB1.click_unique_count) AS click_unique_count, sum(TB1.action_count) AS action_count, sum(TB1.total_cost) as total_cost, sum(TB1.indirect_effect) AS indirect_effect
        FROM(
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_201311 LEFT JOIN media_owner on (stat_log_201311.m_owner_id=media_owner.m_owner_id) LEFT JOIN media_group on (media_owner.g_id=media_group.g_id)  WHERE c_owner_id=298 AND c_id=1424
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_asp_201311 LEFT JOIN asp on (stat_log_asp_201311.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND (asp.asp_media_flg is null OR asp.asp_media_flg = 0)
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH,sum(click_count) as click_count,sum(click_count) as click_unique_count,sum(action_count) as action_count,sum(click_cost_cl)+sum(action_cost_cl) as total_cost,0 as indirect_effect
FROM stat_log_asp_media_201311 LEFT JOIN asp on (stat_log_asp_media_201311.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND asp.asp_media_flg = 1
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_listing_201311 LEFT JOIN listing on (stat_log_listing_201311.listing_id=listing.listing_id)  WHERE c_owner_id=298 AND c_id=1424
        ) AS TB1
        WHERE TB1.YEARMONTH is not null
        GROUP BY TB1.YEARMONTH UNION ALL 
SELECT TB1.YEARMONTH, sum(TB1.click_count) AS click_count, sum(TB1.click_unique_count) AS click_unique_count, sum(TB1.action_count) AS action_count, sum(TB1.total_cost) as total_cost, sum(TB1.indirect_effect) AS indirect_effect
        FROM(
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_201310 LEFT JOIN media_owner on (stat_log_201310.m_owner_id=media_owner.m_owner_id) LEFT JOIN media_group on (media_owner.g_id=media_group.g_id)  WHERE c_owner_id=298 AND c_id=1424
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_asp_201310 LEFT JOIN asp on (stat_log_asp_201310.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND (asp.asp_media_flg is null OR asp.asp_media_flg = 0)
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH,sum(click_count) as click_count,sum(click_count) as click_unique_count,sum(action_count) as action_count,sum(click_cost_cl)+sum(action_cost_cl) as total_cost,0 as indirect_effect
FROM stat_log_asp_media_201310 LEFT JOIN asp on (stat_log_asp_media_201310.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND asp.asp_media_flg = 1
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_listing_201310 LEFT JOIN listing on (stat_log_listing_201310.listing_id=listing.listing_id)  WHERE c_owner_id=298 AND c_id=1424
        ) AS TB1
        WHERE TB1.YEARMONTH is not null
        GROUP BY TB1.YEARMONTH UNION ALL 
SELECT TB1.YEARMONTH, sum(TB1.click_count) AS click_count, sum(TB1.click_unique_count) AS click_unique_count, sum(TB1.action_count) AS action_count, sum(TB1.total_cost) as total_cost, sum(TB1.indirect_effect) AS indirect_effect
        FROM(
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_201309 LEFT JOIN media_owner on (stat_log_201309.m_owner_id=media_owner.m_owner_id) LEFT JOIN media_group on (media_owner.g_id=media_group.g_id)  WHERE c_owner_id=298 AND c_id=1424
        UNION ALL
        SELECT DATE_FORMAT(stat_date,'%Y%m') AS YEARMONTH
        ,sum(click_count_pc)+sum(click_count_d)+sum(click_count_v)+sum(click_count_e)+sum(click_count_a)+sum(click_count_i) AS click_count
        ,sum(click_unique_count_pc)+sum(click_unique_count_d)+sum(click_unique_count_v)+sum(click_unique_count_e)+sum(click_unique_count_a)+sum(click_unique_count_i) AS click_unique_count
        ,sum(action_count_pc)+sum(action_count_d)+sum(action_count_v)+sum(action_count_e)+sum(action_count_a)+sum(action_count_i) AS action_count
        ,sum(click_cost_cl_pc)+sum(click_cost_cl_d)+sum(click_cost_cl_v)+sum(click_cost_cl_e)+sum(click_cost_cl_a)+sum(click_cost_cl_i)+sum(action_cost_cl_pc)+sum(action_cost_cl_d)+sum(action_cost_cl_v)+sum(action_cost_cl_e)+sum(action_cost_cl_a)+sum(action_cost_cl_i) AS total_cost
        ,sum(indirect_effect_d)+sum(indirect_effect_v)+sum(indirect_effect_e) AS indirect_effect
FROM stat_log_asp_201309 LEFT JOIN asp on (stat_log_asp_201309.asp_id=asp.asp_id)  WHERE c_owner_id=298 AND c_id=1424 AND (asp.asp_

他の形式にエクスポート: Atom PDF

i-generation

チケット

カスタムクエリ

バグ #90

DB負荷調査

履歴

#1 山本義治が10年以上前に更新

#2 山本義治が10年以上前に更新

#3 山本義治が10年以上前に更新

#4 山本義治が10年以上前に更新

#5 山本義治が10年以上前に更新

#6 山本義治が10年以上前に更新

#7 山本義治が10年以上前に更新

#8 山本義治が10年以上前に更新

#9 山本義治が10年以上前に更新

#10 山本義治が10年以上前に更新

#11 山本義治が10年以上前に更新

#12 山本義治が10年以上前に更新

#13 山本義治がほぼ10年前に更新

i-generation

チケット

カスタムクエリ

バグ #90

DB負荷調査

履歴

#1 山本 義治 が10年以上前に更新

#2 山本 義治 が10年以上前に更新

#3 山本 義治 が10年以上前に更新

#4 山本 義治 が10年以上前に更新

#5 山本 義治 が10年以上前に更新

#6 山本 義治 が10年以上前に更新

#7 山本 義治 が10年以上前に更新

#8 山本 義治 が10年以上前に更新

#9 山本 義治 が10年以上前に更新

#10 山本 義治 が10年以上前に更新

#11 山本 義治 が10年以上前に更新

#12 山本 義治 が10年以上前に更新

#13 山本 義治 がほぼ10年前に更新

#1 山本義治が10年以上前に更新

#2 山本義治が10年以上前に更新

#3 山本義治が10年以上前に更新

#4 山本義治が10年以上前に更新

#5 山本義治が10年以上前に更新

#6 山本義治が10年以上前に更新

#7 山本義治が10年以上前に更新

#8 山本義治が10年以上前に更新

#9 山本義治が10年以上前に更新

#10 山本義治が10年以上前に更新

#11 山本義治が10年以上前に更新

#12 山本義治が10年以上前に更新

#13 山本義治がほぼ10年前に更新