Redshift: найти MAX в списке без учета нерастущих чисел

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

Select teamid, MAX(weekdiff)
(Select teamid, DATEDIFF(week, dateuploaded, GETDATE()) as weekdiff 
from leroy_events
group by teamid, weekdiff)

Мне дали список идентификаторов команд и уникальных различий в недельных датах. Затем я хотел бы найти максимальное значение для каждого идентификатора команды, не нарушая приращения 1. Например, если мой набор данных:

Team     datediff
11453    0
11453    1
11453    2
11453    5
11453    7
11453    13

Я бы хотел, чтобы максимальное значение для команды: 11453 было равно 2.

Любые идеи были бы потрясающими.


postgresql sql amazon-redshift gaps-and-islands
person etreznicek    schedule 23.11.2015    source источник

Ответы (2)


arrow_upward
0
arrow_downward

Я упростил ваш пример, предполагая, что у меня уже есть таблица с weekdiff столбцом. Это то, что вы делаете с DATEDIFF для его расчета.

Во-первых, я использую оконную функцию LAG(), чтобы присвоить предыдущее значение (в упорядоченном наборе) недельного различия текущей строке.

Затем, используя условие WHERE, я получаю значение max(weekdiff), которое имеет предыдущее значение, равное current_value - 1 для последовательных weekdiffs.

Данные:

create table leroy_events ( teamid int, weekdiff int);
insert into leroy_events values (11453,0),(11453,1),(11453,2),(11453,5),(11453,7),(11453,13);

Код:

WITH initial_data AS (
Select 
    teamid, 
    weekdiff,
    lag(weekdiff,1) over (partition by teamid order by weekdiff) as lag_weekdiff
from
    leroy_events
)
SELECT
  teamid,
  max(weekdiff) AS max_weekdiff_consecutive
FROM
  initial_data
WHERE weekdiff = lag_weekdiff + 1 -- this insures retrieving max() without breaking your consecutive increment
GROUP BY 1

SQLFiddle с примерами данных, чтобы увидеть, как работает этот код.

Результат:

teamid  max_weekdiff_consecutive
11453   2
person Kamil Gosciminski    schedule 23.11.2015
comment
огромное спасибо за вашу помощь. Влияет ли это на числовые ряды, которые начинаются позже в спектре? Например, команда с набором данных weekdiff из 45, 46, 47 ... вернет ли этот запрос 47 или вообще не вернет? Если возвращается 47, есть ли способ вообще не возвращаться? - person etreznicek; 24.11.2015
comment
На мой вопрос в комментарии выше, возможно, уже ответил Джон в его ответе ниже. Он смог провести сравнение CASE, чтобы увидеть, было ли MIN равным 0. В сочетании с вашим ответом, похоже, у меня есть потрясающий конечный продукт! - person etreznicek; 24.11.2015
comment
Да, возможно, но я вижу, что Джон уже помог вам в этом. Не забудьте проголосовать за ответы, которые помогли вам решить эту проблему. - person Kamil Gosciminski; 25.11.2015

arrow_upward
0
arrow_downward

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

Вам по-прежнему необходимо агрегировать или фильтровать в целом количество строк для каждой интересующей группы (т.е. каждой команды) до 1. В этом случае удобно агрегировать. В целом это может выглядеть так:

select
  team,
  case min(datediff)
    when 0 then max(datediff)
    else -1
  end as max_weeks
from (
    select
      team,
      datediff,
      case
        when (lag(datediff) over (partition by team order by datediff) != datediff - 1)
          then 0
        else 1
      end as is_consec
    from diffs
  ) cd
where is_consec = 1
group by team

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

Тут есть несколько тонкостей:

  • Выражение case во встроенном представлении написано так, чтобы использовать тот факт, что lag(), вычисленный для первой строки каждого раздела, будет NULL, который не оценивает неравенство (или равенство) какому-либо значению. Таким образом, первая строка в каждом разделе всегда помечается как последовательная.

  • case тестирование min(datediff) во внешнем предложении select выбирает команды, у которых нет записи с datediff = 0, и назначает -1 столбцу max_weeks для них.

  • Также можно было бы отмечать строки не подряд, если бы у первого в своей группе не было datediff = 0, но тогда вы бы вообще потеряли такие команды из результатов.

person John Bollinger    schedule 23.11.2015