Вот пример реального запроса с BETWEEN. Обратите внимание, что '2021-07-22 00:00:00' попадает в условие BETWEEN '2021-07-21' AND '2021-07-22'. Если это не учитывать, то можно с легкостью посчитать лишнюю транзакцию или еще что-то, что не подразумевалось автором запроса.

Поэтому я бы запрос к задаче №10 сформировал такой:

SELECT *

FROM Trip AS t

WHERE t.time_out >='1900-01-01 10:00'

AND t.time_out <='1900-01-01 14:00'

Разбор задачи SQL №13

#SQL_trainer7

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

Задача - Вывести имена людей, у которых есть полный тёзка среди пассажиров.

На самом деле, сообразительность нужна только для того, чтобы переформулировать постановку "Вывести имена людей, у которых есть полный тёзка среди пассажиров" в более понятную "Вывести name из таблицы Passenger, встречающиеся более 1 раза".

Для начала поймем, как построить запрос с группировкой по имени пассажиров (name) и кол-вом таких имен в таблице. Это не очень сложно:

SELECT

name, COUNT(*)

FROM Passenger

GROUP BY name

ORDER BY COUNT(*) DESC

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

Так, если в запросе выше после GROUP BY добавить условие HAVING COUNT(*)>1, мы получим почти то, что нужно в задаче:

SELECT

name, COUNT(*)

FROM Passenger

GROUP BY name

HAVING COUNT(*)>1

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

Всем привет, совместно с Мариной с канала Продакт аналитикс - продуктовым аналитиком в AliExpress - подготовили небольшой разбор BI-систем: я имел значительный опыт работы с MS PowerBI, Марина - с Tableau. Бесспорно, это самые распространенные BI-инструменты, каждый из нас расскажет о своем.


1. PowerBI (сайт PowerBI)


➕ Гибкая ценовая политика. Есть лицензии Pro, которые стоят 10$ в месяц на пользователя. То есть, если в небольшой компании, скажем, 2 аналитика и 28 человек, которым нужен доступ на просмотр отчетов - то это выйдет в 300$ в месяц (250 тысяч рублей в год). Возможности в Pro-версии, конечно, порезаны, но многим с головой хватит и этого - обновление по расписанию есть, коннекторы к десяткам источников данных на месте. Premium-версия отличается возможностью развернуть PBI на своем сервере и выделенным сервером отчетов, что нужно для компаний со строгой политикой безопасности и работы с большими объемами данных

➕ Низкий порог вхождения в создание отчетов, буквально возможность создать отчет, не написав ни одной строчки кода. Да, в PBI есть свой язык формул - DAX и язык запросов Power Query (как и в Excel), но для несложных отчетов про это можно не вспоминать. После загрузки данных в PBI для работы с ними открывается визуальный интерфейс, очень сильно напоминающий Excel, в котором табличные данные легко обрабатываются, преобразуются, добавляются новые столбцы и вычисляются новые меры. Опять же, если приводить в качестве аналогии Excel, то это как записать макрос, только проще.

➕ Много разнообразных визуализаций - от столбчатых диаграмм до диаграмм Ганта и карты мира.

➕ Неплохая мобильная версия, в т.ч. приложения для IOS/Android. Как и в Tableau, посмотреть в дороге, не упала ли вчера выручка, очень подойдет.

➕ По единичному личному опыту - неплохая русскоязычная поддержка, вопрос был решен в течение 2 дней.

Минусы, конечно же, тоже есть.

➖ Отсутствие PowerBI Desktop - основного инструмента создания отчетов и подключения к данным - на Mac OS.

➖ Ограничения на объемы. Смешные для некоторых компаний ограничения на объемы в лицензии Pro - это еще ничего (1Гб размер отчета/файла .pbix, 10Гб размер одного источника данных), т.к. в Premium на порядки больше, подробнее здесь. А вот ограничение в 150 тысяч строк на экспорт из отчета в Excel/csv - это в 2021 году недопустимо. Конечно, прекрасно, что любой пользователь опубликованного отчета может скачать сырые данные таблиц/графиков себе в Excel/csv и работать с ними самостоятельно. Но 150 тысяч строк? Excel уже давно поддерживает 2 в степени 20 строк (если калькулятора под рукой нет, то это 1 048 576 строк), я писал об этом здесь. И этот объем лицензией Premium не увеличить.

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

➖ Подключение к локальным источникам через персональный шлюз работает не очень хорошо. Обновление в оперативной памяти компьютера, на котором установлен PowerBI Desktop при использовании персонального шлюза, ограничивает объем данных, которые может вместить PBI, а также загружает оперативку компьютера под 100%.

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

2. Tableau (сайт Tableau) - опыт Марины


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

➕ Достаточно легкий и интуитивно понятный в использовании

➕ Обладает высокой производительностью, потому что тянет даже очень big data

➕ Поддерживает мобильную версию (что крайне важно, если вы, например, делаете много дашбордов с KPI, а у ваших заказчиков возникла резкая потребность что-то проверить - можете быть уверены, качество на мобильной версии не поедет)

➕ Огромное сообщество пользователей Tableau -на 99,9% вопросов вы найдете ответы в различных видео на ютубе, stackoverflow, официальных видео на сайте инструмента и т.д.)

Теперь о минусах.

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

➖ Ценообразование негибкое, то есть, отсутствует индивидуальный подход к клиентам и всем из них предлагается расширенная лицензия, хотя она может быть не особо актуальна для компании

➖ Не самый безопасный инструмент - не обеспечивает 100% защиту данных

➖ Фактическое отсутствие постпродажного обслуживания - в вашу проблему навряд ли будут вникать, скорее, просто предложат докупить какой-то пакет, и на сим поддержка закончится

➖ Вечные проблемы с версиями - что-то будет доступно в старой версии, а что-то - уже нет

➖ Несмотря на опцию постановки каких-то отчетов на расписание, тем не менее что-то придется перепинывать вручную, что не всегда удобно

➖ Чтобы сделать какие-то элементы, которые часто нужны в дашбордах, вам часто может понадобиться использование различных костылей с копированием элементов из Гугла, хитровыдуманных функций и просмотров видео с коллегами из Индии

Разбор задачи SQL №16 (с ошибкой)

#SQL_trainer8

Задача номер 16. Вывести отсортированный по количеству перелетов (по убыванию) и имени (по возрастанию) список пассажиров, совершивших хотя бы 1 полет.

В этой задачке тренажер обхитрил сам себя ) сейчас все расскажу

Как уже было упомянуто, задачка интересна тем, что в ней есть ошибка в онлайн-тренажере. Для начала - как бы я решал эту задачу. Запрос несложный, но... неправильный!

SELECT

p.name,

COUNT(DISTINCT pit.trip) AS count

FROM Pass_in_trip AS pit

JOIN Passenger AS p ON p.id = pit.passenger

GROUP BY p.id

ORDER BY count DESC, p.name ASC

В разборе задачи №5 я писал, что COUNT(DISTINCT id) лучше, чем COUNT(*), т.к. помогает в том числе избежать дублей при джоинах. В текущей задаче дублей при джойнах не наблюдается, но все равно пример показательный. Видим, что для пассажира 'Michael Caine' COUNT(DISTINCT trip) дал результат 3, а COUNT(*) = 4.

Лезем дальше в таблицы. Пропускаю этап, где я определил, что id этого пассажира Passenger.id = 14, и что мы видим по нему в таблице Pass_in_trip? Видим, что на одном рейсе 7771 он купил 2 места! Поэтому COUNT(DISTINCT trip) = 3, а COUNT(*) = 4.

Я абсолютно уверен, что тренажер, подсунув эту задачку с небольшой хитростью, обманул сам себя. Пассажир совершил 3 полета, но чтобы получить выполнение задания, нужно в запросе поставить COUNT(*), который вернет для этого пассажира число 4. Ай-яй-яй, тренажер!

Друзья, всем привет ) Давно не было постов на канале по одной простой причине - как ни странно, сейчас банально не хватает времени и оперативки в голове, чтобы уделять время каналу. С сентября я ушел из ЛитРеса, в котором провел почти 6 лет (буду впоминать это время с теплотой), и перешел в другой проект, в рамках которого в скором времени релоцируюсь на Кипр.

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

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

1. Морально подготовьтесь к началу работы на новом месте. Моей стратегической ошибкой было то, что между последним днем в ЛитРесе и началом работы в новой компании прошло 2 дня. По возможности, не делайте так, возьмите перерыв хотя бы в неделю. Потому что первые недели (а то и месяцы) на новом месте потребуют куда более интенсивной работы мозга и памяти, чем впоследствии. "Как зовут людей вокруг? Кто чем занимается? Чем я должен заниматься? Где почитать про структуру БД? Как устроен такой-то процесс?" - вопросов будет миллион, а ответы на эти вопросы надо будет запоминать. С другой стороны, отдыхать 3 месяца тоже не стоит: все мы помним еще со школьной парты, как стираются за лето все знания предыдущего года. Оптимальный срок - 1-2 недели, как будто настал очередной отпуск.

2. Не стесняйтесь задавать вопросы всем подряд. Если вы задаете много вопросов - это не значит, что вы туго соображаете. Это значит, что вы хотите во всем разобраться. Даже если вам кажется, что ваши вопросы покажутся глупыми - это вообще никак не должно вас смущать. Лучше задать глупый вопрос, получить на него ответ и навсегда для себя этот вопрос закрыть, чем умолчать, но впоследствии сделать ошибку. Временно забудьте пословицу "Промолчишь - за умного сойдешь". Обычно в мире взрослых воспитанных индивидов люди рады помогать новичкам. Конечно, везде есть грань, почувствовать которую поможет эмоциональный интеллект и здравый смысл, но поверьте, перейти эту грань довольно сложно.

3. Фиксируйте знания. Вы точно забудете 80% информации, полученной устно. Информации поначалу так много, что в голове все уместить нереально. Поэтому конспектируйте, записывайте в блокнот, записывайте видеовстречи. Если вам кто-то что-то объясняет, начинайте встречу с фразы "Я буду тезисно конспектировать, если что, попрошу помедленее". Это поможет вам не задавать одни и те же вопросы.

4. Не вы*бывайтесь. У вас за плечами докторская диссертация, 73 года опыта в похожей сфере и трехзначный IQ - в бизнесе и процессах той компании, куда вы пришли, вы все равно разбираетесь хуже своих новых коллег. Со временем вы органическим образом наберете "вес" и авторитет в компании, если вы действительно хороший специалист. Навсегда стоит забыть фразы "Я в этом разбираюсь лучше", "Вы это так считаете? Какой ужас, все неправильно", "Никто уже давно это не использует, удивлен, что у вас до сих пор Windows Vista", "А у нас вот было вот так вот в сто раз лучше".

5. Уточните максимально четко круг своих обязанностей. Не сделать то, что от вас ждали и делать много "чужой" работы - одинаково плохо. Вы должны как можно четче понимать и дать понять другим, с какими вопросами стоит обращаться к вам, а с какими - не к вам. Хороший руководитель старается этот круг более-менее четко очертить для своих подчиненных, но иногда приходится чертить самому. Главное, как и всегда, не забывать про вежливость.

Про АБ-тесты

Для многих аналитиков анализ АБ-тестов занимает значительную часть рабочей деятельности. Для проформы. АБ-тестирование - метод исследования, при котором показатели одной или нескольких тестовых групп, в которых присутсвуют изменения, сравниваются с показателями контрольной группы, в которую изменения не вносились.

Например, есть страница сайта по аренде недвижимости. Заходя на сайт, пользователи видят строку поиска по адресу/городу/району. Может быть, будет лучше, если пользователей будет встречать карта местности? Для ответов на такие вопросы проводят АБ-тесты. Под тест выделяют определенный процент аудитории (скажем, 10% всех заходящих на сайт пользователей), который разбивают поровну между контрольной и тестовой группой. Контрольная группа, заходя на сайт, видит строку с поиском. Тестовая группа видит карту с отмеченными объектами недвижимости.

Что происходит в отделе аналитики? Выбирается показатель - в данном случае уместно, допустим, рассмотреть конверсию из посещения сайта в заявку - и анализируется, насколько он различается в контрольной и тестовой группе. Вообще, показатели могут быть разные. Могут быть временные ряды, могут быть накопительные/ненакопительные значения показателей на пользователя, но подход к анализу примерно одинаков.

1. Расчет необходимого кол-ва посетителей в группах. На самом деле, нарушая хронологический порядок событий в посте, этот этап нужно делать перед запуском теста. Он необходим для того, чтобы избежать так называемой проблемы подглядывания, когда при недостаточном наборе можно ошибочно принять различия между группами за значимые, хотя значимость на самом деле "мигает" от наблюдения к наблюдению. Если набор рассчитан заранее, то нужно единожды посчитать значимость тогда, когда кол-во пользователей в группах достигло заданного значения, а после этого набралось достаточно значений для расчета. Не углубляясь в математику, расчет необходимого кол-ва участников теста можно произвести в одном из онлайн-калькуляторов, например, вот этом.

2. Определение статистической значимости различий. Необходимое кол-во пользователей в тест набрано, тест шел 2 недели, допустим, у нас есть почасовые данные по конверсиям из посещения в заявку, всего 336 значений конверсий в каждой группе. Далее применяя статистические критерии, можно определить, случайные или нет различия в выбранном показателе между группами. Данный пост скорее ознакомительный и направлен на то, чтобы обрисовать общую логику без погружения в математику, но немного статистики тут все равно понадобится, потому что возникает вопрос: а как считать?
2.1. Если показатель имеет нормальное распределение, применяем t-тест Стьюдента, рассчитываем p-value (можно интерепретировать как вероятность того, что наблюдаемые нами различия случайны). Если p-value оказывается меньше уровня значимости (общепринято 0,05), делаем вывод о том, что различия между группами статистически значимы. Тут стоит, конечно, отметить, что нормальность распределения самого показателя - необязательное требование. Нормальными (согласно ЦПТ) должны быть распределены выборочные средние из наших данных. Но сейчас пока обойдемся такой вот грубой классификацией
2.2. Если распределение показателя не является нормальным, то тут можно:
а) не анализировать ряд, а свести все к анализу четырехпольных таблиц с помощью критерия Хи-квадрат. Из 10 000 пользователей в группе А конверсию совершили 500 человек, а из 10 500 пользователей в группе Б - 550 человек. Судя по вот таком калькулятору, такие различия не являютя значимыми.
б) использовать bootstrap
в) использовать непараметрические критерии, один из самых распространенных - критерий Манна-Уитни.

Если хотите углубиться подробнее в суть и реализацию t-теста, bootstrap и Манна-Уитни, советую посмотреть вебинар от Анатолия Карпова, ни убавить ни прибавить, там все отлично расписано )