Использование регулярных выражений для выбора определенных ячеек таблицы используется в SQL не так часто, как могло бы. И очень зря — этот инструмент легко позволяет найти в таблице нужные значения, так как использует шаблон для поиска последовательности метасимволов в тексте. Такие задачи встречаются как в различных тренажерах или на собеседованиях на позиции аналитика, так и в реальной практике аналитиков, которые работают в базах SQL. Подобные шаблоны для поиска определенных элементов и последовательностей в тексте используются в самых разных областях. Например, на многих сайтах существует проверка email-адреса, который вы вводите при регистрации, на соответствие стандартному шаблону.

Как это сделать, мы разберёмся постепенно, а пока давайте начнем с самого начала: с определения.

Что такое «регулярное выражение»?
Регулярное выражение — последовательность букв и/или символов, которая может встречаться в слове. Например, есть достаточно простое регулярное выражение “bat”. Оно читается как буква b, за которой следует буква a и t, и этому шаблону соответствуют такие слова, как, bat, combat и batalion.
Давайте разберем несколько типовых задачек, чтобы вам было понятнее, как правильно работать с регулярными выражениями в SQL. Для решения всех задач, которые мы сегодня рассмотрим, мы будем использовать функцию regexp_matches(), которая будет сравнивать значения в ячейках с шаблоном, который задается внутри этой функции.

Количество гласных букв в выражении

Итак, предположим, вам нужно посчитать количество гласных букв в каждой ячейке определенного столбца таблицы. Именно для такой задачи и нужны регулярные выражения. Код, который приведен ниже (вы можете легко его прогнать в своем SQL), на простом примере показывает, как легко решить эту задачу. В результате, мы получаем еще одну колонку Count, в которой хранится искомая информация.

with example_table as (select * from (values (1, 'google'), (2, 'yahoo'), (3, 'bing'), (4, 'rambler')) 
as map(id, source_type))

select source_type, count(1) from (
select *, regexp_matches(source_type,'([aeiou])','g') as pattern from example_table ) as t
group by source_type

Количество согласных букв в выражении

Если мы хотим решить обратную задачу, то можно подойти к решению двумя способами. Первый способ — аналогично предыдущему можно перечислить все согласные буквы английского алфавита в квадратных скобках. Но почему бы не решить задачу элегантнее? Для этого есть второй способ — использовать отрицание, то есть посчитать количество всех букв, которые не являются гласными. Для этого используется оператор ^.

with example_table as (select * from (values (1, 'google'), (2, 'yahoo'), (3, 'bing'), (4, 'rambler')) 
as map(id, source_type))

select source_type, count(1) from (
select *, regexp_matches(source_type,'([^aeiou])','g') as pattern from example_table ) as t
group by source_type

Количество цифр в выражении равно 3

Если нужно найти конкретное число определенных символов в выражении, то в конце запроса нужно указать оператор HAVING.

with example_table as (select * from (values (1, '1a2s3d'), (2, 'qw12e'), (3, 'q56we1651qwe'), (4, 'qw4e2')) 
as map(id, source_type))

select source_type, COUNT(*) from (
select *, regexp_matches(source_type,'\d','g') as pattern from example_table ) as t
GROUP BY source_type
HAVING COUNT(*) = 3

В номере телефона есть два дефиса

Теперь давайте перейдем к более конкретным запросам, которые могут пригодиться в реальной практике. Например, у аналитика может стоять задача найти все номера телефона, в которых присутствует два или более дефисов.
В первом блоке кода мы создаем тестовую таблицу, затем считаем количество дефисов в каждой ячейке (ячейки без дефисов не включаются в финальную таблицу), а после этого проставляем значения True/False относительно условия на количество дефисов. Сделать это можно с помощью оператора CASE WHEN COUNT ().

with example_table as (
  select * from (
    values 
    (1, '8931-123-456'), 
    (2, '8931123-456'), 
    (3, '+7812123456'), 
    (4, '8-931-123-42-24')
  )
as map(id, source_type))

select source_type, CASE WHEN COUNT(1) >= 2 THEN 'True' ELSE 'False' END from (
select *, regexp_matches(source_type,'-','g') as pattern from example_table ) as t
GROUP BY 1

Все имена, которые написаны с большой буквы

Тут мы уже приступаем к задаче посложнее: нужно найти имена людей, которые написаны с заглавной буквы со всем датасете. Для этого нам нужно найти все значения, подходящие под заданный шаблон: первая буква слова — заглавная.

with example_table as (
  select * from (
    values 
    (1, 'alex'), 
    (2, 'Alex'), 
    (3, 'Vasya'), 
    (4, 'petya')
  )
as map(id, source_type))

select source_type from (
select *, regexp_matches(source_type,'^[A-Z]','g') as pattern from example_table ) as t
GROUP BY 1

Вывести номера телефонов, которые попадают под паттерн +71234564578

Последней задачей мы разберем поиск телефонных номеров в списке. Для этого нам нужно найти те значения, которые начинаются со знака “+”, затем идет цифра 7 и 10 любых цифр после этого.

with example_table as (
  select * from (
    values 
    (1, '+7(931)1234546'), 
    (2, '+79312991809'), 
    (3, '89311234565'), 
    (4, '244-02-38')
  )
as map(id, source_type))

select source_type from (
select *, regexp_matches(source_type,'^\+7[0-9]{10}','g') as pattern from example_table ) as t
GROUP BY 1

Вывести все настоящие email-адреса

Как мы говорили в начале, регулярные выражения могут использоваться для таких задач как поиск сложных выражений по определённому шаблону. На самом деле, ничего особенного в такой задаче нет — главное, грамотно сформировать шаблон выражения и дело в шляпе!

with example_table as (
    select * from (
    values
        (1, 'email.asd@ya.ru'),
        (2, 'something@new.ru'),
        (3, '@ya.ru'),
        (4, 'asdasd'),
        (5, '_asdasdasd@mail.ru'),
        (6, 'asd_asdas@mail.ru'),
        (7, '.asdasd@mail.ru'),
        (8, '007asd@email.com')
        ) as map(id, source_type)
)
​
select source_type 
from (
    select source_type, regexp_matches(source_type, '^[^_.0-9][a-z0-9._]+@[a-z]+\.[a-z]+$')
    from example_table ) as t

Использование регулярных выражений может помочь легко и просто решить достаточно трудные задачи. Пишите в комментариях, если у вас есть какая-то задача по поиску определенных шаблонов в тексте, которая вам никак не дается. Попробуем решить её вместе!

Расчет накопленной (или кумулятивной, что то же самое) суммы SQL — это очень распространенный запрос, который часто используют в анализе финансов, динамики прибыли и прочих показателей компании. В сегодняшней статье вы узнаете, что такое накопленная сумма и как можно написать SQL-запрос для ее вычисления.

Если вы вдруг являетесь начинающим пользователем SQL, то давайте, как в школьной задаче, поймем, что нам дано и что нам необходимо найти. Накопленная сумма — это совокупная сумма предыдущих чисел в столбце. Давайте посмотрим на пример ниже, чтобы точно знать, какой результат мы ожидаем увидеть в итоге. Итак, существует таблица leftjoin.daily_sales_sample, в которой есть всего два столбца date и revenue. По столбцу revenue нам нужно рассчитать накопленную сумму и записать результат в отдельный столбец.

Что у нас есть?

Что мы хотим найти?

На графике две этих переменных выглядят следующим образом:

Итак, без лишних слов, давайте приступать к решению задачи.

Способ 1 — Идеальный — Используем оконные функции

Итак, если в базе данных можно пользоваться оконными функциями, то жизнь хороша и прекрасна. С их помощью можно написать простой запрос, который будет суммировать значения из столбца revenue по мере увеличения даты и сразу вернет нам таблицу с кумулятивной суммой в столбце, который мы назвали total.

SELECT
	date,
	revenue,
	SUM(revenue) OVER (ORDER BY date asc) as total
FROM leftjoin.daily_sales_sample 
ORDER BY date;

Способ 2 — Хитрый — Решение без оконных функций

Вполне возможно, что вам понадобится решить такую задачу без использования оконных функций. К примеру, если вы используете MySQL (до 8 версии) или любую другую БД, в которой оконных функций нет. Тогда решение задачи чуть усложняется. Однако, вы ведь знаете, что нет ничего невозможного?
Чтобы провернуть все то же самое без оконных функций, нужно использовать INNER JOIN для присоединения таблицы к себе самой. Так, к каждой строке таблицы мы присоединяем строки, которые соответствуют всем предыдущим датам до текущей даты включительно. В нашем примере, для 10 ноября — 10 ноября, для 11 ноября — 10 и 11 ноября и так далее. Промежуточный запрос будет выглядеть вот так:

SELECT * 
FROM leftjoin.daily_sales_sample ds1 
INNER JOIN leftjoin.daily_sales_sample ds2 on ds1.date>=ds2.date
ORDER BY ds1.date, ds2.date;

А его результат:

А затем, нужно просуммировать прибыли, группируя их по каждой дате. Если собрать все в единый запрос, то он будет выглядеть вот так:

SELECT
	ds1.date,
	ds1.revenue,
	SUM(ds2.revenue) as total
FROM leftjoin.daily_sales_sample ds1 
INNER JOIN leftjoin.daily_sales_sample ds2 on ds1.date>=ds2.date
GROUP BY ds1.date, ds1.revenue
ORDER BY ds1.date;

Способ 3 — Специфический — Решение с помощью массивов в ClickHouse

Если вы используете Clickhouse, то в этой системе есть специальная функция, которая может помочь рассчитать кумулятивную сумму. Для начала, нам нужно преобразовать все столбцы таблицы в массивы и рассчитать показатель «Moving Sum» для столбца revenue.

SELECT groupArray(date) dates, groupArray(revenue) as revs, 
groupArrayMovingSum(revenue) AS total
FROM (SELECT date, revenue FROM leftjoin.daily_sales_sample
	  ORDER BY date)

Спасибо Дмитрию Титову из Altinity за комментарий про сортировку в подзапросе

Так, мы получим три массива значений:

Но три массива, которые записаны в ячейки — это не то, что мы хотим получить, хотя значения этих массивов уже абсолютно соответствуют искомому результату. Теперь массивы нужно привести обратно к табличному виду с помощью функции ARRAY JOIN.

SELECT dates, revs, total FROM
(SELECT groupArray(date) dates, groupArray(revenue) as revs, 
groupArrayMovingSum(revenue) AS total
FROM (SELECT date, revenue FROM leftjoin.daily_sales_sample
	  ORDER BY date)) as t
ARRAY JOIN dates, revs, total;

Бонус — Оконные функции в Clickhouse

Если вам не хочется иметь дело с массивами, что иногда и правда бывает затратно по времени, то есть еще один вариант решения задачи. Можно использовать оконные функции, например функцию runningAccumulate(), которая суммирует значения всех ячеек с первой до текущей.

SELECT date, runningAccumulate(revenue)
  FROM 
  (
    SELECT date, sumState(revenue) AS revenue
    FROM leftjoin.daily_sales_sample
    GROUP BY date 
    ORDER BY date ASC
  )
ORDER BY date

Если вы столкнетесь с необходимостью рассчитать кумулятивную сумму в SQL, то теперь вы сможете решить эту задачу, в какой бы системе управления баз данных ни была организована работа :)

У большинства игровых и мобильных компаний имеется кривая Retention, ранее мы писали о том, что такое Retention и как его посчитать. Вкратце — это метрика, которая позволяет понять насколько хорошо продукт вовлекает пользователей в ежедневное использование. А ещё при помощи Retention и ARPDAU можно посчитать LTV (Lifetime Value), пожизненный доход с одного пользователя. Зная средний доход с пользователя за день и кривую Retention мы можем смоделировать ее и спрогнозировать LTV.

Для материала были взяты данные из одного реального игрового проекта. Нулевой день не отображен для того, чтобы видеть динамику в деталях

В сегодняшнем материале мы подробно разберём, как смоделировать LTV для 180 дней при помощи SQL и просто линейной регрессии.

Как посчитать LTV?

В общем случае формула LTV выглядит как ARPDAU умноженное на Lifetime — время жизни пользователя в проекте.

Посмотрим на классический график Retention:

Lifetime — это площадь фигуры под Retention:

Откуда взялись интегралы и площади можно подробнее узнать в этом материале

Значит, чтобы посчитать Lifetime, нужно взять интеграл от функции удержания по времени. Формула приобретает следующий вид:

Для описания кривой Retention лучше всего подходит степенная функция a*x^b. Вот как она выглядит в сравнении с кривой Retention:

При этом x — номер дня, a и b — параметры функции, которую мы построим при помощи линейной регрессии. Регрессия появилась неслучайно — эту степенную функцию можно привести к виду линейной функции, логарифмируя:

ln(a) — intercept, b — slope. Остаётся найти эти параметры — в линейной регрессии для этого используют метод наименьших квадратов. Lifetime — кумулятивная сумма прогноза за 180 дней. Посчитав её, остаётся умножить Lifetime на ARPDAU и получим LTV за 180 дней.

Строим LTV

Перейдём к практике. Для всех расчётов мы использовали данные одной игровой компании и СУБД PostgreSQL — в ней уже реализованы функции поиска параметров для линейной регрессии. Начнём с построения Retention: соберём общее количество пользователей в период с 1 марта по 1 апреля 2021 года — мы изучаем активность за один месяц:

--общее количество юзеров в когорте
with cohort as (
    select count(distinct id) as total_users_of_cohort
    from users
    where date(registration) between date '2021-03-01' and date '2021-03-30'
),

Теперь посмотрим, как ведут себя эти пользователи в последующие 90 дней:

--количество активных юзеров на 1ый день, 2ой, 3ий и тд. из когорты
active_users as (
    select date_part('day', activity.date - users.registration) as activity_day, 
               count(distinct users.id) as active_users_of_day
    from activity
    join users on activity.user_id = users.id
    where date(registration) between date '2021-03-01' and date '2021-03-30' 
    group by 1
    having date_part('day', activity.date - users.registration) between 1 and 90 --берем только первые 90 дней, остальные дни предсказываем.
),

Кривая Retention — отношение количества активных пользователей к размеру когорты текущего дня. В нашем случае она выглядит так:

По данным кривой посчитаем параметры для линейной регрессии. regr_slope(x, y) — функция для вычисления наклона регрессии, regr_intercept(x, y) — функция для вычисления перехвата по оси Y. Эти функции являются стандартными агрегатными функциями в PostgreSQL и для известных X и Y по методу наименьших квадратов.

Вернёмся к нашей формуле — мы получили линейное уравнение, и хотим найти коэффициенты линейной регрессии. Перехват по оси Y и коэффициент наклона можем найти по дефолтным для PostgreSQL функциям. Получается:

Подробнее о том, как работают функции intercept(x, y) и slope(x, y) можно почитать в этом мануале

Из свойства натурального логарифма следует, что:

Наклон считаем аналогичным образом:

Эти же вычисления запишем в подзапрос для расчёта коэффициентов регрессии:

--рассчитываем коэффициенты регрессии
coef as (
    select exp(regr_intercept(ln(activity), ln(activity_day))) as a, 
                regr_slope(ln(activity), ln(activity_day)) as b
    from(
                select activity_day,
                            active_users_of_day::real / total_users_of_cohort as activity
                from active_users 
                cross join cohort order by activity_day 
            )
),

И получим прогноз на 180 дней, подставив параметры в степенную функцию, описанную ранее. Заодно посчитаем Lifetime — кумулятивную сумму спрогнозированных данных. В подзапросе coef мы получим только два числа — параметр наклона и перехвата. Чтобы эти параметры были доступны каждой строке подзапроса lt, делаем cross join к coef:

lt as(
    select generate_series as activity_day,
               active_users_of_day::real/total_users_of_cohort as real_data,
               a*power(generate_series,b) as pred_data, 	 
               sum(a*power(generate_series,b)) over(order by generate_series) as cumulative_lt
    from generate_series(1,180,1)
    cross join coef
    join active_users on generate_series = activity_day::int
),

Сравним прогноз на 180 дней с Retention:

Наконец, считаем сам LTV — Lifetime, умноженный на ARPDAU. В нашем случае ARPDAU равняется $83.7:

select cumulative_lt as LT,
           cumulative_lt * 83.7 as LTV
from lt

Наконец, построим график LTV на 180 дней:

Весь запрос:

--общее количество юзеров в когорте
with cohort as (
    select count(*) as total_users_of_cohort
    from users
    where date(registration) between date '2021-03-01' and date '2021-03-30'
),
--количество активных юзеров на 1ый день, 2ой, 3ий и тд. из когорты
active_users as (
    select date_part('day', activity.date - users.registration) as activity_day, 
               count(distinct users.id) as active_users_of_day
    from activity
    join users on activity.user_id = users.id
    where date(registration) between date '2021-03-01' and date '2021-03-30' 
    group by 1
    having date_part('day', activity.date - users.registration) between 1 and 90 --берем только первые 90 дней, остальные дни предсказываем.
),
--рассчитываем коэффициенты регрессии
coef as (
    select exp(regr_intercept(ln(activity), ln(activity_day))) as a, 
                regr_slope(ln(activity), ln(activity_day)) as b
    from(
                select activity_day,
                            active_users_of_day::real / total_users_of_cohort as activity
                from active_users 
                cross join cohort order by activity_day 
            )
),
lt as(
    select generate_series as activity_day,
               active_users_of_day::real/total_users_of_cohort as real_data,
               a*power(generate_series,b) as pred_data, 	 
               sum(a*power(generate_series,b)) over(order by generate_series) as cumulative_lt
    from generate_series(1,180,1)
    cross join coef
    join active_users on generate_series = activity_day::int
),
select cumulative_lt as LT,
            cumulative_lt * 83.7 as LTV
from lt

Буквально на днях закончил обучение Clickhouse от Altinity (101 Series Training). Для тех, кто только знакомится с Clickhouse Altinity предлагает базовый бесплатный тренинг: Data Warehouse Basics. Рекомендую начать с него, если планируете погружаться в обучение.

Сертификация от Altinity

Хочу поделиться своими впечатлениями об обучении и поделиться своим конспектом с тренинга.
Обучение стоит $500 и длится четыре дня по два часа, проводится в наше вечернее время (начиная с 19:00 GMT+3).

Сессия №1

Первый день в бОльшей степени повторяет пройденное в Data Warehouse Basics, однако в нем есть несколько новых идей, например о том, как можно получить полезную информацию о запросах из системных таблиц.

Например, такой query выдаст какие команды запущены и в каком они статусе:

SELECT command, is_done
FROM system.mutations
WHERE table = 'ontime'

Помимо этого, для меня было очень полезно узнать про компрессию колонок с использованием кодеков:

ALTER TABLE ontime
 MODIFY COLUMN TailNum LowCardinality(String) CODEC(ZSTD(1))

Для тех, кто начинает погружение в Clickhouse первый день будет супер-полезным в том, чтобы разобраться с движками таблиц и синатксисом их создания, партициями, вставкой данных (к примеру, напрямую из S3).

INSERT INTO sdata
SELECT * FROM s3(
 'https://s3.us-east-1.amazonaws.com/d1-altinity/data/sdata*.csv.gz',
 'aws_access_key_id',
 'aws_secret_access_key',
 'Parquet',
 'DevId Int32, Type String, MDate Date, MDatetime
DateTime, Value Float64')

Сессия №2

Второй день мне представляется максимально насыщенным и полезным, потому что в рамках него Robert из Altinity подробно рассказывает про агрегирующие функции в Clickhouse и про создание материализованных представлений (подробно по шагам разбирается схема создания материализованного представления).

Отдельное внимание устройству джойнов в Clickhouse

Мне было супер-полезно узнать про типы индексов в CH

Сессия №3

В рамках третьего дня коллеги делятся знаниями о том как работать с Kafka, JSON-объектами, которые хранятся в таблицах.
Интересно было узнать, что работа с типами данных массив в Clickhouse очень похоже на работу с массивами в Python:

WITH [1, 2, 4] AS array
SELECT
 array[1] AS First,
 array[2] AS Second,
 array[3] AS Third,
 array[-1] AS Last,
 length(array) AS Length

И при работе с массивами крутая фича это ARRAY JOIN, который «разворачивает» массив в плоскую реляционную таблицу:

Clickhouse позволяет эффективно взаимодействовать с JSON-объектами, которые хранятся в таблице:

-- Get a JSON string value
SELECT JSONExtractString(row, 'request') AS request
FROM log_row LIMIT 3
-- Get a JSON numeric value
SELECT JSONExtractInt(row, 'status') AS status
FROM log_row LIMIT 3

На примере этого кусочка кода отдельно извлекаются элементы JSON-массива ’request’ и ’status’.

Их можно сложить в ту же таблицу:

ALTER TABLE log_row
 ADD COLUMN
status Int16 DEFAULT
 JSONExtractInt(row, 'status')
ALTER TABLE log_row
UPDATE status = status WHERE 1 = 1

Сессия №4

А на заключительный четвертый день оставлена самая трудная тема с моей точки зрения: построение шардированных и реплицированных кластеров, построение запросов на распределенных серверах Clickhouse.

Отдельный респект Altinity за отличную подборку лабораторных заданий в ходе обучения.

Ссылки:

• Конспект в Notion
• ClickHouse 101 Training от Altinity

Главный принцип анализа данных GIGO (от англ. garbage in — garbage out, дословный перевод «мусор на входе — мусор на выходе») говорит нам о том, что ошибки во входных данных всегда приводят к неверным результатам анализа. От того, насколько хорошо подготовлены данные, зависят результаты всей вашей работы.

Например, перед нами стоит задача подготовить выборку для использования в алгоритме машинного обучения (модели k-NN, k-means, логической регрессии и др). Признаки в исходном наборе данных могут быть в разном масштабе, как, например, возраст и рост человека. Это может привести к некорректной работе алгоритма. Такого рода данные нужно предварительно масштабировать.

В данном материале мы рассмотрим способы масштабирования данных через запрос в SQL: масштабирование методом min-max, min-max для произвольного диапазона и z-score нормализация. Для каждого из методов мы подготовили по два примера написания запроса — один с помощью подзапроса SELECT, а второй используя оконную функцию OVER().

Для работы возьмем таблицу students с данными о росте учащихся.

Min-Max масштабирование

Подход min-max масштабирования заключается в том, что данные масштабируются до фиксированного диапазона, который обычно составляет от 0 до 1. В данном случае мы получим все данные в одном масштабе, что исключит влияние выбросов на выводы.

Выполним масштабирование по формуле:

Умножаем числитель на 1.0, чтобы в результате получилось число с плавающей точкой.

SQL-запрос с подзапросом:

SELECT height, 
       1.0 * (height-t1.min_height)/(t1.max_height - t1.min_height) AS scaled_minmax
  FROM students, 
      (SELECT min(height) as min_height, 
              max(height) as max_height 
         FROM students
      ) as t1;

SQL-запрос с оконной функцией:

SELECT height, 
       (height - MIN(height) OVER ()) * 1.0 / (MAX(height) OVER () - MIN(height) OVER ()) AS scaled_minmax
  FROM students;

В результате мы получим переменные в диапазоне [0...1], где за 0 принят рост самого невысокого учащегося, а 1 рост самого высокого.

Масштабирование для заданного диапазона

Вариант min-max нормализации для произвольных значений. Не всегда, когда речь идет о масштабировании данных, диапазон значений находится в промежутке между 0 и 1.
Формула для вычисления в этом случае такая:

Это даст нам возможность масштабировать данные к произвольной шкале. В нашем примере пусть а=10.0, а b=20.0.

SQL-запрос с подзапросом:

SELECT height, 
       ((height - min_height) * (20.0 - 10.0) / (max_height - min_height)) + 10 AS scaled_ab
  FROM students,
      (SELECT MAX(height) as max_height, 
              MIN(height) as min_height
         FROM students  
      ) t1;

SQL-запрос с оконной функцией:

SELECT height, 
       ((height - MIN(height) OVER() ) * (20.0 - 10.0) / (MAX(height) OVER() - MIN(height) OVER())) + 10.0 AS scaled_ab
  FROM students;

Получаем аналогичные результаты, что и в предыдущем методе, но данные распределены в диапазоне от 10 до 20.

Нормализация с помощью z-score

В результате z-score нормализации данные будут масштабированы таким образом, чтобы они имели свойства стандартного нормального распределения — среднее (μ) равно 0, а стандартное отклонение (σ) равно 1.

Вычисляется z-score по формуле:

SQL-запрос с подзапросом:

SELECT height, 
       (height - t1.mean) * 1.0 / t1.sigma AS zscore
  FROM students,
      (SELECT AVG(height) AS mean, 
              STDDEV(height) AS sigma
         FROM students
        ) t1;

SQL-запрос с оконной функцией:

SELECT height, 
       (height - AVG(height) OVER()) * 1.0 / STDDEV(height) OVER() AS z-score
  FROM students;

В результате мы сразу заметим выбросы, которые выходят за пределы стандартного отклонения.