如何编写SQL查询同一客户对同一产品多次评分存在降分的相关数据

原有SQL存在的问题 #

• 表名错误：实际表结构中产品表为product、客户表为customer，不是你写的products/customers
• 未写JOIN关联条件，会生成错误的笛卡尔集结果
• 缺少筛选「同一客户对同一产品存在后续评分低于之前」的逻辑
• 聚合查询缺少GROUP BY子句，语法不合法

正确查询语句（窗口函数实现，兼容性较好） #

WITH rating_with_prev AS (
    SELECT 
        cust_id,
        prod_id,
        rating_stars,
        -- 按客户+产品分组，按评分日期排序，取上一次的评分
        LAG(rating_stars) OVER (PARTITION BY cust_id, prod_id ORDER BY rating_date) AS prev_rating
    FROM rating
),
qualified_cust_prod AS (
    -- 筛选出存在后续评分低于前序评分的客户+产品唯一组合
    SELECT DISTINCT cust_id, prod_id
    FROM rating_with_prev
    WHERE prev_rating IS NOT NULL AND rating_stars < prev_rating
)
SELECT 
    c.customer_name,
    p.product_name,
    MIN(r.rating_stars) AS min_rating_stars
FROM qualified_cust_prod q
INNER JOIN rating r ON q.cust_id = r.cust_id AND q.prod_id = r.prod_id
INNER JOIN customer c ON r.cust_id = c.cust_id
INNER JOIN product p ON r.prod_id = p.prod_id
-- 按客户、产品分组求最低评分
GROUP BY c.customer_name, p.product_name, q.cust_id, q.prod_id;

可选实现（自连接方式，不支持窗口函数的低版本数据库可用） #

SELECT 
    c.customer_name,
    p.product_name,
    MIN(r_all.rating_stars) AS min_rating_stars
FROM rating r_earlier
INNER JOIN rating r_later
    ON r_earlier.cust_id = r_later.cust_id
    AND r_earlier.prod_id = r_later.prod_id
    AND r_earlier.rating_date < r_later.rating_date
    AND r_later.rating_stars < r_earlier.rating_stars
INNER JOIN rating r_all
    ON r_earlier.cust_id = r_all.cust_id
    AND r_earlier.prod_id = r_all.prod_id
INNER JOIN customer c ON r_earlier.cust_id = c.cust_id
INNER JOIN product p ON r_earlier.prod_id = p.prod_id
GROUP BY c.customer_name, p.product_name, r_earlier.cust_id, r_earlier.prod_id;

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