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

考虑下表

 =# \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

我需要找到在给定日期范围内每个月具有关联视图的所有用户。目前我正在做以下工作：

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)

我安装了一把小提琴。
目前的结果包括用户凯尔谁没有意见在8月和9月。正确的结果应该只包括在给定范围内的所有3个月内都有视图的用户Stan。如何修改此查询以返回所需的结果？

最佳答案

您似乎有一个扩展的关系划分，即您正在寻找仅在给定范围内拥有视图的用户，尽管他们也可能拥有感兴趣范围之外的视图。
除了GROUP BY之外，您还可以通过EXCEPT构造来检查。基本上，如果将给定范围内的所有视图减去该范围内的所有月份，则不应收到任何行：

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的一段话：
如果所有行都产生true，则ALL的结果为“true”
（包括子查询不返回行的情况）。
我的两个问题实际上是一样的。第一个计算内侧的行数，并将它们与零进行比较（我同意，这一点更为明显）。第二个比较currentviews.time与子查询的所有结果。只有子查询返回的所有条目都等于views.time（当然，截断为月边界）时，此构造才会产生true。而且，正如所引用的，如果子查询不返回行，则此构造也会产生true。
根据意图，子查询不应该产生行，这表示所有视图都发生在所需的时间范围内。
Check on SQL Fiddle

关于sql - SQL:查找具有给定日期范围内每月数据的记录，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/26562117/