Как выполнить стратификацию по столбцу в Snowflake

Я использую Snowflake для написания своих sql-запросов. У нас есть огромная таблица с миллиардами записей, содержащих информацию о клиентах. Цель состоит в том, чтобы получить случайную выборку и использовать R для просмотра распределений. К сожалению, мы не можем использовать соединение JDBC / ODBC от RStudio к базе данных. Это ограничение. Итак, мне осталось извлечь отрывок из Snowflake и импортировать его в R.

Сложность в том, что у нас есть столбец CUSTOMER SEGMENT, который содержит почти 24 уникальных значения. Цель состоит в том, чтобы получить выборку, представляющую значительную долю от каждого сегмента. Я попробовал следующий запрос;

SELECT DISTINCT *
FROM test sample(10)

для получения случайной выборки, в которой каждая строка имеет 10-процентную вероятность быть выбранной. Но я не получаю выборку из каждой ценности клиентского сегмента. Могу ли я узнать о каких-либо командах sql, которые могут помочь в расслоении на основе сегмента клиентов. заранее спасибо.


r sql snowflake-cloud-data-platform
person jay    schedule 18.10.2019    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Альтернативный способ выборки для большего количества разделов одинакового размера - использовать циклическую выборку.

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;

Число «20» означает до 20 строк для каждого сегмента.

Это может быть изменено для выборки, основанной на процентах. Не ясно, нужно ли это.

person Gordon Linoff    schedule 18.10.2019
comment
Спасибо за совет. Могу я узнать, как мы можем изменить, чтобы получить процентную выборку для каждого сегмента. Приносим извинения, если это не было выяснено ранее. - person jay; 18.10.2019
comment
например, если в сегменте A содержится 30 процентов данных, а в сегменте B - 15 процентов, то я также хотел бы иметь такое же представление в случайной выборке. не могли бы вы посоветовать, как это получить? - person jay; 18.10.2019
comment
@Jayant. . . В этом случае случайная выборка должна работать достаточно хорошо. Вам нужна точная стратификация? Если да, то ваш вопрос по этому поводу неясен. - person Gordon Linoff; 18.10.2019
comment
полезно знать, что стратификация по потребительскому сегменту может быть проведена. Есть ли способ реализовать это в Snowflake? - person jay; 18.10.2019

arrow_upward
0
arrow_downward

Вот пример стратификации в Snowflake (или SQL), основанный на следующем:

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

Это может быть возвращено как фиксированное число или процент.

Это было выполнено как один запрос, в нашей реализации мы фактически предварительно создали таблицу столбцов отсортированных сегментов (W0) как временную таблицу вместо того, чтобы один и тот же запрос выполнялся несколько раз.

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));
person Paul Sheppard    schedule 20.03.2021