Здравствуй, уважаемый читатель!
Сегодня разбираем решение последней практической задачи по SQL, которую опубликовал в нашем Telegram-канале, с которой, кстати, справился только один из участников нашей группы!
Вначале напомню что нужно было решить:
Дана таблица ACCOUNTS, содержащая данные о счетах клиентов (у клиента может быть несколько счетов). Среди прочих, в таблице ACCOUNTS есть следующие важные для нас столбцы:
ID_CLIENT - идентификатор клиента
DT_OPEN – дата открытия счета
DT_CLOSE – дата закрытия счета
Если дата закрытия счёта не заполнена (содержит значение NULL), то счёт открыт на текущий день.
Требуется написать SQL-запрос, который должен посчитать количество клиентов с открытым счётом помесячно. Учитывать только счета, которые были открыты более одного дня. При решении нельзя использовать оператор DISTINCT.
Если счёт открыт в марте и закрыт в мае, то клиент с таким счётом должен войти в количество клиентов с открытым счётом во всех месяцах с марта по май включительно.
Отчёт должен быть вида:
SQL-файл для создания таблицы ACCOUNTS с тестовыми данными я подготовил для тебя здесь: https://prime-soft.biz/std/sql/240701.zip
Моя оценка трудности задачи: ⭐⭐⭐⭐(4 звезды из 5ти). Начинающим специалистам некоторые шаги в решении задачи могут быть сложны в понимании. Срочно записывайся на наш мощный курс по SQL и базам данных 😀
Приступаем к решению!
Так как нам нужно вывести количество клиентов в каждом месяце, у которых есть хотя бы один открытый счёт, то сразу приходится принимать во внимание то, что могут быть месяца, в которых ни один счёт не открывался, но были действующие ранее открытые счета (открыты месяцами ранее). Поэтому, основной таблицей, из которой будем выбирать данные, предлагаю сделать таблицу всех возможных месяцев. И уже к этой таблице (со списком месяцев) сможем приджоинить и посчитать количество клиентов, у которых есть хотя бы один открытый счёт в месяце таблицы.
Таблицу всех месяцев всех лет в условии задачи нам не озвучили (возможно такой таблицы в базе данных и нет), поэтому сформируем её сами налету (динамически). Сделаем, чтобы в таблице были все месяца, начиная с самого первого, в котором открылся первый счёт, заканчивая текущим месяцем (он рассчитается на основе текущей даты).
Возьмём дату самого ранее открытого счета:
Месяц этой даты и будет первым месяцем в формируемой нами динамической таблицы месяцев. С помощью CONNECT BY зациклим прибавление к этому месяцу столько месяцев, пока не дойдём до месяца текущей даты:
Нам нужен и самый первый месяц (месяц самой ранней даты открытия счета) и текущий месяц включительно. Для каждого месяца выведем первый и последний день (далее, в результирующей таблице эти столбцы можно будет в блоке SELECT не выводить). Первый и последний дни каждого месяца нам будут нужны для упрощения джоина таблицы счетов (см.ниже):
Получили таблицу всех месяцев. Назовём её periods (см. скриншот ниже). Я думаю, это было самое сложное. Теперь к каждому месяцу приджоиним счета, бывшие открытыми минимум один день в месяце: дата их открытия должна быть строго до последнего дня месяца (вдумайся!), а дата закрытия никак не может быть раньше начала месяца. Если счёт закрыт до даты начала какого-то месяца, то этот счёт может быть виден в более раннем месяце.
Анекдот:
- Бог даёт каждому только такие проблемы, с которыми он может справиться. Поэтому, либо он с этим справится, либо это не его проблемы.
Не забываем учитывать только счета, которые были открытыми минимум один день. Если дата закрытия счёта не указана, то счёт всё ещё открыт на текущий день. Клиент такого счёта будет учтён во всех месяцах, начиная с месяца открытия по текущий:
Вначале решим задачу, используя DISTINCT (чтобы увидеть, насколько это проще). Сгруппируем полученную таблицу данных по месяцам (по годам и месяцам), подсчитывая сколько в каждом месяце клиентов, у которых есть хотя бы один открытый счёт. С помощью DISTINCT каждый клиент посчитается только один раз. Для того, чтобы месяца выводились по порядку, добавим ещё и сортировку с помощью ORDER BY:
Получим:
Теперь тоже самое сделаем без DISTINCT, как от нас и хотели по условию задачи. Для этого вначале сгруппируем по месяцам и клиентам (так мы получим каждого клиента в каждом месяце только один раз).
Затем сгруппируем полученную таблицу данных ещё раз, но теперь только по месяцам. Можно смело подсчитать клиентов по каждому месяцу без DISTINCT. Теперь-то каждый клиент в любом месяце числится только один раз. Итого, вот решение:
Результат выполнения запроса будет таким же как на предыдущем скриншоте:
Поддержи статью лайком или подпиской!
Ещё больше интересных практических задач по SQL и задач с собеседований в нашем Телеграмм-канале.