|
Содержание работы или список заданий
|
ЛАБОРАТОРНАЯ РАБОТА № 6
MongoBD
Учебные вопросы:
1. Работа с коллекциями в MongoDB.
1) Для работы с MongoDB при выполнении заданий лабораторной работы необходимо перейти к перейдите по ссылке https://online.ifmo.ru/mongo/.
2) Введите логин: "itmo", затем после нажатия клавиши "Enter" введите пароль "online", а после пароля – название демонстрационной базы данных для работы: "globalStudent". Обратите внимание, что пароль при вводе не отображается.
3) После подключения к базе рекомендуется выполнить тестовый запрос (чтобы убедиться, что с подключением все в порядке): show collections.
В ответ на исполнение этого запроса вы сможете увидеть список доступных коллекций демонстрационной базы. Вы можете читать содержимое базы globalStudent, но не можете его изменять.
Из представленных в демонстрационной базе коллекций для выполнения заданий необходимы коллекции student и UNDERGROUND.
По коллекциям данной базы выполните следующие запросы:
1. Напишите команду, которая выведет все документы из коллекции student.
2. Напишите команду, которая выведет все документы из коллекции student, в которых значение по ключу "name" равно "Anna".
3. Напишите команду, которая выведет все документы из коллекции student, в которых значение по ключу "name" равно "Anna", а значение ключа "age" - 25.
4. Напишите команду, которая выведет все документы из коллекции student, в которых в значениях по ключу surname встречается вхождение подстроки ova.
5. Выведите неповторяющийся список имен из коллекции student.
6. Напишите команду, которая подсчитает количество документов в коллекции student, в которых в значение по ключу surname равно Ivanova.
7. Отсортируйте все документы в коллекции student в лексикографическом порядке (по возрастанию) по ключу name.
8. Выведите первые 10 документов из коллекции student в лексикографическом порядке (по убыванию) по ключу name.
9. Сколько в коллекции student документов, у которых значение поля age больше 20?
10. Укажите минимальный возраст студентов (из коллекции student) с фамилией (поле surname) "Semenov".
11. Сколько станций на линии "Circle" в метро Лондона (коллекция UNDERGROUND).
ЛАБОРАТОРНАЯ РАБОТА № 7
ЗНАКОМСТВО С GOOGLE SHEETS
1. Первое, что нужно сделать для того, чтобы начать работу с Гугл таблицами – это создать себе почтовый ящик на gmail.
При желании возможно отредактировать личные данные, проверить язык – русский. Далее переходим на Гугл диск, всплывающее меню Приложения Google.
Перед вами диск, на котором можно хранить любые ваши данные. Размещать файлы на диске можно, перетаскивая их мышью. Но скачивать информацию с диска или удалять можно только вызвав контекстное меню правой клавишей.
2. Для начала работы с Гугл таблицами в меню левой части экрана нажимаем Создать – Гугл таблицы.
Гугл таблицы готовы к работе. В таблицы возможно вписывать значения, текст, вызывать функции. Сейчас в Гугл таблицах можно работать с функциями на любом из поддерживаемых языков, их всего 22.
3. Совместное использование и редактирование страниц является одной из важнейших функций Гугл таблиц. Можно предоставить другим пользователям редактирование и использование ваших файлов одновременно с вами.
Сохраним таблицу и дадим ей имя. В поле Новая таблица задаем имя Test1.
4. Для того, чтобы предоставить права доступа другим пользователям к вашей таблице:
либо меню Файл – Совместный доступ
либо кнопка в правом верхнем углу Настройки доступа.
Далее в окне мы можем ввести электронные адреса тех, кому предоставляем доступ.
При предоставлении прав на доступ к вашей таблице мы можем не прописывать адреса. В сплывающем окне мы можем скопировать ссылку и задать права читателя, комментатора или редактора таблицы.
Все, кому ссылка станет доступна, смогут производить с ней заданные действия.
Если же вы хотите защитить часть данных в таблице от редактирования другими пользователями, действия будут следующими:
-выделяем часть данных,
-вызываем контекстное меню,
-выбираем Защитить диапазон.
В сплывающем окне мы даем имя листу/диапазону и разрешаем редактирование, к примеру, только себе. После данных действий другие пользователи изменять диапазон не смогут.
Для отмены защиты:
-выбираем вкладку Данные,
-Защищенные листы и диапазоны,
-Удаляем ранее выставленную защиту.
Если вы получили ссылку на работу с Гугл таблицей, но не можете вносить в нее изменения, то для редактирования файла есть две возможности:
• Либо отправить запрос владельцу файла с просьбой открыть доступ на редактирование;
• Сохранить себе версию файла, выбрав в меню Файл – Создать копию. Но при этом вы будете работать с файлом в монорежиме.
5. История всех изменений, которые вносятся в Гугл таблицу сохраняется. С этим можно ознакомиться, нажав вкладку Файл – История версий – смотреть историю версий. Мы можем просмотреть версии документа и, если последние изменения не нужны, можем удалить версию.
6. Сохранять документ не требуется. Мы можем просто закрыть его, а потом с любого устройства продолжить работу с момента, на котором закончили.
7. При редактировании документа MS Excel, находящегося на Гугл диске будет предложено открыть его в приложении Гугл, соглашаемся. Открывается таблица, которую мы редактируем в Гугл таблице. По окончании работы в таблице и ее закрытии на диске появляется второй документ, он уже не таблица MS Excel, а Гугл таблица. Это будет видно по значку файла.
Начало работы с Google Sheets. Функции, сортировка, фильтрация
Для начала работы с таблицами заполняем данными 5 листов.
Два листа с товарами, два листа с клиентами и последний лист назовем Сделки.
Заполняем данными лист товары1.
Для работы с любыми данными их необходимо привести к удобному виду. Первые два столбца выглядят одинаково, но цена должна быть числом. Пока наш столбец имеет текстовый формат. Убедиться в этом можно, проверив в соседней ячейке, выполнив любую текстовую функцию, например, умножив на любое число. Увидим, что математические функции не работают, т.к. это не число.
Уберем единицы измерения. Для этого выделяем столбец – Правка – Найти и изменить. Меняем р. на пустое место, таким образом избавляемся от единиц измерения. Точно таким же образом можно заменить точку на запятую, и т.д.
Следующим шагом произведем контроль диапазонов: Формат – условное форматирование. Явно цена должна быть положительным числом. Выделим правило Меньше или равно нулю, значение ноль и присвоим, к примеру красный цвет.
Исправляем отрицательное значение. Теперь весь лист корректный.
Переходим ко второму листу Товары. Заполняем таблицу.
Снова столбец Цена в неудобном формате. Можно изменить копейки с помощью Замены. Но познакомимся с иной функцией. Данная функция позволяет использовать регулярные выражения.
Необходимо извлечь число из текстовой строки.
Регулярные выражения представляют собой инструмент для поиска строк, проверки их на соответствие какому-либо шаблону. На английском regular expressions или regexp. Регулярные выражения - это специальный язык для описания шаблонов строк. Для создания шаблонов есть множество различных обозначений. Например, точка обозначает один любой символ, плюс означает, что указанный перед ним символ может повторяться несколько раз, в квадратных скобках перечисляют возможные искомые символы и т.д.
Извлечем числа из текстовых ячеек при помощи функции REGEXEXTRACT/
У этой функции два параметра: 1- текстовая строка, из которой будем извлекать, 2 – собственно регулярное выражение/шаблон. Мы хотим извлекать цифры от 0 до 9, и цифры могут повторяться, их может быть несколько (знак+), шаблон заключаем в кавычки.
Правильные цифры извлекли из текстовой строки. Протягиваем функцию по столбцу для расчета.
Но в записи цены всё равно присутствует ошибка. Нам необходимо указывать цену в рублях, а не в копейках. Для этого формулу делим на 100. После этого всё верно.
После этого данные можно объединить.
Выделяем элементы первых двух столбцов, затем, удерживая CTRL, элементы четвертого столбца, копируем, идем на первый лист, правой клавишей – специальная вставка – вставить только значения.
Теперь наблюдаем, что значения 5 и 6 строки совпадают. Бывает, что в данных есть дубликаты. Удалить их можно при помощи специальной команды: выделяем наши данные – вкладка Данные – удалить повторы – выделяем все столбцы – удаляем повторы.
После данного действия все строки корректные.
Переходим к работе с листом Клиенты1. Заполняем данные
Первое, что необходимо сделать, это разделить столбец ФИО на три: Фамилия, имя, отчество. Для этого сначала вставим два столбца справа от ФИО, в которые потом войдут данные.
Далее выделяем столбец ФИО – Данные – Разделить текст на столбцы – в качестве разделителя выбираем пробел. Далее столбцы назовем правильно.
Теперь необходимо проверить корректность данных в столбцах.
Фамилия должна состоять только из русских букв, при это начинаться с заглавной. Используем для этого снова регулярные выражения.
Для начала используем функцию REGEXMATCH и определим ее параметры. Первым параметром будет фамилия, а вторым первая заглавная русская буква в количестве одна, далее прописные буквы, их несколько, на этом должна закончиться строка.
Таким образом, мы увидим, где ошибки, ложь или истина. Исправим ошибки.
Далее рассмотрим лист Клиенты2. Заполним его.
Что здесь указано неверно на первый взгляд? Город и Место необходимо соединить в Адрес. Для этого воспользуемся функцией Сцепить. Добавим справа столбец Адрес . Прописываем функцию Сцепить, далее указываем Город, через точку с запятой, затем запятую и пробел, чтоб строки не слепились, далее место.
Далее протягиваем функцию для всех. Копируем нужные столбцы на предыдущий лист.
Переходим на вкладку Сделки. Заполняем её.
Нам необходимо узнать общую сумму от продаж за каждый квартал. Нужно умножить количество товара на его цену и сложить значения по каждому кварталу.
Для этого нам необходимо перемножить матрицы. Для начала необходимо матрицу о количестве покупок транспонировать – поменять строки и столбцы местами. Для этого мы воспользуемся функцией ТРАНСП , далее выделяем столбцы в таблице, которые будем транспонировать.
После этого мы можем матрицу умножить. Используем функцию МУМНОЖ ,в качестве первого множителя указываем покупки в кварталах из транспонированной матрицы, а в качестве второго множителя столбец с ценой.
По лучили суммы покупок за I и II кварталы. Необходимо отметить, что при смене значений в первичных матрицах, результат меняется везде.
Сортировка и фильтрация в Таблицах сходна по функционалу с таблицами Excel.
Фильтры создаются Выделяя заголовок – вкладка Данные – создать фильтр. Сортировать данные можно по нескольким параметрам. Выделяя всю таблицу – Данные – Сортировка диапазона – задаем необходимые для таблицы параметры.
Использование условного форматирования: выделяем столбец – Формат – Условное форматирование, выбираем всё, что хотим выделить.
Создадим 3 листа: Товары, Клиенты, Сделки. Данные возьмем уже прошедшие.
Третья таблица (Лист) состоит из данных первых двух. Даты, количество ставим в хаотичном порядке, как и товар (НО соответствие артикулу, наименованию и цене не путаем).
В столбце Фамилия мы будем видеть соответствие фамилии и коду клиента из другой таблицы. Для это пишем в первую ячейку в столбце функцию ПРОСМОТР. Данная функция имеет три параметра.
1 – сам критерий, по которому мы будем производить поиск – это код клиента.
2 – столбец, в котором мы будем искать значения. (Находится на листе Клиенты в столбце Код клиента. Важно, чтобы этот столбец был упорядочен по возрастанию).
3 – столбец подстановки / Столбец фамилия. В нем мы и будем искать соответствующие значения и вставлять их в свой столбец.
Для продолжения работы и агрегирования данных необходимо сосчитать суммы.
Как узнать, кто, каких товаров и на какую сумму в целом купил? Для этого необходимо сагрегировать данные. Лучшим средством для этого являются сводные таблицы.
Чтобы построить сводную таблицу мы должны выделить диапазон данных, и во вкладке данные выбираем Создать сводную таблицу.
Создается новый лист, в который мы можем указывать нужные значения, строки и столбцы.
Справа на панели в Строки мы добавим Фамилию клиента. Они сразу упорядочиваются пофамильно. В столбцы добавим Наименование товара. В значения добавим Сумму, чтобы подсчитывать общую сумму, на которую был куплен тот или иной товар. Также в значения добавим Количество товара.
Подправим шапку таблицы, чтоб стало читабельно.
Можно перетащить значение из столбцов в строки, вид таблицы сразу изменится.
Таким образом стало четко видно, каким клиентом сколько товара, на какую сумму было приобретено.
Также возможно добавление фильтров и показателей, так мы сможем выделить данные от остального массива информации.
При смене связанных данных в первых таблицах (в нашем случае фамилий клиентов), изменятся данные и в сводной.
ЛАБОРАТОРНАЯ РАБОТА № 8-9
УПРАЖНЕНИЯ В GOOGLE SHEETS
УПРАЖНЕНИЕ 1
Создаем на Гугл диске таблицу, называем Упражнение 1.
Шел трамвай десятый номер…
Обычно при подходе к остановке у каждого трамвая фиксируется время. Но иногда датчик дает сбой, и время прохождения остановки не фиксируется. А данные с пропущенными значениями – это очень неудобно. Давайте попробуем восстановить пропущенное время прохождения остановок. Как это сделать? Определить пропорции прохождения каждой неопределенной остановки маршрута, усреднив время прохождения этой же остановки другими трамваями этого маршрута без пропусков. Конечно, если бы наш трамвай попал в пробку, или на его пути оказалась бы авария, то время на прохождение остановки могло бы сильно отличаться от среднестатистического – мы бы приняли его за выброс и удалили из рассмотрения. Но с нашими трамваями в тот день такие неприятности не случались.
Общее время прохода нескольких остановок тоже надо усреднить, определить пропорции – и пропущенные значения легко будут восстановлены.
Исходные данные задачи находятся в таблице. Скачайте, вставьте табличные значения в Гугл таблицы.
Для начала определяем длительность интервала, в котором были пропущены значения прибытия трамвая на остановки.
Логично, это решается вычитанием, получим длительность интервала в минутах.
Далее определим длительность всех интервалов известных нам рейсов. Т.е. сколько времени каждый из трамваев шел от остановки до остановки в тот промежуток, когда в четвертом рейсе значения были пропущены.
После определяем среднюю длительность интервалов, для этого воспользуемся формулой среднего значения.
Далее считаем сумму средних длительностей по известным нам рейсам при помощи функции Суммы.
Затем, чтобы вычислить сколько ехал 4 трамвай от одной остановки до другой, нам нужно высчитать пропорцию, используя вычисленные значения.
Среднюю длительность от одной остановки до другой умножаем на длительность пропущенного интервала рейса 4 (!здесь необходимо зафиксировать формулу F4 либо знак $ перед названием строки и столбца!) и делим на сумму средних длительностей по всем трем известным нам трамваям (!Снова фиксируем ячейку!).
И получаем длительность движения 4 трамвая между остановками.
Теперь, чтобы получить заключительный ответ необходимо к известному нам времени 4го трамвая прибавить время движения до следующей остановки.
|