Комментарии 85
А ещё есть lag :) и first_value() + distinct ..(для oracle с rownum() <2)
Можно и без подзапроса…
Ну и без дистинкт есть qualify но увы в терадате :)
Решение с max возвращает всегда одну строку, в которой или вторая зарплата или null. И если я правильно понял, именно так и написано в условии задачи.
А ваше решение в случае если второй зарплаты нет, вернет 0 строк, что не совсем соответствует условию задачи.
Красивый способ выравнивания условий, надо попробовать датагрип так настроить
Был случай… Новый человек взял и прошёлся автоформатом по пакеты на 1000+ строк, пока вносил дополнения, а он был вылизан с расставленными комментариями, выровненными полями где это важно и собранными в длинные строки где не особо… Ну в общем как-то нехорошо получилось...
Надо было вылизывать автоформаттер, а не 1000+ строк кода, решая, где ещё не лень расставлять отступы, а где уже пофиг и не жаль читателя.
Спорно. Если вместо неявных соединений использовать явные — то верхний стиль как раз окажется проще выровнять:
FROM Weather a
JOIN Weather b ON DATEDIFF(a.Recorddate, b.Recorddate) = 1А вот в нижнем не понятно куда этот самый JOIN писать, чтобы таблицы остались под таблицами.
Кроме того, во втором варианте кажется, что DISTINCT относится к конкретному столбцу, а не ко всему набору столбцов.
Я когда в новой команде так писал запросы, на меня смотрели как на колдуна — фигасе, как красиво! Меня же скорее удивляло, что для кого-то это не норма.
Наш тимлид вообще не запаривался и писал всё сплошной строкой, типа внутри кода никто не видит потому и пофиг. Это был просто ужас перфекциониста.
Плохая читаемость.
Выбивается из общего принципа построения запросов.
…
Зачем?
да ну? разве запрос SELECT a.x, b.y FROM table1 a, table2 b WHERE b.c=a.c плохо читается?
из какого такого ещё «общего» принципа? где и кем он описан? в каком стандарте?
нездоровым догматизмом попахивает.
В оракл тоже работает a.b(+) = c.d, но вот пойди вспомни что оно там означает в каких сочетаниях
Ваш пример, по мне, тоже неудобно, особенно если таблиц много.
В последнее время часто приходится использовать большие (сотни строк) запросы, так в них (если удаётся оптимизировать) стараюсь выносить параметры в первый with, чтобы хотябы собрать их в одном месте, и далее каждый подзапрос (with) начинать со таблицы параметров к которой уже идут join-ы, разобрав пару сложных запросов пришёл к выводу что лучше сразу писать более структурировано. хоть и многословнее.
CREATE TABLE [dbo].[T](
[DayNumber] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Value] [int] NOT NULL
) ON [PRIMARY]
SELECT a.DayNumber, a.Value
FROM T a, T b
WHERE a.Value > b.Value
AND a.DayNumber = 1 + b.DayNumber
Отработает мгновенно
CREATE TABLE #T(
[DayNumber] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[Value] [int] NOT NULL
)
SELECT
a.DayNumber
,a.Value
FROM #T a
WHERE a.Value > (SELECT b.Value
FROM #T b
WHERE a.DayNumber - b.DayNumber = 1)
OPTION (MAXDOP 1)Очень плох, потому что начинает потоково обрабатывать запрос. (в WHERE чачти вычисление, которое не даёт правильно оптимизировать запрос и он втупую по кускам данных пытается их сматчить с подзапросом)
Намного лучше:
SELECT
a.DayNumber
,a.Value
FROM #T a
WHERE a.Value > (SELECT b.Value
FROM #T b
WHERE b.DayNumber = a.DayNumber - 1)
OPTION (MAXDOP 1)План запроса у этого запроса такой же как и у
SELECT
a.DayNumber
,a.Value
FROM #T a, #T b
WHERE a.Value > b.Value
AND a.DayNumber = 1 + b.DayNumber
OPTION (MAXDOP 1)И эффективность соответственная (лучше).
Лучший вариант:
;WITH tmp AS (
SELECT
a.*
,Lag(a.[Value]) OVER (ORDER BY [DayNumber]) AS lgValue
FROM #T as a
)
SELECT
t.DayNumber
,t.Value
FROM tmp as t
WHERE t.[Value] > t.lgValue
OPTION (MAXDOP 1)Потому что использует один проход по таблице, в отличие от предыдущих вариантов с повторными запросами к таблице.
Могу привести и планы запросов, но лучше сами проверьте, потому что картинки вставлять совсем не хочется.
ЗЫ: MS SQL. ~250000 строк.
Небольшое пожелание: называйте пожалуйста CTE осмысленно, спасибо)
Почему непривычно?
да ну, кому какое дело до синтетического примера
В общем случае конечно никакого, но вас же джуны читают) Я буквально недавно объяснял человеку почему запрос с CTE вида
with tmp1 as (),
tmp2 as (),
tmp3 as ()
tmp4 as ()
select from tmp1
left join tmp2
left join tmp3
left join tmp4
Крайне хреновая идея. Ониж откуда то берутся. Даш разок слабину и все, толпа говнокодеров)
Почему непривычно?
Из-за квадратных скобочек. Смотрятся необычно, а сам я с этой базой не работаю еще с тех пор ка кона была виндовс онли
В общем случае конечно никакого, но вас же джуны читают)Чёрт! Срочно нужен маркер 6+ (стаж)!!)
Крайне хреновая идея. Ониж откуда то берутся. Даш разок слабину и все, толпа говнокодеров)Ну всё, ну всё, мне стыдно )
А берутся они из-за того, что программисты входят в профессию без надлежащего образования (хоть кого то в универе заставляли читать чистый код?), работают с базами данных через фреймворки (и не парятся что там как написано) и работают частенько не в команде (из-за чего объяснить им что то некому).
Из-за квадратных скобочек. Смотрятся необычно, а сам я с этой базой не работаю еще с тех пор ка кона была виндовс онлиОни и в МС не обязательны по большей части. =)
Я далек от этой темы, но рискну предположить, что не факт «Лучший вариант», возможно для большинства практических случаев это так и есть, но не для всех сценариев. Этот курсорообразный Lag может и хорош, когда записи в последовательности рядом, но если завтра понадобится переделать запрос под далеко отстоящие друг от друга записи, то это значит, что в памяти надо будет держать всю CTE временную таблицу, то есть, кто-то другой будет ждать, пока вы освободите этот десяток-другой гигабайт общей памяти. Плюс, может возникнуть вопрос к актуальности этих данных в памяти.
В случае же вложенного запроса — таки да, есть повторные запросы к таблице. Но цена этих запросов не так уж велика, если поле проиндексировано, — с современными корпоративными SSD. Сколько надо дисковых операций, чтобы выйти, к примеру) на нужную из 2 в степени 40 записей, — всего пару десятков в среднем, типа того. (Тера записей) Не знаю, может вы и правы с этими CTE, но мне это не особенно очевидно для общего случая.
Lag не курсорообразен, а окнообразен. И вполне себе работает так же по индексу — так что тут чистая выгода по сравнению с двойным обращением. =)
С оконными функциями, конечно, свои нюансы (к примеру — забить память до сгружения вычислений на диск), но в таких ситуация и двойной запрос будет печален.
В прочем — в жизни всякое бывает )) и, да — это только про MS SQL. В других системах CTE могут работать принципиально иначе.
Там в оригинале вообще DATEDIFF стоит. Смысл моего комментария был не в том, чтобы найти самое быстрое решение, смысл в том, что вообще кросс джойн там — перебор, можно вполне использовать вложенный запрос. Для этого я и привел код. Я не мог убрать упомянутое вычисление, так как тогда мне сказали бы, что я не корректно сравниваю. Я от DATEDIFF ушел не для оптимизации, а для упрощения кода, — с этими датами в разных системах скорее всего слишком много нюансов.Очень двоякий вопрос эквивалентности разных решений с одинаковым ответом ))
Но, как кто то выше заметил, нас могут читать джуны, поэтому лишний раз обратить внимание на такие мелочи (совсем не мелкие в плане оптимизации) — не лишне.
ЗЫ: и всё-таки лучше использовать Datediff, потому что, к примеру, тип Date не поддерживает арифметические операции… (
Но если я пишу ORM, и запросы генерируются по метаданным, то там явные джойны использовать будет совсем уж неудобно.
Ну и, наконец, от базы зависит. Если Oracle, то джойны тоже будут неявные.
Я пишу так, как мне удобно в каждом конкретном случае.Не говорю, что у вас так, но частенько это приводит к нечитабельному коду.
А если их в запросах сильно больше 10, то это значит, что я что-то неправильно напроектировал, и надо подумать, как переделать схему, чтобы не было этих километровых запросов.Это называется — нормализация! )
На самом деле ничего в большом количестве джоинов, обычно, плохого нет. Плохое обычно совсем в другой степи кроется )
Но если я пишу ORM, и запросы генерируются по метаданным, то там явные джойны использовать будет совсем уж неудобно.Ну, дело, конечно, каждого, но не вижу разницы. =)
Ну и, наконец, от базы зависит. Если Oracle, то джойны тоже будут неявные.ну это да.=)
За последнюю пару десятилетий я юзал СУБД всех крупных вендоров, кроме MS. Oracle, MySQL (включая MariaDB), Postgre, DB2, Firebird. Вообще особо без разницы, под что писать, если оставаться в рамках SQL99. Ну, последние несколько лет можно и фичи из SQL2003 юзать, особо не задумываясь, какая там база. Вот более новые стандарты как-то трудно внедряются.
Обширная практика позволяет как-то не особо зацикливаться на синтаксисе запросов (особенно если руками пишется только шаблон) или наборе функций, просто больше думаешь о прагматике той системы, которую разрабатываешь. Требования на базу часто спускают сверху. И если в конкретной СУБД нету какой-нибудь window function, вот тогда приходится вспоминать, как это делалось 10 лет назад.
Иной раз нужна хорошая денормализация %)Всё хорошо в меру.
За последнюю пару десятилетий я юзал СУБД всех крупных вендоров, кроме MS. Oracle, MySQL (включая MariaDB), Postgre, DB2, Firebird. Вообще особо без разницы, под что писать, если оставаться в рамках SQL99. Ну, последние несколько лет можно и фичи из SQL2003 юзать, особо не задумываясь, какая там база. Вот более новые стандарты как-то трудно внедряются.Тогда наш опыт не пересекается. Я работаю только с MSSQL и в основном именно пишу и читаю запросы и логику, учитывая что вся логика и процессы на стороне сервера. Тут и шаблонов на всё не запасёшь и разрабатывали это не один десяток людей и лет. И что бы хотя бы понять что происходит — приходится много скриптов перекопать. И тут то и возникает большое желание привести всё в красивый, единообразный и читабельный вид. =)
Обширная практика позволяет как-то не особо зацикливаться на синтаксисе запросов (особенно если руками пишется только шаблон) или наборе функций, просто больше думаешь о прагматике той системы, которую разрабатываешь. Требования на базу часто спускают сверху. И если в конкретной СУБД нету какой-нибудь window function, вот тогда приходится вспоминать, как это делалось 10 лет назад.
А если их в запросах сильно больше 10, то это значит, что я что-то неправильно напроектировал
Везёт. А вот у нас в среднем в репорте количество полей больше 15, и всё время норовят туда ещё чего-нибудь докинуть, чтобы "вся система на одном экране".
А если связь выражена функцией? Особенно – когда пересекаются функции по областям значений, ещё и с некоторой функцией, выражающей критерий совпадения? На практике встречается редко, за пару лет у меня было всего пара таких случаев. Не нужно только рассказывать про CROSS и APPLY (для MS SQL Server): это не JOIN по символьному написанию. А тогда какая разница?
В чём проблема с неявностью, если для понимания запроса в любом случае требуется знать и как он интерпретируется, и как может исполняться? Обычно, основная проблема – что СУБД в таком случае не всегда находит оптимальный план запроса. Ну так проверять исполнение запроса по фактическому плану и метрикам необходимо всегда. Конечно, есть ещё проблема изменения плана запроса при изменении структуры индексов и таблиц. Что с этим делать – тоже понятно. Т.е. не бывает спонтанного и случайного изменения плана запроса и столь нелюбимых и потому избегаемых "сюрпризов".
В идее явности мне не нравится запрет на использование умолчаний, контекста и всего того, чему не дано обозначения, и что этим повышает эффективность использования языка. Язык так устроен в принципе: достаточно понаблюдать за вариантами применения. При этом русский язык куда больше использует и опирается на контекст, чем английский (это хорошо известная особенность разных культур). Даже когда выстраивается система определений из выражений, возникает тот же эффект: чтобы представить и посчитать в голове значения итогового выражения, требуется знать все определения, использованные в нём.
И дальше эта история при обратном прослеживании выводит на одно и тоже: "не хочу знать, выяснять, разбираться". Однако, это проблема того, кто не хочет, а не того, кто пишет и исполняющего устройства. Незнание – всегда изначально проблема исключительно того, кто не знает: по признаку того, в ком происходит/делается. И он стремится переложить её решение на других. Часто – чтобы сэкономить своё мыслетопливо. Однако в случае с исполняющим устройством имеется критерий: если получается требуемый результат, значит кода необходимо и достаточно для исполнения. А тогда считать в голове за него, чтобы делать выводы о том, почему и откуда получилось данное выражением, является попыткой реконструирования исходной системы сведений, из которой это выражение выведено. Ну так значит проблема в передаче этих сведений, а не коде.
Единственное и основное назначение кода – быть исполненным некоторым устройством. Передавать через код сведения для человека, которые код представляет для этой цели, всё равно что использовать посредника и дополнительный уровень кодирования/представления для прочтения какого-либо произведения. Т.е. заниматься обратным процессом – реконструированием, декодированием, деассемблированием, восстановлением исходных положений и сведений из системы их специальных записей, заведомо для этого не предназначенной. Невозможно передать исчерпывающие сведения о методе, реализованном в коде. Такие сведения передаются людям совершенно иными путями, средствами и методами и используют для этого иные языки. Поэтому это выбор между тем, требуется их знать или нет, а не между тем, должен ли автор передавать их через код или нет. Так устроено и выбор неизбежен: это не предмет для мнений или (не)согласия.
Уровень знаний sql у датасаентистов спрашивают примерно начальный.
Или это у начинающих датасаентистов?
Давайте дальше.
По каким областям знаний ещё датасаентистов спрашивают?
Не говоря уже о том, что не указан хотя бы SQL-диалект, для которого приведены примеры. Учитывая неявные джойны — наверное, оракл? Впрочем, для таких слабых вопросов это, наверное, действительно неважно.
Да, и большую часть вопросов здесь элементарно решить с помощью оконных функций — dense_rank в первом, lead в третьем, четвертый так вообще прямо напрашивается на max () over (partition by DepartmentID)
Вот чего здесь не хватает — так это анализа времени выполнения в explain, с объяснением какой способ оптимальнее.
понимание концепции оконных функций позволяет решать целый класс задач, которые без них решить невозможно или очень сложно. наличие хотя бы пары вопросов на собеседовании по этой теме, на мой взгляд, необходима. и это не блажь и не проверка знаний документации, а суровая повседневная реальность в моей работе.
в частности, в приведенных ответах решение явно не оптимально. что же касается оптимизаций, то в БД, с которой я привык работать, оконные функции работают почти всегда быстрее, чем джойны (hive)
Вот тоже ожидал увидеть специфические сценарии для hive, impala т.к. их специфика как раз подразумевает полные проходы по таблицам с накоплением результата и возможно сравнение с классическими рсубд и, как пример, с teradata, а тут совсем база, такие запросы и бизнес в состоянии писать, как то не похоже на вопросы к кандидатам на позиции data scientist...
Ну или стоит хорошо настроенный hdp с поднятыми llap-демонами / cloudera cdh с hive-on-spark в качестве движка, ибо классический MR для исполнения Hive SQL слишком медленный и в процессе выполнения запроса может временными файлами загадить пару сотен Тб в hdfs при неаккуратном обращении.
PS если совсем серьёзно — нужно писать отдельную статью с тестами под конкретные случаи и обзором движков для исполнения запросов — но не уверен, что это кому-то будет интересно читать.
PS Да, и ОС, вместе с журналами zookeeper/namenode/journalnode на m2 ssd писать — тогда сплошное благолепие.
которые без них решить невозможно
Без аналитики можно решить любую задачу. SQL все же алгебру множеств реализует. Просто с аналитикой эти задачи решаются гораздо эффективнее, а значит создаваемое ПО работает оптимально, а не абы как.
Оптимизация и сравнение эффективности запросов (собственно, самое важное, обычно, в запросах в базе) не рассмотрено.
Сложилось впечатление что статья специально написана для вылавливания тех кто по ней готовился к собеседованию :)
Как пример, первый запрос, вложенные подзапросы когда есть оконные функции, которые как раз хорошо подходят (не всегда и не во всех базах) как раз к операциям такого вида в классических СУБД, если уж предлагаются ответв то хорошо предложить варианты с объяснением того как это работает, ведь DS-ы как раз могут для удобства писать запросы с помощью impala либо hice, и на примере их можно было показать "как оно будет работать", и именно такие примеры ожидал увидеть :)
Есть начиная с восьмой версии кажется
Вроде как есть, в MySQL 8:
Window functions. MySQL now supports window functions that, for each row from a query, perform a calculation using rows related to that row. These include functions such as RANK(), LAG(), and NTILE(). In addition, several existing aggregate functions now can be used as window functions (for example, SUM() and AVG()). For more information, see Section 12.21, “Window Functions”.
MariaDB: 10.2:
Window functions have been introduced. This release adds support for a limited set of functions, and the basic execution algorithm. Development continues to expand the set of supported functions and optimize the execution.
Насколько я понимаю, вопросы и ответы взяты с Leetcode. А там, кажется, используется MySQL версии 5.7
Пишу на MSSQL, конструкция LIMIT 1 OFFSET 1 в запросе выдаст ошибку.
Может стоит в список тегов добавить что это не просто SQL, а MySQL?
IFNULL(expression, alt): эта функция возвращает свой аргумент expression в том случае, если он не равен null. В противном случае возвращается аргумент alt. Мы воспользуемся этой функцией для того чтобы возвратить null в том случае, если в таблице не окажется искомого значения.
Я не спец по SQL но кажись данная функция в этой задаче не нужна.
Учитывая, что сейчас такие дата-сайентисты пошли (да и вообще инженеры, по-честному), что они лефт джойн не могут написать, эти вопросы достаточно сложные. Народ вообще пошел интересный: sql и регулярные выражения — два камня преткновения.
with t as
(select 1 id,'Abbot' student from dual union all
select 2,'Doris' from dual union all
select 3,'Emerson' from dual union all
select 4,'Green' from dual union all
select 5,'Jeames' from dual)
select
id,
case when mod(id , 2) = 1 then nvl(lead (student,1) over (ORDER BY id),student) else nvl(lag (student,1) over (ORDER BY id),student) end
from t
Много "хитростей" используют диалект MySQL.
В других реализациях такие финтьі ушами не пройдуть.
что SQL — это не только SELECT
а еще и FROM.
сеньер так еще и WHERE знять обязан…
Народ на полном серьёзе обсуждает проходную корпоративную заказуху, написанную студентом по заказу маркетингового отдела. Это я не знаю — как обсуждать сео тексты типа "Читатели часто спрашивают как приобрести пластиковые окна в рассрочку. Рассказываем, как приобрести пластиковые окна. Приступая к выбору пластиковых окон..." или разговаривать с роботом в онлайн чате.
Что задачи, что решения показывают крайне примитивный уровень владения SQL.
with Employee as
(select 1 id, 'Joe' name, 70000 salary, 1 DepartmentId from dual union all
select 2, 'Jim' , 90000, 1 from dual union all
select 3, 'Henry', 80000, 2 from dual union all
select 4, 'Sam' , 60000, 2 from dual union all
select 5, 'Max' , 90000, 1 from dual)
,Department as
(select 1 id , 'IT' name from dual union all
select 2, 'Sales' from dual)
select
dn
,en
,salary
from
(select
Employee.name en
,salary
,Department.name dn
,DENSE_RANK() OVER (PARTITION BY departmentid ORDER BY salary DESC NULLS LAST) m
from Employee, Department
where Employee.DepartmentId = Department.id
)
where m=1
зачем задавать датасатанисту вопросы, которые бизнес сам может в два клика в powerBI/tableau узнать, не тратя время специалиста?
мне кажется датасатанистов нужно интервьюировать не на чистые тех навыки, а на способность взять реальную бизнес-проблему, сформулировать в домене данных и продумать прототип решения, т.е. end-to-end анализ:
1. кейс интервью с бизнесом, например с главой продажников, надо замечать какие вопросы кандидат будет задавать бизнесу
2. формулирование бизнес-задачи в общем виде — надо увеличить продажи через сайт
3. предложение парочки гипотез, типа а давайте делать А/B тесты на сайте и смотреть как улучшить конверсию
4. сформулировать модель исследование (randomized controlled trial) — какие механизмы использовать для сбора данных и сбора метрик
5. как анализировать данные и какие выводы делать из результатов анализа и т.д.
5 вопросов по SQL, которые часто задают дата-сайентистам на собеседованиях