多表关联下按指定customerID基于hm_value统计risk记录SQL问题

实现逻辑 #

1. 先对riskMov表按logNo分组，取每个logNo最新date_created对应的唯一一条记录
2. 关联risk表过滤指定customerID的记录，按hm_value统计符合条件的记录数
3. 以热力图HeatMap表为基础左关联统计结果，无匹配的记录count值补0，保证返回全量x、y轴坐标

正确SQL代码 #

SELECT 
    h.xAxis,
    h.yAxis,
    COALESCE(stat.cnt, 0) AS count
FROM HeatMap h
LEFT JOIN (
    -- 统计指定customerID对应各hm_value的记录数
    SELECT 
        rm.hm_value,
        COUNT(*) AS cnt
    FROM risk r
    INNER JOIN (
        -- 取riskMov每个logNo最新的一条记录
        SELECT 
            logNo,
            hm_value
        FROM (
            SELECT 
                logNo,
                hm_value,
                ROW_NUMBER() OVER (PARTITION BY logNo ORDER BY date_created DESC) AS rn
            FROM riskMov
        ) t
        WHERE rn = 1
    ) rm ON r.logNo = rm.logNo
    WHERE r.customerID = '001' -- 这里替换为你需要查询的customerID
    GROUP BY rm.hm_value
) stat ON h.value = stat.hm_value
ORDER BY h.xAxis, h.yAxis;

注：如果你的数据库是大小写敏感的（如PostgreSQL），请对应给表名、字段名加上双引号即可。

原SQL错误点说明 #

• 未实现riskMov取每个logNo最新记录的逻辑，仅用DISTINCT无法满足该需求
• 关联字段错误：riskMov表没有customerID字段，不能直接和Customer表关联；关联HeatMap时误用b.value字段，实际riskMov对应字段为hm_value
• GROUP BY字段拼写错误，不存在d."axis"、c."cu_customer_id"字段，导致分组逻辑完全错误
• 关联顺序不合理，无法保证返回HeatMap表的全量x、y轴坐标数据

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