Postgres Count IS不运行准确的结果

Question

我有此Postgres查询可以通过从多个表查询2列从2列返回计数。

这是一个例子：

SELECT
        subscribers.email,
        COUNT(campaign_views.subscriber_id) AS views,
        COUNT(link_clicks.subscriber_id) AS clicks
        FROM campaign_views, link_clicks, subscribers, links
        WHERE campaign_views.campaign_id = 586
        AND link_clicks.campaign_id = 586
        AND link_clicks.link_id = links.id
        AND subscribers.channel = 'email'
        AND subscribers.id = campaign_views.subscriber_id
        AND subscribers.id = link_clicks.subscriber_id
        GROUP BY subscribers.id

问题是所有这样的人的数量结果是相同的：

views | clicks
16  16
6   6
2   2
4   4

请有什么想法吗？

Answer 1

您

FROM campaign_views, link_clicks, subscribers, links

正在创建一个CROSS JOIN。

表 a

value_a
-------
1
2
3

和表 b

value_b
-------
a
b

的示例在

SELECT
    *
FROM a, b

您生成时：

value_a | value_b
--------|--------
      1 | a
      1 | b
      2 | a
      2 | b
      3 | a
      3 | b

当您执行时：

SELECT
    COUNT(a.value_a),
    COUNT(b.value_b)
FROM a,b

您将收到：

count | count
------|------
    6 | 6

COUNT() 返回全部列。在 CROSS JOIN 的结果中，您为所有列生成了 3 * 2 = 6 记录。因此，您得到的所有结果都是相同的，这是有道理的。

Answer 2

因此，我设法以准确的结果进行了计数。

WITH camps AS (
SELECT
    subscriber_id,
    COUNT(CASE WHEN subscriber_id = null THEN 0 ELSE 1 END) AS views 
FROM campaign_views
GROUP BY subscriber_id ),

links AS (
SELECT
    subscriber_id,
    COUNT(CASE WHEN subscriber_id = null THEN 0 ELSE 1 END) AS clicks 
FROM link_clicks
GROUP BY subscriber_id )

SELECT 
    subscribers.email, camps.views, links.clicks FROM camps, links, subscribers

WHERE camps.subscriber_id = links.subscriber_id
AND camps.subscriber_id = subscribers.id
AND links.subscriber_id = subscribers.id