ще одна задачка
Mar. 19th, 2021 03:44 pm![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
malyj_gorganЯ тут пару разів писав всякі співбесідні штучки, і вони, o tempora, o, mores, викликали більше інтересу ніж майже будь яка інша тема, крім холіварів про дискримінацію і здорогвий глузд. Що ж, будемо популістами, додаю теґ про співбесіди і пощу ще одну задачку з минулорічних. На неї давали хвилин 5, ну, 10, якщо переписувати код, а не лише критикувати. А потім мені сказали, що лише двоє кандидатів нормально відповіли. (Нас і найняли, гиги.) Враховуючи, що це найперше завдання в довгому процесі відсіяння, навіть цікаво, мо, справді, хороша задачка?Enjoy, значиться...
Let’s say you’re running a query that looks like this:
WITH cte AS (
SELECT DISTINCT t1.x, t2.abc, t2.bcd, t2.cde,t2.def
FROM t1 LEFT JOIN t2 ON t1.x = t2.x
WHERE t1.y LIKE 'some_string%' AND SUBSTRING(t1.y, 1, 4) = 'some'
ORDER BY t1.x ASC, t2.abc DESC
)
SELECT COUNT(DISTINCT cte.x) AS num_x,
t3.some_date,
COUNT(DISTINCT t3.z)
FROM cte LEFT JOIN t3 ON cte.x = t3.x
WHERE LOWERCASE(t3.x) LIKE '%some_other_string%'
AND LOWERCASE(cte.x) NOT LIKE '%yet_another_string%'
GROUP BY t3.some_date
HAVING t3.some_date > some_start_date AND t3.some_date < some_end_date
ORDER BY num_x ASC
LIMIT 10
It is taking a really long time, and showing no signs of finishing even after you took a lunch break. You realize that t1, t2 and t3 all have several billion rows each. What would you potentially change to speed up the query, and why?
Let’s say you’re running a query that looks like this:
WITH cte AS (
SELECT DISTINCT t1.x, t2.abc, t2.bcd, t2.cde,t2.def
FROM t1 LEFT JOIN t2 ON t1.x = t2.x
WHERE t1.y LIKE 'some_string%' AND SUBSTRING(t1.y, 1, 4) = 'some'
ORDER BY t1.x ASC, t2.abc DESC
)
SELECT COUNT(DISTINCT cte.x) AS num_x,
t3.some_date,
COUNT(DISTINCT t3.z)
FROM cte LEFT JOIN t3 ON cte.x = t3.x
WHERE LOWERCASE(t3.x) LIKE '%some_other_string%'
AND LOWERCASE(cte.x) NOT LIKE '%yet_another_string%'
GROUP BY t3.some_date
HAVING t3.some_date > some_start_date AND t3.some_date < some_end_date
ORDER BY num_x ASC
LIMIT 10
It is taking a really long time, and showing no signs of finishing even after you took a lunch break. You realize that t1, t2 and t3 all have several billion rows each. What would you potentially change to speed up the query, and why?
no subject
Date: 2021-03-19 11:32 pm (UTC)Мені співбесіду якось розпочали з питання, що таке змінна, і кількох завдань на логіку дитячого рівня. На моє здивування відповіли, що хочуть подивитися, як людина мислить.
no subject
Date: 2021-03-19 11:46 pm (UTC)А тут питання чисто на SQL, робота не програмістом, а датазнавцем (Data Scientist), там майже всюди це основне знаряддя праці.
Питання якраз не дуже легке, прочитати чужий код і сказати, де там лажа, для людини, яка лише почала це все вчити, буде нелегко. Це якраз тест на те, чи кандидат реально має той рік чи більше досвіду користування, який обіцяє в своєму резюме.
no subject
Date: 2021-03-20 12:01 am (UTC)Как будет по украински "человек, специализирующийся в накручивании хвостов
быкамбазам данных"?no subject
Date: 2021-03-20 12:16 am (UTC)А якщо Data Scientіst, то це лише один з необхідних параметрів для роботи. Може 10% обходиться без цього діла, 20% від сили.
Це як для програміста, який щось робить в мережах, знати базовий синтакс Unix shell'а (а Redshift буде аналогом bash'а)
no subject
Date: 2021-03-20 02:22 am (UTC)(из любопытства)
Я SQL не знаю совсем, as in "совсем", но это?
>WHERE t1.y LIKE 'some_string%' AND SUBSTRING(t1.y, 1, 4) = 'some'
и наверное ещё много, но см. выше.
(no subject)
From:(no subject)
From:no subject
Date: 2021-03-20 12:02 am (UTC)no subject
Date: 2021-03-20 12:16 am (UTC)no subject
Date: 2021-03-20 01:48 am (UTC)(no subject)
From:(no subject)
From:(no subject)
From:(no subject)
From:(no subject)
From:no subject
Date: 2021-03-20 03:48 am (UTC)(no subject)
From:(no subject)
From:(no subject)
From:(no subject)
From:(no subject)
From:(no subject)
From:(no subject)
From:(no subject)
From:(no subject)
From:(no subject)
From:(no subject)
From:(no subject)
From:no subject
Date: 2021-03-19 11:51 pm (UTC)no subject
Date: 2021-03-19 11:57 pm (UTC)Очікуваний час роботи кандидатові кажуть для його, кандидата, користі, щоби дати зрозуміти, що "якщо у мене на таке іде не 10 хвилин, а півдня, то ця позиція, можливо, не для мене"
no subject
Date: 2021-03-20 01:42 am (UTC)no subject
Date: 2021-03-20 01:51 am (UTC)(no subject)
From:(no subject)
From:(no subject)
From:(no subject)
From:(no subject)
From:(no subject)
From:(no subject)
From:(no subject)
From:no subject
Date: 2021-03-20 01:04 am (UTC)Хотя задача и похоже на реализм, я б держался как можно дальше от контор, где возникают подобного рода задачи. Или начал бы с вопросов, на хрена такой cte вообще. Но задача реальная, да.
no subject
Date: 2021-03-20 01:49 am (UTC)no subject
Date: 2021-03-20 02:55 am (UTC)Ну, в реале размер этих кверей нужно умножить на 10, если не на 100.
no subject
Date: 2021-03-20 05:09 am (UTC)У нас один новий клієнт генерує для нас в середньому 270 гігабайтів даних на секунду. І це вже пару місяців. Я до того масиву поки що боюся підступатися.
no subject
Date: 2021-03-20 07:10 am (UTC)оскільки всі strings фіксовані, створити індексовану колонку, яку заповнювати при заповненні таблиці. ну, власне, можна і пост-фактом це зробити, але я не уявляю, чи швидше це буде (сучасні БД багато таких питань вирішують без участі лохів від архітектури своїх даних).
сучасні БД дозволяють створювати індекси навіть по текстових колонках для пошуку на зразок like. (нещодавно якраз дивились на функцію CONTAINS() - щоправда, у всіх БД свої назви, реалізації та нюанси)
no subject
Date: 2021-03-20 07:52 pm (UTC)some_date,
count(distinct z)
from t3
where x in (select x from t1 where substring(y, 1, 11) = 'some_string' and lowercase(x) like '%some_other_string%' and lowercase(x) not like '%yet_another_string%')
group by some_date.... (etc)
no subject
Date: 2021-03-20 08:23 pm (UTC)Я розумію, що про якісь з дрібніших речей, типу заміни JOIN'a на WHERE x IN (...) можна не подумати на перший погляд, або, там сперечатися чи перший substring() можна залишити LIKE'ом (обидва ходи вірні і потрібні, але не аж так сильно впливають на результат). Але все інше людина, зяка зацікавлена в позиції і, не бреше в резюме про досвід роботи з великими табличним базами даних, повинна побачити.
no subject
Date: 2021-03-20 10:22 pm (UTC)Я це, звичайно, цілий день ходив, обмірковував. Нарешті спробував з'ясувати, що саме там малось на увазі. Не просто, якщо не знаєш схеми - хто там ключі, а хто foreign key (а може й зовсім не key ні в t1, ні в t2). І чи не швидше було б, скажімо, спочатку t3 профільтрувати по датах, а вже серед них шукати підходящі x. Бо є різниця - чи то про однакові x створюються нові рядки на щодень (тоді краще фільтрувати підходящі x), чи то щодня створюються нові x (тоді краще спочатку відсіяти по датах).
no subject
Date: 2021-03-20 10:28 pm (UTC)Переважно, так. Можна придумати якісь граничні випадки, коли x корелюють з часом хитрим способом або (SELECT .. FROM t1 ..) повертає дуже маленьку кількість іксів, але, в принципі, прямий часовий фільтр спочатку завжди кращий, а вже потім перевіряти, що залишиться на членство в зовнішній множині.
(no subject)
From:(no subject)
From:no subject
Date: 2021-03-20 09:34 pm (UTC)no subject
Date: 2021-03-20 10:16 pm (UTC)no subject
Date: 2021-03-21 01:50 am (UTC)(no subject)
From: