如何使用SQL从5000行数据集中选取具维度代表性的分层随机样本？

Hey there! Let's work through this stratified sampling problem you're tackling. You need 200 rows that match the original 5000-row dataset's distribution across Date, Location, Department, and Funding—great call for getting a representative sample. Your current PARTITION BY + ORDER BY approach works, but here are more efficient and precise methods tailored to common SQL dialects:

1. Use Database-Built-In Functions (Most Efficient) #

Modern databases have tools to simplify stratified sampling, which are more reliable than manual partitioning for maintaining proportionality. Here are examples for popular systems:

PostgreSQL #

First calculate the proportion of each stratum (combination of your four dimensions) in the original dataset, then assign sample sizes and pull random rows from each group:

WITH strata_proportions AS (
    SELECT 
        Date, Location, Department, Funding,
        COUNT(*) AS stratum_total,
        -- Calculate how many samples to take from each stratum
        ROUND((COUNT(*) / 5000.0) * 200) AS sample_count
    FROM your_dataset
    GROUP BY Date, Location, Department, Funding
),
ranked_rows AS (
    SELECT 
        d.*,
        -- Assign random rank within each stratum
        ROW_NUMBER() OVER (
            PARTITION BY d.Date, d.Location, d.Department, d.Funding 
            ORDER BY RANDOM()
        ) AS row_rank
    FROM your_dataset d
    JOIN strata_proportions s 
        ON d.Date = s.Date 
        AND d.Location = s.Location 
        AND d.Department = s.Department 
        AND d.Funding = s.Funding
)
SELECT RowID, Date, Location, Department, Funding
FROM ranked_rows
WHERE row_rank <= sample_count
ORDER BY RowID;

SQL Server #

Similar logic, but use NEWID() for true random ordering (since RAND() generates a single value per query):

WITH strata_proportions AS (
    SELECT 
        Date, Location, Department, Funding,
        COUNT(*) AS stratum_total,
        ROUND((COUNT(*) / 5000.0) * 200, 0) AS sample_count
    FROM your_dataset
    GROUP BY Date, Location, Department, Funding
),
ranked_rows AS (
    SELECT 
        d.*,
        ROW_NUMBER() OVER (
            PARTITION BY d.Date, d.Location, d.Department, d.Funding 
            ORDER BY NEWID()
        ) AS row_rank
    FROM your_dataset d
    INNER JOIN strata_proportions s 
        ON d.Date = s.Date 
        AND d.Location = s.Location 
        AND d.Department = s.Department 
        AND d.Funding = s.Funding
)
SELECT RowID, Date, Location, Department, Funding
FROM ranked_rows
WHERE row_rank <= sample_count
ORDER BY RowID;

BigQuery #

Use probability-based sampling for speed, then trim to exactly 200 rows (handles minor rounding discrepancies):

WITH strata_proportions AS (
    SELECT 
        Date, Location, Department, Funding,
        COUNT(*) AS stratum_total,
        SAFE_CAST(ROUND((COUNT(*) / 5000.0) * 200) AS INT64) AS sample_count
    FROM your_dataset
    GROUP BY Date, Location, Department, Funding
)
SELECT 
    d.RowID, d.Date, d.Location, d.Department, d.Funding
FROM your_dataset d
JOIN strata_proportions s 
    USING (Date, Location, Department, Funding)
-- Assign each row a chance of being selected equal to its stratum's sample proportion
WHERE RAND() <= (s.sample_count / s.stratum_total)
ORDER BY RAND()
LIMIT 200;

2. Optimize Your Existing Partitioning Method #

If you want to stick with your initial approach, tweak it to enforce proportional sampling instead of arbitrary row counts per partition:

SELECT RowID, Date, Location, Department, Funding FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY Date, Location, Department, Funding 
            ORDER BY RANDOM() -- Swap with NEWID() for SQL Server, RAND() for BigQuery
        ) AS row_rank,
        -- Get total rows in each stratum via window function
        COUNT(*) OVER (PARTITION BY Date, Location, Department, Funding) AS stratum_total
    FROM your_dataset
) ranked_data
-- Calculate exact sample count per stratum on the fly
WHERE row_rank <= CEIL((stratum_total / 5000.0) * 200)
ORDER BY RowID;

Key Tips for Accuracy & Efficiency #

• Adjust for rounding: If the sum of sample counts across strata isn't exactly 200, tweak the largest strata by adding/removing a row to hit your target.
• Pick the right random function: Avoid RAND() in window functions for SQL Server/MySQL—it generates a single value per query, so partitioning won't be random. Use NEWID() (SQL Server) or RANDOM() (PostgreSQL) instead.
• Performance: For your 5000-row dataset, any method will work fast, but for larger datasets, probability-based sampling (like BigQuery's example) avoids full-table sorting and is more efficient.

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