$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 ," //当月のユニーク入金者数