如何从records和scores表获取符合状态的连续X天分数平均值

Alright, let's tackle this problem. I'll split the solution into two clear parts: first pulling the raw qualifying data you need for charting, then calculating the average score over your target consecutive date range.

First, let's make some reasonable assumptions about your table structures (since you didn't specify them explicitly):

• records includes columns like record_id, personid, date, and status
• scores links to records (either via record_id, or directly via personid and date if that's how your data is structured)

This query grabs all the valid score entries that meet your status criteria, ordered chronologically for easy charting:

SELECT 
    r.date,
    s.score,
    r.status,
    r.personid
FROM 
    records r
-- Adjust the JOIN condition based on your actual table relationship
JOIN 
    scores s ON r.record_id = s.record_id
WHERE 
    r.personid = 133  -- Replace with your target person ID
    AND r.status IN ('T', 'P')  -- Only include valid statuses
    -- Define your consecutive X-day range here; example uses relative date to today
    AND r.date BETWEEN DATE_SUB(CURDATE(), INTERVAL X DAY) AND CURDATE()
ORDER BY 
    r.date ASC;

If your scores table links directly to records via personid and date (no shared record_id), update the JOIN line to:

JOIN scores s ON r.personid = s.personid AND r.date = s.date

Once you have the raw data, you can calculate the average over the same date range with this query:

SELECT 
    r.personid,
    ROUND(AVG(s.score), 2) AS average_score,  -- Round to 2 decimal places for readability
    CONCAT(DATE_SUB(CURDATE(), INTERVAL X DAY), ' to ', CURDATE()) AS date_range
FROM 
    records r
JOIN 
    scores s ON r.record_id = s.record_id
WHERE 
    r.personid = 133
    AND r.status IN ('T', 'P')
    AND r.date BETWEEN DATE_SUB(CURDATE(), INTERVAL X DAY) AND CURDATE()
GROUP BY 
    r.personid;

• Replace X with your actual number of consecutive days (e.g., 7 for a week, 30 for a month)
• If you need a fixed date range instead of a relative one (e.g., Jan 1 to Jan 31, 2024), swap the BETWEEN clause with explicit dates:

  AND r.date BETWEEN '2024-01-01' AND '2024-01-31'
  

• The raw data query is ordered by date to ensure your chart displays data in chronological order, which is critical for time-based visualizations.

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