我敢肯定有一个简单的解决方案,我的豌豆脑现在无法理解。
我正在将以下查询与FULL OUTER JOIN
配合使用,并且我想对DISTINCT memberid
进行计数:
SELECT a.year,COUNT(DISTINCT a.memberid) AS members
FROM (SELECT DISTINCT YEAR,memberid
FROM (SELECT EXTRact(YEAR FROM created_at) AS YEAR,EXTRact(MONTH FROM created_at) AS MONTH,member_id AS memberid,COUNT(DISTINCT field1) AS field1
FROM table1
GROUP BY YEAR,MONTH,member_id
ORDER BY YEAR,eids DESC)) a
FULL OUTER JOIN (SELECT DISTINCT YEAR,memberid
FROM (SELECT EXTRact(YEAR FROM created) AS YEAR,EXTRact(MONTH FROM created) AS MONTH,memberid,COUNT(field2) AS field2
FROM table2
GROUP BY YEAR,memberid
ORDER BY YEAR,questions DESC)) b
ON a.year = b.year
AND a.memberid = b.memberid
GROUP BY a.year
ORDER BY a.year
此查询正确执行,但是我很确定结果不是我期望的。
我得到以下结果:
2014 26834
2015 58573
2016 178378
2017 233291
2018 297404
2019 281088
现在将FULL OUTER JOIN
两侧的查询称为Left query
和Right query
。当我在Right query
上汇总year
并计算不同的memberid
时,得到以下结果:
2013 3915
2014 59025
2015 115514
2016 176528
2017 216675
2018 301007
2019 311141
我们可以看到,DISTINCT COUNT
本身的结果(Right query
)高于具有FULL OUTER JOIN
的完整查询。这显然是没有道理的。
在最终结果中,我想对所有COUNT DISTINCT
(即出现在memberid
中的memberid
和{{1} }出现在Left query
中,而没有两次计算任何memberid
并通过Right query
对其进行汇总。
我知道解决方案必须很简单。任何帮助将不胜感激。