Відповідь

[malyj_gorgan]

Відповідь до вчорашньої задачки. Основні проблеми виловив sassa_nf:

SELECT some_date, COUNT(DISTINCT x) as num_x, COUNT(DISTINCT z)
FROM t3
WHERE some_date > some_start_date AND some_date < some_end_date
  AND 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
ORDER BY num_x
LIMIT 10

UPDATE: Якщо кому не віриться, ось варіант з JOIN'ом, трохи симетричніший на вигляд, а виконується за той самий час або трохи довше, залежно від платформи:

SELECT some_date, COUNT(DISTINCT x) as num_x, COUNT(DISTINCT z)
FROM
 (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%')  
INNER JOIN
  (SELECT x,z,some_date
   FROM t3
   WHERE  some_date > some_start_date AND some_date < some_end_date) 
USING(x)
GROUP BY 1 ORDER BY 2 LIMIT 10


Але верхній варіант швидший... імхо

Comments

[ichthuss]

Хм, а where x in (select ...) працює швидше, ніж inner join? Інтуїтивно мені здавалось, що ймовірніше навпаки.

[malyj_gorgan]

Інтуїтивно мені теж так довший час здавалось, але практика (в Redshift і BigQuery) показує, що як мінімум однаково, а часом WHERE...IN... швидше, залежно від платформи і відносних розмірів таблиць. Системної освіти з цього діла у мене нема, так, нахапався верхами, плюс трошки досвіду, тому нижченаписане прошу сприймати як домисл:
WHERE .... IN ... переважно імплементується через створення хеш таблиці, тоді як JOIN або через неї, або через цикли, як де, тому часом оданково, а часом перше швидше. Ще там можуть різниці виникати за рахунок того, коли саме цю таблицю генерують.

[sassa_nf]

я не знаю, як БД влаштовані насправді, але з точки зору можливої реалізації - where x in (select ...) дає змогу обрубувати деякі обчислення в (select ...) - з контексту зрозуміло, що достатньо знайти один рядок, який задовольняє x in ... (неповний index scan), тоді як inner join вимагає щось робити із повторами (full table scan).

[ppk_ptichkin]

Ха, я ещё подумал, что там join лишние, наверное - но что я знаю..

[malyj_gorgan]

Так я ж кажу: common sense достатньо, щоби побачити основні проблеми

[juan_gandhi]

Да я как-то не верю, что это эквивалентно.

[malyj_gorgan]

Ну, не знаю як доказувати, але таки так :)
Я у свій час оптимізував readability, у мене було:
SELECT ...
FROM (SELECT x FROM t1 WHERE [t1 filters]) a
     INNER JOIN
     (SELECT x,z,some_date FROM t3 WHERE [t3 filters]) b
     ON a.x=b.x
GROUP BY ... ORDER ... LIMIT ...

Але варіант з "WHERE x IN ()" замість JOIN'а виглядає швидшим, хоч і не настільки симетричним :)

[sassa_nf]

я підходив до спрощення так: (правда, піддивившись, що інші кажуть, бо сам прогавив)

В оригіналі маємо count(distinct cte.x). Цей вираз означає, що cte поводиться як set of x і власне порядок і повтори рядків у cte не мають значення. Додаткові умови в group by та sort стосуються лише даних з t3 - повністю визначаються t3. Також, умови в where змушують cte left join t3 поводитись, як inner join (наприклад, lowercase(t3.x) like '....' не може бути задоволене для рядків left join, де t3.x is null).

Тому маємо право викинути сортування і left join з t2 у виразі, який утворює cte (left join не створює нові і не викидає рядки, які існують в t1), і cte left join t3 on cte.x=t3.x на where x in (select ...).

[malyj_gorgan]

Все так.
Цю задачку для інтерв'ю такі і писали: взяли нормальний СЕЛЕКТ і пододавали зайвого :)