sql - 优化多对多sql查询

Question

我有两个具有多对多关系的表，我加入了`userId，然后对值进行求和/计数，以便获得它们的摘要。这是它的外观：

        select COUNT(DISTINCT [s].[UserId]) AS NumberOfCustomers, 
        sum(s.[ProbabilityAlive]) as ProbabilityAlive,
        avg([s].[ExpectedMonetaryValue]) as ExpectedMonetaryValue, sum(ExpectedTransactions) as ExpectedTransactions,
        sum(s.[CustomerLifetimeValue]) as CustomerLifetimeValue, sum(s.[ResidualLifeTimeValue]) as ResidualLifetimeValue, 
        sum(s.[Transactions]) as Transactions, sum(s.[TotalSpend]) as TotalSpend,
        sum(s.[DeltaProbabilityAlive]) as DeltaProbabilityAlive, sum(s.[ProbabilityActive]) as ProbabilityActive,
        sum(s.[DeltaProbabilityActive]) as DeltaProbabilityActive,
        sum(s.[DeltaEstimatedNumberOfTransactionsShort]) as DeltaEstimatedNumberOfTransactionsShort, sum(s.[EstimatedNumberOfTransactionsLong]) as EstimatedNumberOfTransactionsLong,
        sum(s.[DeltaEstimatedNumberOfTransactionsLong]) as DeltaEstimatedNumberOfTransactionsLong, sum(s.[EstimatedNumberOfTransactionsLongLeft]) as EstimatedNumberOfTransactionsLongLeft,
        sum(s.[ResidualLifeTimeValueLeft]) as ResidualLifeTimeValueLeft, sum(s.[CustomerLifetimeValueLeft]) as CustomerLifetimeValueLeft,
        SUM(a.PeakClv) as PeakClv
        FROM (
        SELECT DISTINCT([Clv].[UserId]), sum([dbo].[Clv].[ProbabilityAlive]) as ProbabilityAlive,
        avg([Clv].[ExpectedMonetaryValue]) as ExpectedMonetaryValue, sum([dbo].[Clv].[EstimatedNumberOfTransactionsShort]) as ExpectedTransactions,
        sum([Clv].[CustomerLifetimeValue]) as CustomerLifetimeValue, sum([dbo].[Clv].[ResidualLifeTimeValue]) as ResidualLifetimeValue, 
        sum([dbo].[Clv].[NumberOfTransactions]) as Transactions, sum([dbo].[Clv].[TotalSpend]) as TotalSpend,
        sum([dbo].[Clv].[DeltaProbabilityAlive]) as DeltaProbabilityAlive, sum([dbo].[Clv].[ProbabilityActive]) as ProbabilityActive,
        sum([dbo].[Clv].[DeltaProbabilityActive]) as DeltaProbabilityActive,
        sum([dbo].[Clv].[DeltaEstimatedNumberOfTransactionsShort]) as DeltaEstimatedNumberOfTransactionsShort, sum([dbo].[Clv].[EstimatedNumberOfTransactionsLong]) as EstimatedNumberOfTransactionsLong,
        sum([dbo].[Clv].[DeltaEstimatedNumberOfTransactionsLong]) as DeltaEstimatedNumberOfTransactionsLong, sum([dbo].[Clv].[EstimatedNumberOfTransactionsLongLeft]) as EstimatedNumberOfTransactionsLongLeft,
        sum([dbo].[Clv].[ResidualLifeTimeValueLeft]) as ResidualLifeTimeValueLeft, sum([dbo].[Clv].[CustomerLifetimeValueLeft]) as CustomerLifetimeValueLeft  
        FROM [Clv]  WHERE Clv.[ClientId] = '2' AND   Clv.[CalculationDate] = '02/28/2021' AND  Clv.[Product] = 'Total'  GROUP BY UserId ) s
        LEFT JOIN (
            SELECT  DISTINCT(UserId), SUM([PeakClv]) as PeakClv
            FROM [dbo].[AdditionalClvData]
            WHERE ClientId = '2' AND CalculationDate = '02/28/2021'
            GROUP BY [UserId]
        ) a ON a.[UserId] = s.[UserId]

我从一个表中获取几乎所有值，从另一个表中获取一个值，并在子查询中使用预聚合。从单个表中选择需要几秒钟，但此查询大约需要 20 秒。我是否在错误地加入/聚合？有没有办法优化，让它工作得更快？

标签： sqlsql-server