How to perform stratification based on a column in Snowflake

user_5278594 0
Viewed 2054

I am using Snowflake to write my sql queries. We have a huge table with billions of records containing customer information. The goal is to get random sample out and use R to look at the distributions. Unfortunately, we can't use JDBC/ODBC connection from RStudio to the Database. It is a limitation. So I am left with pulling extract from Snowflake and importing into R.

The difficulty, is we have a column called CUSTOMER SEGMENT which has almost 24 unique values. The goal is to get a sample which represents significant proportion from each segment. I tried the following query;

SELECT DISTINCT *
FROM test sample(10)

to obtain random sample where each row has 10 percent probability of being selected. But I am not obtaining sample from each values of customer segment. May I know of any sql commands, which can help to stratify based on Customer segment. thanks in advance.

snowflake-cloud-data-platform r sql
3 Answers

An alternative way of sampling for more equal-sized partitions is to use round robin sampling

select t.*
from (select t.*, 
             row_number() over (partition by segment order by random()) as seqnum,
             count(*) over () as cnt
      from test t
     ) t
where seqnum <= 20;

The "20" says up to 20 rows for each segment.

This can be modified for a percentage based sample. It is not clear if that is necessary.

user_11440350

Here is an example of stratification in Snowflake (or SQL), based on the following:

https://en.wikipedia.org/wiki/Stratified_sampling

This can be returned as a fixed number or percentage.

This has been complied as a single query, in our implementation we actually pre-created the sorted segment column table (W0) as a temp table instead of having the same query run mulitple times.

SELECT 
    W1.Id, 
    W1.EmploymentStatus,    
    W1.Gender
    FROM 
        ( SELECT 
            ID, 
            Row_Number() OVER ( PARTITION BY COALESCE(Gender, '') || COALESCE(EmploymentStatus,'') ORDER BY random() ) as iInternalRank, 
            COALESCE(Gender, '') || COALESCE(EmploymentStatus,'') as sInternalGroupVal 
            FROM STAFF ) W0,
        STAFF W1    -- Linked back the original table (Inbound query)
    WHERE (
            SELECT 
                    MAX(case when W2.sInternalGroupVal = W3.sInternalGroupVal then W3.iGroupSegmentVolume else 0 end ) -- This is where the magic happens...
                    FROM ( 
                            SELECT 
                                ID, 
                                Row_Number() OVER ( PARTITION BY COALESCE(Gender, '') || COALESCE(EmploymentStatus,'') ORDER BY random() ) as iInternalRank, 
                                COALESCE(Gender, '') || COALESCE(EmploymentStatus,'') as sInternalGroupVal 
                                FROM STAFF ) W2, 
                        (SELECT 
                            sInternalGroupVal, 
                            COUNT(Id)*(40/iTotalPopulation::DOUBLE PRECISION) as iGroupSegmentVolume -- as a fixed volumne (40 Records)
                            --COUNT(Id)*((iTotalPopulation*(23/100.00))/iTotalPopulation::DOUBLE PRECISION) as iGroupSegmentVolume -- as a percentage (23% of overall population)
                            FROM (SELECT 
                                    ID, 
                                    Row_Number() OVER ( PARTITION BY COALESCE(Gender, '') || COALESCE(EmploymentStatus,'') ORDER BY random() ) as iInternalRank, 
                                    COALESCE(Gender, '') || COALESCE(EmploymentStatus,'') as sInternalGroupVal 
                                    FROM STAFF ), 
                                 (SELECT 
                                    COUNT(Id) as iTotalPopulation 
                                    FROM ( SELECT 
                                            ID, 
                                            Row_Number() OVER ( PARTITION BY COALESCE(Gender, '') || COALESCE(EmploymentStatus,'') ORDER BY random() ) as iInternalRank, 
                                            COALESCE(Gender, '') || COALESCE(EmploymentStatus,'') as sInternalGroupVal 
                                            FROM STAFF )
                                         ) W4
                            GROUP BY sInternalGroupVal, iTotalPopulation) W3
                    WHERE ((W2.sInternalGroupVal = W0.sInternalGroupVal)) AND ((W2.sInternalGroupVal = W3.sInternalGroupVal))
            ) >= iInternalRank 
            AND ((W1.Id = W0.Id));
user_40049480

For some large number of rows, this will approach a stratified sample.

select     *
from       test
order by   row_number() over (partition by segment_1, segment_2 order by random()) /
           count(*) over (partition by segment_1, segment_2)
limit      1000000
user_157855130
Related