MariaDB中获取日回报率最高TOP2产品的SQL实现问询

Hey Carol, let's fix this up for your MariaDB 10.1.39 setup! Your current query calculates daily returns but doesn't focus on the latest date or handle sorting/limiting correctly. Here's a tailored solution:

Step-by-Step Explanation & SQL Query #

We need to:

1. Match each product's latest day close value with its previous day's close
2. Calculate daily return using your specified formula
3. Filter only the latest date's records
4. Sort returns from highest to lowest and pick the top 2

SELECT 
    t1.id,
    t1.date,
    t1.product_name,
    t1.close,
    ROUND((t1.close - t2.close) / t2.close, 9) AS daily_return
FROM prices t1
INNER JOIN prices t2 
    ON t1.product_name = t2.product_name
    AND t2.date = DATE_SUB(t1.date, INTERVAL 1 DAY)
WHERE t1.date = (SELECT MAX(date) FROM prices)  -- Target only the most recent date
ORDER BY daily_return DESC
LIMIT 2;

Breakdown of the Query: #

• JOIN Clause: We join the prices table to itself (t1 for current day, t2 for previous day) using product_name to ensure we're comparing the same product, and DATE_SUB to link each date with the day before it.
• WHERE Filter: (SELECT MAX(date) FROM prices) dynamically grabs the latest date in your table, so this works even if you add newer data later.
• Return Calculation: We use ROUND to match the decimal precision in your expected output (9 decimal places).
• Sort & Limit: ORDER BY daily_return DESC sorts returns from highest to lowest, then LIMIT 2 gives us the top 2 products.

Why This Is Better Than Your Current Query: #

• Your original query includes records from the last two days (due to DATE >= DATE(NOW()) - INTERVAL 1 DAY), while this one focuses only on the latest date's data.
• The JOIN approach is more efficient than nested subqueries for large datasets, as it avoids running a subquery for every single row.

Expected Output: #

内容的提问来源于stack exchange，提问作者Carol.Kar