Изучение и анализ базы данных MySQL с помощью SQL

Ознакомьтесь с этими SQL-запросами, чтобы изучить и проанализировать набор данных в базе данных MySQL.

Чтобы иметь возможность извлекать информацию из базы данных MySQL, вы должны знать несколько запросов SQL для анализа данных и расчета некоторой сводной статистики.

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

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

Я использовал MySQL Workbench для этого проекта, чтобы упростить написание больших запросов. Я могу порекомендовать использовать графический интерфейс вместо клиента CLI для исследования данных, поскольку вы можете сохранять несколько запросов в файле и выполнять их независимо.

1. Вычисление сводной статистики для числовых столбцов

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

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

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

Вот краткий обзор сводной статистики, которую я использовал для профилирования числовых столбцов:

• Количество. Общее количество всех значений в столбце.
• Среднее/среднее. Среднее значение столбца, рассчитанное путем деления суммы всех значений на счетчик.
• Медиана: среднее значение в столбце.
• Mode: наиболее часто встречающееся значение в столбце.
• Максимальное/минимальное: максимальное и минимальное значения в столбце, формирующие диапазон столбца.
• Стандартное отклонение. Изменчивость точек данных в столбце вокруг среднего значения.
• 90-й и 99-й процентили: пороговые значения превышают 90% или 99% всех точек данных в столбце.

В качестве примера мы собираемся вычислить эти значения для столбца valuation_b_usd набора данных запуска единорога. В этом столбце сохраняется оценка единорогов в миллиардах долларов США в виде двойного типа данных.

Чтобы подсчитать все значения, мы можем использовать агрегатную функцию COUNT(). С помощью ключевого слова DISTINCT мы можем подсчитывать только уникальные значения.

-- Count of all values
SELECT COUNT(valuation_b_usd) AS 'Count of Valuation' FROM startups;
​
-- Count of unique values
SELECT COUNT(DISTINCT valuation_b_usd) AS 'Count of Valuation' FROM startups;

С помощью агрегатных функций СУММ() и СРЕДН() мы также можем легко вычислить сумму всех значений и среднее значение. Встроенная функция STDDEV() также позволяет легко вычислить стандартное отклонение для столбца.

-- Sum of all values
SELECT SUM(valuation_b_usd) AS 'Total Valuation [B$]' FROM startups;
​
-- Average/Mean
SELECT AVG(valuation_b_usd) AS 'Average Valuation [B$]' FROM startups;
​
-- Standard Deviation
SELECT STDDEV(valuation_b_usd) AS 'Standard Deviation [B$]' FROM startups;

Агрегатные функции MAX() и MIN() могут выбирать максимальное и минимальное значение столбца. Чтобы получить связанные записи, я использовал подзапрос в предложении WHERE, так что извлекается только запись, оценка которой является максимальной.

-- Maximum value and minimum value
SELECT MAX(valuation_b_usd) AS 'Maximum Valuation [B$]', MIN(valuation_b_usd) AS 'Minimum Valuation [B$]' FROM startups;
​
-- Unicorn with highest valuation
SELECT company_name AS 'Company', valuation_b_usd AS 'Valuation [B$]', country AS 'Country' FROM startups WHERE valuation_b_usd = (SELECT MAX(valuation_b_usd) FROM startups);

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

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

-- Mode
SELECT COUNT(valuation_b_usd) AS 'Mode' FROM startups GROUP BY valuation_b_usd ORDER BY valuation_b_usd DESC LIMIT 1;

Для медианы разделите общее количество значений в столбце на 2 и используйте результат как СМЕЩЕНИЕ. Это даст вам среднее значение столбца. В этом случае общее количество значений равно 1074. Деление на 2 дает 537 в качестве СМЕЩЕНИЯ.

Тот же принцип можно использовать для расчета процентилей и квартилей. Чтобы получить 90-й процентиль, вы можете умножить количество значений на 0,9, вычесть произведение из количества и использовать результат как СМЕЩЕНИЕ. В этом случае умножение 1074 на 0,9 дает 966,6. Если вычесть из 1074 и округлить в меньшую сторону, получится 107.

Чтобы получить 99-й процентиль, умножьте количество значений на 0,99, вычтите произведение из количества и используйте результат как СМЕЩЕНИЕ. Если вы хотите рассчитать квартили, вам нужно умножить количество значений на 0,25 и 0,75. Все эти показатели могут дать вам приблизительную оценку распределения значений.

-- Median
SELECT valuation_b_usd AS 'Median' FROM startups ORDER BY valuation_b_usd DESC LIMIT 1 OFFSET 537;
​
-- 90th Percentile
SELECT valuation_b_usd AS '90th Percentile' FROM startups ORDER BY valuation_b_usd DESC LIMIT 1 OFFSET 107;
​
-- 99th Percentile
SELECT valuation_b_usd AS '99th Percentile' FROM startups ORDER BY valuation_b_usd DESC LIMIT 1 OFFSET 11;

2. Создание гистограммы с операторами SQL CASE

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

С помощью оператора CASE мы собираемся создать новый столбец, определяющий ячейки значений. Это также то, как вы можете легко установить бины нестандартного размера с различным размером бина, если ваше распределение сильно искажено. Обычно вы должны выбрать один и тот же размер бина и создать больший бин для выбросов в конце диапазона, но для упрощения я объединил несколько бинов для гистограммы SQL.

С помощью функции RPAD() мы можем генерировать полосы, состоящие из символа *. Функция RPAD() добавляет определенный символ в правую часть строки, пока она не достигнет заданной длины. Синтаксис функции: RPAD(строка, длина, символ заполнения). Чтобы получить столбцы, мы добавляем * до тех пор, пока длина не достигнет частоты значений, деленной на 10. Мы делим на 10, чтобы масштабировать столбцы, чтобы они не становились слишком длинными.

И столбцы, и частота группируются по ячейкам с помощью оператора GROUP BY.

SELECT
CASE
    WHEN valuation_b_usd = 1 THEN '01'
    WHEN valuation_b_usd = 2 THEN '02'
    WHEN valuation_b_usd = 3 THEN '03'
    WHEN valuation_b_usd = 4 THEN '04'
    WHEN valuation_b_usd = 5 THEN '05'
    WHEN valuation_b_usd = 6 THEN '06'
    WHEN valuation_b_usd = 7 THEN '07'
    WHEN valuation_b_usd = 8 THEN '08'
    WHEN valuation_b_usd = 9 THEN '09'
    WHEN valuation_b_usd BETWEEN 10 AND 11 THEN '10-11'
    WHEN valuation_b_usd BETWEEN 12 AND 14 THEN '12-14'
    WHEN valuation_b_usd BETWEEN 15 AND 19 THEN '15-19'
    WHEN valuation_b_usd BETWEEN 20 AND 29 THEN '20-29'
    WHEN valuation_b_usd BETWEEN 30 AND 50 THEN '30-50'
    WHEN valuation_b_usd > 50 THEN '50-180'
END AS valuation_b_usd_c,
COUNT(valuation_b_usd) AS 'Frequency',
RPAD('', COUNT(*)/10, '*') AS 'Bars'
FROM startups GROUP BY valuation_b_usd_c ORDER BY valuation_b_usd_c;

Результат выглядит так в MySQL Workbench. Как видите, распределение оценки не следует распределению Гаусса. Вместо этого он сильно смещен вправо, и большинство оценок приходится на нижнюю часть диапазона. Между тем, оценки выше 50 миллиардов долларов США кажутся выбросами.

3. Выявление выбросов с помощью метода Z-оценки

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

Одним из наиболее распространенных методов является метод Z-оценки. Показатель Z — это количество стандартных отклонений, на которое точка данных отклоняется от среднего значения. Например, оценка Z, равная 2, будет означать, что рассматриваемая точка данных ровно в два раза больше стандартного отклонения, чем среднее значение. Здесь выброс определяется как точка данных с показателем Z больше 3 или меньше -3, что означает, что любая точка данных, которая дает более 3 стандартных отклонений от среднего значения, классифицируется как выброс.

Вы также можете использовать метод IQR. IQR представляет собой межквартильный диапазон и рассчитывается путем вычитания первого квартиля (Q1) из третьего квартиля (Q3). Здесь выброс классифицируется как точка данных, которая находится более чем на 1,5 * IQR выше Q3 или ниже Q1. Этот метод более устойчив к асимметричным распределениям и серьезным выбросам, но, поскольку в SQL отсутствуют встроенные функции для расчета этих показателей, вместо этого мы собираемся использовать метод Z-оценки.

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

-- Outliers with Z score method
SELECT company_name, valuation_b_usd, country FROM startups WHERE valuation_b_usd > (SELECT STDDEV(valuation_b_usd)*3 + AVG(valuation_b_usd) FROM startups) ORDER BY valuation_b_usd DESC;

4. Расчет коэффициента корреляции

Мы также можем рассчитать коэффициент корреляции между двумя числовыми столбцами. Этот показатель помогает выявить потенциальные взаимосвязи между переменными. Коэффициент корреляции может варьироваться от 0 до 1. Коэффициент 0–0,3 указывает на отсутствие корреляции, тогда как коэффициент 0,3–0,7 указывает на умеренную корреляцию, а коэффициент от 0,7 до 1 указывает на сильную корреляцию.

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

Для расчета коэффициента корреляции я просто перевел формулу в SQL-запрос. Результат показывает коэффициент корреляции 0,6, что указывает на умеренную корреляцию между оценкой и финансированием стартапа-единорога.

-- Correlation coefficient between valuation and funding
SELECT
(SUM((valuation_b_usd - (SELECT AVG(valuation_b_usd) FROM startups)) * (funding_b_usd - (SELECT AVG(funding_b_usd) from startups))) / (COUNT(*) - 1)) / ((SELECT STDDEV(valuation_b_usd) FROM startups) * (SELECT STDDEV(funding_b_usd) FROM startups)) AS correlation_coefficient
FROM startups WHERE funding_b_usd IS NOT NULL;

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

Это можно показать, выполнив один и тот же запрос, удалив выбросы как из столбцов оценки, так и из столбцов финансирования. Теперь коэффициент составляет всего 0,08, что означает отсутствие корреляции. Это означает, что рассчитанная корреляция действительно сильно зависит от выбросов и, скорее всего, не является репрезентативной для всего набора данных.

-- Correlation coefficient while removing outliers
SELECT
(SUM((valuation_b_usd - (SELECT AVG(valuation_b_usd) FROM startups)) * (funding_b_usd - (SELECT AVG(funding_b_usd) from startups))) / (COUNT(*) - 1)) / ((SELECT STDDEV(valuation_b_usd) FROM startups) * (SELECT STDDEV(funding_b_usd) FROM startups)) AS correlation_coefficient
FROM startups WHERE funding_b_usd IS NOT NULL
AND company_name NOT IN (SELECT company_name FROM startups WHERE valuation_b_usd > (SELECT STDDEV(valuation_b_usd)*3 + AVG(valuation_b_usd) FROM startups))
AND company_name NOT IN (SELECT company_name FROM startups WHERE funding_b_usd > (SELECT STDDEV(funding_b_usd)*3 + AVG(funding_b_usd) FROM startups));

5. Получение нового столбца из двух других столбцов

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

Я использовал оператор ALTER TABLE, чтобы добавить новый столбец с типом данных double. После этого я заполнил столбец данными, используя оператор UPDATE, разделив оценку на финансирование для каждой записи.

-- Create derived column for ROI
ALTER TABLE startups ADD roi double;
UPDATE startups SET roi = valuation_b_usd / funding_b_usd;

Для масштабного времени я сначала создал новый столбец, извлекая год из столбца date_joined. Это можно реализовать с помощью функции YEAR(). Результирующий столбец содержит год, когда стартап превратился в единорога, как тип данных year.

После этого я создал еще один столбец, вычисляющий разницу между годом, когда стартап стал единорогом, и годом его основания. Я использовал функцию CAST() для преобразования лет в целое число во время вычисления, поскольку выполнение арифметических операций над типами данных года не разрешено в MySQL.

-- Create derived column for year joined
ALTER TABLE startups ADD year_joined year;
UPDATE startups SET year_joined = YEAR(date_joined);
​
-- Create derived column for scale time
ALTER TABLE startups ADD scale_time double;
UPDATE startups SET scale_time = CAST(year_joined AS SIGNED INTEGER) - CAST(year_founded AS SIGNED INTEGER);

6. Изучение категориальных столбцов с помощью COUNT и GROUP BY

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

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

-- Unicorns and percentages per continent
SELECT continent AS 'Continent', COUNT(*) AS 'Count', (COUNT(*) * 100 / (SELECT COUNT(*) FROM startups)) AS 'Percent' FROM startups GROUP BY continent ORDER BY COUNT(*) DESC;

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

Приведенный ниже запрос вычисляет среднюю стоимость единорогов для каждого континента. Функция ОКРУГЛ() обрезает десятичные разряды вывода до 2, используя синтаксис ОКРУГЛ(значение, десятичные_знаки).

-- Average valuation per continent
SELECT continent AS 'Continent', ROUND(AVG(valuation_b_usd),2) AS 'Average Valuation [B$]' FROM startups GROUP BY continent;

7. Извлечение информации из строковых столбцов с помощью подстановочных знаков

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

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

Приведенный ниже запрос подсчитывает количество единорогов, в которые инвестировала инвестиционная фирма Andreessen Horowitz. Подстановочный знак указывается с помощью LIKE в предложении WHERE. Символ % означает, что шаблон может соответствовать любому количеству символов до или после указанной строки.

-- Count of unicorns Andreessen Horowitz invested in
SELECT COUNT(*) FROM startups WHERE investors LIKE '%Andreessen Horowitz%';
​
-- Count of unicorns Y Combinator invested in
SELECT COUNT(*) FROM startups WHERE investors LIKE '%Y Combinator%';

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