Какие записи удовлетворяют условию название like o
Перейти к содержимому

Какие записи удовлетворяют условию название like o

  • автор:

SQL. Занимательные задачки

Вот уже более 3-х лет я преподаю SQL в разных тренинг центрах, и одним из моих наблюдений является то, что студенты осваивают и понимают SQL лучше, если ставить перед ними задачу, а не просто рассказывать о возможностях и теоретических основах.

В этой статье я поделюсь с вами своим списком задач, которые я даю студентам в качестве домашнего задания и над которыми мы проводим разного рода брейнстормы, что приводит к глубокому и четкому пониманию SQL.

SQL (ˈɛsˈkjuˈɛl; англ. structured query language — «язык структурированных запросов») — декларативный язык программирования, применяемый для создания, модификации и управления данными в реляционной базе данных, управляемой соответствующей системой управления базами данных. Подробнее…

Почитать об SQL можно из разных источников.
Данная статья не преследует цели обучить вас SQL с нуля.

Итак, поехали.

Будем использовть всем известную схему HR в Oracle с ее таблицами (Подробнее):

Отмечу что мы будем рассматривать только задачи на SELECT. Тут нет задач на DML и DDL.

Задачи

Restricting and Sorting Data

Таблица Employees. Получить список с информацией обо всех сотрудниках

Решение

SELECT * FROM employees 

Таблица Employees. Получить список всех сотрудников с именем ‘David’
Решение

SELECT * FROM employees WHERE first_name = 'David'; 

Таблица Employees. Получить список всех сотрудников с job_id равным ‘IT_PROG’
Решение

SELECT * FROM employees WHERE job_id = 'IT_PROG' 

Таблица Employees. Получить список всех сотрудников из 50го отдела (department_id) с зарплатой(salary), большей 4000

Решение

SELECT * FROM employees WHERE department_id = 50 AND salary > 4000; 

Таблица Employees. Получить список всех сотрудников из 20го и из 30го отдела (department_id)
Решение

SELECT * FROM employees WHERE department_id = 20 OR department_id = 30; 

Таблица Employees. Получить список всех сотрудников у которых последняя буква в имени равна ‘a’
Решение

SELECT * FROM employees WHERE first_name LIKE '%a'; 

Таблица Employees. Получить список всех сотрудников из 50го и из 80го отдела (department_id) у которых есть бонус (значение в колонке commission_pct не пустое)

Решение

SELECT * FROM employees WHERE (department_id = 50 OR department_id = 80) AND commission_pct IS NOT NULL; 

Таблица Employees. Получить список всех сотрудников у которых в имени содержатся минимум 2 буквы ‘n’
Решение

SELECT * FROM employees WHERE first_name LIKE '%n%n%'; 

Таблица Employees. Получить список всех сотрудников у которых длина имени больше 4 букв
Решение

SELECT * FROM employees WHERE first_name LIKE '%_____%'; 

Таблица Employees. Получить список всех сотрудников у которых зарплата находится в промежутке от 8000 до 9000 (включительно)

Решение

SELECT * FROM employees WHERE salary BETWEEN 8000 AND 9000; 

Таблица Employees. Получить список всех сотрудников у которых в имени содержится символ ‘%’
Решение

SELECT * FROM employees WHERE first_name LIKE '%\%%' ESCAPE '\'; 

Таблица Employees. Получить список всех ID менеджеров
Решение

SELECT DISTINCT manager_id FROM employees WHERE manager_id IS NOT NULL; 

Таблица Employees. Получить список работников с их позициями в формате: Donald(sh_clerk)
Решение

SELECT first_name || '(' || LOWER (job_id) || ')' employee FROM employees; 

Using Single-Row Functions to Customize Output

Таблица Employees. Получить список всех сотрудников у которых длина имени больше 10 букв

Решение

SELECT * FROM employees WHERE LENGTH (first_name) > 10; 

Таблица Employees. Получить список всех сотрудников у которых в имени есть буква ‘b’ (без учета регистра)

Решение

SELECT * FROM employees WHERE INSTR (LOWER (first_name), 'b') > 0; 

Таблица Employees. Получить список всех сотрудников у которых в имени содержатся минимум 2 буквы ‘a’
Решение

SELECT * FROM employees WHERE INSTR (LOWER (first_name),'a',1,2) > 0; 

Таблица Employees. Получить список всех сотрудников зарплата которых кратна 1000
Решение

SELECT * FROM employees WHERE MOD (salary, 1000) = 0; 

Таблица Employees. Получить первое 3х значное число телефонного номера сотрудника если его номер в формате ХХХ.ХХХ.ХХХХ

Решение

SELECT phone_number, SUBSTR (phone_number, 1, 3) new_phone_number FROM employees WHERE phone_number LIKE '___.___.____'; 

Таблица Departments. Получить первое слово из имени департамента для тех у кого в названии больше одного слова

Решение

SELECT department_name, SUBSTR (department_name, 1, INSTR (department_name, ' ')-1) first_word FROM departments WHERE INSTR (department_name, ' ') > 0; 

Таблица Employees. Получить имена сотрудников без первой и последней буквы в имени
Решение

SELECT first_name, SUBSTR (first_name, 2, LENGTH (first_name) - 2) new_name FROM employees; 

Таблица Employees. Получить список всех сотрудников у которых последняя буква в имени равна ‘m’ и длинной имени большей 5ти

Решение

SELECT * FROM employees WHERE SUBSTR (first_name, -1) = 'm' AND LENGTH(first_name)>5; 

Таблица Dual. Получить дату следующей пятницы
Решение

SELECT NEXT_DAY (SYSDATE, 'FRIDAY') next_friday FROM DUAL; 

Таблица Employees. Получить список всех сотрудников которые работают в компании больше 17 лет
Решение

SELECT * FROM employees WHERE MONTHS_BETWEEN (SYSDATE, hire_date) / 12 > 17; 

Таблица Employees. Получить список всех сотрудников у которых последня цифра телефонного номера нечетная и состоит из 3ех чисел разделенных точкой

Решение

SELECT * FROM employees WHERE MOD (SUBSTR (phone_number, -1), 2) != 0 AND INSTR (phone_number,'.',1,3) = 0 AND INSTR (phone_number,'.',1,2) > 0; 

Таблица Employees. Получить список всех сотрудников у которых в значении job_id после знака ‘_’ как минимум 3 символа но при этом это значение после ‘_’ не равно ‘CLERK’

Решение

SELECT * FROM employees WHERE LENGTH (SUBSTR (job_id, INSTR (job_id, '_') + 1)) > 3 AND SUBSTR (job_id, INSTR (job_id, '_') + 1) != 'CLERK'; 

Таблица Employees. Получить список всех сотрудников заменив в значении PHONE_NUMBER все ‘.’ на ‘-‘
Решение

SELECT phone_number, REPLACE (phone_number, '.', '-') new_phone_number FROM employees; 

Using Conversion Functions and Conditional Expressions

Таблица Employees. Получить список всех сотрудников которые пришли на работу в первый день месяца (любого)

Решение

SELECT * FROM employees WHERE TO_CHAR (hire_date, 'DD') = '01'; 

Таблица Employees. Получить список всех сотрудников которые пришли на работу в 2008ом году
Решение

SELECT * FROM employees WHERE TO_CHAR (hire_date, 'YYYY') = '2008'; 

Таблица DUAL. Показать завтрашнюю дату в формате: Tomorrow is Second day of January
Решение

SELECT TO_CHAR (SYSDATE, 'fm""Tomorrow is ""Ddspth ""day of"" Month') info FROM DUAL; 

Таблица Employees. Получить список всех сотрудников и дату прихода на работу каждого в формате: 21st of June, 2007

Решение

SELECT first_name, TO_CHAR (hire_date, 'fmddth ""of"" Month, YYYY') hire_date FROM employees; 

Таблица Employees. Получить список работников с увеличенными зарплатами на 20%. Зарплату показать со знаком доллара

Решение

SELECT first_name, TO_CHAR (salary + salary * 0.20, 'fm$999,999.00') new_salary FROM employees; 

Таблица Employees. Получить список всех сотрудников которые приши на работу в феврале 2007го года.
Решение

SELECT * FROM employees WHERE hire_date BETWEEN TO_DATE ('01.02.2007', 'DD.MM.YYYY') AND LAST_DAY (TO_DATE ('01.02.2007', 'DD.MM.YYYY')); SELECT * FROM employees WHERE to_char(hire_date,'MM.YYYY') = '02.2007'; 

Таблица DUAL. Вывезти актуальную дату, + секунда, + минута, + час, + день, + месяц, + год
Решение

SELECT SYSDATE now, SYSDATE + 1 / (24 * 60 * 60) plus_second, SYSDATE + 1 / (24 * 60) plus_minute, SYSDATE + 1 / 24 plus_hour, SYSDATE + 1 plus_day, ADD_MONTHS (SYSDATE, 1) plus_month, ADD_MONTHS (SYSDATE, 12) plus_year FROM DUAL; 

Таблица Employees. Получить список всех сотрудников с полными зарплатами (salary + commission_pct(%)) в формате: $24,000.00

Решение

SELECT first_name, salary, TO_CHAR (salary + salary * NVL (commission_pct, 0), 'fm$99,999.00') full_salary FROM employees; 

Таблица Employees. Получить список всех сотрудников и информацию о наличии бонусов к зарплате (Yes/No)

Решение

SELECT first_name, commission_pct, NVL2 (commission_pct, 'Yes', 'No') has_bonus FROM employees; 

Таблица Employees. Получить уровень зарплаты каждого сотрудника: Меньше 5000 считается Low level, Больше или равно 5000 и меньше 10000 считается Normal level, Больше иои равно 10000 считается High level

Решение

SELECT first_name, salary, CASE WHEN salary < 5000 THEN 'Low' WHEN salary >= 5000 AND salary < 10000 THEN 'Normal' WHEN salary >= 10000 THEN 'High' ELSE 'Unknown' END salary_level FROM employees; 

Таблица Countries. Для каждой страны показать регион в котором он находится: 1-Europe, 2-America, 3-Asia, 4-Africa (без Join)

Решение

SELECT country_name country, DECODE (region_id, 1, 'Europe', 2, 'America', 3, 'Asia', 4, 'Africa', 'Unknown') region FROM countries; SELECT country_name country, CASE region_id WHEN 1 THEN 'Europe' WHEN 2 THEN 'America' WHEN 3 THEN 'Asia' WHEN 4 THEN 'Africa' ELSE 'Unknown' END region FROM countries; 

Reporting Aggregated Data Using the Group Functions

Таблица Employees. Получить репорт по department_id с минимальной и максимальной зарплатой, с ранней и поздней датой прихода на работу и с количествов сотрудников. Сорировать по количеству сотрудников (по убыванию)

Решение

 SELECT department_id, MIN (salary) min_salary, MAX (salary) max_salary, MIN (hire_date) min_hire_date, MAX (hire_date) max_hire_Date, COUNT (*) count FROM employees GROUP BY department_id order by count(*) desc; 

Таблица Employees. Сколько сотрудников имена которых начинается с одной и той же буквы? Сортировать по количеству. Показывать только те где количество больше 1

Решение

SELECT SUBSTR (first_name, 1, 1) first_char, COUNT (*) FROM employees GROUP BY SUBSTR (first_name, 1, 1) HAVING COUNT (*) > 1 ORDER BY 2 DESC; 

Таблица Employees. Сколько сотрудников которые работают в одном и тоже отделе и получают одинаковую зарплату?

Решение

SELECT department_id, salary, COUNT (*) FROM employees GROUP BY department_id, salary HAVING COUNT (*) > 1; 

Таблица Employees. Получить репорт сколько сотрудников приняли на работу в каждый день недели. Сортировать по количеству

Решение

SELECT TO_CHAR (hire_Date, 'Day') day, COUNT (*) FROM employees GROUP BY TO_CHAR (hire_Date, 'Day') ORDER BY 2 DESC; 

Таблица Employees. Получить репорт сколько сотрудников приняли на работу по годам. Сортировать по количеству

Решение

SELECT TO_CHAR (hire_date, 'YYYY') year, COUNT (*) FROM employees GROUP BY TO_CHAR (hire_date, 'YYYY'); 

Таблица Employees. Получить количество департаментов в котором есть сотрудники
Решение

SELECT COUNT (COUNT (*)) department_count FROM employees WHERE department_id IS NOT NULL GROUP BY department_id; 

Таблица Employees. Получить список department_id в котором работают больше 30 сотрудников
Решение

 SELECT department_id FROM employees GROUP BY department_id HAVING COUNT (*) > 30; 

Таблица Employees. Получить список department_id и округленную среднюю зарплату работников в каждом департаменте.

Решение

 SELECT department_id, ROUND (AVG (salary)) avg_salary FROM employees GROUP BY department_id; 

Таблица Countries. Получить список region_id сумма всех букв всех country_name в котором больше 60ти
Решение

 SELECT region_id FROM countries GROUP BY region_id HAVING SUM (LENGTH (country_name)) > 60; 

Таблица Employees. Получить список department_id в котором работают работники нескольких (>1) job_id
Решение

 SELECT department_id FROM employees GROUP BY department_id HAVING COUNT (DISTINCT job_id) > 1; 

Таблица Employees. Получить список manager_id у которых количество подчиненных больше 5 и сумма всех зарплат его подчиненных больше 50000

Решение

 SELECT manager_id FROM employees GROUP BY manager_id HAVING COUNT (*) > 5 AND SUM (salary) > 50000; 

Таблица Employees. Получить список manager_id у которых средняя зарплата всех его подчиненных находится в промежутке от 6000 до 9000 которые не получают бонусы (commission_pct пустой)

Решение

 SELECT manager_id, AVG (salary) avg_salary FROM employees WHERE commission_pct IS NULL GROUP BY manager_id HAVING AVG (salary) BETWEEN 6000 AND 9000; 

Таблица Employees. Получить максимальную зарплату из всех сотрудников job_id которыз заканчивается на слово ‘CLERK’

Решение

SELECT MAX (salary) max_salary FROM employees WHERE job_id LIKE '%CLERK'; SELECT MAX (salary) max_salary FROM employees WHERE SUBSTR (job_id, -5) = 'CLERK'; 

Таблица Employees. Получить максимальную зарплату среди всех средних зарплат по департаменту
Решение

 SELECT MAX (AVG (salary)) FROM employees GROUP BY department_id; 

Таблица Employees. Получить количество сотрудников с одинаковым количеством букв в имени. При этом показать только тех у кого длина имени больше 5 и количество сотрудников с таким именем больше 20. Сортировать по длинне имени

Решение

 SELECT LENGTH (first_name), COUNT (*) FROM employees GROUP BY LENGTH (first_name) HAVING LENGTH (first_name) > 5 AND COUNT (*) > 20 ORDER BY LENGTH (first_name); SELECT LENGTH (first_name), COUNT (*) FROM employees WHERE LENGTH (first_name) > 5 GROUP BY LENGTH (first_name) HAVING COUNT (*) > 20 ORDER BY LENGTH (first_name); 

Displaying Data from Multiple Tables Using Joins

Таблица Employees, Departaments, Locations, Countries, Regions. Получить список регионов и количество сотрудников в каждом регионе

Решение

 SELECT region_name, COUNT (*) FROM employees e JOIN departments d ON (e.department_id = d.department_id) JOIN locations l ON (d.location_id = l.location_id) JOIN countries c ON (l.country_id = c.country_id) JOIN regions r ON (c.region_id = r.region_id) GROUP BY region_name; 

Таблица Employees, Departaments, Locations, Countries, Regions. Получить детальную информацию о каждом сотруднике:
First_name, Last_name, Departament, Job, Street, Country, Region

Решение

SELECT First_name, Last_name, Department_name, Job_id, street_address, Country_name, Region_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) JOIN locations l ON (d.location_id = l.location_id) JOIN countries c ON (l.country_id = c.country_id) JOIN regions r ON (c.region_id = r.region_id); 

Таблица Employees. Показать всех менеджеров которые имеют в подчинении больше 6ти сотрудников
Решение

 SELECT man.first_name, COUNT (*) FROM employees emp JOIN employees man ON (emp.manager_id = man.employee_id) GROUP BY man.first_name HAVING COUNT (*) > 6; 

Таблица Employees. Показать всех сотрудников которые ни кому не подчиняются
Решение

SELECT emp.first_name FROM employees emp LEFT JOIN employees man ON (emp.manager_id = man.employee_id) WHERE man.FIRST_NAME IS NULL; SELECT first_name FROM employees WHERE manager_id IS NULL; 

Таблица Employees, Job_history. В таблице Employee хранятся все сотрудники. В таблице Job_history хранятся сотрудники которые покинули компанию. Получить репорт о всех сотрудниках и о его статусе в компании (Работает или покинул компанию с датой ухода)
Пример:
first_name | status
Jennifer | Left the company at 31 of December, 2006
Clara | Currently Working

Решение

SELECT first_name, NVL2 ( end_date, TO_CHAR (end_date, 'fm""Left the company at"" DD ""of"" Month, YYYY'), 'Currently Working') status FROM employees e LEFT JOIN job_history j ON (e.employee_id = j.employee_id); 

Таблица Employees, Departaments, Locations, Countries, Regions. Получить список сотрудников которые живут в Europe (region_name)

Решение

 SELECT first_name FROM employees JOIN departments USING (department_id) JOIN locations USING (location_id) JOIN countries USING (country_id) JOIN regions USING (region_id) WHERE region_name = 'Europe'; SELECT first_name FROM employees e JOIN departments d ON (e.department_id = d.department_id) JOIN locations l ON (d.location_id = l.location_id) JOIN countries c ON (l.country_id = c.country_id) JOIN regions r ON (c.region_id = r.region_id) WHERE region_name = 'Europe'; 

Таблица Employees, Departaments. Показать все департаменты в которых работают больше 30ти сотрудников

Решение

SELECT department_name, COUNT (*) FROM employees e JOIN departments d ON (e.department_id = d.department_id) GROUP BY department_name HAVING COUNT (*) > 30; 

Таблица Employees, Departaments. Показать всех сотрудников которые не состоят ни в одном департаменте

Решение

SELECT first_name FROM employees e LEFT JOIN departments d ON (e.department_id = d.department_id) WHERE d.department_name IS NULL; SELECT first_name FROM employees WHERE department_id IS NULL; 

Таблица Employees, Departaments. Показать все департаменты в которых нет ни одного сотрудника
Решение

SELECT department_name FROM employees e RIGHT JOIN departments d ON (e.department_id = d.department_id) WHERE first_name IS NULL; 

Таблица Employees. Показать всех сотрудников у которых нет ни кого в подчинении
Решение

SELECT man.first_name FROM employees emp RIGHT JOIN employees man ON (emp.manager_id = man.employee_id) WHERE emp.FIRST_NAME IS NULL; 

Таблица Employees, Jobs, Departaments. Показать сотрудников в формате: First_name, Job_title, Department_name.
Пример:
First_name | Job_title | Department_name
Donald | Shipping | Clerk Shipping

Решение

SELECT first_name, job_title, department_name FROM employees e JOIN jobs j ON (e.job_id = j.job_id) JOIN departments d ON (d.department_id = e.department_id); 

Таблица Employees. Получить список сотрудников менеджеры которых устроились на работу в 2005ом году но при это сами эти работники устроились на работу до 2005 года

Решение

SELECT emp.* FROM employees emp JOIN employees man ON (emp.manager_id = man.employee_id) WHERE TO_CHAR (man.hire_date, 'YYYY') = '2005' AND emp.hire_date < TO_DATE ('01012005', 'DDMMYYYY'); 

Таблица Employees. Получить список сотрудников менеджеры которых устроились на работу в январе месяце любого года и длина job_title этих сотрудников больше 15ти символов

Решение

SELECT emp.* FROM employees emp JOIN employees man ON (emp.manager_id = man.employee_id) JOIN jobs j ON (emp.job_id = j.job_id) WHERE TO_CHAR (man.hire_date, 'MM') = '01' AND LENGTH (j.job_title) > 15; 

Using Subqueries to Solve Queries

Таблица Employees. Получить список сотрудников с самым длинным именем.

Решение

SELECT * FROM employees WHERE LENGTH (first_name) = (SELECT MAX (LENGTH (first_name)) FROM employees); 

Таблица Employees. Получить список сотрудников с зарплатой большей средней зарплаты всех сотрудников.

Решение

SELECT * FROM employees WHERE salary > (SELECT AVG (salary) FROM employees); 

Таблица Employees, Departments, Locations. Получить город в котором сотрудники в сумме зарабатывают меньше всех.

Решение

SELECT city FROM employees e JOIN departments d ON (e.department_id = d.department_id) JOIN locations l ON (d.location_id = l.location_id) GROUP BY city HAVING SUM (salary) = ( SELECT MIN (SUM (salary)) FROM employees e JOIN departments d ON (e.department_id = d.department_id) JOIN locations l ON (d.location_id = l.location_id) GROUP BY city); 

Таблица Employees. Получить список сотрудников у которых менеджер получает зарплату больше 15000.
Решение

SELECT * FROM employees WHERE manager_id IN (SELECT employee_id FROM employees WHERE salary > 15000) 

Таблица Employees, Departaments. Показать все департаменты в которых нет ни одного сотрудника
Решение

SELECT * FROM departments WHERE department_id NOT IN (SELECT department_id FROM employees WHERE department_id IS NOT NULL); 

Таблица Employees. Показать всех сотрудников которые не являются менеджерами
Решение

SELECT * FROM employees WHERE employee_id NOT IN (SELECT manager_id FROM employees WHERE manager_id IS NOT NULL) 

Таблица Employees. Показать всех менеджеров которые имеют в подчинении больше 6ти сотрудников
Решение

SELECT * FROM employees e WHERE (SELECT COUNT (*) FROM employees WHERE manager_id = e.employee_id) > 6; 

Таблица Employees, Departaments. Показать сотрудников которые работают в департаменте IT
Решение

SELECT * FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'IT'); 

Таблица Employees, Jobs, Departaments. Показать сотрудников в формате: First_name, Job_title, Department_name.
Пример:
First_name | Job_title | Department_name
Donald | Shipping | Clerk Shipping

Решение

SELECT first_name, (SELECT job_title FROM jobs WHERE job_id = e.job_id) job_title, (SELECT department_name FROM departments WHERE department_id = e.department_id) department_name FROM employees e; 

Таблица Employees. Получить список сотрудников менеджеры которых устроились на работу в 2005ом году но при это сами эти работники устроились на работу до 2005 года

Решение

SELECT * FROM employees WHERE manager_id IN (SELECT employee_id FROM employees WHERE TO_CHAR (hire_date, 'YYYY') = '2005') AND hire_date < TO_DATE ('01012005', 'DDMMYYYY'); 

Таблица Employees. Получить список сотрудников менеджеры которых устроились на работу в январе месяце любого года и длина job_title этих сотрудников больше 15ти символов

Решение

SELECT * FROM employees e WHERE manager_id IN (SELECT employee_id FROM employees WHERE TO_CHAR (hire_date, 'MM') = '01') AND (SELECT LENGTH (job_title) FROM jobs WHERE job_id = e.job_id) > 15; 

На этом пока всё.

Надеюсь, задачи были интересными и увлекательными.
Буду по возможности дополнять этот список задач.
Также буду рад любым замечаниям и предложениям.

P.S.: Если кому то в голову придет интересная задача на SELECT, пишите в коментариях, добавлю в список.

  • Занимательные задачки
  • Oracle
  • SQL
  • Учебный процесс в IT

27 распространённых вопросов по SQL с собеседований и ответы на них

Аватарка пользователя Марина Александровна

Вопросы по SQL часто входят в техническое собеседование. Здесь мы собрали популярные вопросы, задачи и дали на них развёрнутые ответы.

Вопросы по SQL на собеседованиях — обычное дело, и чтобы не завалиться, нужно хорошо понимать, как работать с базами данных. В этом списке представлены основные вопросы и задачи по SQL, которые часто встречаются на собеседованиях, а также ответы на них.

Как стать разработчиком с нуля

Теория

Что такое СУБД?

Допустим, есть большая база данных, которой пользуются многие сотрудники: кто-то ищет информацию, а кто-то изменяет или даже удаляет её. Чтобы правильно обрабатывать все эти запросы, нужно специальное программное обеспечение, и именно такое ПО получило название системы управления базами данных (СУБД).

Какие типы СУБД в соответствии с моделями данных вы знаете?

Этот вопрос по SQL предполагает не просто назвать, но и дать краткое описание каждому типу.Существует несколько типов СУБД:

  1. Реляционные, которые поддерживают установку связей между таблицами с помощью первичных и внешних ключей. Пример — MySQL.Flat File — базы данных с двумерными файлами, в которых содержатся записи одного типа и отсутствует связь с другими файлами, как в реляционных. Пример — Excel.Иерархические подразумевают наличие записей, связанных друг с другом по принципу отношений один-к-одному или один-ко-многим. А вот для отношений многие-ко-многим следует использовать реляционную модель. Пример — Adabas.Сетевые похожи на иерархические, но в этом случае «ребёнок» может иметь несколько «родителей» и наоборот. Примеры — IDS и IDMS.Объектно-ориентированные СУБД работают с базами данных, которые состоят из объектов, используемых в ООП. Объекты группируются в классы и называются экземплярами, а классы в свою очередь взаимодействуют через методы. Пример — Versant.Объектно-реляционные обладают преимуществами реляционной и объектно-ориентированной моделей. Пример — IBM Db2.Многомерная модель является разновидностью реляционной и использует многомерные структуры. Часто представляется в виде кубов данных. Пример — Oracle Essbase.Гибридные состоят из двух и более типов баз данных. Используются в том случае, если одного типа недостаточно для обработки всех запросов. Пример — Altibase HDВ.

Что такое первичный ключ?

Первичный ключ или PRIMARY KEY предназначен для однозначной идентификации каждой записи в таблице и является строго уникальным ( UNIQUE ): две записи таблицы не могут иметь одинаковые значения первичного ключа. Нулевые значения ( NULL ) в PRIMARY KEY не допускаются. Если в качестве PRIMARY KEY используется несколько полей, их называют составным ключом.Пример:

CREATE TABLE USERS ( id INT NOT NULL, name VARCHAR (20) NOT NULL, PRIMARY KEY (id)); 

Здесь в качестве первичного ключа используется поле id.

Когда используется PRIMARY KEY?

PRIMARY KEY — это первичный ключ, который используется в качестве основного ключа и может быть использован для связи с дочерней таблицей, содержащей внешний ключ.

А что такое внешний ключ?

Внешний ключ или FOREIGN KEY также является атрибутом ограничения и обеспечивает связь двух таблиц. По сути, это поле или несколько полей, которые ссылаются на PRIMARY KEY в родительской таблице.Пример использования:

CREATE TABLE order ( order_id INT NOT NULL, user_id INT, PRIMARY KEY (order_id), FOREIGN KEY (user_id) REFERENCES users(id)); 

В данном случае внешний ключ, привязанный к полю user_id в таблице order , ссылается на первичный ключ id в таблице users , и именно по этим полям происходит связывание двух таблиц.

Какие ещё ограничения вы знаете, как они работают и указываются?

SQL-ограничения (constraints) указываются при создании или изменении таблицы. Это правила для ограничения типа данных, которые могут храниться в таблице. Действие с данными не будет выполнено, если нарушаются установленные ограничения.

  • UNIQUE — гарантирует уникальность значений в столбце;
  • NOT NULL — значение не может быть NULL ;
  • INDEX — создаёт индексы в таблице для быстрого поиска/запросов;
  • CHECK — значения столбца должны соответствовать заданным условиям;
  • DEFAULT — предоставляет столбцу значения по умолчанию&

Для чего используется ключевое слово ORDER BY?

Для сортировки данных в порядке возрастания ( ASC ) или убывания ( DESC ).Пример использования:

SELECT * FROM user ORDER BY name DESC; 

Выбираются пользователи, которые будут отсортированы по имени в порядке убывания. Дополните ответ на этот вопрос по SQL тем, что без указания DESC данные были бы отсортированы по умолчанию — в порядке возрастания:

SELECT * FROM user ORDER BY name; 

Назовите четыре основных типа соединения в SQL

Чтобы объединить две таблицы в одну, следует использовать оператор JOIN . Соединение таблиц может быть внутренним ( INNER ) или внешним ( OUTER ), причём внешнее соединение может быть левым ( LEFT ), правым ( RIGHT ) или полным ( FULL ).

  • INNER JOIN — получение записей с одинаковыми значениями в обеих таблицах, т.е. получение пересечения таблиц.
  • FULL OUTER JOIN — объединяет записи из обеих таблиц (если условие объединения равно true) и дополняет их всеми записями из обеих таблиц, которые не имеют совпадений. Для записей, которые не имеют совпадений из другой таблицы, недостающее поле будет иметь значение NULL .
  • LEFT JOIN — возвращает все записи, удовлетворяющие условию объединения, плюс все оставшиеся записи из внешней (левой) таблицы, которые не удовлетворяют условию объединения.
  • RIGHT JOIN — работает точно так же, как и левое объединение, только в качестве внешней таблицы будет использоваться правая.

27 распространённых вопросов по SQL с собеседований и ответы на них 1

Рассмотрим пример соединения SQL таблиц с использованием INNER JOIN . Следующий запрос выбирает все заказы с информацией о клиенте:

SELECT Orders.OrderID, Customers.CustomerNameFROM OrdersINNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID; 

А что такое Self JOIN?

Такой вопрос тоже может прозвучать на собеседовании по SQL. Это выражение используется для того, чтобы таблица объединилась сама с собой, словно это две разные таблицы. Чтобы такое реализовать, одна из таких «таблиц» временно переименовывается.Например, следующий SQL-запрос объединяет клиентов из одного города:

SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.CityFROM Customers A, Customers BWHERE A.CustomerID <> B.CustomerIDAND A.City = B.CityORDER BY A.City; 

Для чего нужен оператор UNION?

Он используется для объединения полученных данных из двух или более запросов, которые должны иметь одинаковое количество столбцов с одинаковыми типами данных и расположенных в том же порядке.Пример использования:

SELECT column(s) FROM first_tableUNIONSELECT column(s) FROM second_table; 

Как работают подстановочные знаки?

Это специальные символы, которые нужны для замены каких-либо знаков в запросе. Они используются вместе с оператором LIKE , с помощью которого можно отфильтровать запрашиваемые данные.

Какими бывают подстановочные знаки?

  • % — заменить ноль или более символов;
  • _ — заменить один символ.
SELECT * FROM user WHERE name LIKE '%test%'; 

Данный запрос позволяет найти данные всех пользователей, имена которых содержат в себе «test».

SELECT * FROM user WHERE name LIKE 't_est'; 

А в этом случае имена искомых пользователей начинаются на «t», после содержат какой-либо символ и «est» в конце.

Что делают псевдонимы Aliases?

SQL-псевдонимы нужны для того, чтобы дать временное имя таблице или столбцу. Это нужно, когда в запросе есть таблицы или столбцы с неоднозначными именами. В этом случае для удобства в составлении запроса используются псевдонимы. SQL-псевдоним существует только на время запроса.Пример:

SELECT very_long_column_name AS alias_nameFROM table; 

Для чего нужен оператор INSERT INTO SELECT?

Данный оператор копирует данные из одной таблицы и вставляет их в другую, при этом типы данных в обеих таблицах должны соответствовать.Пример использования:

INSERT INTO second_tableSELECT * FROM first_tableWHERE condition; 

Что такое нормализация и денормализация?

Нормализация отношений в SQL призвана организовать информацию в базе данных таким образом, чтобы она не занимала много места и с ней было удобно работать. Это удаление избыточных данных, устранение дублей, идентификация наборов связанных данных через PRIMARY KEY , etc.Соответственно, денормализация является обратным процессом, который вносит в нормализованную таблицу избыточные данные.Подробнее о пяти нормальных формах и форме Бойса-Кодда можно узнать из данного видеокурса.

Объясните разницу между командами DELETE и TRUNCATE”

Команда DELETE — это DML-операция, которая удаляет записи из таблицы, соответствующие заданному условию:

DELETE FROM table_name WHERE condition; 

При этом создаются логи удаления, то есть операцию можно отменить.А вот команда TRUNCATE — это DDL-операция, которая полностью пересоздаёт таблицу, и отменить такое удаление невозможно:

TRUNCATE TABLE table_name; 

Чем VARCHAR отличается от NVARCHAR?

Главное отличие в том, что VARCHAR хранит значения в формате ASCII, где символ занимает один байт, а NVARCHAR хранит значения в формате Unicode, где символ «весит» 2 байта. Тип VARCHAR следует использовать, если вы уверены, что в значениях не будет Unicode-символов. Например, VARCHAR можно применить к адресам электронной почты, состоящих из ASCII-символов.

Практика

Как выбрать записи с нечётными Id?

Один из самых распространённых вопросов на собеседовании. SQL запрос для выбора записей с нечётными id должен выглядеть следующим образом:

SELECT * FROM sample WHERE id % 2 != 0; 

Если остаток от деления id на 2 равен нулю, перед нами чётное значение, и наоборот.

Как найти дубли в поле email?

SELECT email, COUNT(email)FROM customersGROUP BY emailHAVING COUNT(email) > 1; 

Функция COUNT() возвращает количество строк из поля email . Оператор HAVING работает почти так же, как и WHERE , вот только применяется не для всех столбцов, а для набора, созданного оператором GROUP BY .

При выборке из таблицы прибавьте к дате 1 день

SELECT DATE_ADD(date, 1 DAY) as new_date FROM table; 

Функция DATE_ADD() прибавляет к дате заданный промежуток времени. Синтаксис выглядит следующим образом:

SELECT DATE_ADD(дата, INTERVAL что_прибавить) FROM имя_таблицы WHERE условие; 

Выберите только уникальные имена

SELECT DISTINCT name FROM users; 

SELECT DISTINCT возвращает разные значения, даже если в выбранном столбце есть дубли.

Найдите в таблице среднюю зарплату работников

SELECT AVG(salary) FROM workers; 

Функция AVG() применяется только к числовым типам данных и возвращает среднее значение по столбцу.

А теперь получите список сотрудников с зарплатой выше средней

SELECT * FROM workersWHERE salary > (SELECT AVG (salary) FROM workers); 

Даны таблицы workers и departments. Найдите все департаменты без единого сотрудника

SELECT department_nameFROM workers wRIGHT JOIN departments d ON (w.department_id = d.department_id)WHERE first_name IS NULL; 

Замените в таблице зарплату работника на 1000, если она равна 900, и на 1500 в остальных случаях

Замена значений — одна из наиболее часто встречаемых задач по SQL на собеседованиях. Решить её несложно:

UPDATE table SET salary =CASEWHEN salary = 900 THEN 1000ELSE 1500END; 

Оператор UPDATE используется для изменения существующих записей. Но ответы на подобные вопросы с собеседований по SQL должны быть более развёрнутыми. Уточните,что после UPDATE следует указать, какие записи должны быть обновлены. В противном случае обновятся все записи в таблице.В нашем примере условие задаётся через оператор CASE: если текущая зарплата равна 900, изменяем её на 1000, в остальных случаях — на 1500.

При выборке из таблицы пользователей создайте поле, которое будет включать в себя и имена, и зарплату

SELECT CONCAT(name, salary) AS new_field FROM users; 

Функция CONCAT() используется для конкатенации (объединения) строк, неявно преобразуя при этом любые типы данных в строки.

Переименуйте таблицу

ALTER TABLE first_table RENAME second_table; 

С помощью оператора ALTER TABLE можно добавлять, удалять, изменять столбцы, а также изменять название таблицы.

Оператор SELECT

Наиболее используемым, но и самым сложным оператором является оператор выборки SELECT. Он позволяет производить выборку данных из таблиц и преобразовывать к нужному виду полученные результаты.

20 дек. 2020 · 9 минуты на чтение

Результатом выполнения оператора SELECT является таблица. К этой таблице может быть снова применен оператор SELECT и т.д., то есть такие операторы могут быть вложены друг в друга. Вложенные операторы SELECT называют подзапросами.

Синтаксис оператора SELECT использует следующие основные предложения:

SELECT FROM [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY ]

Кратко пояснить смысл предложений оператора SELECT можно следующим образом:

  • SELECT - выбрать данные из указанных столбцов и (если необходимо) выполнить перед выводом их преобразование в соответствии с указанными выражениями и (или) функциями
  • FROM - из перечисленных таблиц, в которых расположены эти столбцы
  • WHERE - где строки из указанных таблиц должны удовлетворять указанному перечню условий отбора строк
  • GROUP BY - группируя по указанному перечню столбцов с тем, чтобы получить для каждой группы единственное значение
  • HAVING - имея в результате лишь те группы, которые удовлетворяют указанному перечню условий отбора групп
  • ORDER BY - сортируя по указанному перечню столбцов

Как видно из синтаксиса рассматриваемого оператора, обязательными являются только два первых предложения: SELECT и FROM .

Рассмотрим каждое предложение оператора SELECT .

Спонсор поста

База данных для примеров

Дальше будет много примеров и логично постоянно использовать одну и ту же БД. Так что на основании базы данных ниже будут продемонстрированы все примеры, не только в этой статье, но и в других.

Постановка задачи: пусть требуется разработать БД для предметной области «Поставка деталей»!

Требуется хранить следующую информацию:

  • О поставщиках (P) pnum, pname
  • О деталях (D) pnum, dname, dprice
  • О поставках (PD) volume
Значения таблицы P
pnum pname
1 Иванов
2 Петров
3 Сидоров
4 Кузнецов
Значения таблицы D
pnum dname dprice
1 Болт 10
2 Гайка 20
3 Винт 30
Значения таблицы PD
pnum dnum volume
1 1 100
1 2 100
1 3 300
2 1 150
1 2 250
3 1 1000

Предложение SELECT

После служебного слова SELECT перечисляются имена столбцов, значения которых будут входить в результат выполнения запроса.

Столбцы в результирующей таблице размещаются в том порядке, в котором они были указаны в предложении SELECT . Имена столбцов указываются через запятую.

Если имя столбца содержит пробелы или разделители, то его необходимо заключить в квадратные скобки.

При обработке данных из разных таблиц может возникнуть ситуация, когда столбцы разных таблиц имеют одинаковые имена. В этом случае имя столбца необходимо записывать как составное, указывая перед ним имя соответствующей таблицы: .

Предложение FROM

В предложении FROM перечисляются имена таблиц, которые содержат столбцы, указанные после слова SELECT .

Пример 1.
Вывести список наименований деталей из таблицы D (“Детали”).

SELECT dname FROM D

Пример 2.
Получить всю информацию из таблицы D (“Детали”).

Получить результат можно двумя способами:

    Явным указанием всех столбцов таблицы.

SELECT dnum, dname, dprice FROM D 
SELECT * FROM D 

В результате и первого и второго запроса получаем новую таблицу, представляющую собой полную копию таблицы D (“Детали”).

Можно осуществить выбор отдельных столбцов и их перестановку.

Пример 3.
Получить информацию о наименовании и номере поставщика.

SELECT pname, pnum FROM P

Пример 4.
Определить номера поставщиков, которые поставляют детали в настоящее время (то есть номера тех поставщиков, которые присутствуют в таблице PD (“Поставки”)).

SELECT pnum FROM PD
pnum
1
1
1
2
2
3

Дополнительно о SELECT

Теперь, когда мы научились делать простые запросы с SELECT и FROM , можно ненадолго снова вернуться к SELECT .

Агрегатные функции

В операторе SELECT можно использовать агрегатные функции, которые дают единственное значение для целой группы строк в таблице.

Агрегатная функция записывается в следующем виде: ()

Пользователю доступны следующие агрегатные функции:

  • SUM ‑ вычисляет сумму множества значений указанного столбца;
  • COUNT ‑ вычисляет количество значений указанного столбца;
  • MIN / MAX ‑ определяет минимальное/максимальное значение в указанном столбце;
  • AVG ‑ вычисляет среднее арифметическое значение множества значений столбца;
  • FIRST / LAST ‑ определяет первое/последнее значение в указанном столбце.

Пример 5.
Определить общий объем поставляемых деталей.

SELECT SUM(volume) FROM PD
Expr1000
2000

Вычисляемые столбцы

Столбцы результирующей таблицы, которых не существовало в исходных таблицах, называются вычисляемыми. Таким столбцам СУБД присваивает системные имена, что не всегда является удобным.

При вычислении результатов любой агрегатной функции СУБД сначала исключает все NULL -значения, после чего требуемая операция применяется к оставшимся значениям.

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

Следует запомнить, что агрегатные функции нельзя вкладывать друг в друга. Такая конструкция работать не будет: MAX(SUM(VOLUME))

Переименование столбца

Язык SQL позволяет задавать новые имена столбцам результирующей таблицы, для чего используется операция AS . Переименование также используют для изменения сложных имен столбцов таблицы.

Например, присвоить новое имя вычисляемому столбцу в предыдущем примере позволит выполнение следующего запроса.

SELECT SUM(volume) AS SUM FROM PD

Пример 6.
Определить количество поставщиков, которые поставляют детали в настоящее время.

SELECT COUNT(pnum) AS COUNT FROM PD

Несмотря на то, что реальное число поставщиков деталей в таблице PD равно 3, СУБД возвращает число 6. Такой результат объясняется тем, что СУБД подсчитывает все строки в таблице PD, не обращая внимание на то, что в строках есть одинаковые значения.

Операция DISTINCT

Если до применения агрегатной функции необходимо исключить дублирующиеся значения, следует перед именем столбца указать ключевое слово DISTINCT .

SELECT COUNT(DISTINCT pnum) AS COUNT FROM PD

DISTINCT можно задать только один раз для одного предложения SELECT.

Противоположностью DISTINCT является операция ALL . Она имеет противоположное действие «показать все строки таблицы» и предполагается по умолчанию.

Операция TOP

Итоговый набор записей, получаемых после выполнения запроса можно ограничить первыми N строками или первыми N процентами от общего количества строк результата.

Для этого используется операция TOP , которая записывается в предложении SELECT следующим образом: SELECT TOP N [PERCENT]

Пример 7.
Определить номера первых двух деталей таблицы D.

SELECT TOP 2 dnum FROM D

Стандарт SQL требует, чтобы при сортировке NULL -значения трактовались либо как превосходящие, либо как уступающие по сравнению со всеми остальными значениями. Так как конкретный вариант стандартом не оговаривается, то в зависимости от используемой СУБД при сортировке NULL -значения следуют до или после остальных значений. В MS SQL Server NULL -значения считаются уступающими по сравнению с остальными значениями.

Рандомный блок

Предложение WHERE

После служебного слова WHERE указываются условия выбора строк, помещаемых в результирующую таблицу. Существуют различные типы условий выбора:

  • Сравнение значений атрибутов со скалярными выражениями, другими атрибутами или результатами вычисления выражений.
  • Проверка значения на принадлежность множеству.
  • Проверка значения на принадлежность диапазону.
  • Проверка строкового значения на соответствие шаблону.
  • Проверка на наличие null -значения.

Сравнение

В языке SQL используются традиционные операции сравнения = , <> , < , , >= .

В качестве условия в предложении WHERE можно использовать сложные логические выражения, использующие атрибуты таблиц, константы, скобки, операции AND , OR , отрицание NOT .

Пример 8.
Определить номера деталей, поставляемых поставщиком с номером 2.

SELECT dnum FROM PD WHERE pnum = 2

Пример 9.
Получить информацию о поставщиках Иванов и Петров.

SELECT * FROM P WHERE pname='Иванов' OR pname='Петров'

Строковые значения атрибутов заключаются в апострофы.

Проверка на принадлежность множеству

Операция IN проверяет, принадлежит ли значение атрибута заданному множеству.

Пример 10.
Получить информацию о поставщиках ‘Иванов’ и ‘Петров’.

SELECT * FROM P WHERE pname IN ('Иванов','Петров')

Пример 11.
Получить информацию о деталях с номерами 1 и 2.

SELECT * FROM D WHERE dnum IN (1, 2)

Проверка на принадлежность диапазону

Операция BETWEEN определяет минимальную и максимальную границу диапазона, в которое должно попадать значение атрибута. Обе границы считаются принадлежащими диапазону.

Пример 12.
Определить номера деталей, с ценой от 10 до 20 рублей.

SELECT dnum FROM D WHERE dprice BETWEEN 10 AND 20

Пример 13.
Вывести наименования поставщиков, начинающихся с букв от ‘К’ по ‘П’.

SELECT pname FROM P WHERE pname BETWEEN 'К' AND 'Р'

Сравнение символов

Буква Р в условии запроса объясняется тем, что строки сравниваются посимвольно. Для каждого символа при этом определяется код. Для нашего случая справедливо условие: П < Петров < Р

Проверка строкового значения на соответствие шаблону

Операция LIKE используется для поиска подстрок. Значения столбца, указываемого перед служебным словом LIKE сравниваются с задаваемым после него шаблоном. Форматы шаблонов различаются в конкретных СУБД.

Для СУБД MS SQL Server:

  • Символ % заменяет любое количество любых символов.
  • Символ _ заменяет один любой символ.
  • [] ‑ вместо символа строки может быть подставлен один любой символ из множества возможных, указанных в ограничителях.
  • [^] ‑ вместо символа строки может быть подставлен любой из символов кроме символов из множества, указанного в ограничителях.

Множество символов в квадратных скобках можно указывать через запятую, либо в виде диапазона.

Пример 14.
Вывести фамилии поставщиков, начинающихся с буквы И .

SELECT pname FROM P WHERE pname LIKE 'И%'

Пример 15.
Вывести фамилии поставщиков, начинающихся с букв от К по П .

SELECT pname FROM P WHERE dname LIKE '[К-П]%'

Проверка на наличие null -значения

Операции IS NULL и IS NOT NULL используются для сравнения значения атрибута со значением NULL .

Пример 16.
Определить наименования деталей, для которых не указана цена.

SELECT dname FROM D WHERE dprice IS NULL

Пример 17.
Определить номера поставщиков, для которых указано наименование.

SELECT pnum FROM P WHERE pname IS NOT NULL

Предложение GROUP BY

Использование GROUP BY позволяет разбивать таблицу на логические группы и применять агрегатные функции к каждой из этих групп. В результате получим единственное значение для каждой группы.

Обычно предложение GROUP BY применяют, если формулировка задачи содержит фразу «для каждого…», «каждому..» и т.п.

Пример 18.
Определить суммарный объем деталей, поставляемых каждым поставщиком.

SELECT pnum, SUM(VOLUME) AS SUM FROM PD GROUP BY pnum
pnum sum
1 600
2 400
3 1000

Выполнение запроса можно описать следующим образом: СУБД разбивает таблицу PD на три группы, в каждую из групп помещаются строки с одинаковым значением номера поставщика. Затем к каждой из полученных групп применяется агрегатная функция SUM , что дает единственное итоговое значение для каждой группы.

Рассмотрим два похожих примера. В примере 19 определяется минимальный объем поставки каждого поставщика. В примере 20 определяется объем минимальной поставки среди всех поставщиков.

Пример 19:

SELECT pnum, MIN(VOLUME) AS MIN FROM PD GROUP BY pnum

Пример 20:

SELECT MIN(VOLUME) AS MIN FROM P

Результаты запросов представлены в следующей таблице:

pnum min max
1 100 100
2 150
3 1000

Следует обратить внимание, что в первом примере мы можем вывести номера поставщиков, соответствующие объемам поставок, а во втором примере – не можем.

Все имена столбцов, перечисленные после ключевого слова SELECT должны присутствовать и в предложении GROUP BY , за исключением случая, когда имя столбца является аргументом агрегатной функции.

Однако в предложении GROUP BY могут быть указаны имена столбцов, не перечисленные в списке вывода после ключевого слова SELECT .

Если предложение GROUP BY расположено после предложения WHERE , то группы создаются из строк, выбранных после применения WHERE .

Пример 21.
Для каждой из деталей с номерами 1 и 2 определить количество поставщиков, которые их поставляют, а также суммарный объем поставок деталей.

SELECT dnum, COUNT(pnum) AS COUNT, SUM(volume) AS SUM FROM PD WHERE dnum=1 OR dnum=2 GROUP BY dnum
dnum COUNT SUM
1 3 1250
2 2 450

Чтобы организовать вложенные группировки, после GROUP BY следует указать несколько группирующих столбцов через запятую. В этом случае реальный подсчет данных будет происходить по той группе, которая указана последней.

Предложение HAVING

Предложение HAVING определяет критерий, согласно которому, определенные группы, сформированные с помощью предложения GROUP BY , исключаются из результирующей таблицы.

Выполнение предложения HAVING сходно с выполнением предложения WHERE . Но предложение WHERE исключает строки до того, как выполняется группировка, а предложение HAVING - после. Поэтому предложение HAVING может содержать агрегатные функции, а предложение WHERE - не может.

Пример 22.
Определить номера поставщиков, поставляющих в сумме более 500 деталей.

SELECT pnum, SUM(volume) AS SUM FROM PD GROUP BY pnum HAVING SUM(volume) > 500
pnum SUM
1 600
3 1000

Пример 23.
Определить номера поставщиков, которые поставляют только одну деталь.

SELECT pnum, COUNT(dnum) AS COUNT FROM PD GROUP BY pnum HAVING COUNT(dnum) = 1

Предложение ORDER BY

При выполнении запроса СУБД возвращает строки в случайном порядке. Предложение ORDER BY позволяет упорядочить выходные данные запроса в соответствии со значениями одного или нескольких выбранных столбцов.

Можно задать возрастающий - ASC (от слова Ascend) или убывающий - DESC (от слова Descend) порядок сортировки. По умолчанию принят возрастающий порядок сортировки.

Пример 24.
Отсортировать таблицу PD в порядке возрастания номеров поставщиков, а строки с одинаковыми значениями pnum отсортировать в порядке убывания объема поставок.

SELECT pnum, volume, dnum FROM PD ORDER BY pnum ASC, volume DESC
pnum volume dnum
1 300 3
1 200 2
1 100 1
2 250 2
2 150 1
3 1000 1

Операцию TOP удобно применять после сортировки результирующего набора с помощью предложения ORDER BY .

Пример 25.
Определить номера первых двух деталей с наименьшей стоимостью.

SELECT TOP 2 dnum FROM D ORDER BY dprice ASC

Следует отметить, что если в таблице D будут две детали без указания цены, то именно их и отобразит предыдущий запрос. Поэтому при наличии NULL -значений их необходимо исключать с помощью предложения WHERE .

SELECT TOP 2 dnum FROM D WHERE dprice IS NOT NULL ORDER BY dprice ASC

Заключение

В статье было рассмотрен оператор выборки SELECT . Знание оператора SELECT является ключевым при написании любых SQL-запросов. Он позволяет производить выборку данных из таблиц и преобразовывать результаты в соответствии с нужными выражениями и функциями.

Результатом выполнения оператора SELECT является таблица, которую можно вложить в другой оператор SELECT в качестве подзапроса.

Синтаксис оператора SELECT содержит несколько предложений, из которых обязательными являются только SELECT и FROM . Остальные предложения, такие как WHERE , GROUP BY , HAVING и ORDER BY , могут использоваться по желанию для уточнения выборки данных.

dsibi / 1. Анализ данных продолжается здесь

Save dsibi/bbf6f3d445768fda730a81ea326bd04b to your computer and use it in GitHub Desktop.

yandex_python_data_analyst_5_Pandas для анализа данных

This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters

Данные исследуют в четыре стадии:
Получение данных и ознакомление с ними
Предподготовка данных
Анализ данных
Оформление результатов исследования
В этой теме мы начнём с первой стадии. В получении данных и ознакомлении с ними важную роль выполняет библиотека Pandas.
Чему вы научитесь
Познакомитесь с библиотекой Pandas и её базовыми методами: чтением файла, выводом данных на экран, получением сводной информации и запросом значений из определённых ячеек таблицы.
Сколько времени это займёт
2 часа = 6 уроков от 2 до 30 минут.
Постановка задачи
Решим задачу из реальной практики сервиса Яндекс.Музыка: исследовать предпочтения пользователей и ответить на вопрос, целесообразна ли интеграция двух сервисов — Музыки и Радио.

This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters

Мы готовим к запуску новый релиз, где соединим вместе приложения Музыка и Радио. Нам важно понимать, как пользователи отреагируют на такое новшество. Для этого мы оцениваем на небольшом количестве пользователей, насколько им понравилась возможность быстрого доступа к Радио.
Каждая компания по-разному оценивает реакцию пользователя на нововведения. Продуктовые исследования в нашем сервисе показали, что среднее время прослушивания лучше всего отражает лояльность пользователей.
Метрика удовлетворенности пользователя сервисом называется happiness. В Яндекс.Музыке метрика happiness равна среднему времени прослушивания треков. Чем выше этот показатель, тем больше доволен пользователь.
image
Есть и другие метрики, которые помогают ответить на вопрос: «Как дела у нашего продукта?»
Если метрика happiness в эксперименте повысится, команда примет решение добавить Радио всем пользователям.
Ваша задача как аналитика Яндекс.Музыки определить текущее значение метрики happiness. Вы пройдёте все стадии – от знакомства с данными до решения бизнес-задачи.

This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters

Вот так выглядят наши данные на этапе знакомства:
image
Этот хаос нужно превратить в аккуратную табличку, поддающуюся обработке. Для такой задачи подойдёт и Excel, но лучше использовать профильный инструмент — программную библиотеку Pandas.
Библиотеки – это наборы готовых методов для решения распространённых задач. Из того, что есть в Python, для операций с таблицами чаще всего применяют Pandas. Название — от сокращения panel data (англ. «панельные данные») — пришло из терминологии применяемого в экономике панельного анализа, который изучает изменение определённого признака у определённого объекта во времени (например, уровень бедности в Бразилии во второй половине 20 века). Библиотека Pandas оказалась таким универсальным инструментом, что годится для исследования любых данных, которые вообще можно собрать в таблицу.
Почему библиотека Pandas такая крутая и популярная? У неё богатейшие возможности:
Готовые методы для всяческих манипуляций с таблицами: добавления, удаления, преобразования, агрегирования данных;
Одновременная обработка данных из разных файлов;
Готовые методы для операций с пропущенными значениями, выявления и устранения проблемных данных;
Использование данных в самых разных форматах.
Кроме того, вы всегда можете обратиться к хорошо подготовленной документации и активному комьюнити.
Инструменты библиотеки становятся доступны, когда мы вызываем её командой import.
import pandas
Библиотека хранится в переменной, через которую можно вызвать её методы. В сообществе принято давать ей короткое имя pd.
import pandas as pd
Эта команда означает «импортируй библиотеку Pandas как pd».
У нас есть набор данных, который нужно превратить в таблицу. Это делается вызовом конструктора DataFrame().
Конструктор принимает два аргумента – список данных и названия столбцов, которые должны быть в таблице. Например, если информация о столицах разных стран хранится в переменной atlas:
atlas = [
['Франция','Париж'],
['Россия','Москва'],
['Китай','Пекин'],
['Мексика','Мехико'],
['Египет','Каир']
]
и нужно построить таблицу из двух столбцов country и capital,
geography = ['country', 'capital']
синтаксис вызова конструктора DataFrame() выглядит так:
world_map = pd.DataFrame(data = atlas , columns = geography)
image
Обратите внимание, что DataFrame() – это конструктор библиотеки Pandas, поэтому перед именем конструктора стоит обращение к переменной, в которой библиотека хранится – pd.DataFrame().
atlas = [
['Франция','Париж'],
['Россия','Москва'],
['Китай','Пекин'],
['Мексика','Мехико'],
['Египет','Каир'],
]
geography = ['country', 'capital']
world_map = pd.DataFrame(data = atlas , columns = geography) # таблица сохраняется в переменной с произвольно выбранным именем world_map
print(world_map) # вывод на экран
COUNTRY CAPITAL
0 Франция Париж
1 Россия Москва
2 Китай Пекин
3 Мексика Мехико
4 Египет Каир
В результате простой список пар страна-столица превратился в таблицу с индексами и именованными столбцами. Давайте создадим таблицу с данными о ваших музыкальных предпочтениях.
TASK_1_4
Получите доступ к библиотеке Pandas, используйте имя переменной pd.
SOLUTION
import pandas as pd
TASK_2_4
Создайте список music с 5 парами «имя вашего любимого исполнителя - название его песни». Пример такого списка - atlas из теоретического введения к этому уроку.
import pandas as pd
SOLUTION
import pandas as pd
music=[['Меладзе','Она была'],
['Билан','Тоска'],
['Пресняков','Стюардесса'],
['Тальков','Чистые пруды'],
['Розенбаум','Брат мой'],
]
TASK_3_4
Создайте список entries с названиями для двух столбцов — artist и track (здесь эти английские слова употребляются в значении «исполнитель» и «композиция»).
SOLUTION
import pandas as pd
music=[['Меладзе','Она была'],
['Билан','Тоска'],
['Пресняков','Стюардесса'],
['Тальков','Чистые пруды'],
['Розенбаум','Брат мой'],
]
entries=['artist','track']
TASK_4_4
Используя конструктор DataFrame(), создайте таблицу из списка ваших любимых исполнителей music и списка столбцов entries. Сохраните таблицу в переменной playlist и выведите эту сборную таблицу на экран.
SOLUTION
import pandas as pd
music=[['Меладзе','Она была'],
['Билан','Тоска'],
['Пресняков','Стюардесса'],
['Тальков','Чистые пруды'],
['Розенбаум','Брат мой'],
]
entries=['artist','track']
playlist=pd.DataFrame(data=music, columns=entries)
print (playlist)

This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters

У вас есть набор данных. Чтобы начать работу с ним, данные нужно прочитать. Давайте разберёмся, как это сделать.
Знакомому формату электронной таблицы Excel в Pandas соответствует структура данных DataFrame. Аналитики обычно называют такие объекты просто DataFrame.
В рабочей практике вы столкнётесь с тем, что данные хранят в файлах разных форматов. Из них самый распространённый – CSV (от англ. Comma-Separated Values, «значения, разделённые запятой»). Каждая строка такого файла представляет собой одну строку таблицы, где данные разделены запятыми. В первой строке собраны заголовки столбцов (если они есть).
Посмотрите, как одинаковые данные выглядят в Excel (снизу) и CSV (сверху):
image
Файлы CSV удобнее всего открывать вызовом метода read_csv() из библиотеки Pandas.
import pandas as pd
df = pd.read_csv('music_log.csv') # аргумент - путь к файлу
Обратите внимание, что содержимое файла CSV сохраняется в переменной df. Это имя, которое будет встречаться постоянно — общепринятое сокращение от DataFrame.
Теперь все данные из файла можно напечатать на экране командой print(df), но это не всегда нужно делать — не исключено, что таблица огромна и неудобна для изучения. Для знакомства с данными запрашивают несколько строк из начала или конца таблицы, вызывая специальные методы head() и tail(). По умолчанию head() возвращает первые 5 строк набора данных, а метод tail() – последние 5 строк. Когда нужно не 5, количество строк передаётся этим методам как аргумент. Например, head(10) вернёт первые 10 строк. Давайте возьмёмся за поставленную менеджером задачу и откроем файл с данными сервиса Яндекс.Музыка. Получим первый десяток строк этой обширной таблицы:
print(df.head(10))
USER_ID TOTAL PLAY ARTIST GENRE TRACK
0 BF6EA5AF 92.851388 Marina Rei pop Musica
1 FB1E568E 282.981000 Stive Morgan ambient Love Planet
2 FB1E568E 282.981000 Stive Morgan ambient Love Planet
3 EF15C7BA 8.966000 NaN dance Loving Every Minute
4 82F52E69 193.776327 Rixton pop Me And My Broken Heart
5 4166D680 3.007000 Henry Hall & His Gleneagles Hotel Band jazz Home
6 F4F5677 0.100000 NaN classicmetal NaN
7 386FE1ED 211.880000 NaN electronic Riviera
8 A5E0D927 3.161000 Andrew Paul Woodworth pop The Name of This Next Song Is Called
9 E9E8A0CA 274.390000 Pillar Point indie Dove
TASK_1_2
Прочитайте файл music_log.csv и сохраните его в переменной df. Сохраните первые 5 строк с данными из music_log.csv в переменной music_head и выведите значение переменной на экран.
SOLUTION
#
import pandas as pd
df=pd.read_csv('music_log.csv')
music_head = df[:5]
print(music_head.head())
TASK_2_2
Прочитайте файл music_log.csv и сохраните его в переменной df. Сохраните последние 10 строк с данными из music_log.csv в переменной music_tail и выведите значение переменной на экран.
SOLUTION
import pandas as pd
df=pd.read_csv('music_log.csv')
music_tail = df[-10:]
print(music_tail.tail(10))

This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters

Таблица, которую мы получили, хранится в структуре данных DataFrame. Давайте подробно разберём, из чего состоит этот объект и какие операции с ним можно выполнять.
image
DataFrame — это двумерная структура данных Pandas, где у каждого элемента есть две координаты: по строке и по столбцу.
image
Вы видите две оси, которые формируют объект DataFrame. Первая ось называется индексы, вторая ось — столбцы. По умолчанию индексация в DataFrame начинается с нуля.
Каждая строка — это одно наблюдение, запись об объекте исследования. А столбцы — признаки объектов. В нашем случае одна строка — это одно действие одного пользователя. Прослушивание такой-то композиции в исполнении такой-то группы в течение такого-то времени.
Для лучшего понимания данных полезно получить доступ к их описанию. Это либо документация со сведениями о содержании каждого столбца, либо – не самый лучший вариант – рассказ человека, который предоставил вам эту информацию. Сейчас документация выглядит так:
user_id — содержит информацию об уникальном идентификаторе пользователя;
total play — сколько секунд пользователь слушал трек;
Artist — имя исполнителя;
genre — жанр (рок, поп, электронная музыка, классическая и др.);
track — название трека.
Такое описание поможет нам ставить себе корректные задачи.
У DataFrame есть неотъемлемые свойства, значения которых можно запросить. Они называются атрибуты. Например, атрибут columns содержит информацию о названиях столбцов в наборе данных.
print(df.columns)
image
В данном случае атрибут columns вернул список названий столбцов и сообщил, что каждое из них имеет тип данных object.
Вообще типы данных могут быть разные. Для просмотра типа данных каждого столбца лучше всего использовать атрибут dtypes.
print(df.dtypes)
image
Типы данных, о которых сообщают нам атрибуты — это типы данных библиотеки Pandas. Каждому из них соответствует определённый тип данных языка Python.
Так, для int таким «двойником» в Pandas будет int64. Тип данных object используется, когда данные не подходят ни под одну категорию или соответствуют в Python типу «строка». Вот таблица соответствия типов данных Pandas и Python:
PANDAS DTYPE PYTHON TYPE ЗНАЧЕНИЕ
object str Строка
int64 int Целые числа
float64 float Вещественные числа
bool bool Логический тип данных
О размерах таблицы с данными сообщает её атрибут shape. В результате получается кортеж (неизменяемый список) из двух чисел: первое – количество строк, второе – количество столбцов.
print(df.shape)
image
В таблице 67963 строк (наблюдений) и 5 столбцов.
Кортеж – одномерная неизменяемая последовательность. Это структура данных, похожая на список, её тоже можно сохранять в переменной. Например, кортеж artist содержит имена исполнителей:
artist = ('Queen', 'Led Zeppelin', 'Scorpions')
Но если мы попытаемся изменить элемент кортежа, то Python вернёт ошибку:
artist[0] = 'Spice Girls'
---------------------------------------------------------------------------
TypeError Traceback (most recent call last)
in ()
----> 1 artist[0] = 'Spice Girls'
TypeError: 'tuple' object does not support item assignment
Можно получить информацию как обо всём кортеже, так и об отдельных его элементах:
print(artist)
('Queen', 'Led Zeppelin', 'Scorpions')
print(artist[0])
'Queen'
Кортеж нужен для хранения и чтения данных, которые лучше не изменять. Он похож на текстовый документ, защищённый от редактирования.
Всю информацию, которую предоставляют разные атрибуты DataFrame, можно получить вызовом одного-единственного метода info(). Изучив результаты, которые этот метод возвращает, аналитик выбирает тактику дальнейшей работы с таблицей.
df.info()
RangeIndex: 67963 entries, 0 to 67962
Data columns (total 5 columns):
user_id 67963 non-null object
total play 67963 non-null float64
Artist 60157 non-null object
genre 65223 non-null object
track 65368 non-null object
dtypes: float64(1), object(4)
memory usage: 2.6+ MB
image
Например, здесь в разных столбцах разное количество элементов с определёнными значениями (non-null). Следовательно, в таблице есть пропущенные значения (null). Прежде чем анализировать такие данные, их нужно обработать. Это одна из самых интересных задач аналитика, и мы поговорим о ней подробнее в следующей теме.
TASK_1_4
Прочитайте файл music_log.csv и сохраните его в переменной df. Создайте переменную shape_table и сохраните в ней размеры таблицы music_log.csv. Напечатайте на экране размер таблицы в таком виде:
Размер таблицы: .
SOLUTION
import pandas as pd
df=pd.read_csv('music_log.csv')
shape_table=df.shape
print('Размер таблицы:', shape_table)
TASK_2_4
Сколько наблюдений в наборе данных? В переменной shape_table хранится кортеж. Его первый элемент — количество наблюдений, который надо сохранить в переменной observations_table (не забывайте, что индексация элементов идёт с 0). Напечатайте на экране ответ в таком виде:
Количество наблюдений: .
SOLUTION
import pandas as pd
df = pd.read_csv('music_log.csv')
shape_table = df.shape
observations_table = shape_table [0]
print('Количество наблюдений:', observations_table)
TASK_3_4
Найдите в информации, которую вернул метод info(), число наблюдений. Вручную присвойте это число как значение переменной observations_info_table.
SOLUTION
import pandas as pd
df = pd.read_csv('music_log.csv')
df.info()
observations_info_table = 67963
TASK_4_4
Вы ещё не запутались? Давайте осмотримся и заодно вспомним условные конструкции.
Поскольку в ходе работы аналитик объявляет разные переменные и сохраняет в них добытую разными способами информацию, запутаться очень легко. Именно поэтому необходимо проверять себя и текущие результаты. Сравните полученные результаты в переменных observations_info_table и observations_table. Если значения переменных совпадают, то выведите количество наблюдений и сообщение:
"Решение верно, количество наблюдений равно", observations_table
Если значения переменных не совпадают, то выведите сообщение:
"Решение неверно, проверьте ещё раз!"
SOLUTION
import pandas as pd
df = pd.read_csv('music_log.csv')
observations_info_table = df.shape[0]
observations_table = 67963
if observations_info_table == observations_table:
print("Решение верно, количество наблюдений равно", observations_table)
else:
print("Решение неверно, проверьте ещё раз!", observations_table)

This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters

Сейчас мы, наконец, попробуем получить данные, которые понадобятся непосредственно для выполнения задачи, поставленной менеджером Яндекс.Музыки. Для этого надо уметь запрашивать информацию из определённых ячеек таблицы.
К каждой ячейке с данными в DataFrame можно обратиться по её индексу и названию столбца. Мы можем получать различные срезы данных в зависимости от того, какой запрос к DataFrame мы сформулируем. Этот процесс называется индексация. Для DataFrame она проводится разными способами.
Атрибут loc[строка, столбец] даёт доступ к элементу по строке и столбцу.
image
ВИД РЕАЛИЗАЦИЯ
Одна ячейка .loc[7, 'genre']
Один столбец .loc[:, 'genre']
Несколько столбцов .loc[:, ['genre', 'Artist']]
Несколько столбцов подряд (срез) .loc[:, 'user_id': 'genre']
Одна строка .loc[1]
Все строки, начиная с заданной .loc[1:]
Все строки до заданной .loc[:3]
Несколько строк подряд (срез) .loc[2:5]
image image
Также вы могли заметить, что запрос к атрибуту loc[] использует квадратные скобки, это напоминает списки в Python. Индексация здесь очень похожа на индексацию списков.
Вспомните, как вы играли в «Морской бой» на уроках. Для победы недостаточно просто стрелять куда попало — нужно обдумывать ситуацию на поле противника, чтобы бить в цель как можно точнее.
Посмотрите на поле для игры, оно подобно DataFrame: есть столбцы с буквенными обозначениями и ось индексов. Обратите внимание на разницу в индексах — в DataFrame они от 0 до 9.
image
import pandas as pd
data = [[0,0,0,0,0,0,0,0,0,0],
[0,'-','-','-',0,0,0,0,0,0],
[0,'-','X','-',0,0,'X','X','X','X'],
[0,'-','X','-',0,0,0,0,0,0],
[0,'-','-','-',0,0,0,0,0,0],
[0,0,'-',0,0,0,0,0,'X',0],
[0,'-','X','X',0,0,0,0,0,0],
[0,0,'-','-',0,0,0,0,0,0],
[0,0,0,0,'-','X',0,0,0,0],
[0,0,0,0,0,0,0,0,0,0]]
columns = ['А','Б','В','Г','Д','Е','Ж','З','И','К']
battle = pd.DataFrame(data = data, columns = columns)
print(battle)
А Б В Г Д Е Ж З И К
0 0 0 0 0 0 0 0 0 0 0
1 0 - - - 0 0 0 0 0 0
2 0 - X - 0 0 X X X X
3 0 - X - 0 0 0 0 0 0
4 0 - - - 0 0 0 0 0 0
5 0 0 - 0 0 0 0 0 X 0
6 0 - X X 0 0 0 0 0 0
7 0 0 - - 0 0 0 0 0 0
8 0 0 0 0 - X 0 0 0 0
9 0 0 0 0 0 0 0 0 0 0
Ячейки DataFrame со значением 0 обозначают пустую клетку. Информация в ячейке меняется на X, если игрок выстрелил и попал в корабль противника, а если не попал, то меняется на прочерк.
Давайте исследуем текущую ситуацию на поле. Просмотрим столбец 'В' и убедимся, что там уже началась атака на корабль:
print(battle.loc[:,'В'])
0 0
1 -
2 X
3 X
4 -
5 -
6 X
7 -
8 0
9 0
Name: В, dtype: object
Действительно, атака уже началась в 6-ой строке, но корабль ещё не убит, а выстрелы в ячейки сверху и снизу кончились промахом. Просмотрим 6-ую строку и узнаем расположение корабля.
print(battle.loc[6])
А 0
Б -
В X
Г X
Д 0
Е 0
Ж 0
З 0
И 0
К 0
Name: 6, dtype: object
Корабль ориентирован по горизонтали, по нему произведено два удачных выстрела, которые пришлись на столбцы В и Г. Нужно оценить ситуацию вокруг корабля. Для этого просмотрим строки с 5 по 7.
print(battle.loc[5:7])
А Б В Г Д Е Ж З И К
5 0 0 - 0 0 0 0 0 X 0
6 0 - X X 0 0 0 0 0 0
7 0 0 - - 0 0 0 0 0 0
Очевидно, что следующий выстрел нужно сделать по координате 6Д. Ура, корабль убит!
Теперь мы хотим найти второй трёхпалубный корабль. Оценим, где вероятнее всего он может располагаться. Для этого просмотрим две половины игрового поля. Срез из столбцов от А до Д даст возможность вывести левую часть таблицы:
print(battle.loc[:,'А':'Д'])
А Б В Г Д
0 0 0 0 0 0
1 0 - - - 0
2 0 - X - 0
3 0 - X - 0
4 0 - - - 0
5 0 0 - 0 0
6 0 - X X 0
7 0 0 - - 0
8 0 0 0 0 -
9 0 0 0 0 0
Такая же операция для столбцов от Е до К покажет правую часть таблицы:
print(battle.loc[:,'Е':'К'])
Е Ж З И К
0 0 0 0 0 0
1 0 0 0 0 0
2 0 X X X X
3 0 0 0 0 0
4 0 0 0 0 0
5 0 0 0 X 0
6 0 0 0 0 0
7 0 0 0 0 0
8 X 0 0 0 0
9 0 0 0 0 0
Важное замечание: когда мы используем срезы в списках, то конец среза не включается в результат. А вот атрибут .loc[] тем и выделяется, что включает и начало, и конец среза.
Например, есть список исполнителей:
artist = ['Marina Rei', 'Stive Morgan','Rixton','Henry Hall & His Gleneagles Hotel Band', 'Andrew Paul Woodworth', 'Pillar Point','Steve Campbell','David Civera','Lumipa Beats', 'Henning Wehland']
Элементы с 2 по 4 получают запросом:
print(artist[2:5])
['Rixton', 'Henry Hall & His Gleneagles Hotel Band', 'Andrew Paul Woodworth']
Последним в запросе указан индекс 5 — именно для того, чтобы в срез попал элемент с индексом 4. Запрос на получение со 2 по 4 строки в таблице будет выглядеть вот так:
print(df.loc[2:4])
USER_ID TOTAL PLAY ARTIST GENRE TRACK
2 FB1E568E 282.981000 Stive Morgan ambient Love Planet
3 EF15C7BA 8.966000 NaN dance Loving Every Minute
4 82F52E69 193.776327 Rixton pop Me And My Broken Heart
Итак, вы видели, как запрашивать один столбец, одну строку, диапазон столбцов и диапазон строк. Это самые ходовые запросы, которые вам предстоит делать как аналитику данных.
На практике чаще применяют сокращённую форму записи для индексации. Но возможности у неё ограничены. Имейте в виду, что она не всегда возвращает те же результаты, что атрибут .loc[] в его полном варианте.
ВИД РЕАЛИЗАЦИЯ СОКРАЩЁННАЯ ЗАПИСЬ
Одна ячейка .loc[7, 'genre'] -
Один столбец .loc[:, 'genre'] df['genre']
Несколько столбцов .loc[:, ['genre', 'Artist']] df [['genre', 'Artist']]
Несколько столбцов подряд (срез) .loc[:, 'user_id': 'genre'] -
Одна строка .loc[1] -
Все строки, начиная с заданной .loc[1:] df[1:]
Все строки до заданной .loc[:3] включая 3 df[:3] не включая 3
Несколько строк подряд (срез) .loc[2:5]включая 5 df[2:5] не включая 5
Неприятельский трёхпалубный корабль, скорее всего, на правой половине поля. Это можно предположить, оглядев по очереди оба среза. Аналитики называют такой метод изучения «посмотреть на данные глазами».
На глаз хорошо выбирать направление дальнейших поисков, но так не получишь точных цифр, которые можно включить в отчёт. Надо уметь подсчитать количество определённых значений, например, точных попаданий. В Pandas для этого есть метод count().
Его вызывают и приказывают сосчитать, например, количество ячеек столбца В, где были попадания. Удачный выстрел — это значение "X" в ячейке. Для столбца В таблицы battle такие ячейки отвечают логическому условию battle.loc[:,'В'] == 'X'. Поскольку в указании, какие именно значения считать, нужен логический оператор, такой доступ к значению ячейки называют логическая индексация.
image
print(battle.loc[battle.loc[:,'В'] == 'X']['В'].count()) # используем метод .count() для подсчёта записей, удовлетворяющих условию в столбце В
3
ВИД РЕАЛИЗАЦИЯ СОКРАЩЁННАЯ ЗАПИСЬ
Все строки, удовлетворяющие условию battle.loc[battle.loc[:,'В'] == 'X'] battle[battle['В'] == 'X']
Столбец, удовлетворяющий условию battle.loc[battle.loc[:,'В'] == 'X']['В'] battle[battle['В'] == 'X']['В']
Применение метода battle.loc[battle.loc[:,'В'] == 'X']['В'].count() battle[battle['В'] == 'X']['В'].count()
Конечно, писать вызов метода count() для подсчёта попаданий в «морском бою» то же, что стрелять из пушки по воробьям. Но в анализе таблиц на много тысяч строк счётный метод — мощное орудие. Попробуйте в задаче с данными Яндекс.Музыки определить, какой жанр оказался популярнее у пользователей: поп или рок?
TASK_1_4
Получите таблицу, состоящую из столбцов genre и Artist. Сохраните её в переменной genre_fight.
SOLUTION
import pandas as pd
df = pd.read_csv('music_log.csv')
genre_fight=df.loc[:,['genre', 'Artist']]
TASK_2_4
Посчитайте число прослушанных треков в жанре поп. Для этого лучше всего использовать логическое условие genre_fight['genre'] == 'pop'. Сохраните результат в переменной genre_pop. Напечатайте ответ на экране в таком виде:
Число прослушанных треков в жанре поп равно .
SOLUTION
import pandas as pd
df = pd.read_csv('music_log.csv')
genre_fight=df.loc[:,['genre', 'Artist']]
genre_pop=genre_fight.loc[genre_fight.loc[:,'genre'] == 'pop']['genre'].count()
print('Число прослушанных треков в жанре поп равно', genre_pop)
TASK_3_4
Теперь посчитайте число прослушанных треков в жанре рок. Допишите в код подсчёт, похожий на предыдущий, только с логическим условием df['genre'] == 'rock'. Сохраните результат в переменной genre_rock. Напечатайте ответ на экране в таком виде:
Число прослушанных треков в жанре поп равно . Число прослушанных треков в жанре рок равно .
SOLUTION
import pandas as pd
df = pd.read_csv('music_log.csv')
genre_fight=df.loc[:,['genre', 'Artist']]
genre_pop=genre_fight.loc[genre_fight.loc[:,'genre'] == 'pop']['genre'].count()
genre_rock=genre_fight.loc[genre_fight.loc[:,'genre'] == 'rock']['genre'].count()
print('Число прослушанных треков в жанре поп равно', genre_pop)
print('Число прослушанных треков в жанре рок равно', genre_rock)
TASK_4_4
Напишите условную конструкцию, которая сравнивает полученные значения и выводит информацию о победителе в этом бою! Если победил жанр рок, то выведите сообщение "Рок победил!", а если победил жанр поп - сообщение "Попса forever!"
Не удаляйте вывод числа прослушанных треков в жанрах поп и рок.
SOLUTION
import pandas as pd
df = pd.read_csv('music_log.csv')
genre_fight=df.loc[:,['genre', 'Artist']]
genre_pop=genre_fight.loc[genre_fight.loc[:,'genre'] == 'pop']['genre'].count()
genre_rock=genre_fight.loc[genre_fight.loc[:,'genre'] == 'rock']['genre'].count()
print('Число прослушанных треков в жанре поп равно', genre_pop)
print('Число прослушанных треков в жанре рок равно', genre_rock)
if genre_pop>genre_rock:
print('Попса forever!')
else:
print('Рок победил!')

This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters

В таблице, которую мы рассматривали весь прошлый урок, каждый столбец сам по себе — вовсе не структура данных DataFrame. Удивлены? Давайте проверим.
Таблица всё так же имеет тип DataFrame.
image
Но если мы возьмём отдельный столбец таблицы, то он представляет собой совсем иную структуру данных — Series.
image
Давайте разберёмся, что интересного в новом объекте Series.
Series — одномерная таблица, и её элементы можно получить по индексу. Каждый индекс — это номер отдельного наблюдения, и поэтому несколько различных Series вместе составляют DataFrame. В Series хранятся данные одного типа.
image
У каждой Series есть имя (Name), информация о количестве данных в столбце (Length) и тип данных, которые хранятся в ней (dtype).
print(df['Artist'])
0 Marina Rei
1 Stive Morgan
2 Stive Morgan
3 NaN
4 Rixton
5 Henry Hall & His Gleneagles Hotel Band
6 NaN
7 NaN
8 Andrew Paul Woodworth
9 Pillar Point
10 Steve Campbell
11 David Civera
12 Lumipa Beats
13 Henning Wehland
14 NaN
15 Пётр Каледин
16 ChinKong
.
67951 Julien Mier
67952 Bierstrassen Cowboys
67953 Flip Grater
67954 Alt & J
67955 TKN
67956 89ers
67957 Steel Pulse
67958 Nadine Coyle
67959 Digital Hero
67960 Red God
67961 Less Chapell
67962 NaN
Name: Artist, Length: 67963, dtype: object
Не все данные в этом столбце Artist относятся к типу object — есть и пропущенные значения NaN. О них мы подробно расскажем в теме, посвящённой «информационному мусору». Вместе с ними длина столбца равна общему числу наблюдений 67963.
Индексация в Series аналогична индексации элементов столбца в DataFrame. Давайте рассмотрим на примере. Сохраним столбец total play в переменной total_play.
total_play = df.loc[:, 'total play']
Для получения пятого по индексу элемента укажем 5 в квадратных скобках.
print(total_play[5])
3.007
Если надо получить диапазон ячеек, запросите атрибут loc с границами среза в квадратных скобках.
print(total_play.loc[5:10])
5 3.007
6 0.100
7 211.880
8 3.161
9 274.390
10 8.836
Name: total play, dtype: float64
Вот таблица различных вариантов индексации в Series с сокращённой записью.
ВИД РЕАЛИЗАЦИЯ СОКРАЩЁННАЯ ЗАПИСЬ
Один элемент total_play.loc[7] total_play[7]
Несколько элементов total_play.loc[[5, 7, 10]] total_play[[5, 7, 10]]
Несколько элементов подряд (срез) total_play.loc[5:10] включая 10 total_play[5:10] не включая 10
Все элементы, начиная с заданного total_play.loc[1:] total_play[1:]
Все элементы до заданного total_play.loc[:3] включая 3 total_play[:3] не включая 3
Для Series также возможна логическая индексация. Рассмотрим такой пример — пользователю может не понравиться песня, которая начала играть, и он нажмёт кнопку «Следующий трек». Тогда в таблице сохраняется очень малое время прослушивания — от нуля до нескольких секунд. Вы можете проверить, сколько пользователей в течение нескольких секунд — не более 10 — приняли решение пропустить песню, которая только началась.
Для решения задачи воспользуемся логическим условием total_play
image
print(total_play.loc[total_play
3 8.966000
5 3.007000
6 0.100000
8 3.161000
10 8.836000
11 0.000000
13 2.000000
14 0.000000
15 0.000000
18 0.100000
19 7.000000
24 8.109446
26 0.000000
31 3.253000
33 0.100000
34 0.000000
36 9.200000
39 3.663453
40 0.100000
41 0.100000
42 1.464589
45 0.100000
47 0.100000
49 6.000000
51 0.872000
55 8.443000
63 1.687000
64 3.773000
66 7.670000
68 0.000000
.
67878 0.919000
67880 1.090000
67885 2.000000
67886 4.040000
67887 7.680563
67888 8.578000
67894 2.065000
67896 0.000000
67897 0.100000
67899 3.676000
67900 0.000000
67905 0.000000
67906 3.682000
67907 4.179592
67908 0.000000
67912 0.000000
67916 6.947000
67917 4.000000
67918 0.000000
67923 1.753000
67927 1.954282
67929 0.100000
67932 1.439000
67946 2.101000
67949 0.100000
67950 3.496000
67951 1.318000
67953 2.502000
67956 2.000000
67962 0.100000
Name: total play, Length: 29160, dtype: float64
print(total_play.loc[total_play
29160
Порог в 10 секунд мы выбрали произвольно. Но было бы интересно установить, существует ли на самом деле какое-нибудь пороговое время — длительность воспроизведения композиции, после которого пользователь чаще всего не пропускает трек, а слушает его до конца. Анализ данных позволяет ставить такие задачи и находить на них ответы.
Давайте вернёмся к войне между роком и попсой! Вам предлагается установить, сколько композиций этих жанров слушали не более 5 секунд и определить: зависит ли быстрота принятия решения о пропуске трека от жанра или на это влияют другие причины?
TASK_1_7
Получите таблицу только с жанром rock и сохраните её в переменной rock.
SOLUTION
import pandas as pd
df = pd.read_csv('music_log.csv')
df['genre'] == 'rock'
rock = df[df['genre'] == 'rock']
TASK_2_7
Выделим время прослушивания роковых композиций в особую структуру данных. Сохраните столбец 'total play' таблицы rock в переменной rock_time.
SOLUTION
import pandas as pd
df = pd.read_csv('music_log.csv')
df['genre'] == 'rock'
rock = df[df['genre'] == 'rock']
# Выделим время прослушивания роковых композиций в особую структуру данных
# Сохраним столбец 'total play' таблицы rock в переменной rock_time.
rock_time = rock['total play']
TASK_3_7
Обратитесь к новой Series c именем rock_time и посчитайте количество треков жанра рок, пропущенных в течение 5 секунд. Логическим условием укажите rock_time
Количество пропущенных треков жанра рок равно .
SOLUTION
import pandas as pd
df = pd.read_csv('music_log.csv')
df['genre'] == 'rock'
rock = df[df['genre'] == 'rock']
# Выделим время прослушивания роковых композиций в особую структуру данных
# Сохраним столбец 'total play' таблицы rock в переменной rock_time.
rock_time = rock['total play']
rock_haters=rock_time.loc[rock_time
print('Количество пропущенных треков жанра рок равно',rock_haters)
TASK_4_7
Выберите из исходной таблицы только строки с жанром 'pop' и сохраните эту новую таблицу в переменной pop.
Вывод результата предыдущей задачи закомментируйте.
SOLUTION
import pandas as pd
df = pd.read_csv('music_log.csv')
#df['genre'] == 'rock'
#rock = df[df['genre'] == 'rock']
# Выделим время прослушивания роковых композиций в особую структуру данных
# Сохраним столбец 'total play' таблицы rock в переменной rock_time.
#rock_time = rock['total play']
#rock_haters=rock_time.loc[rock_time
#print('Количество пропущенных треков жанра рок равно',rock_haters)
df['genre'] == 'pop'
pop = df[df['genre'] == 'pop']
TASK_5_7
Теперь по аналогии с роком создайте Series, где хранятся только данные о времени воспроизведения композиций в жанре поп. Назовите его pop_time и сохраните в нём данные столбца 'total play' из таблицы pop .
SOLUTION
import pandas as pd
df = pd.read_csv('music_log.csv')
#df['genre'] == 'rock'
#rock = df[df['genre'] == 'rock']
# Выделим время прослушивания роковых композиций в особую структуру данных
# Сохраним столбец 'total play' таблицы rock в переменной rock_time.
#rock_time = rock['total play']
#rock_haters=rock_time.loc[rock_time
#print('Количество пропущенных треков жанра рок равно',rock_haters)
df['genre'] == 'pop'
pop = df[df['genre'] == 'pop']
pop_time = pop['total play']
TASK_6_7
Снова по аналогии с роком обратитесь к Series, на сей раз pop_time, чтобы посчитать количество пропущенных в течение 5 секунд треков жанра поп. Используйте условие pop_time
Количество пропущенных треков жанра поп равно .
Вывод данных о роке закомментируйте.
SOLUTION
import pandas as pd
df = pd.read_csv('music_log.csv')
#df['genre'] == 'rock'
#rock = df[df['genre'] == 'rock']
# Выделим время прослушивания роковых композиций в особую структуру данных
# Сохраним столбец 'total play' таблицы rock в переменной rock_time.
#rock_time = rock['total play']
#rock_haters=rock_time.loc[rock_time
#print('Количество пропущенных треков жанра рок равно',rock_haters)
df['genre'] == 'pop'
pop = df.loc[df['genre'] == 'pop']
pop_time = pop['total play']
pop_haters=pop_time.loc[pop_time
print('Количество пропущенных треков жанра поп равно',pop_haters)
TASK_7_7
Для обоих жанров посчитайте долю быстро пропущенных пользователями композиций в процентах. Разделите количество треков, которые пользователи пропустили — соответственно rock_haters и pop_haters — на общее количество треков жанра рок и жанра поп.
Общее количество треков жанра равно количеству наблюдений в таблицах rock и pop, т.е. значению атрибута shape[0] этих таблиц.
Результаты сохраните в переменных rock_skip и pop_skip. Выведите значения новых переменных в процентах с точностью до одного знака после запятой в такой форме:
Доля пропущенных композиций жанра рок равна: .
Доля пропущенных композиций жанра поп равна: .
Вывод результата предыдущей задачи закомментируйте.
SOLUTION
import pandas as pd
df = pd.read_csv('music_log.csv')
# Получим таблицу только с жанром rock и сохраним её в переменной rock
df['genre'] == 'rock'
rock = df[df['genre'] == 'rock']
# Выделим время прослушивания роковых композиций в особую структуру данных
# Сохраним столбец 'total play' таблицы rock в переменной rock_time.
rock_time = rock['total play']
# Обратитмся к новой Series c именем rock_time и посчитайте количество треков жанра рок, пропущенных в течение 5 секунд.
rock_haters = rock_time[rock_time
# print('Количество пропущенных треков жанра рок равно', rock_haters)
# Выберем из исходной таблицы только строки с жанром 'pop' и сохраним эту новую таблицу в переменной pop.
df['genre'] == 'pop'
pop = df[df['genre'] == 'pop']
# Теперь по аналогии с роком создайте Series, где хранятся только данные о времени воспроизведения композиций в жанре поп.
pop_time = pop['total play']
#Снова по аналогии с роком обратимся к Series, на сей раз pop_time, чтобы посчитать количество пропущенных в течение 5 секунд треков жанра поп.
pop_haters = pop_time[pop_time
# print('Количество пропущенных треков жанра поп равно', pop_haters)
# Для обоих жанров посчитайте долю быстро пропущенных пользователями композиций в процентах.
df['genre'] == 'rock'
rock = df[df['genre'] == 'rock']
rock_time = rock['total play']
rock_haters = rock_time[rock_time
df['genre'] == 'pop'
pop = df[df['genre'] == 'pop']
pop_time = pop['total play']
pop_haters = pop_time[pop_time
rock_skip=rock_haters/rock.shape[0]
print('Доля пропущенных композиций жанра рок равна: '.format(rock_skip))
pop_skip = pop_haters/pop.shape[0]
print('Доля пропущенных композиций жанра поп равна: '.format(pop_skip))

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *