计算返回数字的两个不同SQL查询结果的比率

Question

我有查询1

SELECT COUNT(DISTINCT user_id) total_daily_active_user_group_month FROM (SELECT  user_id , MONTHNAME(time) mon , COUNT(*) cnt FROM ACTIVITIES 
WHERE  MONTH(time) = MONTH(NOW() - INTERVAL 1 MONTH) GROUP by user_id, MONTH(time) ) as x 

返回18

查询2

SELECT COUNT(DISTINCT user_id) total_daily_active_user_group_month FROM (SELECT  user_id , MONTHNAME(time) mon , COUNT(*) cnt FROM ACTIVITIES 
WHERE  MONTH(time) = MONTH(NOW() - INTERVAL 1 MONTH) GROUP by user_id, MONTH(time) having cnt=31) as x 

返回6

我想要查询1和2的比率。指

18/6。我正在使用MySQL

Answer 1

如果您将两个查询都用作CTE，那么它就变得相对简单：

WITH q1
AS (SELECT Count(DISTINCT user_id) total_daily_active_user_group_month
FROM   (SELECT user_id,
Monthname(TIME) mon,
Count(*)        cnt
FROM   activities
WHERE  Month(TIME) = Month(Now() - interval 1 month)
GROUP  BY user_id,
Month(TIME))),
q2
AS (SELECT Count(DISTINCT user_id) total_daily_active_user_group_month
FROM   (SELECT user_id,
Monthname(TIME) mon,
Count(*)        cnt
FROM   activities
WHERE  Month(TIME) = Month(Now() - interval 1 month)
GROUP  BY user_id,
Month(TIME)
HAVING cnt = 31))
SELECT q1.total_daily_active_user_group_month /
q2.total_daily_active_user_group_month
AS result
FROM   dual;  

---

您评论说您在指向WITH关键字时出错；切换到两个子查询，然后；简化：

select a.value / b.value as result 
from (select count(distinct user_id) value
from ... your 1st query goes here
) a,
(select count(distinct user_id) value
from ... your 2nd query goes here
) b;