MySQL多记录查询问题：按日期获取TF/IDF计算Top10结果

Got it, let's tackle this issue head-on. The problem you're seeing—too many records per date and messed-up row numbering—usually boils down to not correctly configuring the window function for ranking. Let's walk through how to fix this step by step.

First, let's assume your current query might look something like this (the common culprit is missing proper partitioning and ordering in the row number logic):

SELECT 
  date,
  term,
  tf_idf_score,
  ROW_NUMBER() OVER () AS row_num
FROM (
  -- Your existing TF/IDF calculation subquery
  SELECT 
    date,
    term,
    (tf * idf) AS tf_idf_score
  FROM your_tf_idf_dataset
) raw_calculations

The Core Fix: Partition & Order the Window Correctly #

To get exactly 1-10 unique row numbers per date (only the top 10 TF/IDF scores), you need three key adjustments:

• Use PARTITION BY date to group the ranking logic by each individual date
• Order the window by tf_idf_score DESC so the highest scores get the lowest (most important) row numbers
• Filter the final results to only keep rows where row_num <= 10

Here's the corrected query structure tailored to your needs:

WITH tf_idf_calculations AS (
  -- Replace this with your actual TF/IDF computation logic
  SELECT 
    date,
    term,
    -- Example TF/IDF formula (swap with your real calculation)
    (term_frequency * inverse_document_frequency) AS tf_idf_score
  FROM your_source_data
  -- Add any joins, filters, or aggregations needed for your TF/IDF here
)
SELECT 
  date,
  term,
  tf_idf_score,
  row_num
FROM (
  SELECT 
    *,
    -- This is the critical line: partition by date, order by score descending
    ROW_NUMBER() OVER (PARTITION BY date ORDER BY tf_idf_score DESC) AS row_num
  FROM tf_idf_calculations
) ranked_results
WHERE row_num <= 10
ORDER BY date, row_num;

Quick Notes for Edge Cases: #

• If you want to include tied scores (instead of arbitrarily picking one when scores are equal), swap ROW_NUMBER() with RANK() or DENSE_RANK(). Just keep in mind this might return slightly more than 10 records per date if multiple terms tie for the 10th spot.
• Double-check your date column is consistent (all values like 20160401 as integers or strings) so the PARTITION BY groups dates correctly.
• Verify your TF/IDF calculation outputs valid numeric scores—if it's returning non-orderable values, the ranking will break.

This setup will ensure each date only has rows with row numbers 1 through 10, giving you the exact Top10 TF/IDF results you need per date.

内容的提问来源于stack exchange，提问作者Mustafa K.