交叉应用的SQL查询优化

147 发表于 Dev

147

问题：为所有自2015-06-30开始没有交易的客户获取上次交易的详细信息。交易类型不为2。

我想出了以下内容。虽然可以提供所需的结果，但效率似乎很低。有什么改善吗？

    SELECT
          LastCustTrans.CustAccount 
        , LastCustTrans.DimID       
        , LastCustTrans.TransDate       
    FROM
    (   SELECT      CustTrans.CustAccount
        FROM        @CustTrans AS [CustTrans]
        WHERE       CustTrans.TransType != 2
        GROUP BY    CustTrans.CustAccount
        HAVING      MAX(CustTrans.TRANSDATE) < DATEADD(MONTH, -6, GETDATE())
    )   AS [LastCust]
        CROSS APPLY 
        (   SELECT TOP 1 *
            FROM @CustTrans AS [CUSTTRANS]
            WHERE   CustTrans.CustAccount   = LastCust.CustAccount
                AND CustTrans.TransType != 2
            ORDER BY CustTrans.TransDate DESC 
        ) AS LastCustTrans
    ORDER BY LastCustTrans.CustAccount

样本数据：

    DECLARE @CustTrans TABLE
    (     CustAccount   NVARCHAR(3)
        , TransDate     DATE
        , TransType     INT
        , DimID         NVARCHAR(3)
        , Amt           NUMERIC(32, 16)
        , RECID         INT
    )

    INSERT INTO @CustTrans 
        VALUES    ('C01', '2015-06-15', 1, 'D01', 10, 1) 
                , ('C01', '2015-11-15', 1, 'D01', 15, 2)
                , ('C01', '2015-04-15', 1, 'D02', 20, 3)
                , ('C02', '2015-05-15', 1, 'D03', 25, 4)
                , ('C02', '2015-03-15', 1, 'D04', 30, 5)
                , ('C02', '2015-12-15', 2, 'D03', 35, 6)

预期产量

    CustAccount DimID   TransDate
    C02         D03     2015-05-15

乔治·贝索斯（Giorgos Betsos）

您可以尝试一下：

SELECT CustAccount, DimID, TransDate
FROM (
  SELECT CustAccount, DimID, TransDate,
         MAX(TransDate) OVER (PARTITION BY CustAccount) AS maxDate,
         ROW_NUMBER() OVER (PARTITION BY CustAccount 
                            ORDER BY TransDate DESC) AS rn
  FROM CustTrans
  WHERE TransType != 2) AS t
WHERE t.rn = 1 AND maxDate < DATEADD(MONTH, -6, GETDATE())

查询的问题很可能是由于查询的相关性CROSS APPLY。上面的查询专门使用窗口函数，因此效率可能更高。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。