SQL双重权重查询实现：关联大陆计数子查询完成权重筛选

原始SQL表 #

选取规则 #

• 过去6个月未被使用
• 所属国家过去7天未被使用
• 优先选中使用时间最久远的记录
• 新增反向权重：过去14天使用次数越高的大陆，越不易被选中

解决方案 #

通过LEFT JOIN将主查询与大陆使用次数子查询关联，处理大陆未被使用的空值情况，整合权重逻辑后排序选取：

合并权重版本（推荐） #

SELECT
    t1.`Image_Key`,
    -- 合并日期权重与大陆反向权重，使用次数越高的大陆权重占比越低
    (-LOG(RAND()) / DATEDIFF(CURRENT_DATE, t1.`Date_Used`)) * 
    (CASE WHEN cc.continent_count IS NULL THEN 1 ELSE 1/cc.continent_count END) AS combined_priority
FROM
    table1 t1
LEFT JOIN (
    SELECT
        continent,
        COUNT(*) AS continent_count
    FROM
        (
        SELECT DISTINCT
            *
        FROM
            table1
        WHERE
            `Date_Used` BETWEEN CURRENT_DATE - INTERVAL 14 DAY AND CURRENT_DATE
    ) AS total_count
    GROUP BY Continent
) cc ON t1.Continent = cc.continent
WHERE
    t1.`Date_Used` < CURRENT_DATE - INTERVAL 182 DAY 
    AND t1.`Country` NOT IN (
        SELECT Country
        FROM table1
        WHERE `Date_Used` > CURRENT_DATE - INTERVAL 7 DAY
    )
ORDER BY
    combined_priority DESC
LIMIT 1;

分开权重排序版本 #

SELECT
    t1.`Image_Key`,
    -- 日期权重：使用时间越久远，值越小
    -LOG(RAND()) / DATEDIFF(CURRENT_DATE, t1.`Date_Used`) AS Date_priority,
    -- 大陆反向权重：使用次数越高，值越小
    CASE WHEN cc.continent_count IS NULL THEN -LOG(RAND()) ELSE -LOG(RAND()) / cc.continent_count END AS Continent_priority
FROM
    table1 t1
LEFT JOIN (
    SELECT
        continent,
        COUNT(*) AS continent_count
    FROM
        (
        SELECT DISTINCT
            *
        FROM
            table1
        WHERE
            `Date_Used` BETWEEN CURRENT_DATE - INTERVAL 14 DAY AND CURRENT_DATE
    ) AS total_count
    GROUP BY Continent
) cc ON t1.Continent = cc.continent
WHERE
    t1.`Date_Used` < CURRENT_DATE - INTERVAL 182 DAY 
    AND t1.`Country` NOT IN (
        SELECT Country
        FROM table1
        WHERE `Date_Used` > CURRENT_DATE - INTERVAL 7 DAY
    )
ORDER BY
    Date_priority ASC, -- 越久远的记录越靠前
    Continent_priority DESC -- 使用次数越少的大陆越靠前
LIMIT 1;

关键说明 #

1. 使用LEFT JOIN关联主表与大陆计数子查询，确保过去14天未被使用的大陆也能被包含，避免过滤掉有效数据
2. 用CASE WHEN处理continent_count为NULL的情况，设置默认值1，避免出现除以0的错误
3. 权重逻辑：日期部分通过DATEDIFF计算间隔天数，间隔越久权重占比越高；大陆部分通过倒数实现反向权重，使用次数越高权重占比越低

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