Поскольку в остальных ответах вывод названия департамента не требовалось, я слегка переписал ваш запрос. Но обращает внимание на то, что вы window функции применяете к таблице employee, а партицируете их по таблице departments. С точки зрения функционала может это не важно, с точки зрения потенциальной оптимизации запроса косяк. Но не суть в этом.
test=# explain analyze select employee_name, salary from (select e.name employee_name, first_value(e.salary) over (partition by e.department_id order by salary desc) salary, row_number() over (partition by e.department_id) rn from employees e ) t where rn = 1;
Здесь, как и в других аналогичных ответах в комментах запрос идет по таблице с индексом только на department_id, раз уж решили, что такой вариант, наиболее реалистичный.
test=# \d employees Таблица "public.employees" Столбец | Тип | Правило сортировки | Допустимость NULL | По умолчанию ---------------+---------+--------------------+-------------------+-------------- employee_id | integer | | not null | department_id | integer | | not null | name | text | | not null | salary | money | | not null | Индексы: "employees_pkey" PRIMARY KEY, btree (employee_id) "d" btree (department_id) Ограничения внешнего ключа: "employees_department_id_fkey" FOREIGN KEY (department_id) REFERENCES departments(department_id)
Контр довод, оконная функция rank() будет считать ранги для всех сотрудников (и тратить на это ресурс), в то время как max() потребует меньше машинного времени. Плюс размер таблицы передаваемой из внутреннего запроса во внешний, в вашем случае она будет с размером с число сотрудников, в случае с max() размером с число подразделений. Но, зачем теоретизировать? Обоснуйте свою точку зрения неголословно. Приведите такое распределение данных, при котором использование rank() будет выигрывать. Вот это уже будут слова не мальчика, но мужа.
Ну, вполне себе вариант. Почему нет, да как-то было очевидно, что по сравнению с вариантом с max() будет хуже.
test=# explain analyze select name, salary from employees a where not exists(select * from employees b where a.department_id=b.department_id and a.salary<b.salary); QUERY PLAN
Hash Anti Join (cost=62.00..122.50 rows=1333 width=26) (actual time=0.307..0.956 rows=20 loops=1) Hash Cond: (a.department_id = b.department_id) Join Filter: (a.salary < b.salary) Rows Removed by Join Filter: 9635 -> Seq Scan on employees a (cost=0.00..37.00 rows=2000 width=30) (actual time=0.008..0.089 rows=2000 loops=1) -> Hash (cost=37.00..37.00 rows=2000 width=12) (actual time=0.284..0.284 rows=2000 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 110kB -> Seq Scan on employees b (cost=0.00..37.00 rows=2000 width=12) (actual time=0.003..0.138 rows=2000 loops=1) Planning Time: 0.093 ms Execution Time: 0.972 ms (10 строк)
Ну да мой косяк. На самом деле в статье эта фраза была дважды, второй раз, действительно, с формулировкой задачи. Первую фразу я уже стёр. Спасибо за бдительность.
Это обычное правило в математике начиная со школы и решения квадратных уравнений. В ответе на задачу должны быть приведены все решения задачи. А иначе задача не решена. А в этом контексте и смысла невозможно придумать выводить только одного сотрудника, если в отделе два таких.
Ну, это очевидное правило в математике начиная со школы и решений квадратных уравнений. :) Нельзя "угадывать ответы", надо вывести их все. Иначе ответ не правильный, даже если вы одно решение угадали.
Никто не против того, чтобы использовать оконные функции там где нужны. Тезис этой статьи, который я и написал в выводах, это то, что собеседование можно построить таким образом, что на работу будут набирать только и исключительно только сотрудников, которые предлагают самый худший вариант решения проблемы. Выглядит как забавный парадокс. Но столь громкое утверждения нельзя было подтвердить технической аргументаций, чтобы не выглядело голословно. Вы это не оценили.
Я думаю это реальная задача, которую он сам не смог решить до сих пор. Выложил под видом якобы задачи на собеседование, в надежде, что кто-то из комментаторов напишет ему запрос.
Я перевел статью с JitBit и выложил в Хабр, как раз ту, где была эта задача. И там говорят, что ни кандидатам всегда предоставляют компьютеры подключенные к базе данных. Работай в привычных условиях. А в России да, раньше надо было писать что-то ручкой на огрызке бумаги, сейчас так вообще тебе показывают скриншот, который даже не скопипастишь и надо решить задачу в уме. И от непривычных условий, которые не наработаны практикой, начинаешь стрессовать. А что касается Красного Волка, то да, это была остроумная шутка, которая умнее, чем кажется. Дело в том, что некоторые (может многие) потенциальные начальники, в том числе и те, кто отписывались в комментариях, имеют странные предпочтения. Мол, если нет вложенных запросов, значит вариант хороший. А поскольку это единственный мне известный вариант без позапросов, получается вариант Красного Волка с точки зрения собеседователя самый лучший. А с точки зрения человека с реальной практикой отладки запросов очевидно, что всё будет наоборот. Декартово произведение, действительно, видно. :)
Хотел бы я было поставить респект, но прочитал про CTE. То что вы относитесь к CTE только как косметическому украшению уже вас характеризует как потенциального начальника. С CTE всё не так просто. Если до 12го Postgresql СTE не оптимизировались и ими можно было как указать PostgreSQL использовать более оптимальный план выполнения запроса (с точки зрения разработчика, причем не всегда эта точка зрения правильная), так и наоборот, можно было бы ухудшить план. Т.е. это как бы "оптимизация", но которая иногда приводит к полностью неработающему сервису. После 12го их функционал несколько изменили несовместимым образом, так это вообще головная боль при апгрейде на 12ю версию. Просмотром кода это не предсказать, даже если бы в коде были SQL запросы. Это я к тому, что использование CTE надо оценивать вовсе не с точки зрения читабельности кода. Более того, поскольку в отличии от всякой экзотики, вроде курсоров, CTE используются очень часто, практический ежедневно, то это хороший вопрос для собеседования, их надо держать в памяти, когда они оптимизируются, а когда нет.
Знаете, что слово бэкенд имеет очень разные смыслы для, например JavaScript программиста или специалиста по базам данных. :) Тут смотря с какого конца смотреть.
Да всё просто. Физлабы идут с первой недели первого семестра. И буквально с первой недели первого курса студентам уже надо хоть как-то объяснить как обрабатывать результаты эксперимента. Потом, ближе к третьему, математика становится гораздо сложнее, но там упор идёт на диффуры.
О, так на самом деле нет такого стандарта "в инженерной записи". И в разных отраслях может быть по разному. В физике там пишут сигму, среднеквадратичную погрешность. Это где-то две трети. В каких-то инженерных отраслях там пишут две сигмы, а это 95%. Так что буквально 100% там нет.
Это среднеквадратичная погрешность (или стандартная девиация), а не среднеквадратичное значение Root mean square (RMS). И кто только вам респекты ставил? И это уже было не важно, потому что я вспомнил, что всё же и в физике в этом контексте работают со среднем арифметическим.
Хм, но все же быстрота запроса и его эффективность определяется не количеством символов в тексте. Где-то была хороший пример, там сравнивали текст пузырькового алгоритма сортировки для функционального языка программирования и обычного, алгоритмического. Для алгоритмического текста было значительно больше, для функционального несколько строк с рекурсивными вызовами. Но это же не значило, что вариант с функциональным языком лучше, там было ровно наоборот.
Не просто знание оконных функций, а то единственное решение через rank(), про которое он прочитал в интернете. Так он сам так говорит. :) Ну или отчаянно на это намекает. А статья вовсе не про эту задачу, если вы не поняли, а про особенности собеседований и собеседователь.
Поскольку в остальных ответах вывод названия департамента не требовалось, я слегка переписал ваш запрос. Но обращает внимание на то, что вы window функции применяете к таблице employee, а партицируете их по таблице departments. С точки зрения функционала может это не важно, с точки зрения потенциальной оптимизации запроса косяк. Но не суть в этом.
test=# explain analyze select employee_name,
salary
from
(select e.name employee_name,
first_value(e.salary) over (partition by e.department_id order by salary desc) salary,
row_number() over (partition by e.department_id) rn
from employees e ) t
where rn = 1;
Subquery Scan on t (cost=146.66..241.66 rows=10 width=26) (actual time=0.520..1.137 rows=20 loops=1)
Filter: (t.rn = 1)
-> WindowAgg (cost=146.66..216.66 rows=2000 width=46) (actual time=0.519..1.134 rows=20 loops=1)
Run Condition: (row_number() OVER (?) <= 1)
-> WindowAgg (cost=146.66..186.66 rows=2000 width=38) (actual time=0.517..1.058 rows=2000 loops=1)
-> Sort (cost=146.66..151.66 rows=2000 width=30) (actual time=0.513..0.569 rows=2000 loops=1)
Sort Key: e.department_id, e.salary DESC
Sort Method: quicksort Memory: 158kB
-> Seq Scan on employees e (cost=0.00..37.00 rows=2000 width=30) (actual time=0.008..0.141 rows=2000 loops=1)
Planning Time: 0.071 ms
Execution Time: 1.155 ms
(11 строк)
Здесь, как и в других аналогичных ответах в комментах запрос идет по таблице с индексом только на department_id, раз уж решили, что такой вариант, наиболее реалистичный.
test=# \d employees
Таблица "public.employees"
Столбец | Тип | Правило сортировки | Допустимость NULL | По умолчанию
---------------+---------+--------------------+-------------------+--------------
employee_id | integer | | not null |
department_id | integer | | not null |
name | text | | not null |
salary | money | | not null |
Индексы:
"employees_pkey" PRIMARY KEY, btree (employee_id)
"d" btree (department_id)
Ограничения внешнего ключа:
"employees_department_id_fkey" FOREIGN KEY (department_id) REFERENCES departments(department_id)
Задача сформулирована корректно и работодатель даже её не придумывал. :) Он взял её из блога иностранной компании.
Нет, поищите документацию по rank() и rank_dense(). На собеседованиях об этом спрашивают.
Контр довод, оконная функция rank() будет считать ранги для всех сотрудников (и тратить на это ресурс), в то время как max() потребует меньше машинного времени. Плюс размер таблицы передаваемой из внутреннего запроса во внешний, в вашем случае она будет с размером с число сотрудников, в случае с max() размером с число подразделений.
Но, зачем теоретизировать? Обоснуйте свою точку зрения неголословно. Приведите такое распределение данных, при котором использование rank() будет выигрывать. Вот это уже будут слова не мальчика, но мужа.
Ну, вполне себе вариант. Почему нет, да как-то было очевидно, что по сравнению с вариантом с max() будет хуже.
test=# explain analyze select name, salary from employees a where not exists(select * from employees b where a.department_id=b.department_id and a.salary<b.salary); QUERY PLAN
Hash Anti Join (cost=62.00..122.50 rows=1333 width=26) (actual time=0.307..0.956 rows=20 loops=1)
Hash Cond: (a.department_id = b.department_id)
Join Filter: (a.salary < b.salary)
Rows Removed by Join Filter: 9635
-> Seq Scan on employees a (cost=0.00..37.00 rows=2000 width=30) (actual time=0.008..0.089 rows=2000 loops=1)
-> Hash (cost=37.00..37.00 rows=2000 width=12) (actual time=0.284..0.284 rows=2000 loops=1)
Buckets: 2048 Batches: 1 Memory Usage: 110kB
-> Seq Scan on employees b (cost=0.00..37.00 rows=2000 width=12) (actual time=0.003..0.138 rows=2000 loops=1)
Planning Time: 0.093 ms
Execution Time: 0.972 ms
(10 строк)
Ну да мой косяк. На самом деле в статье эта фраза была дважды, второй раз, действительно, с формулировкой задачи. Первую фразу я уже стёр. Спасибо за бдительность.
Это обычное правило в математике начиная со школы и решения квадратных уравнений. В ответе на задачу должны быть приведены все решения задачи. А иначе задача не решена. А в этом контексте и смысла невозможно придумать выводить только одного сотрудника, если в отделе два таких.
Ну, это очевидное правило в математике начиная со школы и решений квадратных уравнений. :) Нельзя "угадывать ответы", надо вывести их все. Иначе ответ не правильный, даже если вы одно решение угадали.
Никто не против того, чтобы использовать оконные функции там где нужны.
Тезис этой статьи, который я и написал в выводах, это то, что собеседование можно построить таким образом, что на работу будут набирать только и исключительно только сотрудников, которые предлагают самый худший вариант решения проблемы. Выглядит как забавный парадокс. Но столь громкое утверждения нельзя было подтвердить технической аргументаций, чтобы не выглядело голословно. Вы это не оценили.
Я думаю это реальная задача, которую он сам не смог решить до сих пор. Выложил под видом якобы задачи на собеседование, в надежде, что кто-то из комментаторов напишет ему запрос.
А чему тут удивляться. Там вчитаться тяжело, а тогда сразу становится понятно, что нет ничего удивительного в том, что там bad design.
Я перевел статью с JitBit и выложил в Хабр, как раз ту, где была эта задача. И там говорят, что ни кандидатам всегда предоставляют компьютеры подключенные к базе данных. Работай в привычных условиях.
А в России да, раньше надо было писать что-то ручкой на огрызке бумаги, сейчас так вообще тебе показывают скриншот, который даже не скопипастишь и надо решить задачу в уме. И от непривычных условий, которые не наработаны практикой, начинаешь стрессовать.
А что касается Красного Волка, то да, это была остроумная шутка, которая умнее, чем кажется. Дело в том, что некоторые (может многие) потенциальные начальники, в том числе и те, кто отписывались в комментариях, имеют странные предпочтения. Мол, если нет вложенных запросов, значит вариант хороший. А поскольку это единственный мне известный вариант без позапросов, получается вариант Красного Волка с точки зрения собеседователя самый лучший. А с точки зрения человека с реальной практикой отладки запросов очевидно, что всё будет наоборот. Декартово произведение, действительно, видно. :)
Хотел бы я было поставить респект, но прочитал про CTE. То что вы относитесь к CTE только как косметическому украшению уже вас характеризует как потенциального начальника. С CTE всё не так просто. Если до 12го Postgresql СTE не оптимизировались и ими можно было как указать PostgreSQL использовать более оптимальный план выполнения запроса (с точки зрения разработчика, причем не всегда эта точка зрения правильная), так и наоборот, можно было бы ухудшить план. Т.е. это как бы "оптимизация", но которая иногда приводит к полностью неработающему сервису. После 12го их функционал несколько изменили несовместимым образом, так это вообще головная боль при апгрейде на 12ю версию. Просмотром кода это не предсказать, даже если бы в коде были SQL запросы.
Это я к тому, что использование CTE надо оценивать вовсе не с точки зрения читабельности кода. Более того, поскольку в отличии от всякой экзотики, вроде курсоров, CTE используются очень часто, практический ежедневно, то это хороший вопрос для собеседования, их надо держать в памяти, когда они оптимизируются, а когда нет.
Знаете, что слово бэкенд имеет очень разные смыслы для, например JavaScript программиста или специалиста по базам данных. :) Тут смотря с какого конца смотреть.
В физике с систематическими погрешностями борются другими методами. :) Калибруют приборы. :)
Да всё просто. Физлабы идут с первой недели первого семестра. И буквально с первой недели первого курса студентам уже надо хоть как-то объяснить как обрабатывать результаты эксперимента.
Потом, ближе к третьему, математика становится гораздо сложнее, но там упор идёт на диффуры.
О, так на самом деле нет такого стандарта "в инженерной записи". И в разных отраслях может быть по разному. В физике там пишут сигму, среднеквадратичную погрешность. Это где-то две трети. В каких-то инженерных отраслях там пишут две сигмы, а это 95%. Так что буквально 100% там нет.
Это среднеквадратичная погрешность (или стандартная девиация), а не среднеквадратичное значение Root mean square (RMS). И кто только вам респекты ставил?
И это уже было не важно, потому что я вспомнил, что всё же и в физике в этом контексте работают со среднем арифметическим.
Хм, но все же быстрота запроса и его эффективность определяется не количеством символов в тексте. Где-то была хороший пример, там сравнивали текст пузырькового алгоритма сортировки для функционального языка программирования и обычного, алгоритмического. Для алгоритмического текста было значительно больше, для функционального несколько строк с рекурсивными вызовами. Но это же не значило, что вариант с функциональным языком лучше, там было ровно наоборот.
Не просто знание оконных функций, а то единственное решение через rank(), про которое он прочитал в интернете. Так он сам так говорит. :) Ну или отчаянно на это намекает.
А статья вовсе не про эту задачу, если вы не поняли, а про особенности собеседований и собеседователь.