Data analysis - DA SQL

Функции обработки NULL в SQL Список всех нужных функций, которые позволяют работать с NULL в запросах: ▶️COALESCE(v1, v2, ..., vn) - наиболее универсальная функция в SQL. По порядку проверяет все заданные столбцы и возвращает первое попавшееся значение, которое не является NULL. Если в итоге все значения NULL - возвращает NULL. Работает в MySQL и в Oracle. Аргументов может быть сколько угодно (от 2 и больше), в отличие от большинства других функций в этом списке, где строго 1, 2 или 3 аргумента. Пример: COALESCE(phone_mobile, phone_home, phone_work, 'нет телефона') - вернет первый заполненный номер телефона из трех вариантов, а если все три пустые - вернет текст-заглушку. ▶️ISNULL(v1) - в MySQL проверяет, является ли поле NULL. Если да - возвращает 1, если нет - 0. Важно не путать с одноименной функцией ISNULL в SQL Server - там она работает совсем иначе, принимает 2 аргумента и по смыслу ближе к IFNULL/NVL. В MySQL же ISNULL(v1) - это просто проверка, а не замена значения. ▶️IFNULL(v1, v2) - для MySQL возвращает v1, если поле не NULL, и v2 - если поле NULL. Например, IFNULL(discount, 0) - если скидка не указана, в результате будет 0 вместо NULL, что удобно для дальнейших математических расчетов (сложение, умножение), поскольку любая операция с NULL дает в результате NULL. ▶️NVL(v1, v2) - работает точно так же, как и IFNULL, только в Oracle. ▶️NULLIF(v1, v2) - если v1 = v2, возвращает NULL, если не равно, то возвращает v1. Работает в MySQL и в Oracle. Часто применяется, чтобы избежать деления на ноль. Например, если записать revenue / NULLIF(quantity, 0), то если quantity = 0, функция вернет NULL вместо ошибки деления на ноль, и вся операция деления корректно завершится с результатом NULL, а не прервется. ▶️NVL2(v1, v2, v3) - "расширенная версия" NVL, тоже для Oracle. Выполняет тройную логику: если v1 NOT NULL, возвращает v2, если IS NULL - v3. Удобна, когда нужно вернуть не само значение поля, а какой-то текст или флаг в зависимости от того, заполнено оно или нет. Например, NVL2(discount, 'скидка есть', 'скидки нет') - вернет текстовую метку в зависимости от того, заполнено поле discount или нет, без необходимости писать CASE WHEN. ▶️LNNVL(condition) - специфичная функция Oracle: возвращает TRUE, если условие ложно или неопределённо (с NULL). Удобна в WHERE вместо громоздких конструкций. Например, если написать LNNVL(bonus = 500), то функция вернет TRUE, если bonus != 500 или если bonus - NULL. И вернет FALSE, если bonus = 500.

Есть ли смысл искать жильё в аренду напрямую от собственника? На примере выгрузки из Циана по московскому рынку аренды жилья за 2026 год Moscow Rent Cian With Images, содержащей около 25 тысяч объявлений, выясним, действительно ли цена аренды от агента отличается от цены аренды от собственника, и если да, то на сколько? Причем разницу будем искать не в виде единоразового первоначального платежа, а в виде стоимости самой аренды на весь срок. 🟠Сравнение будет проводиться с учетом следующих факторов: — Район — Расстояние до метро (мин пешком) — Тип ремонта — Год постройки дома — Площадь квартиры — Число комнат — Этаж и этажей всего 🟠Анализировать будем с помощью метода ближайших соседей: для каждой квартиры будет подбираться максимально похожий аналог напрямую из датасета с учетом вышеперечисленных факторов. Таким образом будет сформировано множество пар практически полностью аналогичных квартир, но одна из которых - от собственника, а другая - от агента. Затем сравним стоимость аренды внутри каждой пары и проверим это всё на статистическую значимость. 🟠Перед анализом, как и обычно, нужно очистить датасет от пропусков, т.к. метод ближайших соседей с ними не работает. Поэтому можно выделить основные факторы модели: сдающий (собственник/агент), цена, район, площадь и кол-во комнат. Здесь важно удалить все строки с пропусками по этим факторам. И в итоге получится выборка почти из 21 000 объявлений, из которых от собственника - чуть больше 5500. Значит, потенциально может быть выборка примерно из 5500 пар собственник-агент с контролем факторов. Пропуски по второстепенным факторам (тем, которые остались), можно заполнить медианными значениями по каждому району. 🟠Затем кодируем признаки и применяем NearestNeighbors() из библиотеки sklearn. В качестве параметров - евклидово расстояние между факторами и поиск только лишь по 1 ближайшему соседу на каждую квартиру. Ожидаемо, что не все пары получились идеальными. 10% пар нужно было отсечь из-за того, что общее расстояние между ними было слишком велико. Из оставшихся пар еще 24% нужно было отсечь из-за того, что сравнивались несопоставимые факторы при других равных факторах (например, площадь 132 кв. м. сравнивалась с 43 кв. м.). Итого получилось около 3800 вменяемых пар. ❗️Вывод: при анализе 3800 пар практически полностью сопоставимых между собой по перечисленным факторам квартир, обнаружилось, что по медиане стоимость аренды от собственника на 5,5% дороже стоимости аренды от агента. В рублях медианная разница вышла около 4 000 руб/мес. При этом доля пар, где у собственника цена дороже - около 57%. У агента дороже в 33% случаев, а одинаковая цена в 10% пар. Все результаты стат значимы. 🟠А более подробно полный цикл анализа реальных данных можно научиться проводить в моем курсе на Stepik.

Как читать вывод по множественной регрессии? Если вы решите запустить OLS Regression из библиотеки statmodels и вывести результат через summary(), то столкнетесь с прикрепленной выше таблицей. Самое важное, что нужно знать: ▶️Строка Intercept - базовое значение Y, когда факторы находятся в базовой категории ▶️Столбец coef - влияние фактора на Intercept. ▶️Столбцы std err, t и P>|t| - вспомогательные, они все про стат значимость. Её лучше сразу вывести отдельно через Pval. ▶️Столбцы [0.025 0.975] - доверительный интервал, разброс значений ▶️R-squared - R-квадрат, показывает, на сколько % наблюдаемое явление можно объяснить самой моделью ▶️Durbin-Watson - Автокорреляция остатков. Проверяет, коррелируют ли ошибки между собой. Желательно, чтобы значение было ближе к 2, но не меньше 0 и не больше 4. ▶️Cond. No. - мультиколлинеарность, показывает, коррелируют ли факторы между собой. Не должен быть слишком большим (несколько сотен). Но для большого кол-ва факторов иногда допустимо разумное превышение.

Действительно ли удаленка снижает зарплату? Это экспериментальный формат. Я буду брать реальные данные из открытых источников, формулировать насущную гипотезу и доказывать её с помощью разных аналитических методов Данные взяты с kaggle - Stack Overflow Annual Developer Survey 2025. Датасет представляет собой крупнейший опрос айтишников за 2025 год из разных стран мира на тему профессии. Включает в себя более 50 тысяч опрошенных человек и детализацию более чем по 170 параметрам. В датасете представлены профессии разработчиков, аналитиков, инженеров, менеджеров, администраторов. 🟠Стоит держать в голове, что: ▶️Опрос - это прежде всего срез данных по тем людям, которые согласились сами пройти опрос и прошли его. Информация по тем, кто в опросе НЕ участвовал, нам закономерно неизвестна. ▶️Ключевые поля в датасете - это ConvertedCompYearly (зарплата в USD) и RemoteWork (удаленка/гибрид/офис). Поэтому анализируемый датасет будет очищен от тех строк, где хоть 1 ключевое поле имеет значение NaN. ▶️Помимо ключевых полей будут учитываться поля: — MainBranch - основной вид деятельности — Age - возраст — Employment - тип занятости — EdLevel - уровень образования — WorkExp - опыт работы — DevType - специализация профессии — OrgSize - размер организации — Country - страна ▶️Также датасет будет еще дополнительно ограничен по параметрам: — MainBranch - мы будем учитывать только тех, у кого профессия разработчика - основной вид деятельности (не хобби и не подработка); — Employment - учитываются только люди, работающие по ТК или по B2B (без фрилансеров, самозанятых и пр) — Country - страна может очень сильно влиять на разброс зарплат, поэтому, чтобы чересчур не усложнять модель, возьмем только США. 🟠Итак, приступим. После применения всех фильтров (в т.ч. с NaN) получаем выборку 3880 человек. Но это еще не всё. Нужно также очистить датасет от аномалий по зарплате - будем брать только 1-99 перцентили, чтобы не было экстремальных нерепрезентативных перекосов. Получим диапазон зарплат от 9,460 USD до 651,200 USD в год и выборку около 3800 человек. И еще немного правок - сделаем группы побольше с помощью мэппингов. 🟠Основной план - построить множественную регрессию по log(ConvertedCompYearly) с контролем RemoteWork и остальных перечисленных параметров. Зарплата берётся в логарифме, потому что распределение доходов сильно скошено вправо, и лог-трансформация делает эффекты интерпретируемыми в процентах, а не в долларах. Посчитаем данные по мн. регрессии (OLS) и выведем summary с помощью библиотеки statmodels. Базовые значения - офисный формат работы, средний опыт, средний размер компании, профессия - разработчик. Остальные - по умолчанию. С базовыми значениями будет сравниваться эффект. ❗️Вывод: При прочих равных с учетом возраста, опыта, специализации, образования, размера компании - удаленка не снижает зп. Напротив - она её даже повышает в среднем на 15.2% относительно офиса. А гибрид повышает зп относительно офиса в среднем на 6.5%. Все результаты получились стат значимыми. Важно: это статистическая связь при контроле на несколько факторов, а не доказательство причинности. Не исключено, что часть эффекта объясняется неучтёнными переменными - например, регионами внутри США, конкретной компанией или индивидуальными переговорными навыками, которые не входили в модель. Также стоит учесть, что это всё - средний эффект. Далеко не факт, что, условно, для джуна и для сеньора удаленка будет давать по +15% к зп.

Регрессия к среднему как ловушка в аналитике Статистическое правило, которое часто неверно понимается: если какой-то показатель был слишком хорошим или слишком плохим, то в следующем периоде он станет ближе к норме. И это ухудшение или улучшение происходит само по себе, вне зависимости от того, вмешивается ли кто-либо или нет. И из-за этого довольно легко подумать, что именно то самое вмешательство вернуло показатель в норму, хотя это не так (ну или не совсем так). 🟠Как же бороться с таким эффектом? Как оценить эффект именно от вмешательства, а не от статистической закономерности? Для этого вводятся контрольные группы. Контрольная группа - это часть выборки, к которой не применяется вмешательство. Она нужна именно для того, чтобы отделить реальный эффект от естественной регрессии к среднему. Логика следующая: если и тестовая группа, и контрольная синхронно сдвигаются к норме, это значит, что дело не во вмешательстве. А вот если тестовая группа улучшилась заметно сильнее контрольной, то это уже скорее всего реальный эффект. 🟠Но как узнать, насколько сильно должна измениться тестовая группа по отношению к контрольной, чтобы сказать, что это - с огромной вероятностью эффект вмешательства? Где именно проходит эта граница? Для этого нужно посчитать статистическую значимость. Суть в следующем. Например, у нас есть n магазинов. Из этих n магазинов выбираем 10% самых худших по выручке с целью повысить их показатели. Эти 10% магазинов случайным образом делим на 2 группы: по 5% на тестовую и контрольную группы. Затем берем среднее значение внутри каждой группы ДО и ПОСЛЕ эксперимента. Считаем прирост и стандартное отклонение для этого прироста. Получившиеся результаты вставляем в формулу по расчету t-статистики, с помощью которой уже находим p-value и определяем, действительно ли меры были эффективны или это всё же результат статистической закономерности.