И как их использовать
Как давний пользователь SQL, я всегда ищу способы упростить анализ данных с помощью SQL. В предыдущей статье я рассмотрел 6 функций BigQuery SQL, о которых хотел бы знать раньше, а сегодня я хочу поделиться еще 4 функциями, которые, надеюсь, окажутся вам полезными.
1. PERCENTILE_CONT
PERCENTILE_CONT вычисляет процентиль из столбца значений. В BigQuery нет функции MEDIAN, но вы можете использовать PERCENTILE_CONT для вычисления медианы, поскольку она эквивалентна 50-му процентилю. Вычисление медианы и процентилей полезно для получения представления о распределении и определения выбросов, которые могут повлиять на ваш анализ.
В приведенном ниже примере у меня есть 6 чисел ( 1 , 3 , 5 , 8 , 10 и 1000 ) в массиве, который расширен в строки с помощью функции UNNEST. В строке 4 вычисляется медиана с 0,5 в качестве аргумента, указывающего 50-й процентиль, а в строке 5 используется 0,95 для расчета 95-го процентиля. Обратите внимание, что результаты показывают, что 95-й процентиль равен 752, тогда как 25-й процентиль равен 3,5, а медиана равно 6,5. Это указывает на то, что выбросы, возможно, потребуется удалить для анализа, поскольку различия очень велики.
2. СЧЁТЕСЛИ
Функция СЧЁТЕСЛИ считает значение, если оно удовлетворяет условию. Это полезно для получения счетчиков из таблицы с различными условиями без необходимости выполнения нескольких запросов SQL.
В приведенном ниже примере, чтобы получить количество отрицательных и положительных чисел, я мог запустить запрос дважды с двумя разными условиями в строках 3 и 7.
Однако с помощью COUNTIF я могу выполнить этот запрос один раз, чтобы получить количество как положительных, так и отрицательных чисел в строках 1 и 2.
3. IF
ЕСЛИ является альтернативой СЛУЧАЮ, если вам нужно только оценить, является ли условие истинным или ложным. В строке 10, если A меньше B и A больше 0, тогда для столбца result_from_if устанавливается значение true, в противном случае для result_from_if устанавливается значение ложь. В строке 11 CASE используется для получения того же результата, но это более длинное выражение по сравнению с использованием функции ЕСЛИ.
Примечание. Вы можете комбинировать COUNT и IF с DISTINCT, чтобы получить количество различных значений, соответствующих условию. В приведенном ниже примере только положительные числа ( 1 , 3 и 4 ) учитываются в DISTINCT, возвращающем количество 3.
4. ОШИБКА
ОШИБКА полезна, чтобы предупредить вас о неожиданных значениях в данных. В приведенном ниже примере функция ERROR используется в строке 4, когда CASE встречает значение, отличное от cat или dog. Это полезно для устранения неполадок, особенно в конвейерах ETL, где вы можете захотеть, чтобы SQL завершился ошибкой, если в столбце есть непредвиденные значения, требующие изучения.
Последние мысли
Хотя функции, которые я упомянул, доступны в BigQuery, они также могут быть доступны в других базах данных, но с другими именами. Например, COUNTIF равен COUNT_IF в Snowflake. Если у вас есть несколько минут, я настоятельно рекомендую прочитать вашу документацию по базе данных, потому что вы никогда не знаете, какие полезные функции вы можете найти.
Примечание. Все приведенные выше запросы выполнялись в песочнице BigQuery, которая бесплатна для всех, у кого есть аккаунт Google.
