SQL 隨機抽樣查詢：各資料庫 NEWID、RAND、DBMS_RANDOM 寫法大全

SQL Server (T-SQL)

-- 簡單隨機 N 筆
SELECT TOP (10) * FROM dbo.BigTable ORDER BY NEWID();

-- 大表效能優化：TABLESAMPLE (近似隨機，頁面層級)
SELECT * FROM dbo.BigTable TABLESAMPLE (10 PERCENT);  -- 不保證筆數

-- 精確隨機 + 效能：Beroulli 抽樣 (SQL 2005+)
SELECT * FROM dbo.BigTable
WHERE 0.01 >= CAST(CHECKSUM(NEWID(), ID) & 0x7fffffff AS FLOAT) / 0x7fffffff;  -- 約 1%

MySQL / MariaDB

-- 簡單隨機
SELECT * FROM big_table ORDER BY RAND() LIMIT 10;

-- 大表優化：隨機 ID 範圍
SELECT * FROM big_table
WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM big_table)))
ORDER BY id LIMIT 10;

PostgreSQL

-- 簡單隨機
SELECT * FROM big_table ORDER BY RANDOM() LIMIT 10;

-- 系統抽樣 (近似，極快)
SELECT * FROM big_table TABLESAMPLE BERNOULLI (1);  -- 1%

-- 區塊抽樣 (更快，但可能偏態)
SELECT * FROM big_table TABLESAMPLE SYSTEM (1);

Oracle

-- 簡單隨機
SELECT * FROM (
    SELECT * FROM big_table ORDER BY DBMS_RANDOM.VALUE
) WHERE ROWNUM <= 10;

-- 區塊抽樣 (近似)
SELECT * FROM big_table SAMPLE BLOCK (1);  -- 1% 區塊

-- 列抽樣 (近似)
SELECT * FROM big_table SAMPLE (1);

Access (JET/ACE)

-- VBA/查詢中
Dim r As Single: Randomize: r = Rnd
SELECT TOP 10 * FROM big_table ORDER BY Rnd(r * ID);

SQLite

SELECT * FROM big_table ORDER BY RANDOM() LIMIT 10;

大表隨機抽樣效能策略對比

選型建議

1. 報表/統計分析 → TABLESAMPLE (系統/伯努利)
2. 功能需精確 N 筆 (如抽獎) → ORDER BY NEWID() 小表 / CHECKSUM 大表
3. ID 連續且無大洞 → 隨機 ID 範圍 + TOP/LIMIT
4. 永遠先看執行計畫，確認無 Sort/Table Spool