基于列值变化（历史日期）的结果集获取及投资积分计算问题

Let's break this down and refine your approach—you're heading in the right direction, but we can simplify the SQL logic to be more reliable, readable, and efficient for both scenarios (rate changes inside or outside the 13-month window).

First: Align on Core Requirements #

You need two key components for your investment scoring:

• A dataset that includes the last interest rate change record AND the most recent 13 months of data (no unnecessary duplicates)
• Calculations for three scores based on these rules:
  1. Current inflation_rate < value from 1 year ago → +1 point
  2. Current exchange_rate < value from 1 year ago → +1 point
  3. The last interest rate change was a decrease → +1 point

Step 1: Simplify Data Retrieval #

Instead of using UNION (which can create redundant data and extra processing), use window functions like LAG() to identify the last interest rate change, then filter your dataset to include everything from either that change date or 13 months ago—whichever is earlier.

Here's the refined SQL:

WITH rate_changes AS (
  -- Compare each interest rate to the previous (older) record
  SELECT 
    date,
    interest_rate,
    LAG(interest_rate) OVER (ORDER BY date) AS previous_interest_rate
  FROM indicators
),
last_interest_change AS (
  -- Grab the most recent date where interest rate changed, plus rate values
  SELECT date AS change_date, interest_rate, previous_interest_rate
  FROM rate_changes
  WHERE interest_rate != previous_interest_rate
  ORDER BY date DESC
  LIMIT 1
),
thirteen_month_cutoff AS (
  -- Calculate the date 13 months before today
  SELECT CURRENT_DATE - INTERVAL '13 months' AS cutoff_date
)
SELECT i.*
FROM indicators i
CROSS JOIN last_interest_change lic
CROSS JOIN thirteen_month_cutoff tmc
-- Include all data from the earlier of the two cutoff dates
WHERE i.date >= LEAST(lic.change_date, tmc.cutoff_date)
ORDER BY i.date DESC;

Why this works better: #

• If the last rate change is inside the 13-month window, LEAST() picks the 13-month cutoff, so you get all recent data (which already includes the change record)
• If the last rate change is outside the 13-month window, LEAST() picks the change date, so you get the critical change record plus all subsequent data (including the last 13 months)
• No duplicate records, and the logic is far easier to debug than nested subqueries.

Step 2: Calculate Your Investment Scores #

You can compute the scores directly in one query without needing to fetch the full dataset first. This keeps your logic consolidated and efficient:

WITH current_month AS (
  -- Get the latest indicator data
  SELECT * FROM indicators ORDER BY date DESC LIMIT 1
),
year_ago_month AS (
  -- Get the indicator data from exactly 1 year prior
  SELECT * 
  FROM indicators 
  WHERE date <= (SELECT date - INTERVAL '1 year' FROM current_month)
  ORDER BY date DESC LIMIT 1
),
last_interest_change AS (
  -- Find the most recent interest rate change and its prior value
  SELECT 
    interest_rate AS new_rate,
    LAG(interest_rate) OVER (ORDER BY date) AS old_rate
  FROM indicators
  WHERE interest_rate != LAG(interest_rate) OVER (ORDER BY date)
  ORDER BY date DESC
  LIMIT 1
)
SELECT
  -- Inflation score
  CASE WHEN (SELECT inflation_rate FROM current_month) < (SELECT inflation_rate FROM year_ago_month) THEN 1 ELSE 0 END AS inflation_score,
  -- Exchange rate score
  CASE WHEN (SELECT exchange_rate FROM current_month) < (SELECT exchange_rate FROM year_ago_month) THEN 1 ELSE 0 END AS exchange_score,
  -- Interest rate decrease score
  CASE WHEN (SELECT new_rate FROM last_interest_change) < (SELECT old_rate FROM last_interest_change) THEN 1 ELSE 0 END AS interest_rate_score,
  -- Total combined score
  (CASE WHEN (SELECT inflation_rate FROM current_month) < (SELECT inflation_rate FROM year_ago_month) THEN 1 ELSE 0 END) +
  (CASE WHEN (SELECT exchange_rate FROM current_month) < (SELECT exchange_rate FROM year_ago_month) THEN 1 ELSE 0 END) +
  (CASE WHEN (SELECT new_rate FROM last_interest_change) < (SELECT old_rate FROM last_interest_change) THEN 1 ELSE 0 END) AS total_score;

Applying this to your sample data: #

• Current inflation_rate (1.1) < 1 year ago (2.0) → +1
• Current exchange_rate (1.2321) > 1 year ago (1.0691) → +0
• Last interest rate change: 0.05 → 0.0 (a decrease) → +1
• Total score: 2

Note on Your Original UNION Approach #

Your UNION method does work when the rate change is outside the 13-month window, but it's redundant when the change falls within the window—you're combining two overlapping datasets, and UNION has to do extra work to remove duplicates. The window function method avoids this entirely while keeping your logic clear.

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