CONCAT(FORMAT((CASE WHEN impressions!= 0 THEN clicks/impressions*100 ELSE 0.00 END),2),’%’) AS click_rate, – 百分比
SELECT a.org_id,a.ad_set_type,SUM(a.cost),SUM(a.impressions), FORMAT((CASE WHEN SUM(a.impressions)!= 0 THEN SUM(a.cost)/SUM(a.impressions)*1000 ELSE 0.00 END),2) AS avg_1000_impressions_cost,SUM(a.clicks), FORMAT((CASE WHEN SUM(a.clicks)!= 0 THEN SUM(a.cost)/SUM(a.clicks) ELSE 0.00 END),2) AS avg_single_click_cost, CONCAT(FORMAT((CASE WHEN SUM(a.impressions)!= 0 THEN SUM(a.clicks)/SUM(a.impressions)*100 ELSE 0.00 END),2),'%') AS avg_click_rate,SUM(a.clue_count), FORMAT((CASE WHEN SUM(a.clue_count)!= 0 THEN SUM(a.cost)/SUM(a.clue_count)*1000 ELSE 0.00 END),2) AS avg_clue_price,SUM(a.inquiry_count), FORMAT((CASE WHEN SUM(a.inquiry_count)!= 0 THEN SUM(a.cost)/SUM(a.inquiry_count)*1000 ELSE 0.00 END),2) AS avg_inquiry_price FROM ( SELECT org_id,ad_set_type,DATE(date) as date,IFNULL(FORMAT(cost, 2),0) as cost,IFNULL(impressions,0) as impressions, FORMAT((CASE WHEN impressions!= 0 THEN cost/impressions * 1000 ELSE 0.00 END),2) AS 1000_impressions_cost, IFNULL(clicks,0) as clicks, FORMAT((CASE WHEN clicks!= 0 THEN cost/clicks ELSE 0.00 END),2) AS single_click_cost, CONCAT(FORMAT((CASE WHEN impressions!= 0 THEN clicks/impressions*100 ELSE 0.00 END),2),'%') AS click_rate, -- 百分比 IFNULL(clue_count,0) as clue_count, FORMAT((CASE WHEN clue_count!= 0 THEN cost/clue_count ELSE - 1 END),2) AS clue_price, IFNULL(inquiry_count,0) as inquiry_count, FORMAT((CASE WHEN inquiry_count != 0 THEN cost/inquiry_count ELSE - 1 END),2) AS inquiry_price FROM report.bi_facebook_results_sum -- WHERE org_id = 10330 and DATE(date) >= '2020-06-10' and DATE(date)<='2020-06-30' ) as a WHERE a.org_id = 10330 and a.date >= '2020-06-10' and a.date<='2020-06-30' GROUP BY a.org_id,a.ad_set_type