SQL查询-选择组中的“上次更新”记录,更好的数据库设计?

我最近遇到了一个类似的 problem .

我不是一个SQL专家,所以我不能给出很多关于什么是最好的设计的建议。但在专业人士介入之前,这可能有助于:

SELECT s.Person_ID, s.Newsletter_ID  
FROM (
 SELECT MAX(ID) AS mid
 FROM Subscriptions
 GROUP BY 
  Person_ID,Newsletter_ID
) q
JOIN Subscriptions s
ON q.mid = s.ID
WHERE s.Subscribed = 1

注意,我在您的订阅表中添加了一个ID列(稍后我将解释原因)。

现在,让我们来分析一下这是如何工作的(或者我认为它是如何工作的,不管怎样,如果我错了,我很乐意被纠正)。

首先,检索给定人员/新闻稿的所有记录。这就是子查询所做的(是的,我知道你说过你不希望有子查询,但我不确定没有子查询你能做到)。我按个人ID和新闻稿ID分组。这可以返回多行。注意,我选择的是max(id)。如果使用自动增量ID,并且假设ID列中数字最大的行是组中最新的行(即,如果不手动插入ID),则此子查询将为每个人/新闻稿获取最后一行的ID。

因此,可以将其与订阅表联接:联接条件是订阅行的ID必须与从子查询中检索到的最大ID匹配。这里,您只考虑每个新闻稿/个人的最新记录。然后,使用where条件来计算非活动订阅。

如果要将结果限制在给定的新闻稿(或给定的人)中,请将该条件添加到WHERE子句中。

索引应该有助于使这个查询运行得更快。

希望这有帮助。

补充

如果出于某种原因,您不能保证max(subscriptions.id)将对应于最后插入的行,那么您可以这样做(我认为,这遵循相同的逻辑,但有点冗长,而且可能效率较低):

SELECT Person_ID, Newsletter_ID  
FROM (
 SELECT MAX(Updated) AS upd, Newsletter_ID AS nid, Person_ID AS pid 
 FROM Subscriptions
 GROUP BY 
  Person_ID,Newsletter_ID
) q
JOIN Subscriptions s
ON q.pid = s.Person_ID AND q.nid = s.Newsletter_ID and q.upd = s.Updated
WHERE Subscribed = 1

新编辑

在第二个想法上,我添加了一个备选方案 MAX(Updated) )我想是错的。您不能确定子查询中选择的新闻稿ID和个人ID将是对应于max(更新)行的新闻稿ID和个人ID。由于这些列用于联接条件,因此此查询可能会给出虚假的结果。

分开你 Subscriptions 2张表:

• 首先将存储实际订阅列表(订阅,即 true 现在): Person_Id γ Newsletter_Id
• 第二个将存储订阅日志(其更新或状态更改)

有序分析函数”是解决这类问题的标准方法。一百万张唱片,没问题…当然,这取决于你的机器的力量。

MAX( Updated) OVER( PARTITION BY 要“max”的字段列表 )

SELECT
  x.*
FROM
  (
    SELECT
       Person_ID
       , Newsletter_ID
       --, Subscribed
       , Updated
       , MAX(Updated) OVER( PARTITION BY Person_ID, Newsletter_ID, Subscribed) AS myUpdated
   FROM Subscriptions
  ) x 
WHERE Updated = myUpdated

我觉得你的设计不错。子查询没有固有的缓慢性-如果是表达查询的最佳方式,请使用它们。

以下是获取所有最新(即未重写)指令的查询:

SELECT Person_ID, Newsletter_ID, Subscribed, MAX(Updated)
FROM Subscriptions GROUP BY Person_ID, Newsletter_ID

然后,可以将此查询用作另一个查询的子查询,以获取所需的内容。对于您的查询1:

SELECT x.Person_ID, x.Newsletter_ID FROM
  (SELECT Person_ID, Newsletter_ID, Subscribed, MAX(Updated) 
   FROM Subscriptions GROUP BY Person_ID, Newsletter_ID) x
WHERE x.Subscribed;

查询2:

SELECT x.Person_ID FROM
  (SELECT Person_ID, Newsletter_ID, Subscribed, MAX(Updated)
   FROM Subscriptions GROUP BY Person_ID, Newsletter_ID) x
WHERE x.Subscribed AND x.Newsletter_ID = ?

你肯定想要一个索引 Newsletter_ID 在 Subscriptions 表,因为这个查询可能是非常有选择性的。

编辑: 哎呀,子查询中的订阅列可以来自任意行,而不是生成max(更新)的行。您必须重新加入原始表:

SELECT x.Person_ID, x.Newsletter_ID, y.Subscribed FROM
  (SELECT Person_ID, Newsletter_ID, MAX(Updated) as MaxUpdated
   From Subscriptions GROUP by Person_ID, Newsletter_ID) x
  JOIN Subscriptions y WHERE x.Person_ID = y.Person_ID AND
                             x.Newsletter_ID = y.Newsletter_ID AND
                             x.MaxUpdated = y.Updated