如何用SQL查询按多列分组的PostgreSQL指定日期有效价目表数据

假设你的价目表表名为price_list，如果实际表名不同可自行替换。

方案1：窗口函数实现（推荐，性能与可读性最优） #

通过ROW_NUMBER()窗口函数对每个产品、变体、币种组合的有效记录按生效日期倒序排序，取排名第一的记录即可：

-- 查询2021-01-20的有效价目
WITH ranked_prices AS (
    SELECT 
        id,
        productId,
        variantId,
        validFrom,
        currencyCode,
        price,
        ROW_NUMBER() OVER (
            PARTITION BY productId, variantId, currencyCode 
            ORDER BY validFrom DESC
        ) AS rn
    FROM price_list
    WHERE validFrom <= '2021-01-20'::DATE
)
SELECT id, productId, variantId, validFrom, currencyCode, price
FROM ranked_prices
WHERE rn = 1;

如果需要同时查询多个日期的价目（比如同时查2021-01-20和2021-02-08），可以用如下写法：

WITH query_dates(q_date) AS (
    VALUES ('2021-01-20'::DATE), ('2021-02-08'::DATE)
),
ranked_prices AS (
    SELECT 
        pl.id,
        pl.productId,
        pl.variantId,
        pl.validFrom,
        pl.currencyCode,
        pl.price,
        qd.q_date,
        ROW_NUMBER() OVER (
            PARTITION BY pl.productId, pl.variantId, pl.currencyCode, qd.q_date
            ORDER BY pl.validFrom DESC
        ) AS rn
    FROM price_list pl
    CROSS JOIN query_dates qd
    WHERE pl.validFrom <= qd.q_date
)
SELECT 
    q_date AS 查询日期,
    id, productId, variantId, validFrom, currencyCode, price
FROM ranked_prices
WHERE rn = 1
ORDER BY q_date, productId, variantId, currencyCode;

方案2：关联子查询实现（兼容性更佳） #

如果需要兼容不支持窗口函数的低版本数据库，可以通过子查询先取每个组合的最大生效日期，再关联回原表取完整记录：

-- 查询2021-01-20的有效价目
SELECT pl.*
FROM price_list pl
INNER JOIN (
    SELECT 
        productId,
        variantId,
        currencyCode,
        MAX(validFrom) AS max_valid_from
    FROM price_list
    WHERE validFrom <= '2021-01-20'::DATE
    GROUP BY productId, variantId, currencyCode
) t 
ON pl.productId = t.productId 
AND pl.variantId = t.variantId 
AND pl.currencyCode = t.currencyCode 
AND pl.validFrom = t.max_valid_from;

注意事项 #

• 若存在同一个productId、variantId、currencyCode、validFrom对应多条记录的场景，可将ROW_NUMBER()替换为RANK()，即可返回所有同优先级的有效记录。
• 可以为(productId, variantId, currencyCode, validFrom)建立联合索引，大幅提升查询性能。

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