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

Alex 发表于 Dev

亚历克斯

考虑下表

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

我在这里摆了一个小提琴。

当前结果包括用户Kyle，他在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” 。

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

并且出于意图，子查询不应产生任何行，这表明所有视图都在所需的时间范围内发生。

Check on SQL Fiddle

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。