簡化SELECT陳述句

所以我有一個我認為應該有效的宣告......但是它感覺非常不理想，我無法為我的生活弄清楚如何優化它。

我有以下表格：

• 交易
  • [ID] 是PRIMARY KEY IDENTITY
  • [Hash] 有一個UNIQUE約束
  • [BlockNumber] 有一個索引
• 轉移
  • [ID] 是PRIMARY KEY IDENTITY
  • [TransactionId] 是參考 [Transactions].[Id] 的外鍵
• 代幣價格
  • [ID] 是PRIMARY KEY IDENTITY
• 代幣價格嘗試
  • [ID] 是PRIMARY KEY IDENTITY
  • [TransferId] 是參考 [Transfers].[Id] 的外鍵

我想要做的是選擇所有的轉賬，并從他們的相關交易中選擇一些資料（一筆交易對多筆轉賬），我目前沒有存盤在與該轉賬相關的 TokenPrices 中的價格。

在查詢的第一部分，我得到了所有轉賬的串列，并計算了最近找到的代幣價格之間的差異。如果找不到，則為空（我最終要選擇的內容）。我允許交易時間戳的任一側有 3 小時 - 如果在該時間跨度內沒有找到任何內容，則它將為空。

其次，我從這個集合中進行選擇，首先確保 diff 為空，因為這意味著缺少價格，最后，代幣價格嘗試要么沒有嘗試獲取價格的條目，要么它有列出的嘗試少于 5 次，最后一次嘗試是在一周前。

我的布局方式導致 WHERE 子句中基本上有 3 個相同/相似的 SELECT 陳述句，感覺非常不理想......

我該如何改進這種方法？

        WITH [transferDateDiff] AS 
        (
            SELECT
                [t1].[Id],
                [t1].[TransactionId],
                [t1].[From],
                [t1].[To],
                [t1].[Value],
                [t1].[Type],
                [t1].[ContractAddress],
                [t1].[TokenId],
                [t2].[Hash],
                [t2].[Timestamp],
                ABS(DATEDIFF(SECOND, [tp].[Timestamp], [t2].[Timestamp])) AS diff 
            FROM
                [dbo].[Transfers] AS [t1]
                LEFT JOIN
                    [dbo].[Transactions] AS [t2] 
                    ON [t1].[TransactionId] = [t2].[Id]
                LEFT JOIN
                    (
                        SELECT
                            * 
                        FROM
                            [dbo].[TokenPrices]
                    )
                    AS [tp] 
                    ON [tp].[ContractAddress] = [t1].[ContractAddress]
                    AND [tp].[Timestamp] >= DATEADD(HOUR, - 3, [t2].[Timestamp]) 
                    AND [tp].[Timestamp] <= DATEADD(HOUR, 3, [t2].[Timestamp]) 
            WHERE
                [t1].[Type] < 2
        )


        SELECT
            [tdd].[Id],
            [tdd].[TransactionId],
            [tdd].[From],
            [tdd].[To],
            [tdd].[Value],
            [tdd].[Type],
            [tdd].[ContractAddress],
            [tdd].[TokenId],
            [tdd].[Hash],
            [tdd].[Timestamp]
        FROM
            [transferDateDiff] AS tdd
        WHERE
            [tdd].[diff] IS NULL AND
            (
                (
                    SELECT
                        COUNT(*)
                    FROM
                        [dbo].[TokenPriceAttempts] tpa
                    WHERE
                        [tpa].[TransferId] = [tdd].[Id]
                )
                = 0 OR
                (
                    (
                        SELECT
                            COUNT(*)
                        FROM
                            [dbo].[TokenPriceAttempts] tpa
                        WHERE
                            [tpa].[TransferId] = [tdd].[Id]
                    )
                    < 5 AND
                    (
                        DATEDIFF(DAY,
                            (
                                SELECT
                                    MAX([tpa].[Created])
                                FROM
                                    [dbo].[TokenPriceAttempts] tpa
                                WHERE
                                    [tpa].[TransferId] = [tdd].[Id]
                            ),
                            CURRENT_TIMESTAMP
                        ) >= 7
                    )
                )
            )

uj5u.com熱心網友回復：

這是幫助簡化的嘗試。我洗掉了所有真正不需要的 [括號]，除非您遇到類似保留關鍵字或名稱中帶有空格的列（開頭不好）。

無論如何，您的主查詢每個 ID 有 3 個選擇實體。為了消除這種情況，我對子查詢進行了 LEFT JOIN，該子查詢將所有型別 < 2 AND JOINS 的傳輸拉到一次價格嘗試中。這樣，結果將已經預先匯總了 count(*) 和 Max(Created) 完成一次，用于與您的 WITH CTE 宣告相關的相同轉移基礎。因此，您不必每次都繼續運行 3 個查詢，也不必查詢所有傳輸的整個表，只需查詢具有相同基礎型別 < 2 條件的那些。結果子查詢別名“PQ”（preQuery）

現在，這從每個 Id 的冗余計數中簡化了外部 WHERE 子句的可讀性。

WITH transferDateDiff AS 
(
SELECT
        t1.Id,
        t1.TransactionId,
        t1.From,
        t1.To,
        t1.Value,
        t1.Type,
        t1.ContractAddress,
        t1.TokenId,
        t2.Hash,
        t2.Timestamp,
        ABS( DATEDIFF( SECOND, tp.Timestamp, t2.Timestamp )) AS diff 
    FROM
        dbo.Transfers t1
            LEFT JOIN dbo.Transactions t2
                ON t1.TransactionId = t2.Id
                LEFT JOIN dbo.TokenPrices tp
                    ON t1.ContractAddress = tp.ContractAddress
                    AND tp.Timestamp >= DATEADD(HOUR, - 3, t2.Timestamp)
                    AND tp.Timestamp <= DATEADD(HOUR, 3, t2.Timestamp)
    WHERE
        t1.Type < 2
)


SELECT
        tdd.Id,
        tdd.TransactionId,
        tdd.From,
        tdd.To,
        tdd.Value,
        tdd.Type,
        tdd.ContractAddress,
        tdd.TokenId,
        tdd.Hash,
        tdd.Timestamp
    FROM
        transferDateDiff tdd
            LEFT JOIN
            ( SELECT
                    t1.Id,
                    COUNT(*) Attempts,
                    MAX(tpa.Created) MaxCreated
                FROM
                    dbo.Transfers t1
                        JOIN dbo.TokenPriceAttempts tpa
                            on t1.Id = tpa.TransferId
                WHERE
                    t1.Type < 2
                GROUP BY
                    t1.Id ) PQ
                on tdd.Id = PQ.Id
    WHERE
            tdd.diff IS NULL 
        AND (   PQ.Attempts IS NULL
            OR PQ.Attempts = 0
            OR (    PQ.Attempts < 5
                AND DATEDIFF(DAY, PQ.MaxCreated, CURRENT_TIMESTAMP ) >= 7
                )
            )

修訂以將 WITH CTE 洗掉到單個查詢中

SELECT
        t1.Id,
        t1.TransactionId,
        t1.From,
        t1.To,
        t1.Value,
        t1.Type,
        t1.ContractAddress,
        t1.TokenId,
        t2.Hash,
        t2.Timestamp
    FROM
        -- Now, this pre-query is left-joined to token price attempts
        -- so ALL Transfers of type < 2 are considered
        ( SELECT
                t1.Id,
                coalesce( COUNT(*), 0 ) Attempts,
                MAX(tpa.Created) MaxCreated
            FROM
                dbo.Transfers t1
                    LEFT JOIN dbo.TokenPriceAttempts tpa
                        on t1.Id = tpa.TransferId
            WHERE
                t1.Type < 2
            GROUP BY
                t1.Id ) PQ
            -- Now, we can just directly join to transfers for the rest
            JOIN dbo.Transfers t1
                on PQ.Id = t1.Id
                -- and the rest from the WITH CTE construct
                LEFT JOIN dbo.Transactions t2
                    ON t1.TransactionId = t2.Id
                    LEFT JOIN dbo.TokenPrices tp
                        ON t1.ContractAddress = tp.ContractAddress
                        AND tp.Timestamp >= DATEADD(HOUR, - 3, t2.Timestamp)
                        AND tp.Timestamp <= DATEADD(HOUR, 3, t2.Timestamp)
    WHERE
            ABS( DATEDIFF( SECOND, tp.Timestamp, t2.Timestamp )) IS NULL
        AND (   PQ.Attempts = 0
            OR (    PQ.Attempts < 5
                AND DATEDIFF(DAY, PQ.MaxCreated, CURRENT_TIMESTAMP ) >= 7 )
            )

uj5u.com熱心網友回復：

我不明白你為什么這樣做：

LEFT JOIN
(
    SELECT
        * 
    FROM
        [dbo].[TokenPrices]
)
AS [tp] 
ON [tp].[ContractAddress] = [t1].[ContractAddress]
AND [tp].[Timestamp] >= DATEADD(HOUR, - 3, [t2].[Timestamp]) 
AND [tp].[Timestamp] <= DATEADD(HOUR, 3, [t2].[Timestamp]) 

這不是一個

LEFT JOIN [dbo].[TokenPrices] as TP ...

這：

SELECT
    COUNT(*)
FROM
    [dbo].[TokenPriceAttempts] tpa
WHERE
    [tpa].[TransferId] = [tdd].[Id]

可能是另一個 CTE 而不是子...事實上，您的任何子查詢都可能是 CTE，這是 CTE 的一部分，它使事情更容易閱讀。

,TPA
AS
(
    SELECT COUNT(*)
    FROM [dbo].[TokenPriceAttempts] tpa
    WHERE [tpa].[TransferId] = [tdd].[Id]
)

標籤：sql sql服务器 查询优化

上一篇：如何列出每個供應商至少出現一次的類別中的產品？

下一篇：恢復JavascriptPath2D的元素