基于历史滚动数据的全项聚合计算实现及报错求助

Hey there, let's break down how to fix your error and build the value trend report you need.

First, let's unpack why you're seeing that Subquery returned more than 1 value error: when you use a subquery as an expression (like in a SELECT clause), SQL expects it to return exactly one value per row. Your LAST_VALUE subquery was returning multiple values (one for each property) for each date, which violates that rule.

Now, let's solve this. The core goal is to ensure every property has a value on every date (using its most recent evaluation up to that date), then aggregate those values to get totals and averages. We'll use SQL Server syntax since your error message matches its behavior.

Step 1: Assume Your Base Table Structure #

Let's say your raw evaluation data is stored in a table named PropertyEvaluations with columns:

• [Date] (date of evaluation)
• Property (property identifier like A/B)
• [Value] (evaluated value)

Solution 1: Correlated Subquery Approach #

This method first creates all possible date-property pairs, then fetches the latest value for each pair before aggregating:

-- CTE 1: Get all unique dates and properties to create full pairs
WITH AllDatePropertyPairs AS (
    SELECT 
        d.[Date],
        p.Property
    FROM (SELECT DISTINCT [Date] FROM PropertyEvaluations) d
    CROSS JOIN (SELECT DISTINCT Property FROM PropertyEvaluations) p
),
-- CTE 2: Fetch the most recent value for each date-property pair
PropertyLatestValues AS (
    SELECT 
        adpp.[Date],
        adpp.Property,
        -- Subquery returns ONLY the latest value for the property up to the current date
        (SELECT TOP 1 [Value] 
         FROM PropertyEvaluations pe
         WHERE pe.Property = adpp.Property 
           AND pe.[Date] <= adpp.[Date]
         ORDER BY pe.[Date] DESC) AS LatestValue
    FROM AllDatePropertyPairs adpp
)
-- Final aggregation to get total and average per date
SELECT 
    [Date],
    SUM(LatestValue) AS Total,
    AVG(LatestValue * 1.0) AS Average -- Multiply by 1.0 to avoid integer division
FROM PropertyLatestValues
GROUP BY [Date]
ORDER BY [Date];

Solution 2: Window Function Approach #

If you prefer using LAST_VALUE (since you tried it initially), you can use window functions with a proper partition and frame to fill in missing values:

-- CTE 1: Create full date-property pairs and join with raw evaluation data
WITH OrderedEvaluations AS (
    SELECT 
        d.[Date],
        p.Property,
        pe.[Value],
        -- Use LAST_VALUE to carry forward the most recent value for each property
        LAST_VALUE(pe.[Value]) OVER (
            PARTITION BY p.Property 
            ORDER BY d.[Date]
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS LatestValue
    FROM (SELECT DISTINCT [Date] FROM PropertyEvaluations) d
    CROSS JOIN (SELECT DISTINCT Property FROM PropertyEvaluations) p
    LEFT JOIN PropertyEvaluations pe 
        ON pe.Property = p.Property 
        AND pe.[Date] = d.[Date]
)
-- Aggregate to get totals and averages
SELECT 
    [Date],
    SUM(LatestValue) AS Total,
    AVG(LatestValue * 1.0) AS Average
FROM OrderedEvaluations
GROUP BY [Date]
ORDER BY [Date];

Both Solutions Will Produce Your Desired Output #

"Value Trend of All Properties"
Date | Total | Average
1/5/2017 | 10 | 10
2/3/2017 | 18 | 9
2/20/2017 | 22 | 11
3/1/2017 | 21 | 10.5
4/10/2017 | 24 | 12

The key fix here is ensuring we retrieve one value per property-date pair before aggregating—this eliminates the multi-value subquery error while fulfilling your requirement of carrying forward the latest evaluation value until a new one is recorded.

内容的提问来源于stack exchange，提问作者Mi Cun