Замена кода дедупликации на основе цикла на красное смещение SQL

Мы пытаемся перенести много устаревшего кода R, который используется для манипулирования наборами данных, для красного смещения SQL. Все это легко переносится, за исключением нижнего бита, который оказался неразрешимым. Вот почему я иду к вам, нежный ТАК читатель. Я подозреваю, что то, что я спрашиваю, невозможно, но у меня нет возможности доказать это.

То, что делает приведенный ниже код R, это дедупликация уникальных целочисленных идентификаторов с использованием механизма зацикливания. Вы увидите полную информацию в комментариях.

Прежде чем мы перейдем к этому, приведем небольшой аннотированный пример, чтобы дать вам представление о влиянии, которое должен иметь желаемый код SQL:

Вот аннотированный R-код, который мы пытаемся заменить на красное смещение SQL:

# the purpose of this function is to dedupe a set of identifiers
    # so that each month, the set if identifiers grouped under that month
    # will not have appeared in the previous two months
    # it does this by building 3 sets:
        # current month
        # previous month
        # 2 months ago
        # In a loop, it sets the current month set for the current year-month value in the loop
            # then filters that set against the contents of previous 2 months' sets
            # then unions the surving months set against the survivors of previous months so far

# I believe the functionality below is mainly taken from library(dplyr)
library(dplyr)
library(tidyverse)
library(lubridate)
library(multidplyr) 
library(purrr)
library(stringr)
library(RJDBC)

dedupeIdentifiers <- function(dataToDedupe, YearToStart = 2014, YearToEnd = 2016) { 
    # dataToDedupe is input set
    # YearToStart = default starting year
    # YearToEnd = default ending year

    monthYearSeq <- expand.grid(Month = 1:12, Year = YearToStart:YearToEnd) %>% tbl_df() # make a grid having all months 1:12 from starting to ending year
    twoMonthsAgoIdentifiers <- data_frame(propertyid = integer(0)) # make empty data frame to hold list of unique identifiers
    oneMonthAgoIdentifiers  <- data_frame(propertyid = integer(0)) # make empty data frame to hold list of unique identifiers
    identifiersToKeep <- dataToDedupe %>% slice(0) # make empty data frame to hold list of unique identifiers

    for(i in 1:nrow(monthYearSeq)) {
        curMonth <- monthYearSeq$Month[i] # get current month for row in loop of monthYearSeq
        curYear <- monthYearSeq$Year[i] # get current year for row in loop of monthYearSeq

        curIdentifiers <- dataToDedupe %>% filter(year(initialdate) == curYear, month(initialdate) == curMonth)%>% 
            # initialdate is the date variable in the set by which the set is filtered
            # start by filtering to make a subset, curIdentifiers, which is the set where initialdate == current month and year in the loop
            group_by(uniqueidentifier) %>% slice(1) %>% ungroup() %>%  # take just 1 example of each unique identifier in the subset
            anti_join(twoMonthsAgoIdentifiers) %>% # filter out uniqueidentifier that were in set two months ago
            anti_join(oneMonthAgoIdentifiers) # filter out uniqueidentifier that were in set one month ago

        twoMonthsAgoIdentifiers <- oneMonthAgoIdentifiers # move one month set into two month set
        oneMonthAgoIdentifiers <- curIdentifiers %>% select(uniqueidentifier) # move current month set into one month set
        identifiersToKeep <- bind_rows(identifiersToKeep, curIdentifiers) # add "surviving" unique identifiers after filtering for last 2 months
            # to updated set of deduped indentifiers
    } # lather, rinse, repeat

    return(identifiersToKeep) # return all survivors
}

Наконец, вот некоторые вещи, которые мы до сих пор пытались безуспешно:

  1. Рекурсивные CTE были предложены. Redshift не позволяет использовать CTE.
  2. Используйте лаги для оценки разницы между "текущим" значением даты и предыдущими значениями даты, разбитыми по уникальному идентификатору. Это не работает в случае, например, непрерывного набора месяцев 1-5 для одного и того же уникального идентификатора 123. В этом случае будут сохраняться оба месяца 4 и 5, но месяц 5 фактически должен быть отброшен.
  3. Автоматическое левостороннее объединение набора против самого себя по уникальному идентификатору, чтобы можно было оценить все перестановки за месяц. - На самом деле это та же проблема, что и при использовании лагов.
  4. Используйте фиктивную дату со всеми желаемыми месяцами и годами, чтобы ввести пропущенные месяцы и годы в набор для фильтрации. Отметьте строки из исходного набора для фильтрации. Затем с помощью параметра density_rank, разделенного на уникальный идентификатор и флаг, выберите каждую строку с рангом% 3 =0. Проблема заключается в том, что вы не всегда можете заставить значение dens_rank подсчитывать нужное количество разделов, поэтому значение% 3 получается неверным.
  5. Используйте комбинации из вышеперечисленного.
  6. Замена цикла с помощью операции на основе набора.

Мы можем достичь ~90 % паритета с исходным циклическим кодом, но, к сожалению, у нас должна быть идеальная замена.

Пожалуйста, соблюдайте нашу цель воспроизвести это в SQL или доказать, что воспроизведение результатов цикла в этом случае невозможно с SQL. Ответы типа "просто придерживайтесь R", "сделайте цикл в python", "попробуйте этот новый пакет" не будут полезны.

Большое спасибо за любой положительный совет.

Источник

1 ответ

Ваш процесс может быть завершен в Redshift с использованием методов "sql sessionization".

По сути, вы используете несколько операторов LAG() для сравнения данных по вашим конкретным окнам, а затем сравниваете результаты для завершения окончательной классификации.

Источник
Другие вопросы по тегам