sql - 在 With 语句中选择 Null

Question

我在 SQLWorkbenchJ 中使用 PostgreSQL，但遇到困难。

我有一个 WITH 语句，它根据行号选择一个日期。如果语句找不到行号，我想在日期字段中选择null。这目前不会发生，它只是选择所有字段都不为空的记录。我假设它与连接有关，但我不确定。

当前声明如下。它应该返回大约 50,000 条记录，但目前返回的记录不到 2000 条。

WITH FifthEnquiry AS
(
  SELECT emailaddress,
         SentDate,
         ROW_NUMBER() OVER (PARTITION BY emailaddress ORDER BY COUNT(*) DESC) AS rk
  FROM SentEmails
  GROUP BY emailaddress,
           SentDate
),
TenthEnquiry AS
(
  SELECT emailaddress,
         SentDate,
         ROW_NUMBER() OVER (PARTITION BY emailaddress ORDER BY COUNT(*) DESC) AS rk
  FROM SentEmails
  GROUP BY emailaddress,
           SentDate
),
TwentiethEnquiry AS
(
  SELECT emailaddress,
         SentDate,
         ROW_NUMBER() OVER (PARTITION BY emailaddress ORDER BY COUNT(*) DESC) AS rk
  FROM SentEmails
  GROUP BY emailaddress,
           SentDate
)
SELECT FifthEnquiry.emailaddress,
       FifthEnquiry.SentDate AS Fith,
       TenthEnquiry.SentDate AS Tenth,
       TwentiethEnquiry.SentDate AS Twentieth,
FROM FifthEnquiry
  JOIN TenthEnquiry ON FifthEnquiry.emailaddress = TenthEnquiry.emailaddress
  JOIN TwentiethEnquiry ON FifthEnquiry.emailaddress = TwentiethEnquiry.emailaddress
WHERE (FifthEnquiry.rk = 5)
AND   (TenthEnquiry.rk = 10)
AND   (TwentiethEnquiry.rk = 20)

Answer 1

您可以大大简化。并使用 LEFT JOIN在 GROUP BY 之后保留至少 5 行的所有电子邮件地址，即使没有第 10 行或第 20 行:

WITH cte AS (
   SELECT emailaddress, SentDate,
          ROW_NUMBER() OVER (PARTITION BY emailaddress
                             ORDER BY COUNT(*) DESC, SentDate) AS rn
   FROM   SentEmails
   GROUP  BY 1,2
   )
SELECT enq05.emailaddress,
       enq05.SentDate AS fifth,
       enq10.SentDate AS tenth,
       enq20.SentDate AS twentieth
FROM        cte AS enq05
LEFT   JOIN cte AS enq10 ON enq10.emailaddress = enq05.emailaddress
                        AND enq10.rn = 10
LEFT   JOIN cte AS enq20 ON enq20.emailaddress = enq05.emailaddress
                        AND enq20.rn = 20
WHERE  enq05.rn = 5;

• 您不需要单独的 CTE，所有三个都在做同样的事情。 一个 CTE 就足够了，而且速度显然更快。在外部查询中改用具有不同表别名的自联接。

• 因为我们使用 LEFT JOIN 现在，重要的是我们是在 JOIN 还是 WHERE 子句中放置附加条件。 WHERE 子句中的条件有效地强制 Postgres 将连接视为普通 [INNER] JOIN。 .我相应地将条件移动到 JOIN 子句。详情:

  • Join a count query on a generate_series in postgres and also retrieve Null-values as "0"

• 使用 rn , 不是 rk作为列别名。它是“行号”，而不是“排名”。注意 row_number() and rank() 之间重要的差异行为。 .

• 添加SentDate至 ORDER BY 作为 (emailaddress, SentDate) 的决胜局具有相同的计数以获得稳定的排序顺序。我的方式SentDate IS NULL每组排在最后。您可能想使用 NULLS LAST对于降序排序(不适用于 COUNT(*) ，它永远不会为 NULL):

  • PostgreSQL sort by datetime asc, null first?

• 您还需要注意一个微妙的细节:tenth和 twentieth如果SentDate，由于两个不同的原因，结果中都可以为 NULL在基础表中可以为 NULL。 tenth 的 NULL 值结果可能意味着 emailaddress 的不同值少于 10 个或者它可能意味着 NULL 根据您的排序顺序位于第 10 位。