Нет нюанса. Наличие у шефа звезды в другом ресторане никак не влияет на получение звезды тем рестораном, где он ещё работает. Это все равно, что в команду НХЛ без кубков Стэнли перешёл обладатель кубка Стэнли и теперь команду следует называет немножко обладателем кубка Стэнли. Или если они выиграют кубок Стэнли, то надо цокать языком и говорить, что дядя Вася выиграл кубок Стэнли до этого, а что бы вы без него делали?
select * from (
select *, lag((v1, v2), -1) over (partition by sensor_id order by ts desc) prev
from telemetry_data
) t
where prev is null or (v1, v2) <> prev
order by 1
Для двух показаний - v1 и v2, оба integer not null для примера, postgresql
Почему же, предугадаешь. В моем шутливом примере, до которого некоторые начали докапываться с точки зрения производительности, хотя я сразу написал, что она там плохая и что это скорее забавный пример, сразу видно, что тут практически декартово произведение, в рамках департамента.
Зачем вложенные запросы, оконные функции и группировки? Давайте подойдем к задаче с точки зрения того, что SQL декларативный язык:
select e1.id, e1.name, e1.salary
from employees e1
left outer join employees e2 on e1.department_id = e2.department_id
and e2.salary > e1.salary
where e2.id is null
Такие сотрудники, что в их отделе нет никого с более высокой зп. Понятно, что не очень эффективно, но прикольно.
Почему? В вашем примере будет три записи, где следующий head_id не равен текущему или отсутствует.
Грубо говоря,
head_id | next_head_id | Not equals 1 1 F 1 2 T 2 2 F 2 2 F 2 3 T 3 NULL T
Считаем Not equals и получаем 3.
p.s. Можно было бы избавиться от суммы во внешнем подзапросе, но не хотелось два раза писать вызов lead или использовать какие-то магические числа для обозначения отсутствующей головы.
select event_time, next_event_time
from (
select event_time, sum(case when head_state = false then -1 else 1 end) over(order by event_time) status,
lead(event_time) over(order by event_time) next_event_time
from (
select * from (
select *, lag(head_state, -1) over(partition by head_id order by event_time desc) prev_state
from cerberus
) t
where coalesce(prev_state, true) <> head_state
) t
) t where status = -1 * (select count(distinct head_id) from cerberus);
Или даже так
select event_time, next_event_time
from (
select event_time, sum(case when head_state = false then -1 else 1 end) over(order by event_time) status,
lead(event_time) over(order by event_time) next_event_time, sum(case when head_id <> next_head_id or next_head_id is null then 1 else 0 end) over () head_count
from (
select * from (
select *, lag(head_state, -1) over(partition by head_id order by event_time desc) prev_state, lead(head_id) over (order by head_id) next_head_id
from cerberus
) t
where coalesce(prev_state, true) <> head_state
) t
) t where status = -1 * head_count;
Первую задачу можно сделать только с оконными функциями:
-- Выбираем записи, где все головы спят, event_time - начало интервала сна
-- Конец интервала - следующая с точки зрения event_time запись для любой головы.
-- Опять таки, мы можем так делать, потому что мы избавились от дубликатов заранее,
-- так что следующая запись будет выходом из сна
select event_time, next_event_time
from (
-- Считаем кумулятивную сумму по head_state (-1 - спим, 1 - не спим).
-- Если у нас набралось -3 для данной строки, то все головы спят.
-- Мы так можем делать, потому что мы избавились от дубликатов в первом подзапросе
select event_time, sum(case when head_state = false then -1 else 1 end) over(order by event_time) status,
lead(event_time) over(order by event_time) next_event_time
from (
-- избавляемся от событий, дублирующих предыдущие события
select * from (
select *, lag(head_state, -1) over(partition by head_id order by event_time desc) prev_state
from cerberus
) t
where coalesce(prev_state, true) <> head_state
) t
) t where status = -3;
select d.department_name, count(*) total_employees
from employees e
inner join departments d on d.id = e.department.id
group by d.department_name
having sum(case when salary > 100000) / count(*) > 0.1
order by 2 desc
limit 3
Насчёт джоинов - это не объединение (union), а соединение. Это более сложная операция, у которой нет полноценных аналогов в диаграммах Венна. Я бы рассматривал inner join как декартово произведение всех записей из двух таблиц с дальнейшим отбрасыванием кортежей, не прошедших фильтрацию по условию соединения. Понятно, что сама СУБД работает по-другому, но именно для объяснения работы inner join-а имхо подойдёт. Тогда вам будет понятно, почему, например, джоиня юзеров с адресами, где на одного юзера может быть несколько адресов, вы будете получать несколько записей для некоторых юзеров.
Так же делаете два подзапроса с группировкой по компании - один вернёт почту, другой телефоны. С группировками можно даже inner join-ами обойтись, поскольку записи всегда будут.
Первый запрос работает некорректно. Если у вас два телефона и три почты у компании, то это даст шесть записей. Даже если будет по одному делопроизводству и вакансии, то в итоге их будет по шесть в count-ах
Грубо говоря,
Телефоны
(c1, t1), (c1, t2)
Почта
(c1, m1), (c1, m2), (c1, m3)
Делопроизводства
(c1, d1)
Вакансии
(c1, v1), (c2, v2)
Где c1 - некая компания
Тогда (это не настоящий sql), company join phone join mail join fssp join vacancy where company.inn = 'c1' дам нам аж двенадцать записей. Дистинкт по телефонам и почте нам поможет, а для fssp и вакансий его нет. Он бы помог посчитать уникальные вакансии и fssp компании, но вы сами должны понимать, что вы
наплодили кучу ненужных данных в виде декартова произведения четырех коллекций
Первый запрос неправильный. Так с one to many не работают - у вас телефоны умножатся на мыло, потом на fssp, а потом на вакансии, и вы потом будете думать, откуда там столько fssp и вакансий?
Дальнейшие запросы - это не оптимизация первого запроса, они совершенно от него отличаются - вы в них работу с one to many прячете в подзапросы, тем самым не давая данным разъезжаться, при этом второй запрос пердец какой безграмотный. Так же вы так и не избавились от дубликатов при работе с мылом и телефоном, просто вы их стыдливо спрятали под ковер дистинктом.
Нет нюанса. Наличие у шефа звезды в другом ресторане никак не влияет на получение звезды тем рестораном, где он ещё работает. Это все равно, что в команду НХЛ без кубков Стэнли перешёл обладатель кубка Стэнли и теперь команду следует называет немножко обладателем кубка Стэнли. Или если они выиграют кубок Стэнли, то надо цокать языком и говорить, что дядя Вася выиграл кубок Стэнли до этого, а что бы вы без него делали?
Чичваркин открыл ресторан и получил мишленовскую звезду. Это уже достижение.
Ещё в конце 80-ых читал в Технике Молодежи про Токамак, с тех пор несильно что изменилось :(
Для двух показаний - v1 и v2, оба integer not null для примера, postgresql
Почему же, предугадаешь. В моем шутливом примере, до которого некоторые начали докапываться с точки зрения производительности, хотя я сразу написал, что она там плохая и что это скорее забавный пример, сразу видно, что тут практически декартово произведение, в рамках департамента.
Хорошо, что вы не на месте интервьюера!
Зачем вложенные запросы, оконные функции и группировки? Давайте подойдем к задаче с точки зрения того, что SQL декларативный язык:
Такие сотрудники, что в их отделе нет никого с более высокой зп. Понятно, что не очень эффективно, но прикольно.
HttpServer.create(), пфффф
Можно было все завернуть в свою либу и обойтись одной строкой.
Первой "приставкой", на которой я играл, был Агат, а игры - xonix, саботаж, арканоид
Да, сам себя перехитрил. Тогда остается первый вариант :)
Почему? В вашем примере будет три записи, где следующий head_id не равен текущему или отсутствует.
Грубо говоря,
head_id | next_head_id | Not equals
1 1 F
1 2 T
2 2 F
2 2 F
2 3 T
3 NULL T
Считаем Not equals и получаем 3.
p.s. Можно было бы избавиться от суммы во внешнем подзапросе, но не хотелось два раза писать вызов lead или использовать какие-то магические числа для обозначения отсутствующей головы.
Уели!
Тогда так
Или даже так
Первую задачу можно сделать только с оконными функциями:
Относительно задачи 3: какой вопрос - такой ответ
Насчёт джоинов - это не объединение (union), а соединение. Это более сложная операция, у которой нет полноценных аналогов в диаграммах Венна. Я бы рассматривал inner join как декартово произведение всех записей из двух таблиц с дальнейшим отбрасыванием кортежей, не прошедших фильтрацию по условию соединения. Понятно, что сама СУБД работает по-другому, но именно для объяснения работы inner join-а имхо подойдёт. Тогда вам будет понятно, почему, например, джоиня юзеров с адресами, где на одного юзера может быть несколько адресов, вы будете получать несколько записей для некоторых юзеров.
В 96-ом нам преподавали C, в 24-ом я на нем пишу, и он нихрена не изменился с тех пор.
Еще лучше, чтобы условный сеньор был бы автором всех этих аббревиатур. Ну или хотя бы парочки...
А я думал, что это про llvm...
Так же делаете два подзапроса с группировкой по компании - один вернёт почту, другой телефоны. С группировками можно даже inner join-ами обойтись, поскольку записи всегда будут.
Первый запрос работает некорректно. Если у вас два телефона и три почты у компании, то это даст шесть записей. Даже если будет по одному делопроизводству и вакансии, то в итоге их будет по шесть в count-ах
Грубо говоря,
Телефоны
(c1, t1), (c1, t2)
Почта
(c1, m1), (c1, m2), (c1, m3)
Делопроизводства
(c1, d1)
Вакансии
(c1, v1), (c2, v2)
Где c1 - некая компания
Тогда (это не настоящий sql), company join phone join mail join fssp join vacancy where company.inn = 'c1' дам нам аж двенадцать записей. Дистинкт по телефонам и почте нам поможет, а для fssp и вакансий его нет. Он бы помог посчитать уникальные вакансии и fssp компании, но вы сами должны понимать, что вы
наплодили кучу ненужных данных в виде декартова произведения четырех коллекций
Заставили СУБД их обрабатывать
Первый запрос неправильный. Так с one to many не работают - у вас телефоны умножатся на мыло, потом на fssp, а потом на вакансии, и вы потом будете думать, откуда там столько fssp и вакансий?
Дальнейшие запросы - это не оптимизация первого запроса, они совершенно от него отличаются - вы в них работу с one to many прячете в подзапросы, тем самым не давая данным разъезжаться, при этом второй запрос пердец какой безграмотный. Так же вы так и не избавились от дубликатов при работе с мылом и телефоном, просто вы их стыдливо спрятали под ковер дистинктом.
Имхо, если у вас логика кочует из бэкенда во фронт, то вы что-то делаете не так.