基于来自内部查询的组合的子集设置

Question

我在Redshift上使用postgres。我有一个查询这是这样的：

SELECT EXTRACT(year from created_at) AS CustomYear, 
      client_ip, 
      member_id, 
      COUNT(*) AS Views 
     FROM ads.fbs_page_view_staging 
     WHERE member_id = 2 
     GROUP BY CustomYear, 
       client_ip, 
       member_id 
     HAVING COUNT(*) = 1 
     ORDER BY CustomYear 

在这里，我选择的client_ip和member_id其中Views是组合1.我现在想采取的client_ip和member_id这些组合和子集整个表ads.fbs_page_view_staging只有这样的组合。

如果只有一个专栏中，我想在子集，说client_ip，我已经写了下面的查询，并得到了结果：

SELECT EXTRACT(year FROM created_at) AS CustomYear, 
     COUNT(*) 
FROM ads.fbs_page_view_staging 
WHERE member_id = 2 
AND client_ip IN (SELECT client_ip 
        FROM ((SELECT EXTRACT(year from created_at) AS CustomYear, 
            client_ip, 
            member_id, 
            COUNT(*) 
          FROM ads.fbs_page_view_staging 
          WHERE member_id = 2 
          GROUP BY CustomYear, 
            client_ip, 
            member_id 
          HAVING COUNT(*) = 1 
          ORDER BY CustomYear))) 
GROUP BY customyear 
ORDER BY customyear 

注意，在外部查询，我子集化基础的在client_ip。但是，我如何在列的组合上对表进行分组？

任何帮助将不胜感激。

Answer 1

而不是subquerying，尝试直接加入到您的查询结果。这样你可以指定多个标准。

这里是（草案）SQL来选择匹配的子查询中找到的行是IP /成员对（即对于一些年以前，有针对IP &成员只有一个视图。）

SELECT distinct client_ip, member_id 
FROM ads.fbs_page_view_staging Staging 
INNER JOIN (SELECT EXTRACT(year from created_at) AS CustomYear, 
     client_ip, 
     member_id, 
     COUNT(*) AS Views 
    FROM ads.fbs_page_view_staging 
    WHERE member_id = 2 
    GROUP BY CustomYear, 
      client_ip, 
      member_id 
    HAVING COUNT(*) = 1) SingularViews 
ON SingularViews.client_ip=Staging.client_ip 
AND SingularViews.member_id=Staging.member_id 
ORDER BY Staging.client_ip, Staging.member_id 

我不确定我是否已经正确捕获了您的查询的意图，但是如果不希望您可以适应该技术。