Як писати SQL-запити швидше, зрозуміліше і з задоволенням — практики з GoogleSQL у BigQuery

Вітаю! Мене звати Іван, я два роки займаюсь аналітикою в TENTENS Tech.

Написання (і прочитання) запитів — невіддільна частина моєї роботи, яка відбирає чимало часу. Крім того, що запити мають бути правильні, потрібно писати їх:

• швидко;
• зрозуміло для себе самого;
• зрозуміло для колег.

І на таку базову процедуру варто максимально оптимізувати часові й когнітивні ресурси.

За два роки в TENTENS Tech я назбирав добру жменю фішок, які допомагають тримати цей баланс. У нас аналітики — не про «рахувати метрики», а про підтримку складних сценаріїв розвитку диджитал-продуктів зі складними бізнес-моделями. Ми використовуємо BigQuery як сховище даних, який, своєю чергою, використовує діалект GoogleSQL як свою мову запитів.

Я поділюся своїм списком корисних практик у GoogleSQL, що допоможуть писати запити швидше, зручніше та читабельніше. Стаття складається з трьох частин:

👇 А ви вже чули, що 21 червня DOU Mobile Day?

1. Синтаксичний цукор. Це — найбільший розділ, і тут багато практичної інформації про можливості коротше писати запит у різних місцях.
2. Усе в одній CTE. Тут ідеться про практики, які можуть стиснути запит за допомогою щільнішого використання різних частин запиту (select, from, join, where, group, having, window, qualify).
3. Різноманіття агрегатів. Короткий блок про список агрегатів, якими сам часто послуговуюся, але які, однак, не дуже поширені. Буде корисно про них дізнатися, щоб лаконічніше розв’язувати специфічні проблеми.

Синтаксичний цукор

У цьому блоці йтиметься про зручні «скорочення» в GoogleSQL. Особисто регулярно використовую їх, адже це суттєво пришвидшує написання запитів і покращує читабельність, — раджу і вам спробувати.

Те, що не можна не знати

Group by all

Дуже просто: замість звичних:

group by column_1, column_2, ...

group by 1,2,3, ...

Є змога використовувати:

group by all

Зручно, адже цей запит:

1. Коротший.
2. Швидше масштабується. Якщо потрібні додаткові розрізи, їх достатньо додати в select.
3. Прибирає зайве когнітивне навантаження із запиту. group by column_1, column_2, ... насправді не дає додаткової інформації, розуміння запиту. Адже будь-яка колонка має бути або згрупована, або агрегована — і зазвичай у select statement уже є вся ця інформація.

select
	column_1,
	column_2,
	sum(column_3)
	min(column_4),
from
	some_table
...

Тут немає group by statement, але зрозуміло, що має бути згруповано.

Кейс, де group by all не підійде: якщо потрібно агрегувати по колонці, якої немає в select.

Date and timestamp operations

Щоб працювати з timestamp або date, часто використовують функції:

date_add(date, interval)
timestamp_add(timestamp, interval)
date_sub(date, interval)
timestamp_sub(timestamp, interval)

Наприклад:

date_add("2030-01-01", interval 1 day)
timestamp_add("2030-01-01 00:00:00", interval 1 hour)
date_sub("2030-01-01", interval 1 day)
timestamp_sub("2030-01-01 00:00:00", interval 1 hour)

Замість префіксної нотації можна використовувати інфіксну (див. тут і тут):

"2030-01-01" + 1
"2030-01-01 00:00:00" + interval 1 hour
"2030-01-01" - 1
"2030-01-01 00:00:00" - interval 1 hour

... що точно звичніше для людського ока!

До дати можна додавати цілі числа та отримувати іншу дату. Буквально додаємо дні. До timestamp / datetime додаємо інтервали — й отримуємо інший timestamp / datetime.

Насправді й до дати можна додати інтервал:

"2030-01-01" + interval 1 hour -> "2030-01-01 01:00:00"

Але треба памʼятати, що на виході отримується datetime. І якщо, наприклад, додати такий вираз у фільтр:

where true
	and creation_time >= "2030-01-01" + interval 1 hour

Може не спрацювати партиціювання по колонці creation_time .

Concatenation operator

У GoogleSQL є функція, що склеює рядки:

concat(str1, str2)

Функція зручна, головне — памʼятати, що якщо потрібно склеїти три, чотири і т. д. рядків, у жодному разі це не має бути:

concat(concat(concat(str1, str2), str3), str4)

Записувати потрібно так:

concat(str1, str2, str3, str4)

До того ж є ще оператор конкатенації: ||.

З ним запис виглядав би ось так:

str1 || str2 || str3 || str4

Мені подобаються обидва варіанти (і concat, і ||) — і той, і той зручні.

Цікаве застосування! Оператором конкатенації можна швидко приводити різні типи даних до string .

cast(some_type as string) -> string field
some_type || '' -> string field

Те, що варто знати

Window

Віконні функції = функція + вікно.

function(column) over (some window specification)

Якщо в запиті багато віконок з одним і тим же вікном, його можна окремо описати й перевикористовувати:

select
	sum(column_0) over some_window,
	sum(column_0 + column_1) over some_window,
	min(column_0) over some_window,	
from
	some_table
window
	some_window as (
		partition by column_1
		order by column_2
		rows between 5 preceding and 5 following
	)

У моїй практиці траплялися запити, де в селекті було до 20 віконних функцій зі схожим вікном. Дуже допомагає: тут економія і місця, і когнітивного ресурсу, адже коли повертаєшся до запиту, то 20 однакових коротких слів простіше зісканувати очима, аніж 20 описів вікна. Завдяки цьому швидше розумієш, що відбувається в запиті.

Заувага: опис вікон відбувається в кінці запиту, навіть після qualify.

Like all, like any

Думаю, всі знають і хоч раз користувались оператором like. Іноді потрібно прописати декілька like та зʼєднати їх через and/or:

where true
	and string_column like '%this%'
	and string_column like '%that%'
	and string_column like '%something_else%'
	
--

where false
	or string_column like '%this%'
	or string_column like '%that%'
	or string_column like '%something_else%'

Такі записи можна значно скоротити за допомогою quantified like operator.

where true
	and string_column like all ('%this%', '%that%', '%something_else%')
	
--

where false
	or string_column like any ('%this%', '%that%', '%something_else%')

І є аналог з not:

where true
	and string_column not like '%this%'
	and string_column not like '%that%'
	and string_column not like '%something_else%'
-->
where true
	and string_column not like all ('%this%', '%that%', '%something_else%')

Головне — не сплутати.

not like all (...) VS not (like all (...))

Filter by vector

Нехай у нашій таблиці є два поля: column_a, column_b. Потрібно відфільтрувати пари цих значень:

(1,1), (1,2), (2,2), (3,3), (8,9)

Класично це можна було б реалізувати так:

where false
	or column_a = 1 and column_b = 1
	or column_a = 1 and column_b = 2
	or column_a = 2 and column_b = 2
	or column_a = 3 and column_b = 3
	or column_a = 8 and column_b = 9

Але в GoogleSQL є коротша форма запису такої умови:

where true
	and (column_a, column_b) in ((1,1), (1,2), (2,2), (3,3), (8,9))

Погляньте, скільки місця ми заощадили!

Те, що класно знати

Comma cross join, implicit unnest

Тут розкажу про дві фічі, які разом чудово покращують читабельність запиту.

Фіча перша — cross join. Досить коротко: замість cross join у запиті можна залишити просто кому («,»):

from
	table_a cross join table_b -> table_a, table_b

Перш ніж перейти до другої фічі, потрібне інтро. BigQuery дозволяє зберігати REPEATED поля, що по суті є масивами:

with

show_case_data as (
  select
    1 as id,
    [5,6] as ages -- <- repeated field of type array<int64>

  union all

  select
    2 as id,
    [7,8] as ages -- <- repeated field of type array<int64>
)

select
  *
from
  show_case_data

Це повертає нам таку табличку:

Для того, щоб працювати з масивами, є оператор unnest, що «розкриває» масив. І тепер для кожного елементу масиву виділений свій запис у фінальній таблиці:

with

show_case_data as (
  select
    1 as id,
    [5,6] as ages

  union all

  select
    2 as id,
    [7,8] as ages
)

select
  id,
  age
from
  show_case_data cross join unnest(ages) as age

Власне, друга фіча полягає в тому, що можна не писати unnest, а тільки мати його на увазі.

from
  show_case_data cross join unnest(ages) as age
|
V
from
  show_case_data s cross join s.ages as age

А якщо скласти фіча 1 + фіча 2, отримуємо зручний запис.

from
	show_case_data s, s.ages as age

Bool

Тут коротко хотів нагадати, що існує окремий тип даних — bool. Він компактний, і з ним можна працювати 🙂

Часто спостерігаю на різних ресурсах і в запитах колег, коли використовують 1 замість true і 0 замість false. І далі в такому ж дусі працюють з даними.

Ще болючіше бачити:

if(bool_field = true, this, that)
--
case
	when bool_field = true then this
	when bool_field = false then that
	else something_else
end
--
if(a > b, true, false)
--
sum(if(string_column = 'this', 1, 0))

Використовуйте bool як повноцінний тип даних — і запити стануть коротшими та зрозумілішими.

Як мали б виглядати вирази вище:

if(bool_field, this, that)
--
case
	when bool_field then this
	when not bool_field then that
	else something_else
end
--
infull(a > b, false)
--
countif(string_column = 'this')

Ще є ось такі цікаві властивості булевих операторів:

null and false -> false
null or true -> true

Усе в одній CTE

Тут ідеться про методи закласти багато логік в один select.

У прикладах таблиці з такими схемами:

dataset.payments_table: users' instances of payment
	- id_client: int64
	- creation_time: timestamp
	- payment_size: int64
	
dataset.users_table: users data
	- id_client: int64
	- creation_time: timestamp

Advanced left join

У join можна прописувати додаткові логіки, виносячи їх з where. Його переваги:

1. Рятує від неявного inner join!
2. Логіки окремих таблиць винесені в окремі join, а не все разом у where.
3. Дозволяє реалізовувати складні логіки простими словами.

Приклад 1. Рахуємо кількість оплат, створених після 1 лютого 2025 року, усіх користувачів, які зареєструвалися після 1 січня 2025-го.

Можемо так:

with

payments as (
	select
		id_client,
		count(*) payments_count
	from
		dataset.payments_table
	where true
		and date(creation_time) >= "2025-02-01" 
	group by all
)

select
	id_client,
	payments_count,
from
	dataset.users_table
	left join payment
		using(id_client)
where true
	and date(creation_time) >= "2025-01-01"

А можемо й так:

select
	u.id_client,
	count(*) payments_count,
from
	dataset.users_table u
	left join dataset.payments_table p
		on u.id_client = p.id_client
		and date(p.creation_time) >= "2025-02-01"
where true
	and date(u.creation_time) >= "2025-01-01"

Зауваження 1.1. Це не єдиний спосіб вирішення проблеми.

Зауваження 1.2. date(p.creation_time) >= «2025-02-01» не можна виносити у where, бо отримаємо inner join замість бажаного left join.

Приклад 2. Щодо складних логік, ось як можна порахувати по користувачу на визначений LT кількість і суму оплат:

select
	u.id_client,
	lt,
	count(*) payments_count,
	sum(payment_size) payments_size
from
	dataset.users_table u, unnest([1,3,5,7]) as lt
	left join dataset.payments_table p
		on u.id_client = p.id_client
		and p.creation_time < u.creation_time + interval lt + 1 day
where true
	and date(u.creation_time) >= "2025-01-01"

Структура запиту і все, що можна використати в одному select

Витримка з документації BigQuery:

Отже, в одному select можна реалізувати одразу безліч логік.

Те, на чому хотів наголосити і що найрідше бачу: групування + віконні функції. Так, їх можна використовувати разом. Спочатку відбувається агрегування, а далі за групованими та агрегованими полями можна рахувати віконні функції.

Приклад 1. Є таблиця з ключем по полю id, але з 15 січня 2025 року зʼявилися технічні негаразди. Хочемо оцінити масштаб проблеми. Потрібно визначити, скільки рядків і яку кількість дублікатів мають:

select
	count(*) number_of_id_duplicates,
	count(*) over (partition by count(*)) number_of_ids_effected
from
	dataset.some_table
where true
	and date(creation_time) >= "2025-01-15"
group by id
having true
	and instances_of_id > 1

На виході отримаємо дані у вигляді: маємо 100 id з двома дублікатами, 57 — з трьома, 26 — з чотирма і т. д.

Приклад 2. Хочемо глянути на топ користувачів. Витягнути 100 найкативніших користувачів з виторгом і порядковим номером.

select
	id_client,
	sum(revenue) revenue,
	row_number() over (order by sum(revenue) desc) top_payer_number,
from
	dataset.users_table
	left join dataset.payments_table
		using(id_client)
group by all
qualify true
	and top_payer_number <= 100

Різноманіття агрегатів

У GoogleSQL є неймовірна кількість найрізноманітніших незвичайних агрегатів. Повний список можна переглянути за посиланням. Дуже раджу ознайомитися з усіма — будь-яка з них може стати корисною в певних умовах.

Даю перелік незвичайних агрегуючих функцій, які найбільше використовую.

• max_by / min_by. Повертає найбільше / найменше значення (перший аргумент) за ключем (другий аргумент).

with

show_case_data as (
	select 1 as id, 'green' as color
	union all
	select 2 as id, 'yellow' as color
)

select
	min_by(color, id), -- -> 'green'
	max_by(color, id), -- -> 'yellow'
from
	show_case_data

• logical_or / logical_and. Повертає логічне or/and за всіма записами булевої колонки.

with

show_case_data as (
	select 1 as id, true as is_good
	union all
	select 2 as id, false as is_good
	union all
	select 3 as id, false as is_good
)

select
	logical_or(is_good), -- -> true
	logical_and(is_good), -- -> false
	logical_and(id > 0), -- -> true,
	logical_or(mod(id, 4) = 0), -- -> false
from
	show_case_data

• countif. Корисна функція, коли потрібно порахувати кількість «чогось» у колонці.

with

show_case_data as (
	select 1 as id, true as is_good
	union all
	select 2 as id, false as is_good
	union all
	select 3 as id, false as is_good
)

select
	countif(is_good), -- -> 1
	countif(id > 5), -- -> 0
	countif(mod(id - cast(is_good as int64), 2) = 0) -- -> 2
from
	show_case_data

Outro

Google SQL містить різні фічі, які можуть класно прокачати ваші SQL-запити. У цій статті я зібрав лише деякі, найкорисніші для мене практики.

Спробуйте використати ці фішки — і ваші запити стануть лаконічнішими, зрозумілішими й писати їх стане приємніше.

І, звичайно, Google SQL постійно поповнюється новими можливостями. Стежити за апдейтами можна в BQ release notes.

Підписуйтеся на Telegram-канал «DOU #tech», щоб не пропустити нові технічні статті