sql - Oracle——优化SQL查询

Question

我有两个表 - 国家/地区(id、名称)和用户(id、名称、country_id)。每个用户属于一个国家。我想从同一个随机国家/地区随机选择 10 个用户。但是，有些国家/地区的用户数少于 10 个，因此我无法使用它们。我只需从至少有 10 个用户的国家/地区中进行选择。

我可以写这样的东西:

SELECT * FROM(
   SELECT * 
   FROM users u
   {MANY_OTHER_JOINS_AND_CONDITIONS}
   WHERE u.country_id = 
  (
    SELECT * 
    FROM
    (
      SELECT c.id 
      FROM countries c
      JOIN 
      (
        SELECT users.country_id, COUNT(*) as cnt 
        FROM users 
        {MANY_OTHER_JOINS_AND_CONDITIONS}
        GROUP BY users.country_id
      ) X ON X.country_id = c.id 
      WHERE X.cnt >= 10 
      ORDER BY DBMS_RANDOM.RANDOM
    ) Y
    WHERE ROWNUM = 1
  ) 
  ORDER BY DBMS_RANDOM.RANDOM
) Z WHERE ROWNUM < 10

但是，在我的实际场景中，我有更多条件并连接到其他表来确定哪个用户适用。通过使用此查询，我必须在两个位置具有这些条件 - 在实际选择数据的查询中和在计数子查询中。

有什么方法可以编写这样的查询，但在两个地方没有其他条件(这可能在性能方面不太好)？

Answer 1

您可以使用 CTE 作为用户条件，以避免重复逻辑，并允许数据库缓存该设置一次(尽管根据我的经验，数据库在这方面并不像应有的那么好，所以请检查您的执行情况计划)。

与 Oracle 相比，我更喜欢 Sql Server，而且语法略有不同，因此可能还需要一些调整，但请尝试以下操作:

WITH SafeUsers (ID, Name, country_id) As
(
   --criteria for users only has to specified here
   SELECT ID, Name, country_id
   FROM users
   WHERE ... 
),
RandomCountry (ID) As 
( 
    SELECT ID 
    FROM (
        SELECT u.country_id AS ID
        FROM SafeUsers u -- but we reference it HERE
        GROUP BY u.country_id
        HAVING COUNT(u.Id) >= 10
        ORDER BY DBMS_RANDOM.RANDOM
    ) c
    WHERE ROWNUM = 1
)
SELECT u.* 
FROM (
    SELECT s.*        
    FROM SafeUsers s -- and HERE
    INNER JOIN RandomCountry r ON s.country_id = r.ID
    ORDER BY DBMS_RANDOM.RANDOM
) u
WHERE ROWNUM <= 10

通过删除嵌套并为每个中间步骤引入名称，该查询突然变得更易于阅读和维护。