ORDERBY的意外结果

html5 • • 问答

我有以下查询：

SELECT    
    D.[Year] AS [Year]
    , D.[Month] AS [Month]
    , CASE
        WHEN f.Dept IN ('XSD') THEN 'Marketing'
        ELSE f.Dept 
    END AS DeptS 
    , COUNT(DISTINCT f.OrderNo) AS CountOrders
FROM Sales.LocalOrders AS l WITH 
INNER JOIN Sales.FiscalOrders AS f 
    ON l.ORDER_NUMBER = f.OrderNo
INNER JOIN Dimensions.Date_Dim AS D 
    ON CAST(D.[Date] AS DATE) = CAST(f.OrderDate AS DATE)
WHERE YEAR(f.OrderDate) = 2019
AND f.Dept IN ('XSD', 'PPM', 'XPP')
GROUP BY 
    D.[Year]
    , D.[Month]
    , f.Dept 
ORDER BY 
    D.[Year] ASC
    , D.[Month] ASC

我得到以下结果，ORDER BY因为我们可以看到它没有被订购，所以 Month 列没有给出正确的结果：

Year    Month   Depts       CountOrders
2019    1       XSD         200
2019    10      PPM         290
2019    10      XPP         150
2019    2       XSD         200
2019    3       XPP         300

预期输出：

Year    Month   Depts       CountOrders
2019    1       XSD         200
2019    2       XSD         200
2019    3       XPP         300
2019    10      PPM         290
2019    10      XPP         150

回答

您的查询

它是按月订购，因为你D.[Month]就像是在一个文本字符串处理ORDER BY条款。

你可以做两件事之一来解决这个问题：

  • 使用两位数的月份数字（例如 01...12）
  • 为ORDER BY子句使用将被识别为代表月份的数据类型

快速修复

您可以通过快速更改ORDER BY子句来在代码中更正此问题，将这些列视为数字进行分析，这是通过将它们转换（“转换”）为整数数据类型来完成的，如下所示：

ORDER BY 
CAST(D.[Year] AS INT) ASC
,CAST(D.[Month] AS INT) ASC

这将更正您意外的查询结果，但不会解决根本原因，即您的基础数据（更多内容见下文）。

您的基础数据

您问题的根本原因是您的基础数据是如何存储和/或出现的。

您Month似乎显示为默认数据类型 (VarChar)，而不是更适合月份或日期的类型。

如果您管理或有权访问或控制数据库，最好考虑更正此问题。

在考虑这一点时，请注意潜在的背景和变更管理问题，包括：

  • 这是底层数据，还是其他地方的上游数据的表示？（例如，使用您无法控制的流程或定期重新定义的视图定期刷新的内容）
  • 哪些其他查询或流程依赖于这些数据当前的存储或显示方式（包括数据类型），如果您弄乱它可能会中断？
  • 如果更正，可能会出现验证问题吗？（例如从零、空、非数字或非日期数据的存储方式，即使无效）
  • 在您的环境中应遵循哪些变更管理实践？
  • 数据源是否处于高事务负载下？
  • 它是生产数据集吗？
  • 其他报告流程是否依赖于它？

这些问题都不是永远设置错误的好借口，这可能会使问题复杂化并引入其他问题。然而，这只是故事的一部分。

适当的方法（纠正或放弃）将取决于您的情况。在一个完美的教科书世界里，你会纠正它。在你的世界里，你必须做出决定。

更好的方法？

上面的解决方案是一种强制查询工作的快速而讨厌的方法。

CAST在选择和过滤结果之后，该解决方案在查询语法中较晚这一事实暗示这不是实现此目的的最优雅方式。

理想情况下，您可以在此过程中尽早转换数据类型：

  • 如果在底层数据而不是查询中完成，这是最终但可能不适合这种情况（见下文）
  • 如果在查询中完成，请尝试更早地完成。

在您的情况下，您的 GROUP BY 和 ORDER BY 都使用看起来是原始查询结果中冗余数据的列，也就是说，您将获得 DATE、MONTH 和 YEAR。理想情况下，您只需获得一个 DATE，然后使用该日期的 MONTH 或 YEAR。您的问题是您的日期实际上不是日期（请参阅上面的“基础数据”），其中：

  • 在 DATE 的情况下，在您的INNER JOIN行中进行转换ON CAST(D.[Date] AS DATE) = CAST(f.OrderDate AS DATE)（可能会尽量减少连接问题）
  • 在 D.[year] 和 D.[month] 的情况下，没有转换（这就是为什么我们仍然需要将它们进一步向下转换，在ORDER BY）

您可以考虑忽略 D.[month] 并使用MONTH DATEPART计算自DATE，这将避免CAST在ORDER BY子句中使用的需要。

在你的例子中，这种方法是一种中间立场。快速修复包含在此答案的顶部，最佳修复是更正基础数据。最后一部分考虑优化快速修复，但没有纠正潜在问题。提及它只是为了提高认识并避免CAST在ORDER BY子句中推广使用作为使用良好的干净查询语法解决问题的最合法方式。

在您选择的不需要的列（例如所有列D？）、是否从日期计算月份或单独的月份列、是否在过滤之前转换到日期之间也存在潜在的性能权衡，等等。这些超出了本解决方案的范围。

所以：

  • 即时解决方案：使用快速修复
  • 最佳的解决方案：它的工作后，考虑的基础数据（在您的情况）
以上是ORDERBY的意外结果的全部内容。
THE END
分享
二维码
< <上一篇
下一篇>>