问题
给定一个表格,功能,包含两个列:x和y。
如果x1 = y2和x2 = y1,则说两对(x1,y1)和(x2,y2)是对称对的。任务是编写一个SQL查询,该查询以x的值输出所有此类对称对,同时确保x1€y1。
解释
考虑以下输入:
预期的输出将是:
20 20
20 21
22 23
在这里,(20,20),(20,21),其对称对(21,20)和(22,23)及其对称对(23,22)是表中的对称对,分类按X。
按顺序排列解决方案
这是解决该问题的两个SQL解决方案,每个解决方案都强调了一种不同的方法及其各自的优势,劣势和结构。
直接加入row_number()
第一种方法使用通用表表达式(CTE)和直接联接:
WITH CTE AS (
SELECT
X,
Y,
ROW_NUMBER() OVER(ORDER BY X) [rn]
FROM Functions
)
SELECT DISTINCT
f1.X,
f1.Y
FROM CTE f1 JOIN CTE f2 ON f1.X = f2.Y AND f1.Y = f2.X
WHERE f1.X <= f1.Y
AND f1.rn != f2.rn
ORDER BY f1.X
在此方法中,首先创建一个CTE,并且表中的每一行使用ROW_NUMBER()
函数分配了一个唯一的数字。然后,在CTE上执行一个自连接,以匹配X和Y对称的行。 f1.rn != f2.rn
条件可确保同一行与自身匹配,这将导致假阳性结果。
使用row_number()存在
第二种方法还使用CTE和ROW_NUMBER()
函数,但是它使用EXISTS
运算符,而不是直接加入:
WITH CTE AS (
SELECT
X,
Y,
ROW_NUMBER() OVER(ORDER BY X, Y) [rn]
FROM Functions
)
SELECT DISTINCT
f1.X,
f1.Y
FROM CTE f1
WHERE EXISTS (
SELECT 1
FROM CTE f2
WHERE f1.X = f2.Y AND f1.Y = f2.X AND f1.rn <> f2.rn
)
AND f1.X <= f1.Y
ORDER BY f1.X
在这里,WHERE
子句中使用了一个子查询,以检查CTE中存在对称对的存在。如果子查询返回至少一行,则EXISTS
运算符将返回true,在这种情况下,这将表明存在对称对。
结论
这两种方法都有效地解决了问题,但是它们的性能可能会根据数据中数据的大小和分布而变化。通常,如果子查询返回几行,则使用EXISTS
的方法可以更快,因为它在找到匹配项后就停止执行。但是,如果子查询通常返回许多行,则直接联接方法可能会更有效。
性能也可能因使用的实际rdbms以及可用的系统资源(包括内存和CPU。
)而有所不同。您可以在HackerRank中找到原始问题。
有关更有见地的解决方案和与技术相关的内容,请随时在Beacons page上与我联系。