如何在SQL查询中随机取值并确保店铺结果混合展示？

问题描述 #

需求：需列出30个商品，要求不同店铺的商品混合排列，示例如下：

1 - product a - store 1
2 - product b - store 1
3 - product c - store 1
4 - product d - store 5
5 - product e - store 5
6 - product f - store 5
7 - product g - store 3
8 - product h - store 3
and so on

现有products表结构如下：

CREATE TABLE products (
    id INT,
    title VARCHAR(255),
    store_id INT
);

需编写查询语句，实现随机选取商品且确保结果中店铺混合展示。

解决方案 #

方法一：使用窗口函数（适用于MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库） #

通过窗口函数给每个店铺分配随机排序键，同时给店内商品生成随机序号，最终按店铺随机键排序实现混合展示：

SELECT id, title, store_id
FROM (
    SELECT 
        id, 
        title, 
        store_id,
        -- 给每个店铺内的商品生成随机排序序号
        ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY RAND()) AS in_store_seq,
        -- 给每个店铺分配一个全局随机值，用于店铺间的随机排序
        RAND() OVER (PARTITION BY store_id) AS store_random_key
    FROM products
) AS ranked_products
-- 先按店铺随机键排序（实现店铺混合），再按店内随机序号排序
ORDER BY store_random_key, in_store_seq
LIMIT 30;

方法二：使用用户变量（适用于MySQL 5.x等不支持窗口函数的版本） #

通过自定义变量来模拟窗口函数的逻辑，实现相同效果：

SELECT id, title, store_id
FROM (
    SELECT 
        p.*,
        -- 重置店内序号并生成店铺随机键
        @in_store_seq := IF(@current_store = p.store_id, @in_store_seq + 1, 1) AS in_store_seq,
        @store_random_key := IF(@current_store = p.store_id, @store_random_key, RAND()) AS store_random_key,
        @current_store := p.store_id
    FROM products p
    -- 初始化变量
    CROSS JOIN (SELECT @current_store := NULL, @in_store_seq := 0, @store_random_key := 0) AS vars
    -- 先按店铺分组，店内随机排序
    ORDER BY p.store_id, RAND()
) AS ranked_products
-- 按店铺随机键和店内序号排序，实现混合展示
ORDER BY store_random_key, in_store_seq
LIMIT 30;

逻辑说明 #

1. 店内随机排序：通过RAND()函数给每个店铺内的商品随机分配序号，确保每个店铺展示的商品是随机选取的。
2. 店铺混合排列：给每个店铺分配一个唯一的随机键，排序时优先按这个随机键排列，让不同店铺的商品组随机穿插展示，满足混合要求。
3. 结果截取：最终通过LIMIT 30获取30个符合要求的商品。

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