主页 / dba / 问题 / 31561
Accepted
W.Jackson
Asked: 2013-01-08 09:06:35 +0800 CST

此 Group By Query 中 MAX 的 T-SQL 用途

  • 772

我遇到了一些我很难理解的预先存在的 SQL。

SELECT
    MAX(I.Symbol) Symbol
,   MAX(I.Ticker) CUSIP
,   MAX(I.Name) Name
,   SUM(H.Quantity) TotalQuantity
,   SUM(H.MarketValue) TotalMarketValue
,   MAX(H.PriceLC) Price
,   MAX(I.CategoryCode5) BUY_SELL
,   MAX(I.EquivFactor1) PriceTgt
,   MAX(P.LastPrice) CurrPrice
,   MAX(I.AssetClass) Target
,   MAX(I.Industry) Industry
,   MAX(I.CategoryCode1) Risk
FROM
    HOLDINGS_SECURE H
,   INVESTMENTS I
,   PRICE P
WHERE
    H.Symbol = I.Symbol
    AND I.Product = 'stock'
    AND H.Quantity > 0
    AND I.CategoryCode5 NOT IN ('X', '')
    AND H.Symbol = P.Symbol
GROUP BY
    I.Symbol

Symbol、Ticker、Name、CategoryCode5、Assest Class、Industry 和 CategoryCode1 都是 varchar 字段。其余字段为小数。

我最有根据的猜测是，以某种方式使用 max 来避免多个分组列，但这如何返回正确的结果？

表关系图

t-sql group-by

2 个回答

  1. Best Answer

    您说：“我最有根据的猜测是，以某种方式使用 max 来避免多个分组列”

    那是对的。

    然后：“......但这怎么能返回正确的结果？”

    它返回正确的结果，因为Symbol是 theInvestments和Price表中的主键。P.column因此，a或 an上的任何聚合函数都在I.column聚合相同的值。当然是什么MAX(c)时候。c2, 2, 2 or 22

    查询是否可以以其他方式编写，可能没有所有这些聚合？是的，请参阅相关问题：为什么 GROUP BY 语句中的通配符不起作用？

    它必须是一个相当长的GROUP BY子句，或者将聚合移动到只有Holdings_Secure表（Symbol不是主键）的子查询中，然后加入其他两个：

    SELECT
    I.Symbol Symbol
,   I.Ticker CUSIP
,   I.Name Name

,   H.TotalQuantity
,   H.TotalMarketValue
,   H.Price

,   I.CategoryCode5 BUY_SELL
,   I.EquivFactor1 PriceTgt
,   P.LastPrice CurrPrice
,   I.AssetClass Target
,   I.Industry Industry
,   I.CategoryCode1 Risk
FROM
    ( SELECT 
          SUM(Quantity) TotalQuantity
      ,   SUM(MarketValue) TotalMarketValue
      ,   MAX(PriceLC) Price
      ,   Symbol
      FROM
          HOLDINGS_SECURE
      WHERE
          Quantity > 0
      GROUP BY
          Symbol
    ) H
  JOIN
    INVESTMENTS I
      ON H.Symbol = I.Symbol
  JOIN
    PRICE P
      ON H.Symbol = P.Symbol
WHERE
        I.Product = 'stock'
    AND I.CategoryCode5 NOT IN ('X', '') ;
    • 8

  2. 聚合可能更适合子查询，并且当该子查询后来加入另一个表 INVESTMENTS 和 PRICE 时更有意义 - 见下文。

    在这种情况下，您可以说它的“原因”可能是：

    • 开发人员缺乏使用 SQL 的经验
    • 在不支持表子查询的 SQL 平台上获得的开发人员经验
    • 开发者风格

    可能是表中的某些数据未正确规范化，或者与其他评论中建议的类似，或者可能只是完全错误。

    这是一个关于如何使用子查询更整洁地重写它的猜测，然后是另一个查询，如果事实证明有必要，它可能有助于分析异常数据。

    可能的重写：

    SELECT
    I.Symbol Symbol
,   I.Ticker CUSIP
,   I.Name Name
,   H.Quantity TotalQuantity
,   H.MarketValue TotalMarketValue
,   H.PriceLC Price
,   I.CategoryCode5 BUY_SELL
,   I.EquivFactor1 PriceTgt
,   P.LastPrice CurrPrice
,   I.AssetClass Target
,   I.Industry Industry
,   I.CategoryCode1 Risk
FROM (
    --begin holdings_secure_aggregated
    SELECT
    Symbol,
    SUM(Quantity) TotalQuantity,
    SUM(MarketValue) TotalMarketValue,
    MAX(PriceLC) PriceLC
    FROM HOLDINGS_SECURE
    WHERE Quantity > 0
    GROUP BY Symbol
) H, --end holdings_secure_aggregated
INVESTMENTS I, PRICE P
WHERE
    H.Symbol = I.Symbol
    AND I.Product = 'stock'
    AND I.CategoryCode5 NOT IN ('X', '')
    AND H.Symbol = P.Symbol
GROUP BY I.Symbol;

    您可以使用像这样丑陋的东西来查看哪些 I.Symbol 值（如果有）在使用 MAX 聚合的列中返回多个不同的值，并准确查看这些单独的值是什么。

    SELECT
    I.Symbol Symbol
,   I.Ticker CUSIP
,   I.Name Name
,   H.Quantity TotalQuantity
,   H.MarketValue TotalMarketValue
,   H.PriceLC Price
,   I.CategoryCode5 BUY_SELL
,   I.EquivFactor1 PriceTgt
,   P.LastPrice CurrPrice
,   I.AssetClass Target
,   I.Industry Industry
,   I.CategoryCode1 Risk
FROM
    HOLDINGS_SECURE H
,   INVESTMENTS I
,   PRICE P
WHERE
    H.Symbol = I.Symbol
    AND I.Product = 'stock'
    AND H.Quantity > 0
    AND I.CategoryCode5 NOT IN ('X', '')
    AND H.Symbol = P.Symbol
    AND EXISTS (
        SELECT I.Symbol
        FROM
            HOLDINGS_SECURE H
        ,   INVESTMENTS I
        ,   PRICE P
        WHERE
            H.Symbol = I.Symbol
            AND I.Product = 'stock'
            AND H.Quantity > 0
            AND I.CategoryCode5 NOT IN ('X', '')
            AND H.Symbol = P.Symbol
        GROUP BY
            I.Symbol
        HAVING (
            COUNT(DISTINCT I.Ticker)
            + COUNT(DISTINCT I.Name)
            + COUNT(DISTINCT H.PriceLC)
            + COUNT(DISTINCT I.CategoryCode5)
            + COUNT(DISTINCT I.EquivFactor1)
            + COUNT(DISTINCT P.LastPrice)
            + COUNT(DISTINCT I.AssetClass)
            + COUNT(DISTINCT I.Industry)
            + COUNT(DISTINCT I.CategoryCode1)
        )
        > 0
    );
    • 4

相关问题