Часть 2 x of Sporting Stylish SQL со Стивеном
Небольшое примечание вверху: оказывается, когда мы создали и наполнили наш стол, я забыл упомянуть, что мы также коллективно возвращались назад во времени чуть-чуть к 9 февраля 2019 года. С Национальным днем пиццы !
Итак, вот где мы находимся на данный момент: мы создали таблицу xpert.england, где каждая строка представляет собой игру в английской премьер-лиге, но поскольку, как мы теперь знаем, это в настоящее время В середине сезона, по мере продвижения вперед, будет больше CSV с еще большим количеством игр. Вот в чем загвоздка, эти CSV также будут содержать данные для игр, которые мы уже ввели, а это означает, что теперь мы столкнулись с ужасным бременем базы данных: дубликаты данных. Итак, как нам бороться с этим надвигающимся призраком дублирования? Позвольте мне посчитать несколько способов:
- Отредактируйте сам CSV, удалив строки, которые у вас уже есть в БД. риск случайно исказить данные
- Усеките (то есть удалите все внутри) таблицу перед загрузкой нового CSV.
- Каждый раз загружайте полный CSV в таблицу и используйте Просмотр, чтобы отсеять повторяющиеся строки.
Когда дело доходит до принятия решения о том, какой вариант выбрать, возникает важный вопрос: будем ли мы делать эту повторную загрузку снова и снова, или это будет случайным, одноразовым? вид проекта?
Я говорил с нашим менеджером, и она сказала, что мы будем загружать этот файл после каждого игрового дня, поэтому нам нужно будет выбрать вариант, наиболее подходящий для настройки воспроизводимого процесса.
Итак, сразу же, для меня вариант 1 - проигрыш. Это требует редактирования файла данных, и каждый раз, когда вы что-то редактируете, вы рискуете, за неимением лучшего слова, как-то изувечить это. Не говоря уже о том, что это дополнительный шаг, внешний по отношению к базе данных, и кто-то должен будет выполнить его вручную. Это звучит как хлопоты для нас для QA и для тех, кто должен очищать эти обманы (при условии, что эта работа также не ложится на нас, и в этом случае это всего лишь одна большая проблема).
Вариант 1 за пределами острова, у нас остались варианты 2 и 3. Оба варианта хороши тем, что оба могут быть довольно легко превращены в процессы установи и забудь. Вариант 2 (усечение перед загрузкой) можно настроить с помощью функции триггера, которая немного сложнее, но сэкономит место для хранения в вашей БД. Вариант 3 (с использованием представления SQL) довольно прост для написания и освещения базы данных, единственный недостаток, на самом деле, заключается в том, что когда дело доходит до толчка, вы должны помнить, что запрашивать представление, а не таблицу (таблица заполнена). дубликатов данных).
Держу пари, вы уже догадались, что я предпочитаю вариант номер 3 (это название статьи ради Пита). Это просто, как я уже сказал, и это не очень сильно нагружает БД, если вообще. И если мы все сделаем правильно, мы получим дополнительный бонус в виде устранения необходимости в обновлениях SQL при исправлении ошибок или влиянии на изменения оценок (если вы не думаете, что оценки могут измениться постфактум, подумайте еще раз).
Если эта часть обновления звучит немного запутанно, пока не беспокойтесь, мы вернемся к ней, когда будем запускать QA.
Теперь нам сообщили, что новый CSV был загружен в таблицу, давайте откроем pgAdmin и посмотрим на одну игру в частности, я выберу Тоттенхэм против Эвертона. Давайте создадим этот запрос вместе. Давайте посмотрим на всю таблицу, где команда хозяев — Эвертон, а команда гостей — Тоттенхэм.
Здесь вы видите, что я начинаю предложение WHERE с утверждения «1 = 1». Я делаю это так, что все мои операторы WHERE получают свою собственную строку и начинаются с AND, что делает их выстроенными и более удобными для глаз, чем
WHERE home_team = 'Everton' AND away_team = 'Tottenham'
Это также позволяет мне закомментировать условие WHERE, если я хочу немного покопаться в данных, не добавляя больше И.
Что же мы получим, если поместим наш прекрасный маленький запрос в pgAdmin?
Выше мы видим повторяющуюся строку, которая была скопирована в таблицу при последующей загрузке, а также мы можем видеть ключ (подмигивание!), который нам нужен для отслеживания самой последней загрузки. Поскольку id, первичный ключ, постоянно увеличивается, он гарантированно будет выше для новой строки, поэтому, если мы выберем версию строки с наибольшим (или MAXimum) id,имбудет самая последняя загруженная версия строки. И прежде чем вы это скажете, да, столбец created_at также сообщит нам, что было загружено позже, но процессору немного проще найти большее число, чем более позднюю дату.
Итак, теперь, когда мы знаем, что будем использовать столбец id, чтобы сузить строки из самой последней загрузки, мы должны принять решение о том, какие столбцы мы будем использовать для определения данных, которые мы ищем. . Этот вопрос сводится к тому, что мы предполагаем о том, какие столбцы в данных должны оставаться постоянными, а какие могут быть переменными. В нашем случае давайте договоримся, что дата проведения игры и участвующие команды не изменятся, даже если другие детали игры (т.е. окончательный счет) могут измениться. Отсюда следует, что game_date, home_team и away_team будут нашими опорами, мы будем называть их нашими постоянными столбцами.
Теперь, как мы можем использовать эти постоянные столбцы для создания нашего представления? Итак, запрос, который мы строим, нацелен только на одну игру, поэтому давайте посмотрим, что даст нам добавление наших постоянных столбцов в наш запрос.
Теперь вы заметите, что я добавил оператор GROUP BY внизу, позвольте мне объяснить. MAX — это так называемая агрегатная функция. Это означает, что если вы вызываете ее вместе с другими столбцами, вы должны указать, как и в каком порядке вы будете выполнять агрегирование; в нашем случае у нас произвольный порядок всех наших постоянных столбцов.
Теперь, когда у нас есть способ выбрать наши последние строки, все, что нам нужно сделать, это выбрать только эти последние строки. Я собираюсь сделать это, превратив запрос, который мы построили до сих пор, в CTE, а затем присоединив его обратно к таблице с помощью INNER JOIN, чтобы мы получили только эти строки.
Вы видите, что для нашей CTE-версии запроса у нас больше нет постоянных столбцов в разделе SELECT, потому что, как только они появились в GROUP BY, поместив их в SELECT становится ненужным и действительно немного замедляет запрос. ВНУТРЕННЕЕ СОЕДИНЕНИЕ гарантирует, что мы получим только те последние строки, которые мы нашли с помощью CTE, таким образом, мы нашли самую последнюю загруженную строку для каждой игры, то есть мы DE-DUPED этого плохого мальчика!
Теперь, когда у нас есть запрос, который мы собираемся использовать, давайте создадим это представление!
Как видите, все, что нам нужно было сделать, это поместить оператор CREATE VIEW вверху, а затем сделать отступ для нашего запроса. Давайте проверим этого парня через старый psql, как в прошлый раз (ССЫЛКА), и убедимся, что мы ничего не напутали.
QA!
После того, как мы создадим это представление, мы должны убедиться, что оно работает правильно, что означает создание запроса, который подтверждает, что представление делает то, для чего оно предназначено, то есть отображает самые последние обновленные игровые данные. Принимая это во внимание, у нас есть две вещи, которые мы должны оценить:
- Подсчет строк выстраивается в линию.
- Если мы изменим CSV, а затем загрузим его, это изменение отразится в представлении.
Самый простой способ узнать, являются ли данные в CSV, которые были загружены последними, являются единственными данными в представлении, — это подсчитать количество строк в CSV и сравнить его с количеством строк в таблице и Посмотреть.
Давайте создадим один запрос, который может проверять количество строк как в представлении, так и в таблице. Мы можем объединить их по id, так как это в любом случае является основой для представления.
Теперь нам просто нужно знать количество строк, чтобы мы знали, что мы хотим подтвердить. Самый последний CSV имеет 261 строку, что в сумме с 258, которые мы загрузили при построении таблицы, должно составить 519 строк.
Барабанная дробь, пожалуйста.
Мы видим, что повторяющиеся строки из таблицы не видны в представлении. Теперь давайте посмотрим на изменение оценок:
Допустим, вы заядлый болельщик Ливерпуля и вам просто ненавистна мысль о том, что Манчестер Сити выиграет здесь свою игру. Что ж, поскольку мы просто занимаемся контролем качества, давайте перепишем историю и отредактируем таблицу так, чтобы Челси не просто абсолютно капитулировал перед Сити, а счет был гораздо более респектабельно (к сожалению) ничья 0–0:
СТАНОВИТСЯ
После того, как мы загрузим явно (но не демонстративно) отредактированный CSV-файл, мы должны увидеть отражение нашей новой «реальности» в нашем представлении.
Наше представление успешно удалило дубликаты и обновило оценку, как мы и надеялись, мы сделали то, что намеревались сделать, что означает, что мы закончили с контролем качества. Давайте удостоверимся, что мы загружаем неиспорченные (да, я сказал это) данные, чтобы сделать все целостным, а затем пришло время для пива!
Структурированный пивной язык
После завершения проекта я люблю расслабиться и поболтать с коллегами за кружкой пива. На этот раз мы говорим о лучших футбольных моментах и обложках Мэрайи Кэри:
Какой обстрел "Сити" устроил "Челси" в той игре, а? На самом деле было трудно найти подходящее короткое видео с яркими моментами, остальное было не менее 6 минут техно-злорадства, которое было бы слишком много, чтобы вынести.
Кроме того, я не знаю, нажимали ли вы на ссылку Мэрайи Кэри выше, но я совершенно забыл о карнавальном видео со странным клоунским вступлением, роликами и пением в первом ряду. -из-gd-американские горки. И, если честно, хотя я люблю этот трек и многие другие хиты Мэрайи, мое любимое исполнение этой песни, о котором вы не спрашивали, — этот Оуэн Паллетт.
"Ваше здоровье".
