Comments 41
Из не то чтобы сильно каверзных, но постоянно встречающихся в интервью на SQL вещей стоит помнить over (PARTITION BY ORDER BY). По сути дела, всё практически так же просто, как с груп бай, но спрашивают практически всегда, и типа ответишь - считаешься крутым чуваком почему-то. Также каждый второй раз спрашивают разницу между ROW_NUMBER, RANK и DENSE_RANK.
Поэтому, если разработчик может ответить на эти каверзные вопросы, значит ему приходилось решать какие-то нетривиальные задачи. Причём довольно часто (т.к. логика оконных функций всё-таки иногда своеобразная и если периодически мозг ими не тренировать, то навскидку (без базы под руками) будет трудно вспомнить некоторые особенности)
Можно придраться к решению #5, там два решения дают немного разный ответ, первый требует строго меньше 300, а between включает обе границы.
В решении #13 совсем пустые склады не попадут в результат, а надо бы их выдать с нулём сладостей. Используйте outer join и например coalesce.
Как в скрипте создания таблиц задать инкремент?
Скрипт наполнение данными таблиц валится с ошибкой индекса
Покажите скрин как запускаете и ошибку, перепроверила скрипт у себя на БД, отработал
Как в скрипте создания таблиц задать инкремент?
CREATE TABLE public.sweets_types
(
id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 ),
name character varying NOT NULL,
PRIMARY KEY (id)
);Конструкция GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 ) позволяет задать генерацию значения поля id от 1 с увеличением +1. Добавила пояснение в статью
Без этой строки «GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 )»
скрипт наполнения не будет работать.
если речь про Postgres то можно так же дополнительно использовать механизм sequence. Важно помнить если их "дёргать" вручную то они работают вне транзакции, поэтому откат транзакции "дыры" в нумерации не уберёт.
Не хочется выглядеть ворчуном, но структура статьи напоминает содержимое дамской сумочки. Смешались в кучу кони, люди, терминология, архитектура, диалекты SQL, ненужные программы. Структуру таблицы почему-то изучаем после изучения структуры БД, а не наоборот. Аббревиатуры все перепутаны, и снабжены ремаркой "но мы это изучать не будем". AND и OR почему-то в одной куче с BETWEEN, а COUNT — наоборот отдельно от других агрегирующих функций. FK — вторичный ключ, видимо сокращение от "fecondary".
Зачем-то скачивать и устанавливать на компьютер какие-то программы, если в интернете полно онлайн инструментов для работы с БД. Достаточно было дать одну ссылку и сократить статью в два раза, избежав кучи совершенно ненужных шагов.
Но главное — засунуть SQL в DML — это надо было умудриться. То есть собственно тема, заявленная в заголовке статьи — изучение SQL — раскрыта в спойлере, который ещё и некорректно назван.
Если уж заявляемся на SQL, то вся эта премудрость с DISTINCT, WHERE и GROUP BY должна быть не свалена в кучу под спойлером "DML", а составлять основное содержание статьи и снабжена осмысленными комментариями и примерами. А не SELECT AVG(id) FROM public.sweets; и "ну про джойны можно прочитать по ссылке".
В то время как DML запросы как раз можно было бы убрать под спойлер.
Спасибо за ваше мнение.
Выбран вариант скачать и поставить программу, потому что:
1) есть возможность практиковаться без интернета. (есть населённые пункты, где интернет слабый),
2) на работе системному аналитику придётся работать именно с pgadmin, если БД postgre, поэтому удобно если ты уже видел интерфейс
Статья появилась, потому что похожей статьи с примерами я не находила для повторения sql к собеседованию. В тех, что встречала всё пишут шаблонными table_name, colum_name.
FK - foreign key, внешний ключ. Иногда называют, как вторичный ключ.
Согласна с вами, пересмотрю структуру DML спойлер и уберу оттуда агрегатные функции агрегатные функции. Не удачное название спойлера получилось.
На постгресе и pgadmin базы данных и средства работы с ними не заканчиваются. Что делает эту часть статьи слишком узкопециализированной. Становится непонятно, статья про SQL или про работу с pgadmin. Эта информация явно является второстепенной, и как раз её и можно было заутсорсить, просто дав ссылку. В отличие от джойнов, которые напрямую относятся к теме статьи, и являются краеугольным камнем SQL. От системного аналитика почему-то ожидаешь системности мышления, а не вот это вот "может не быть интернета, поэтому для начала работы скачайте два дистрибутива на пол-гигабайта".
Называть внешний ключ вторичным неграмотно. Внешний ключ — constraint, ссылка на внешнюю таблицу. Вторичный ключ — это дополнительный индекс на той же таблице, он вообще не является constraint.
Про DML я и сам ошибся, выборки тоже могут в него включаться. Но в любом случае стоит разделить запросы на выборку и на модификацию данных. Особенно учитывая, что задания даются исключительно на выборку. И не бежать с ними галопом по европам, а сделать основным контентом статьи, группируя по основным операторам. То есть LIKE должен быть в разделе, посвящённом WHERE, а ORDER BY не должен вклиниваться между группировкой и агрегатными функциями. Ну просто я именно так себе представляю структуру статьи, которая озаглавлена "Шпаргалка по SQL". А сейчас из неё вообще неясно, как все эти операторы относятся друг к другу и в каких сочетаниях могут применяться. Сейчас это просто набор слов.
В статье даже не упоминается базовая схема SELECT запроса: SELECT что FROM откуда JOIN с другой таблицей WHERE условия GROUP BY поля HAVING условия ORDER BY сортировка LIMIT сколько OFFSET откуда. То есть нет системы, по которой строится вся дальнейшая аналитика.
на работе системному аналитику придётся работать именно с pgadmin, если БД postgre
Имхо, в целях самообучения SQL намного удобней какой-нибудь GUI над SQLite (например DB Browser for SQLite), чем монструозный по размеру и возможностям Postgres.
Решения заданий #9 и #10 — это один сплошной фейспалм. Мало того что HAVING тут высосан из пальца, и вообще не нужен для решения этих задач (я уверен, что любой, решивший их самостоятельно, напишет нормальный запрос с WHERE), но — главное — эти запросы выдают правильный ответ чисто случайно.
В #9 задаче идёт отработка having, в #10 отработка like, где есть группировка
Какие вы задачи для текущией БД можете предложить, которые будут не высосаны из пальца на ваш взгляд?
Для #9 думаю можно Найти склады, в которых количесво сладостей больше 8, но тогда оно немного будет пересекаться с #13. Сначала сгруппируем данные и найдём количество сладостей на каждом складе, затем выберем по условию больше 8.
Решение задачи - сам запрос может быть разным, главное же получить правильную выборку в результате. Having же используется, чтобы выбрать из сгруппированных данных по некому условию.
Для #10 можно также поискать количество сладостей в складах, в городах на букву M.
Если я непонятно написал выше, ваш запрос с having не выдает правильную выборку. Она получается правильной только из-за специально подогнанных данных. Ну сделайте нормальный тестовый набор данных, чтобы у товаров одного типа не был одинаковый срок годности. И посмотрите на результаты своих запросов глазами. Если уж не понимаете чисто в теории, что делает с запросом ваше GROUP BY st.name, s.expiration_date
По поводу запросов, я не вижу тут двух кейсов для having. Ну то есть like никак не связан с having, его наличие ничего не меняет. Ну просто условие во where.
Да, можно больше 8. Но я бы добавил реализма, и добавил одну строчку с дублем, одни и те же конфеты на складе два раза. И найти запросом этот дубликат
у меня не получается решить эту задачу правильно при помощи WHERE, выдает все в кучу, даже не соответствующие условию поля. Вот мой запрос:
SELECT COUNT(s.id), st.name
FROM public.sweets s
JOIN public.sweets_types st ON s.sweets_types_id = st.id
WHERE s.id > 4
GROUP BY st.name;
Я тоже всю жизнь думал что having - это спутник группировки. Но, оказывается, не совсем. Это по сути where, работающий с алиасами. И лишь частный случай этого - использование при группировке.
Все с точностью до наоборот. Это использование HAVING без GROUP BY - расширение, существующее лишь в некоторых СУБД и в т.ч. допускающее использование алиасов выходного набора вместо соответствующих выражений в условиях отбора.
Вы что-то путаете. Цитирую стандарт SQL:1999:
7.10 <Having clause>
Function
Specify a grouped table derived by the elimination of groups that do not satisfy a <search condition>.
Format
<having clause> ::= HAVING <search condition>
General Rules
1) The <search condition> is applied to each group of R. The result of the <having clause> is a grouped table of those groups of R for which the result of the <search condition> is true.
Спасибо, хорошо написано.
МнеЧастьВ таблице есть: Столбцы, Строки, Ячейки, Ограничения (constraint): PK (primary key) - первичный ключ, FK (foreign key) - внешний ключ, Тип данных.
Знаете, если бы мне кандидат на собеседовании выдал вот это, то на этом собеседование и закончилось бы.
В таблице есть поля и есть записи. Столбцы и строки - это в Экселе.
Ячеек нет - вообще. Если держаться за аналогию с Экселем, то аналогом ячейки Экселя является определённое поле определённой записи.
Тип данных - это атрибут поля. Один из атрибутов.
А констрейнт - это вообще не объект. Это правило. Правило, которое использует подсистема контроля целостности и непротиворечивости данных.
Да, кстати... Вы ещё забыли о таких жёстко связанных именно с таблицами объектах, как индексы (в случае упомянутых PK/FK - используемые как поддержка процесса контроля данных соответствующими констрейнтами-правилами с целью повышения эффективности/скорости контроля). И триггеры, бо́льшая часть которых (а в некоторых СУБД - все) также связана с таблицами.
Далее. Очень хочется узнать, с каких это пор в DDL затесался TRUNCATE. Это однозначный DML, почти алиас DELETE FROM table, который просто оптимизирован на безусловное удаление всех данных (например, вместо удаления записей может быть удалена и пересоздание сама таблица).
Дальше. "COUNT. Количество строк." А дальше - дурь, которая также будет достаточным основанием отправить кандидата учить матчасть. Количество записей - это COUNT(*), а написанный ниже COUNT(id) - это количество не-NULL значений указанного поля. И хоть в данном конкретном случае результат будет таким же, это никак не извиняет.
Вопрос терминологии, главное чтобы человек понимал суть. На аналогии с обычной таблицей excel, людям проще понять эту тему (проверяли на практики - обучении) . Идея статьи овладеть SQL быстро для поиска данных, предварительно создав не сложную БД с этими данными без лишних усложнений.
Да, индексы, тригерры конечно есть, но для поиска данных в работе или на собеседовании их спрашивают редко. Это уже более продвинутый уровень, я бы так сказала. А если спрашивают, то в формате: Какие объекты есть в БД.
Вопрос индексов и триггеров поднимается в процессе проектирования БД и обсуждается на уровне аналитик - разработчик/архитеетор., но всё зависит от компании, где работаешь.
Если Вы аналитик Вам часто приходилось писать индексы, триггеры?
>И хоть в данном конкретном случае результат будет таким же, это никак не извиняет.
Ну кстати, таким же в смысле данных. Но не факт, что таким же по быстродействию. Во всяком случае, есть такие SQL-движки, с хранением по колонкам, скажем в parquet, где запросив одну колонку вы и получите только ее, а остальные данные не будут физически прочитаны с диска. И в нашем случае аналитик такое должен бы понимать тоже.
И в этом контексте для быстродействия никакая колоночная БД не нужна. Достаточно как раз только индекса. И колонка мало того что будет читаться одна, так ещё и не с диска, а из оперативной памяти. А добавить индекс все-же немножечко попроще, чем мигрировать всю инфраструктуру на новую БД. Так что со всех сторон как не крути, про индексы знать надо.
>никакая колоночная БД не нужна.
А память у вас резиновая что-ли? Я в общем тут клоню к тому, что производительность — это отдельная непростая тема, и ей должны заниматься люди, которые немного другую квалификацию имеют. И знают общее положение дел — а не только про один конкретный запрос. И зовут их обычно не аналитиками, и инструменты у них обычно слегка другие.
Далее. "LIKE. Поиск заданного значения в столбце по совпадению." Бред. Этот оператор выполняет проверку на соответствие шаблону. А совпадение, оно же равенство, проверяется другими операторами. Оператор равенства '=', проверка на NULL 'IS [NOT] NULL', в некоторых СУБД есть и другие операторы.
для начинающих - отличное пособие
разве что сомнительный момент с переключением интерфейса на русский
Имхо, структура БД не очень, так как таблица sweets не нормализована, а если точнее, туда свалили атрибуты таблиц sweets и sweets_stock (в которой должен быть id_sweets и информация о партиях или штуках товара с их сроками годности).
Кстати да, верно подмечено.
Плюс отдельная таблица для городов не помешала бы.
Как и в целом какие-то пояснения про нормализацию и связи между таблицами.
Спасибо за комментарии! На мой взгляд, это тема отдельной статьи, как спроектировать БД правильно или нет, как нормализовать.
Ну, как раз готовился к собеседованию, на джуна Data Engineer, и HR прислал мне следующее задание:
1. Установить docker если еще не установлен
2. Запустить docker с clickhouse
3. В clickhouse создать таблицу для сырых данных
4. Загрузить csv файл в clickhouse ((csvшку предоставил)поля в нем user_id, event_time, event_id)
5.Создать вьюху с расчетными данными
Вроде как звучит все не так уж и сложно, но когда понимаешь, что во вьюхе должны быть следующие расчетные данные:
date - user_id - session_id - session_duration, для меня все становится очень сложно.
date и user_id - поля из сырых данных session_id требуется сгенерить по бизнес правилу. session_duration - время между первым и последним ивентом в сессии.
Бизнес правило расчета сессии: Сессией называется активность пользователя, в которой между последовательными действиями проходит менее 15 мин.
Кто нибудь может объяснить, как можно сгенерить session_id и session_duration? У меня вот даже концепции в голове никакой не выстраивается.
Как изучить SQL за ночь или шпаргалка для системного аналитика