【Вопросы для интервью】
Существует «таблица записей входа пользователя», которая содержит два поля: идентификатор пользователя и дату.
[Вопрос] Запросите максимальное количество последовательных дней входа в систему для каждого пользователя в каждом месяце в 2021 году.
【Этапы решения проблемы】
- Универсальный шаблон для бегущих вопросов
В «Вопросы интервью Pinduoduo: как найти контент, который появляется N раз подряд? », я рассказал о том, как решить «непрерывную задачу», и отправил универсальный шаблон. В шаблоне используется оконная функция для решения непрерывной задачи.
2. Функция окна
Как использовать ведущую оконную функцию:
Значение по умолчанию означает: когда значение N строк вверх или вниз на N строк, если оно превышает диапазон строк и столбцов таблицы, значение по умолчанию будет использоваться в качестве возвращаемого значения функции, если значение по умолчанию не указано, он вернет Null.
Ведущая оконная функция может взять следующее n-е значение каждого поля и сгенерировать новый столбец.
«Непрерывный» в «непрерывном входе пользователя», описанном в этом вопросе, можно понимать как разницу между текущей датой входа пользователя и следующей датой входа в этом месяце.
Сначала мы можем получить функцию Window Wead «Дата входа пользователя в месяц»:
Когда «дата» — это когда пользователь входит в систему в последний день текущего месяца, она будет записана как «последняя дата входа в текущий месяц». Если она не установлена, она вернет Null, что не способствует пониманию.
По результатам мы можем получить следующую информацию:
1) Когда «дата» отличается от «даты следующего входа пользователя в текущем месяце» всего на один день, то есть текущий вход пользователя является непрерывным входом в систему;
2) Когда разница между «датой» и «датой следующего входа пользователя в текущем месяце» больше одного дня, то есть текущим входом пользователя является последний день непрерывного входа в систему (также может быть только один день) ;
3) Когда «дата следующего входа пользователя в текущем месяце» равна «дате последнего входа в текущий месяц», то есть текущий вход пользователя — это последний день месяца.
Таким образом, можно судить, что пользователь входит в систему непрерывно.
Далее мы решим расчет количества последовательных дней входа в систему для каждого пользователя.
- подзапрос
Существует определенная связь между количеством последовательных дней входа пользователя в систему и порядком входа пользователя.В настоящее время мы можем сначала использовать подзапрос для запроса порядка чтения пользователя в этом месяце и использовать оконную функцию row_number:
Видно, что при завершении продолжения, т.е.:
1) Разница между «датой» и «датой следующего входа пользователя в текущем месяце» больше одного дня;
2) «дата следующего входа пользователя в текущем месяце» равна «дате последнего входа в текущий месяц»;
два случая.
После фильтрации этих двух ситуаций количество последовательных дней входа в систему для пользователя равно: текущая последовательность входа минус предыдущая последовательность входа.
Когда «Последняя последовательность входа» равна нулю, замените ее на 0 (используйте функцию объединения), затем «Ежемесячная последовательность входа» минус «Последняя последовательность входа» — это количество последовательных дней входа в систему на этот раз.
- Сводный анализ
Наконец, получите «максимальное количество последовательных дней, в течение которых каждый пользователь входит в систему в каждом месяце» и используйте группу по функции.
【Тестовый сайт для этого вопроса】
1. Чтобы проверить свое понимание оконных функций, вы должны вспомнить 4 типа вопросов интервью, которые оконные функции могут решать в «Monkey Learns SQL from Zero»;
2. Проверьте свое понимание подзапросов;
3. Чтобы проверить понимание непрерывных задач, можно применить универсальный шаблон.
рисунок
Рекомендация: Изучить SQL с нуля?