SQL:查找具有给定日期范围内每个月数据的记录

Question

考虑下表

 =# \d users
 Column |         Type
--------+-----------------------
 id     | integer
 name   | character varying(32)

=# \d profiles
 Column  |  Type
---------+---------
 id      | integer
 user_id | integer

=# \d views
   Column   |            Type
------------+-----------------------------
 id         | integer
 profile_id | integer
 time       | timestamp without time zone

我需要在给定日期范围内的每个月查找具有关联 View 的所有用户。目前我正在做以下事情:

with months as (
  select to_char(month, 'MM/YYYY') from generate_series('2014-07-01', '2014-09-01', INTERVAL '1 month') as month
)

select * from users
  join profiles on user_id = users.id
  join views on profile_id = profiles.id
    and to_char(views.time, 'MM/YYYY') in (select * from months)

我已经设置了一个 fiddle here .

目前的结果包括用户 Kyle，他在八月和九月没有观看。正确的结果应该只包括在给定范围内的所有 3 个月内都有浏览量的用户 Stan。我们如何修改此查询以返回所需的结果？

Answer 1

您似乎有一个扩展的关系划分，即您正在寻找仅在给定范围内具有 View 的用户，尽管他们也可能具有感兴趣范围之外的 View 。

与 GROUP BY 一起，您可以通过 EXCEPT 构造来检查它。基本上，如果您用给定范围内的所有 View 减去范围内的所有月份，您应该不会收到任何行:

WITH months(month) AS (
  SELECT DATE '2014-07-01' + m*INTERVAL'1mon'
    FROM generate_series(0,2) m
)
SELECT *
  FROM users    u
  JOIN profiles p ON p.user_id=u.id
  JOIN views    v ON v.profile_id=p.id
 WHERE 0 = (SELECT count(*) FROM (
    SELECT month FROM months
    EXCEPT ALL
    SELECT date_trunc('mon',time) FROM views
     WHERE date_trunc('mon',time) IN (SELECT * FROM months)
       AND profile_id=p.id) minus);

您可以通过 = ALL 构造稍微简化此构造，因为在子查询不返回任何行的情况下它将返回 true:

WITH months(month) AS (
  SELECT DATE '2014-07-01' + m*INTERVAL'1mon'
    FROM generate_series(0,2) m
)
SELECT *
  FROM users    u
  JOIN profiles p ON p.user_id=u.id
  JOIN views    v ON v.profile_id=p.id
 WHERE date_trunc('mon',time) = ALL (
    SELECT month FROM months
    EXCEPT ALL
    SELECT date_trunc('mon',time) FROM views
     WHERE date_trunc('mon',time) IN (SELECT * FROM months)
       AND profile_id=p.id);

引用自 ALL 上的手册:

The result of ALL is "true" if all rows yield true (including the case where the subquery returns no rows).

我的两个查询实际上是相同的。第一个计算内侧的行数并将它们与零进行比较(我同意，这更明显)。第二个将当前 views.time 与子查询的所有结果进行比较。仅当子查询返回的所有条目都等于 views.time(当然，截断到月份边界)时，此构造才会产生 true。并且，如前所述，如果子查询不返回任何行，此构造也会产生 true。

按照意图，子查询不应产生任何行，这表明所有 View 都发生在所需的时间范围内。

Check on SQL Fiddle