两表相连 然后用where count出的俩个数怎么相除 为啥我写出来的sql求出来的答案是1.0

Question

select count(order_zt) from qzy_order left join qzy_visitor on qzy_order.phon_number=qzy_visitor.phon_number where order_zt="支付成功";

select count(fwsj) from qzy_order left join qzy_visitor on qzy_order.phon_number=qzy_visitor.phon_number where fwsj="chargeRequest";

select ROUND(count(order_zt)/count(fwsj),2)from qzy_order left join qzy_visitor on qzy_order.phon_number=qzy_visitor.phon_number where (order_zt="支付成功" and  fwsj="chargeRequest");

下面是表 其实可以不用看的

create table qzy_visitor(
visitor_IP string,
visitor_time bigint,
visitor_twotime bigint,
use_id int,
phon_number string,
book_id int,
fwlj string,
fwsj string,
channel string,
price float
)
row format delimited fields terminated by ','
tblproperties("skip.header.line.count"="1");
load data local inpath '/opt/电商指标/访客表.csv' overwrite into table qzy_visitor;

create table qzy_order(
order_id bigint,
order_timme bigint,
buy_id int,
book_id int,
buy_ip string,
show_address string,
phon_number string,
channel string,
cprice float,
jy_time bigint,
order_zt string
)
row format delimited fields terminated by ','
tblproperties("skip.header.line.count"="1");
load data local inpath '/opt/电商指标/订单表.csv' overwrite into table qzy_order;

Answer 1

count(col) 之后是整数，sql 中整数相除会截断（sql server 中），mysql 和 Oracle 中会自动变换成小数。

所以需要显式把其中一个转换成小数。

ROUND(count(order_zt) / cast(count(fwsj) as float), 2)