sql - 用于选择首选糖果的高效 SQL 2000 查询

Question

(我希望我能想出一个更具描述性的标题...建议一个或编辑这篇文章，如果你能说出我要问的查询类型的话)

数据库:SQL Server 2000

示例数据(假设 500,000 行):

Name   Candy       PreferenceFactorJim    Chocolate   1.0Brad   Lemon Drop   .9Brad   Chocolate    .1Chris  Chocolate    .5Chris  Candy Cane   .5499,995 more rows...

请注意，具有给定“名称”的行数是无限的。

期望的查询结果:

Jim    Chocolate   1.0Brad   Lemon Drop   .9Chris  Chocolate    .5~250,000 more rows...

(由于 Chris 对 Candy Cane 和 Chocolate 的偏好相同，因此一致的结果就足够了)。

问题:如何从数据中选择 Name, Candy，其中每个结果行都包含一个唯一的名称，这样所选的 Candy 对于每个名称都具有最高的 PreferenceFactor。 (首选快速有效的答案)。

表上需要哪些索引？如果 Name 和 Candy 是另一个表的整数索引(除了需要一些连接)，这会有什么不同吗？

Answer 1

您会发现以下查询优于给出的所有其他答案，因为它适用于单次扫描。这模拟了 MS Access 的第一个和最后一个聚合函数，这基本上就是您正在做的。

当然，您的 CandyPreference 表中可能有外键而不是名称。要回答您的问题，实际上最好是 Candy 和 Name 是另一个表的外键。

如果 CandyPreferences 表中还有其他列，那么使用包含相关列的覆盖索引会产生更好的性能。使列尽可能小将增加每页的行数并再次提高性能。如果您最常使用 WHERE 条件进行查询以限制行，那么覆盖 WHERE 条件的索引就变得很重要。

Peter 在这方面走在了正确的轨道上，但有一些不必要的复杂性。

CREATE TABLE #CandyPreference (
   [Name] varchar(20),
   Candy varchar(30),
   PreferenceFactor decimal(11, 10)
)
INSERT #CandyPreference VALUES ('Jim', 'Chocolate', 1.0)
INSERT #CandyPreference VALUES ('Brad', 'Lemon Drop', .9)
INSERT #CandyPreference VALUES ('Brad', 'Chocolate', .1)
INSERT #CandyPreference VALUES ('Chris', 'Chocolate', .5)
INSERT #CandyPreference VALUES ('Chris', 'Candy Cane', .5)

SELECT
   [Name],
   Candy = Substring(PackedData, 13, 30),
   PreferenceFactor = Convert(decimal(11,10), Left(PackedData, 12))
FROM (
   SELECT
      [Name],
      PackedData = Max(Convert(char(12), PreferenceFactor) + Candy)
   FROM CandyPreference
   GROUP BY [Name]
) X

DROP TABLE #CandyPreference

实际上我不推荐这种方法，除非性能很关键。做到这一点的“规范”方法是 OrbMan 的标准 Max/GROUP BY 派生表，然后连接到它以获取选定的行。但是，当有多个列参与 Max 的选择时，该方法开始变得困难，并且选择器的最终组合可以重复，也就是说，当没有列提供任意唯一性时，就像这里的情况一样如果 PreferenceFactor 相同，我们使用该名称。

编辑:最好提供更多使用说明，以帮助提高清晰度并帮助人们避免问题。

• 根据一般经验，在尝试提高查询性能时，如果可以节省 I/O，您可以做很多额外的数学运算。保存整个表查找或扫描可显着加快查询速度，即使包含所有转换和子字符串等。
• 由于精度和排序问题，使用 float 据类型可能不是这种方法的好主意。尽管除非您处理的是非常大或非常小的数字，否则您无论如何都不应该在数据库中使用 float 。
• 最好的数据类型是那些在转换为二进制或字符后未按相同顺序打包和排序的数据类型。 Datetime、smalldatetime、bigint、int、smallint 和 tinyint 都直接转换为二进制并正确排序，因为它们没有打包。对于二进制，避免使用 left() 和 right()，使用 substring() 将值可靠地返回到它们的原始值。
• 我利用了在此查询中小数点前只有一位数字的 Preference，允许直接转换为 char，因为小数点前总是至少有一个 0。如果可能有更多数字，则必须对转换后的数字进行十进制对齐，以便正确排序。最简单的方法可能是乘以您的偏好等级，这样就没有小数部分，转换为 bigint，然后转换为 binary(8)。通常，数字之间的转换比 char 和其他数据类型之间的转换更快，尤其是日期数学。
• 注意空值。如果有，您必须将它们转换成某种东西，然后再转换回来。