Excel多条件INDEX MATCH结合MIN函数查询问题咨询

Hey there! Let's work through this problem together. Your goal is to pull the unique product ID with the smallest percentage for every combination of date, issuer, and product type—here's how to do it effectively.

We need to group the data by three core dimensions: date, issuer, and product type, then within each group, select the product ID tied to the lowest percentage value. Below are two reliable methods, depending on your database capabilities.

1. Window Functions (Most Efficient & Clean) #

Window functions are the go-to for this kind of grouped ranking task. We'll use either ROW_NUMBER() or RANK() to sort each group by percentage, then pick the top result(s).

Example Code (Works in PostgreSQL, SQL Server, MySQL 8+, etc.) #

Assuming your table is named product_data with columns: record_date (date), issuer, product_type, product_id (unique ID), percentage:

WITH ranked_products AS (
    SELECT 
        record_date,
        issuer,
        product_type,
        product_id,
        percentage,
        -- Assign a rank to each product in the group, sorted by smallest percentage first
        ROW_NUMBER() OVER (
            PARTITION BY record_date, issuer, product_type 
            ORDER BY percentage ASC
        ) AS rank_num
    FROM product_data
)
SELECT 
    record_date,
    issuer,
    product_type,
    product_id
FROM ranked_products
WHERE rank_num = 1;

Handle Ties (Multiple Products with the Same Minimum Percentage) #

If you want to keep all products that share the lowest percentage in a group, replace ROW_NUMBER() with RANK():

WITH ranked_products AS (
    SELECT 
        record_date,
        issuer,
        product_type,
        product_id,
        percentage,
        RANK() OVER (
            PARTITION BY record_date, issuer, product_type 
            ORDER BY percentage ASC
        ) AS rank_num
    FROM product_data
)
SELECT 
    record_date,
    issuer,
    product_type,
    product_id
FROM ranked_products
WHERE rank_num = 1;

2. Alternative for Older Databases (No Window Functions) #

If you're using an older database that doesn't support window functions (like MySQL 5.x), use a correlated subquery to find the minimum percentage per group, then match it back to the product ID:

SELECT 
    p1.record_date,
    p1.issuer,
    p1.product_type,
    p1.product_id
FROM product_data p1
WHERE p1.percentage = (
    SELECT MIN(p2.percentage)
    FROM product_data p2
    WHERE p2.record_date = p1.record_date
      AND p2.issuer = p1.issuer
      AND p2.product_type = p1.product_type
);

This will return all products with the minimum percentage. If you only want one per group, add LIMIT 1 inside the subquery (make sure to add an ORDER BY clause to control which product is selected, e.g., ORDER BY product_id ASC LIMIT 1).

Key Notes #

• Ensure your percentage column is stored as a numeric type (not a string) — otherwise, the MIN() function won't calculate correctly.
• If your data has NULL values in the percentage column, add WHERE percentage IS NOT NULL to your queries (adjust based on whether you want to include or exclude these records).

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