mysql-concat-百分比

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
展开阅读全文
©️2020 CSDN 皮肤主题: 游动-白 设计师: 上身试试 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值