Обычно ещё до написания запроса понятно, что в нём может пойти не так — это определяется условиями задачи, которую решает код. Например, нужно собрать историческую витрину со всеми действующими клиентами, которые провели хотя бы одну сделку за месяц, добавить туда полученный доход по всем сделкам в месяц отчёта и рассчитать над доходом некоторые метрики.
Это значит, что в каждый месяц запись о каждом уникальном клиенте должна встречаться только один раз, даты проведения сделок — попадать в этот месяц, а значение дохода должны быть заполнено значением, над которым можно провести арифметические вычисления. После этого нужно понять, по каким полям и на каком этапе расчёта можно проверить эти значения — если всё это записать, то получится план тестирования.
Полезно иметь эталонные значения для сравнения — таблицу, в которой точно учтены все нужные клиенты или хотя бы подсказку от бизнеса, что их примерно столько-то.
Для упрощения процесса стоит заранее придумать метрики качества запроса и значение, на которое допустимо от них отклониться. Метрика должна легко рассчитываться (иначе велик риск ошибиться) и иметь эталонное значение, с которым её можно сравнить.
Примеры метрик:
- Число строк (обычно его сравнивают с таблицей-эталоном);
- Число уникальных клиентов/карт/счетов/чего угодно;
- Сумма или среднее по числовому полю (например, оборот или остатки по счетам);
- Количество пустых или отличающихся от эталонной таблицы значений.
Формат тестовых запросов
Тестирование фрагментов кода удобно оборачивать в шаблоны запросов, которые возвращают метрику качества запроса или примеры строк с ошибками.
Примеры кода дальше будут всегда возвращать строки, не проходящие тест. Их можно превратить в запрос с метрикой качества, переписав по шаблону ниже:
Пример запроса, который вернёт метрику качества кода:
select source_cnt,
target_cnt -- также тут можно написать case, возвращающий нужный ответ, если метрики не совпали
from
(select count(*) as source_cnt --метрика эталонной таблицы
from source_table
where … ) a,
(select count(*) as target_cnt --метрика тестируемой таблицы
from target_table
where … ) b
;
Пример запроса, который вернёт строки, которых нет в эталонной таблице:
select
t1.key
from source_table t1
left join target_table t2
on t1.key = t2.keywhere t2.key is null
;
Проверка на дубли
Дубли — строки, в которых совпадают ключевые значения. Например, если в запросе собирается выборка открытых карт на определённую дату, дублями будут строки, в которых совпадает card_id и дата.
Дубли могут серьезно исказить вычисления, если соединить две таблицы по столбцам с дублями. Каждая дублирующаяся строка будет участвовать в вычислениях столько раз, сколько она появляется в результате соединения, что исказит итог вычисления.
Встречается мнение, что использование inner join убирает дубли, так как возвращает только совпадающие строки. Это не так — строки с дублирующимися ключами точно совпадают, следовательно inner join вернёт все возможные совпадения этих строк, что аналогично результату cross join. Подробнее и с примерами можно почитать тут.
Крайне нежелательно избавляться от дублей с помощью distinct — в этом случае в запросе будут рандомно отобраны первые строки по ключам, которые вернутся в процессе сканирования таблицы. Может быть, что в исходном запросе просто пропущены необходимые фильтры и дублирующиеся строки на самом деле отличаются друг от друга по смыслу — например, датой изменения версии записи.
Правильный подход — сначала выбрать несколько примеров дублей и изучить, какими не попавшими в запрос полями они отличаются, после чего добавить эти поля в условия фильтрации.
Пример запроса, который вернёт дублирующиеся строки:
-- это запрос можно выполнить сначала только по ключам, чтобы выявить дубли по ним
-- после можно вынести перед count(*) все подозрительные строки, чтобы найти по каким из них дубли будут различаться
select key_1
, key_2
, count(*)
from table_
group by key_1
, key_2
having count(*) > 1
;
Проверка на null-значения
Наличие null в данных может привести к неожиданным результатам вычислений или соединений:
- null не равен любому другому значению или другому null. Поэтому невозможно просто соединить две таблицы по столбцу, содержащему null и получить строки, где ключ = null;
- При выполнении арифметических операций достаточно одного null в столбце значений, чтобы вычисление вернуло в качестве результата null;
- В разных агрегирующих функциях null может учитываться или не учитываться в результате. Это нужно проверять в документации или на конкретном примере.
Подробнее про сложности с null
Проверка на null:
-- в postgresql count(*) или count(1) возвращает число строк в таблице, count() по конкретному столбцу - число строк, где значение не равно null
select count(*) all_cnt
, count(column_1) column_1_cnt
from table_
;
--запрос с value = null вернул бы 0, т.к. никакое значение не равно null
select count(*)
from table_
where value_ is null
;
-- если нужно посчитать строки, в которых поле соединения пустое
-- важный момент - значение, которым мы заменяет null должно быть такого же формата, как остальные значения в столбце
-- если бы ключ был числом - нужно подставить число, а не строку
select key_1
, key_2
from table_1
inner join table_2
on coalesce(key_1, ‘1’) = coalesce(key_2, ‘1’)
where key_1 is null
or key_2 is null
;
Проверка сходимости с источником/эталонной таблицей
Если есть источник, в котором хранятся достоверные данные, полезно проверить совпадение с ним. Какие конкретно проверки нужны, зависит от контекста задачи.
В примере приведены приемы, которые помогают сравнивать данные по содержанию, даже если записаны немного по-разному. Во всех случаях уместно сначала проверить наличие null и добавить их исключение, если это необходимо.
Проверка сходимости с источником:
select t1.key_1
, t1.value_1
, t2.key_2
, t2.value_2
from table_1 t1
join table_2 t2
on t1.key_1 = t2.key_2
where lower(t1.value_1) != lower(t2.value_2) -- на случай разных регистров
or substr(t1.mobile_phone, 2, 10) != t2.mobile_phone -- если в первой таблице мобильный формата 7912..., а во второй 912..
or date_trunc('day', t1.some_date) != date_trunc('day', t2.some_date) -- полезно, если в таблицах разная точность даты/времени
Проверка допустимых значений
Если таблица будет использоваться в дашборде или для расчётов над ней, бывает полезно убедиться, что все данные приведены к нужному формату и нет неожиданных значений. Тут тоже всё зависит от контекста, в примере ниже — иллюстрация самых распространённых проверок.
Проверка формата значений в запросе:
select key_
, value_
, date_
, string_
from table_
where value_ < 0 -- или любое другое сравнение в зависимости от бизнес-смысла данных
or date_ not between start_date and end_date
or string_ != 'needed_string' -- например, если нужна выборка по определенным названиям продуктов
or another_value not in
(select needed values
from target)
;
Частые ошибки
Некоторые операции требуют особой внимательности. Ниже неисчерпывающий список операций, в которых легко допустить ошибку:
- Отбор актуальных значений;
- Отбор по временным периодам и их пересечениям;
- Дедубликация;
- Вычисление сложных/составных метрик;
- Приведение типов в сочетании со сравнениями, соединениями и фильтрациями по изменённому полю;
- Арифметика с датами, сравнение дат, обрезка дат, извлечение частей даты, любые сложные модификации дат;
- Поиск по строке и подстроке, строковые функции, регулярные выражения;
- Join более 3-4 таблиц;
- Join по составному ключу из более 3-4 полей;
- Разные типы join в 1 запросе (особенно left и right);
- Join таблиц с разными типами хранения истории (примеры);
- Join с отношениями многие-ко-многим и один-ко-многим;
- Case when со сложной логикой;
- Оконные функции;
- Любые функции, которые используются в первый раз или редко.