$trd_sql .= "SUM(CASE WHEN user.regist_datetime >= " . $month_array["0"] . "000000 THEN 1 ELSE 0 END) AS count_male_0,";
$trd_sql .= "0 AS count_female_0,";
foreach ($month_array as $key => $value) {
//最後はそれ以前として全て取得
if ($key + 1 == 12) {
$trd_sql .= "SUM(CASE WHEN user.regist_datetime < " . $value . "000000 THEN 1 ELSE 0 END) AS count_male_" . ($key + 1) . ",";
$trd_sql .= "0 AS count_female_" . ($key + 1) . " ";
break;
//0ヶ月
} else {
$trd_sql .= "SUM(CASE WHEN user.regist_datetime < " . $value . "000000 AND user.regist_datetime >= " . $month_array[$key + 1] . "000000 THEN 1 ELSE 0 END) AS count_male_" . ($key + 1) . ",";
$trd_sql .= "0 AS count_female_" . ($key + 1) . ",";
}
}
$trd_sql .= "FROM payment_log LEFT JOIN user ON payment_log.user_id = user.id AND user.disable = 0 AND payment_log.disable = 0 " . "WHERE payment_log.create_datetime >= " . $date . "000000 AND payment_log.create_datetime <= " . $date . "235959 " . "GROUP BY ad_code";
$trd_result = $calc->calcTrdDetail($trd_sql);
// 入金ユニーク数の算出（3ヶ月前のユニークに2ヶ月前、1ヶ月前、当月、当日をLeftJoinして一発で算出）
$unique_sql = "SELECT " . "user.media_cd AS ad_code," . "COUNT(DISTINCT user.id) AS three_month_unique," . "two.two_month_unique AS two_month_unique," . "one.one_month_unique AS one_month_unique," . "current.current_month_unique AS current_month_unique," . "yesterday.yesterday_unique AS current_date_unique " . "FROM payment_log " . "LEFT JOIN user ON payment_log.user_id = user.id " . "LEFT JOIN " . "(SELECT " . "user.media_cd AS ad_code_two," . "COUNT(DISTINCT user.id) AS two_month_unique " . "FROM payment_log " . "LEFT JOIN user ON payment_log.user_id = user.id " . "WHERE payment_log.disable = 0 " . "AND payment_log.create_datetime >= " . $two_month_ago . "000000 " . "AND payment_log.create_datetime < " . $tomorrow . "000000 " . "GROUP BY ad_code_two" . ") AS two ON user.media_cd = two.ad_code_two " . "LEFT JOIN " . "(SELECT " . "user.media_cd AS ad_code_one," . "COUNT(DISTINCT user.id) AS one_month_unique " . "FROM payment_log " . "LEFT JOIN user ON payment_log.user_id = user.id " . "WHERE payment_log.disable = 0 " . "AND payment_log.create_datetime >= " . $one_month_ago . "000000 " . "AND payment_log.create_datetime < " . $tomorrow . "000000 " . "GROUP BY ad_code_one" . ") AS one on user.media_cd = one.ad_code_one " . "LEFT JOIN " . "(SELECT " . "user.media_cd AS ad_code_current," . "COUNT(DISTINCT user.id) AS current_month_unique " . "FROM payment_log " . "LEFT JOIN user ON payment_log.user_id = user.id " . "WHERE payment_log.disable = 0 " . "AND user.disable = 0 " . "AND payment_log.create_datetime >= " . $current_month . "000000 " . "AND payment_log.create_datetime < " . $tomorrow . "000000 " . "AND user.regist_datetime >= " . $current_month . "000000 " . "AND user.regist_datetime < " . $tomorrow . "000000 " . "GROUP BY ad_code_current" . ") AS current on user.media_cd = current.ad_code_current " . "LEFT JOIN " . "(SELECT " . "user.media_cd AS ad_code_yesterday," . "COUNT(DISTINCT user.id) AS yesterday_unique " . "FROM payment_log " . "LEFT JOIN user ON payment_log.user_id = user.id " . "WHERE payment_log.disable = 0 " . "AND payment_log.create_datetime >= " . $date . "000000 " . "AND payment_log.create_datetime < " . $tomorrow . "000000 " . "GROUP BY ad_code_yesterday" . ") AS yesterday on user.media_cd = yesterday.ad_code_yesterday " . "WHERE payment_log.disable = 0 " . "AND payment_log.create_datetime >= " . $three_month_ago . "000000 " . "AND payment_log.create_datetime < " . $tomorrow . "000000 " . "GROUP BY ad_code";
/*
$unique_sql = "SELECT "
//アドコード別
. "trade.media_cd AS ad_code,"
//入金種別
. "trade.trade_type AS trade_type,"
//3ヶ月過去のユニーク入金者数
. "COUNT(DISTINCT trade.user_id) AS three_month_unique,"
//2ヶ月過去のユニーク入金者数
. "two.two_month_unique AS two_month_unique ,"
//1ヶ月過去のユニーク入金者数
. "one.one_month_unique AS one_month_unique ,"
//当月のユニーク入金者数
