С помощью какой команды можно произвести автозамену null результата оконной функции в postgresql

С помощью какой команды можно произвести автозамену null результата оконной функции в postgresql

Выражения значения применяются в самых разных контекстах, например в списке результатов команды SELECT , в значениях столбцов в INSERT или UPDATE или в условиях поиска во многих командах. Результат такого выражения иногда называют скаляром, чтобы отличить его от результата табличного выражения (который представляет собой таблицу). А сами выражения значения часто называют скалярными (или просто выражениями). Синтаксис таких выражений позволяет вычислять значения из примитивных частей, используя арифметические, логические и другие операции.

Выражениями значения являются:

Константа или непосредственное значение

Ссылка на столбец

Ссылка на позиционный параметр в теле определения функции или подготовленного оператора

Выражение с индексом

Выражение выбора поля

Вызов оконной функции

Применение правил сортировки

Конструктор табличной строки

В дополнение к этому списку есть ещё несколько конструкций, которые можно классифицировать как выражения, хотя они не соответствуют общим синтаксическим правилам. Они обычно имеют вид функции или оператора и будут рассмотрены в соответствующем разделе Главы 9. Пример такой конструкции — предложение IS NULL .

Мы уже обсудили константы в Подразделе 4.1.2. В следующих разделах рассматриваются остальные варианты.

4.2.1. Ссылки на столбцы

Ссылку на столбец можно записать в форме:

отношение.имя_столбца 

Здесь отношение — имя таблицы (возможно, полное, с именем схемы) или её псевдоним, определённый в предложении FROM . Это имя и разделяющую точку можно опустить, если имя столбца уникально среди всех таблиц, задействованных в текущем запросе. (См. также Главу 7.)

4.2.2. Позиционные параметры

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

$число 

Например, рассмотрим следующее определение функции dept :

CREATE FUNCTION dept(text) RETURNS dept AS $$ SELECT * FROM dept WHERE name = $1 $$ LANGUAGE SQL;

Здесь $1 всегда будет ссылаться на значение первого аргумента функции.

4.2.3. Индексы элементов

Если в выражении вы имеете дело с массивом, то можно извлечь определённый его элемент, написав:

выражение[индекс]

или несколько соседних элементов ( « срез массива » ):

выражение[нижний_индекс:верхний_индекс]

(Здесь квадратные скобки [ ] должны присутствовать буквально.) Каждый индекс сам по себе является выражением, результат которого округляется к ближайшему целому.

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

моя_таблица.столбец_массив[4] моя_таблица.столбец_массив_2d[17][34] $1[10:42] (функция_массив(a,b))[42]

В последней строке круглые скобки необходимы. Подробнее массивы рассматриваются в Разделе 8.15.

4.2.4. Выбор поля

Если результат выражения — значение составного типа (строка таблицы), тогда определённое поле этой строки можно извлечь, написав:

выражение.имя_поля 

В общем случае выражение такого типа должно заключаться в круглые скобки, но их можно опустить, когда это ссылка на таблицу или позиционный параметр. Например:

моя_таблица.столбец $1.столбец (функция_кортеж(a,b)).стол3

(Таким образом, полная ссылка на столбец — это просто частный случай выбора поля.) Важный особый случай здесь — извлечение поля из столбца составного типа:

(составной_столбец).поле (моя_таблица.составной_столбец).поле

Здесь скобки нужны, чтобы показать, что составной_столбец — это имя столбца, а не таблицы, и что моя_таблица — имя таблицы, а не схемы.

Вы можете запросить все поля составного значения, написав .* :

(составной_столбец).*

Эта запись действует по-разному в зависимости от контекста; подробнее об этом говорится в Подразделе 8.16.5.

4.2.5. Применение оператора

Существуют три возможных синтаксиса применения операторов:

выражение оператор выражение (бинарный инфиксный оператор)

оператор выражение (унарный префиксный оператор)

выражение оператор (унарный постфиксный оператор)

где оператор соответствует синтаксическим правилам, описанным в Подразделе 4.1.3, либо это одно из ключевых слов AND , OR и NOT , либо полное имя оператора в форме:

OPERATOR(схема.имя_оператора) 

Существование конкретных операторов и их тип (унарный или бинарный) зависит от того, как и какие операторы определены системой и пользователем. Встроенные операторы описаны в Главе 9.

4.2.6. Вызовы функций

Вызов функции записывается просто как имя функции (возможно, дополненное именем схемы) и список аргументов в скобках:

имя_функции ([выражение [, выражение . ]])

Например, так вычисляется квадратный корень из 2:

sqrt(2)

Список встроенных функций приведён в Главе 9. Пользователь также может определить и другие функции.

Выполняя запросы в базе данных, где одни пользователи могут не доверять другим, в записи вызовов функций соблюдайте меры предосторожности, описанные в Разделе 10.3.

Аргументам могут быть присвоены необязательные имена. Подробнее об этом см. Раздел 4.3.

Примечание

Функцию, принимающую один аргумент составного типа, можно также вызывать, используя синтаксис выбора поля, и наоборот, выбор поля можно записать в функциональном стиле. То есть записи col(table) и table.col равносильны и взаимозаменяемы. Это поведение не оговорено стандартом SQL, но реализовано в PostgreSQL , так как это позволяет использовать функции для эмуляции « вычисляемых полей » . Подробнее это описано в Подразделе 8.16.5.

4.2.7. Агрегатные выражения

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

агрегатная_функция (выражение [ , . ] [ предложение_order_by ] ) [ FILTER ( WHERE условие_фильтра ) ] агрегатная_функция (ALL выражение [ , . ] [ предложение_order_by ] ) [ FILTER ( WHERE условие_фильтра ) ] агрегатная_функция (DISTINCT выражение [ , . ] [ предложение_order_by ] ) [ FILTER ( WHERE условие_фильтра ) ] агрегатная_функция ( * ) [ FILTER ( WHERE условие_фильтра ) ] агрегатная_функция ( [ выражение [ , . ] ] ) WITHIN GROUP ( предложение_order_by ) [ FILTER ( WHERE условие_фильтра ) ]

Здесь агрегатная_функция — имя ранее определённой агрегатной функции (возможно, дополненное именем схемы), выражение — любое выражение значения, не содержащее в себе агрегатного выражения или вызова оконной функции. Необязательные предложения предложение_order_by и условие_фильтра описываются ниже.

В первой форме агрегатного выражения агрегатная функция вызывается для каждой строки. Вторая форма эквивалентна первой, так как указание ALL подразумевается по умолчанию. В третьей форме агрегатная функция вызывается для всех различных значений выражения (или набора различных значений, для нескольких выражений), выделенных во входных данных. В четвёртой форме агрегатная функция вызывается для каждой строки, так как никакого конкретного значения не указано (обычно это имеет смысл только для функции count(*) ). В последней форме используются сортирующие агрегатные функции, которые будут описаны ниже.

Большинство агрегатных функций игнорируют значения NULL, так что строки, для которых выражения выдают одно или несколько значений NULL, отбрасываются. Это можно считать истинным для всех встроенных операторов, если явно не говорится об обратном.

Например, count(*) подсчитает общее количество строк, а count(f1) только количество строк, в которых f1 не NULL (так как count игнорирует NULL), а count(distinct f1) подсчитает число различных и отличных от NULL значений столбца f1 .

Обычно строки данных передаются агрегатной функции в неопределённом порядке и во многих случаях это не имеет значения, например функция min выдаёт один и тот же результат независимо от порядка поступающих данных. Однако некоторые агрегатные функции (такие как array_agg и string_agg ) выдают результаты, зависящие от порядка данных. Для таких агрегатных функций можно добавить предложение_order_by и задать нужный порядок. Это предложение_order_by имеет тот же синтаксис, что и предложение ORDER BY на уровне запроса, как описано в Разделе 7.5, за исключением того, что его выражения должны быть просто выражениями, а не именами результирующих столбцов или числами. Например:

SELECT array_agg(a ORDER BY b DESC) FROM table;

Заметьте, что при использовании агрегатных функций с несколькими аргументами, предложение ORDER BY идёт после всех аргументов. Например, надо писать так:

SELECT string_agg(a, ',' ORDER BY a) FROM table;

SELECT string_agg(a ORDER BY a, ',') FROM table; -- неправильно

Последний вариант синтаксически допустим, но он представляет собой вызов агрегатной функции одного аргумента с двумя ключами ORDER BY (при этом второй не имеет смысла, так как это константа).

Если предложение_order_by дополнено указанием DISTINCT , тогда все выражения ORDER BY должны соответствовать обычным аргументам агрегатной функции; то есть вы не можете сортировать строки по выражению, не включённому в список DISTINCT .

Примечание

Возможность указывать и DISTINCT , и ORDER BY в агрегатной функции — это расширение PostgreSQL .

При добавлении ORDER BY в обычный список аргументов агрегатной функции, описанном до этого, выполняется сортировка строк для « обычной » агрегатной функции, для которой сортировка необязательна. Но есть подмножество агрегатных функций, сортирующие агрегатные функции, для которых предложение_order является обязательным , обычно потому, что вычисление этой функции имеет смысл только при определённой сортировке входных строк. Типичными примерами сортирующих агрегатных функций являются вычисления ранга и процентиля. Для сортирующей агрегатной функции предложение_order_by записывается внутри WITHIN GROUP (. ) , что иллюстрирует последний пример, приведённый выше. Выражения в предложении_order_by вычисляются однократно для каждой входной строки как аргументы обычной агрегатной функции, сортируются в соответствии с требованием предложения_order_by и поступают в агрегатную функции как входящие аргументы. (Если же предложение_order_by находится не в WITHIN GROUP , оно не передаётся как аргумент(ы) агрегатной функции.) Выражения-аргументы, предшествующие WITHIN GROUP , (если они есть), называются непосредственными аргументами, а выражения, указанные в предложении_order_by — агрегируемыми аргументами. В отличие от аргументов обычной агрегатной функции, непосредственные аргументы вычисляются однократно для каждого вызова функции, а не для каждой строки. Это значит, что они могут содержать переменные, только если эти переменные сгруппированы в GROUP BY ; это суть то же ограничение, что действовало бы, будь эти непосредственные аргументы вне агрегатного выражения. Непосредственные аргументы обычно используются, например, для указания значения процентиля, которое имеет смысл, только если это конкретное число для всего расчёта агрегатной функции. Список непосредственных аргументов может быть пуст; в этом случае запишите просто () , но не (*) . (На самом деле PostgreSQL примет обе записи, но только первая соответствует стандарту SQL.)

Пример вызова сортирующей агрегатной функции:

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY income) FROM households; percentile_cont ----------------- 50489

она получает 50-ый процентиль, или медиану, значения столбца income из таблицы households . В данном случае 0.5 — это непосредственный аргумент; если бы дробь процентиля менялась от строки к строке, это не имело бы смысла.

Если добавлено предложение FILTER , агрегатной функции подаются только те входные строки, для которых условие_фильтра вычисляется как истинное; другие строки отбрасываются. Например:

SELECT count(*) AS unfiltered, count(*) FILTER (WHERE i < 5) AS filtered FROM generate_series(1,10) AS s(i); unfiltered | filtered ------------+---------- 10 | 4 (1 row)

Предопределённые агрегатные функции описаны в Разделе 9.20. Пользователь также может определить другие агрегатные функции.

Агрегатное выражение может фигурировать только в списке результатов или в предложении HAVING команды SELECT . Во всех остальных предложениях, например WHERE , они запрещены, так как эти предложения логически вычисляются до того, как формируются результаты агрегатных функций.

Когда агрегатное выражение используется в подзапросе (см. Подраздел 4.2.11 и Раздел 9.22), оно обычно вычисляется для всех строк подзапроса. Но если в аргументах (или в условии_filter ) агрегатной функции есть только переменные внешнего уровня, агрегатная функция относится к ближайшему внешнему уровню и вычисляется для всех строк соответствующего запроса. Такое агрегатное выражение в целом является внешней ссылкой для своего подзапроса и на каждом вычислении считается константой. При этом допустимое положение агрегатной функции ограничивается списком результатов и предложением HAVING на том уровне запросов, где она находится.

4.2.8. Вызовы оконных функций

Вызов оконной функции представляет собой применение функции, подобной агрегатной, к некоторому набору строк, выбранному запросом. В отличие от обычных агрегатных функций, оконные функции не связаны с группировкой выбранных строк в одну — каждая строка остаётся отдельной в результате запроса. Однако оконная функция может просканировать все строки, вошедшие в группу текущей строки согласно указанию (списку PARTITION BY ) при вызове оконной функции. Вызов оконной функции может иметь следующие формы:

имя_функции ([выражение [, выражение . ]]) [ FILTER ( WHERE условие_фильтра ) ] OVER имя_окна имя_функции ([выражение [, выражение . ]]) [ FILTER ( WHERE условие_фильтра ) ] OVER ( определение_окна ) имя_функции ( * ) [ FILTER ( WHERE условие_фильтра ) ] OVER имя_окна имя_функции ( * ) [ FILTER ( WHERE условие_фильтра ) ] OVER ( определение_окна )

Здесь определение_окна записывается в виде:

[ имя_существующего_окна ] [ PARTITION BY выражение [, . ] ] [ ORDER BY выражение [ ASC | DESC | USING оператор ] [ NULLS < FIRST | LAST >] [, . ] ] [ определение_рамки ]

и необязательное определение_рамки может иметь вид:

 < RANGE | ROWS >начало_рамки < RANGE | ROWS >BETWEEN начало_рамки AND конец_рамки 

Здесь начало_рамки и конец_рамки задаются одним из следующих способов:

UNBOUNDED PRECEDING значение PRECEDING CURRENT ROW значение FOLLOWING UNBOUNDED FOLLOWING

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

имя_окна — ссылка на именованное окно, определённое предложением WINDOW в данном запросе. Также возможно написать в скобках полное определение_окна , используя тот же синтаксис определения именованного окна в предложении WINDOW ; подробнее это описано в справке по SELECT . Стоит отметить, что запись OVER имя_окна не полностью равнозначна OVER (имя_окна) ; последний вариант подразумевает копирование и изменение определения окна и не будет допустимым, если определение этого окна включает определение рамки.

Указание PARTITION BY группирует строки запроса в разделы, которые затем обрабатываются оконной функцией независимо друг от друга. PARTITION BY работает подобно предложению GROUP BY на уровне запроса, за исключением того, что его аргументы всегда просто выражения, а не имена выходных столбцов или числа. Без PARTITION BY все строки, выдаваемые запросом, рассматриваются как один раздел. Указание ORDER BY определяет порядок, в котором оконная функция обрабатывает строки раздела. Оно так же подобно предложению ORDER BY на уровне запроса и так же не принимает имена выходных столбцов или числа. Без ORDER BY строки обрабатываются в неопределённом порядке.

определение_рамки задаёт набор строк, образующих рамку окна, которая представляет собой подмножество строк текущего раздела и используется для оконных функций, работающих с рамкой, а не со всем разделом. Рамку можно указать в режимах RANGE или ROWS ; в любом случае она начинается с положения начало_рамки и заканчивается положением конец_рамки . Если конец_рамки опущен, подразумевается CURRENT ROW (текущая строка).

Если начало_рамки задано как UNBOUNDED PRECEDING , рамка начинается с первой строки раздела, а если конец_рамки определён как UNBOUNDED FOLLOWING , рамка заканчивается последней строкой раздела.

В режиме RANGE начало_рамки , заданное как CURRENT ROW , определяет в качестве начала первую родственную строку (строку, которую ORDER BY считает равной текущей), тогда как конец_рамки , заданный как CURRENT ROW , определяет концом рамки последнюю родственную (для ORDER BY ) строку. В режиме ROWS вариант CURRENT ROW просто обозначает текущую строку.

Варианты значение PRECEDING и значение FOLLOWING допускаются только в режиме ROWS . Они указывают, что рамка начинается или заканчивается со сдвигом на заданное число строк перед или после заданной строки. Здесь значение должно быть целочисленным выражением, не содержащим переменные, агрегатные или оконные функции, и может быть нулевым, что будет означать выбор текущей строки.

По умолчанию рамка определяется как RANGE UNBOUNDED PRECEDING , что равносильно расширенному определению RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW . С указанием ORDER BY это означает, что рамка будет включать все строки от начала раздела до последней строки, родственной текущей (для ORDER BY ). Без ORDER BY в рамку включаются все строки раздела, так как все они считаются родственными текущей.

Действуют также ограничения: начало_рамки не может определяться как UNBOUNDED FOLLOWING , а конец_рамки — UNBOUNDED PRECEDING , и конец_рамки не может определяться раньше, чем начало_рамки — например, запись RANGE BETWEEN CURRENT ROW AND значение PRECEDING недопустима.

Если добавлено предложение FILTER , оконной функции подаются только те входные строки, для которых условие_фильтра вычисляется как истинное; другие строки отбрасываются. Предложение FILTER допускается только для агрегирующих оконных функций.

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

Запись со звёздочкой ( * ) применяется при вызове агрегатных функций в качестве оконных, например count(*) OVER (PARTITION BY x ORDER BY y) . Звёздочка ( * ) обычно не применяется для не агрегатных оконных функций. Агрегатные оконные функции, в отличие от обычных агрегатных функций, не допускают использования DISTINCT и ORDER BY в списке аргументов.

Вызовы оконных функций разрешены в запросах только в списке SELECT и в предложении ORDER BY .

Дополнительно об оконных функциях можно узнать в Разделе 3.5, Разделе 9.21 и Подразделе 7.2.5.

4.2.9. Приведения типов

Приведение типа определяет преобразование данных из одного типа в другой. PostgreSQL воспринимает две равносильные записи приведения типов:

CAST ( выражение AS тип ) выражение::тип 

Запись с CAST соответствует стандарту SQL, тогда как вариант с :: — историческое наследие PostgreSQL .

Когда приведению подвергается значение выражения известного типа, происходит преобразование типа во время выполнения. Это приведение будет успешным, только если определён подходящий оператор преобразования типов. Обратите внимание на небольшое отличие от приведения констант, описанного в Подразделе 4.1.2.7. Приведение строки в чистом виде представляет собой начальное присваивание строковой константы и оно будет успешным для любого типа (конечно, если строка содержит значение, приемлемое для данного типа данных).

Неявное приведение типа можно опустить, если возможно однозначно определить, какой тип должно иметь выражение (например, когда оно присваивается столбцу таблицы); в таких случаях система автоматически преобразует тип. Однако автоматическое преобразование выполняется только для приведений с пометкой « допускается неявное применение » в системных каталогах. Все остальные приведения должны записываться явно. Это ограничение позволяет избежать сюрпризов с неявным преобразованием.

Также можно записать приведение типа как вызов функции:

имя_типа ( выражение )

Однако это будет работать только для типов, имена которых являются также допустимыми именами функций. Например, double precision так использовать нельзя, а float8 (альтернативное название того же типа) — можно. Кроме того, имена типов interval , time и timestamp из-за синтаксического конфликта можно использовать в такой записи только в кавычках. Таким образом, запись приведения типа в виде вызова функции провоцирует несоответствия и, возможно, лучше будет её не применять.

Примечание

Приведение типа, представленное в виде вызова функции, на самом деле соответствует внутреннему механизму. Даже при использовании двух стандартных типов записи внутри происходит вызов зарегистрированной функции, выполняющей преобразование. По соглашению именем такой функции преобразования является имя выходного типа, и таким образом запись « в виде вызова функции » есть не что иное, как прямой вызов нижележащей функции преобразования. При создании переносимого приложения на это поведение, конечно, не следует рассчитывать. Подробнее это описано в справке CREATE CAST .

4.2.10. Применение правил сортировки

Предложение COLLATE переопределяет правило сортировки выражения. Оно добавляется после выражения:

выражение COLLATE правило_сортировки 

где правило_сортировки — идентификатор правила, возможно дополненный именем схемы. Предложение COLLATE связывает выражение сильнее, чем операторы, так что при необходимости следует использовать скобки.

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

Предложение COLLATE имеет два распространённых применения: переопределение порядка сортировки в предложении ORDER BY , например:

SELECT a, b, c FROM tbl WHERE . ORDER BY a COLLATE "C";

и переопределение правил сортировки при вызове функций или операторов, возвращающих языкозависимые результаты, например:

SELECT * FROM tbl WHERE a > 'foo' COLLATE "C";

Заметьте, что в последнем случае предложение COLLATE добавлено к аргументу оператора, на действие которого мы хотим повлиять. При этом не имеет значения, к какому именно аргументу оператора или функции добавляется COLLATE , так как правило сортировки, применяемое к оператору или функции, выбирается при рассмотрении всех аргументов, а явное предложение COLLATE переопределяет правила сортировки для всех других аргументов. (Однако добавление разных предложений COLLATE к нескольким аргументам будет ошибкой. Подробнее об этом см. Раздел 23.2.) Таким образом, эта команда выдаст тот же результат:

SELECT * FROM tbl WHERE a COLLATE "C" > 'foo';

Но это будет ошибкой:

SELECT * FROM tbl WHERE (a > 'foo') COLLATE "C";

здесь правило сортировки нельзя применить к результату оператора > , который имеет несравниваемый тип данных boolean .

4.2.11. Скалярные подзапросы

Скалярный подзапрос — это обычный запрос SELECT в скобках, который возвращает ровно одну строку и один столбец. (Написание запросов освещается в Главе 7.) После выполнения запроса SELECT его единственный результат используется в окружающем его выражении. В качестве скалярного подзапроса нельзя использовать запросы, возвращающие более одной строки или столбца. (Но если в результате выполнения подзапрос не вернёт строк, скалярный результат считается равным NULL.) В подзапросе можно ссылаться на переменные из окружающего запроса; в процессе одного вычисления подзапроса они будут считаться константами. Другие выражения с подзапросами описаны в Разделе 9.22.

Например, следующий запрос находит самый населённый город в каждом штате:

SELECT name, (SELECT max(pop) FROM cities WHERE cities.state = states.name) FROM states;

4.2.12. Конструкторы массивов

Конструктор массива — это выражение, которое создаёт массив, определяя значения его элементов. Конструктор простого массива состоит из ключевого слова ARRAY , открывающей квадратной скобки [ , списка выражений (разделённых запятыми), задающих значения элементов массива, и закрывающей квадратной скобки ] . Например:

SELECT ARRAY[1,2,3+4]; array --------- (1 row)

По умолчанию типом элементов массива считается общий тип для всех выражений, определённый по правилам, действующим и для конструкций UNION и CASE (см. Раздел 10.5). Вы можете переопределить его явно, приведя конструктор массива к требуемому типу, например:

SELECT ARRAY[1,2,22.7]::integer[]; array ---------- (1 row)

Это равносильно тому, что привести к нужному типу каждое выражение по отдельности. Подробнее приведение типов описано в Подразделе 4.2.9.

Многомерные массивы можно образовывать, вкладывая конструкторы массивов. При этом во внутренних конструкторах слово ARRAY можно опускать. Например, результат работы этих конструкторов одинаков:

SELECT ARRAY[ARRAY[1,2], ARRAY[3,4]]; array --------------- ,> (1 row) SELECT ARRAY[[1,2],[3,4]]; array --------------- ,> (1 row)

Многомерные массивы должны быть прямоугольными, и поэтому внутренние конструкторы одного уровня должны создавать вложенные массивы одинаковой размерности. Любое приведение типа, применённое к внешнему конструктору ARRAY , автоматически распространяется на все внутренние.

Элементы многомерного массива можно создавать не только вложенными конструкторами ARRAY , но и другими способами, позволяющими получить массивы нужного типа. Например:

CREATE TABLE arr(f1 int[], f2 int[]); INSERT INTO arr VALUES (ARRAY[[1,2],[3,4]], ARRAY[[5,6],[7,8]]); SELECT ARRAY[f1, f2, ',>'::int[]] FROM arr; array ------------------------------------------------ ,>,,>,,>> (1 row)

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

SELECT ARRAY[]::integer[]; array ------- <> (1 row)

Также возможно создать массив из результатов подзапроса. В этом случае конструктор массива записывается так же с ключевым словом ARRAY , за которым в круглых скобках следует подзапрос. Например:

SELECT ARRAY(SELECT oid FROM pg_proc WHERE proname LIKE 'bytea%'); array ----------------------------------------------------------------------- (1 row) SELECT ARRAY(SELECT ARRAY[i, i*2] FROM generate_series(1,5) AS a(i)); array ---------------------------------- ,,,,> (1 row)

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

Индексы массива, созданного конструктором ARRAY , всегда начинаются с одного. Подробнее о массивах вы узнаете в Разделе 8.15.

4.2.13. Конструкторы табличных строк

Конструктор табличной строки — это выражение, создающее строку или кортеж (или составное значение) из значений его аргументов-полей. Конструктор строки состоит из ключевого слова ROW , открывающей круглой скобки, нуля или нескольких выражений (разделённых запятыми), определяющих значения полей, и закрывающей скобки. Например:

SELECT ROW(1,2.5,'this is a test');

Если в списке более одного выражения, ключевое слово ROW можно опустить.

Конструктор строки поддерживает запись составное_значение .* , при этом данное значение будет развёрнуто в список элементов, так же, как в записи .* на верхнем уровне списка SELECT (см. Подраздел 8.16.5). Например, если таблица t содержит столбцы f1 и f2 , эти записи равнозначны:

SELECT ROW(t.*, 42) FROM t; SELECT ROW(t.f1, t.f2, 42) FROM t;

Примечание

До версии PostgreSQL 8.2 запись .* не разворачивалась в конструкторах строк, так что выражение ROW(t.*, 42) создавало составное значение из двух полей, в котором первое поле так же было составным. Новое поведение обычно более полезно. Если вам нужно получить прежнее поведение, чтобы одно значение строки было вложено в другое, напишите внутреннее значение без .* , например: ROW(t, 42) .

По умолчанию значение, созданное выражением ROW , имеет тип анонимной записи. Если необходимо, его можно привести к именованному составному типу — либо к типу строки таблицы, либо составному типу, созданному оператором CREATE TYPE AS . Явное приведение может потребоваться для достижения однозначности. Например:

CREATE TABLE mytable(f1 int, f2 float, f3 text); CREATE FUNCTION getf1(mytable) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL; -- Приведение не требуется, так как существует только одна getf1() SELECT getf1(ROW(1,2.5,'this is a test')); getf1 ------- 1 (1 row) CREATE TYPE myrowtype AS (f1 int, f2 text, f3 numeric); CREATE FUNCTION getf1(myrowtype) RETURNS int AS 'SELECT $1.f1' LANGUAGE SQL; -- Теперь приведение необходимо для однозначного выбора функции: SELECT getf1(ROW(1,2.5,'this is a test')); ОШИБКА: функция getf1(record) не уникальна SELECT getf1(ROW(1,2.5,'this is a test')::mytable); getf1 ------- 1 (1 row) SELECT getf1(CAST(ROW(11,'this is a test',2.5) AS myrowtype)); getf1 ------- 11 (1 row)

Используя конструктор строк (кортежей), можно создавать составное значение для сохранения в столбце составного типа или для передачи функции, принимающей составной параметр. Также вы можете сравнить два составных значения или проверить их с помощью IS NULL или IS NOT NULL , например:

SELECT ROW(1,2.5,'this is a test') = ROW(1, 3, 'not the same'); -- выбрать все строки, содержащие только NULL SELECT ROW(table.*) IS NULL FROM table;

Подробнее см. Раздел 9.23. Конструкторы строк также могут использоваться в сочетании с подзапросами, как описано в Разделе 9.22.

4.2.14. Правила вычисления выражений

Порядок вычисления подвыражений не определён. В частности, аргументы оператора или функции не обязательно вычисляются слева направо или в любом другом фиксированном порядке.

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

SELECT true OR somefunc();

тогда функция somefunc() не будет вызываться (возможно). То же самое справедливо для записи:

SELECT somefunc() OR true;

Заметьте, что это отличается от « оптимизации » вычисления логических операторов слева направо, реализованной в некоторых языках программирования.

Как следствие, в сложных выражениях не стоит использовать функции с побочными эффектами. Особенно опасно рассчитывать на порядок вычисления или побочные эффекты в предложениях WHERE и HAVING , так как эти предложения тщательно оптимизируются при построении плана выполнения. Логические выражения (сочетания AND / OR / NOT ) в этих предложениях могут быть видоизменены любым способом, допустимым законами Булевой алгебры.

Когда порядок вычисления важен, его можно зафиксировать с помощью конструкции CASE (см. Раздел 9.17). Например, такой способ избежать деления на ноль в предложении WHERE ненадёжен:

SELECT . WHERE x > 0 AND y/x > 1.5;

SELECT . WHERE CASE WHEN x > 0 THEN y/x > 1.5 ELSE false END;

Применяемая так конструкция CASE защищает выражение от оптимизации, поэтому использовать её нужно только при необходимости. (В данном случае было бы лучше решить проблему, переписав условие как y > 1.5*x .)

Однако CASE не всегда спасает в подобных случаях. Показанный выше приём плох тем, что не предотвращает раннее вычисление константных подвыражений. Как описано в Разделе 36.6, функции и операторы, помеченные как IMMUTABLE , могут вычисляться при планировании, а не выполнении запроса. Поэтому в примере

SELECT CASE WHEN x > 0 THEN x ELSE 1/0 END FROM tab;

, скорее всего, произойдёт деление на ноль из-за того, что планировщик попытается упростить константное подвыражение, даже если во всех строках в таблице x > 0 , а значит во время выполнения ветвь ELSE никогда не будет выполняться.

Хотя этот конкретный пример может показаться надуманным, похожие ситуации, в которых неявно появляются константы, могут возникать и в запросах внутри функций, так как значения аргументов функции и локальных переменных при планировании могут быть заменены константами. Поэтому, например, в функциях PL/pgSQL гораздо безопаснее для защиты от рискованных вычислений использовать конструкцию IF - THEN - ELSE , чем выражение CASE .

Ещё один подобный недостаток этого подхода в том, что CASE не может предотвратить вычисление заключённого в нём агрегатного выражения, так как агрегатные выражения вычисляются перед всеми остальными в списке SELECT или предложении HAVING . Например, в следующем запросе может возникнуть ошибка деления на ноль, несмотря на то, что он вроде бы защищён от неё:

SELECT CASE WHEN min(employees) > 0 THEN avg(expenses / employees) END FROM departments;

Агрегатные функции min() и avg() вычисляются независимо по всем входным строкам, так что если в какой-то строке поле employees окажется равным нулю, деление на ноль произойдёт раньше, чем станет возможным проверить результат функции min() . Поэтому, чтобы проблемные входные строки изначально не попали в агрегатную функцию, следует воспользоваться предложениями WHERE или FILTER .

Пред.	Наверх	След.
4.1. Лексическая структура	Начало	4.3. Вызов функций

Обработка NULL значений

Часто задают вопрос, как ведут себя агрегатные оконные функции с NULL значениями. Разобьем вопрос на два:

1. Как обрабатываются NULL значения при вычислении значения?
2. Как учитываются NULL значения при разделении данных на группы в PARTITION BY ?

Если отвечать коротко, то так же, как и в обычных агрегатных функциях.

NULL при вычислении значения

Все агрегатные функции, кроме count(*) игнорируют NULL значения.

Выведем сколько магазинов в каждом городе и для скольки из них заданы телефоны:

SELECT sa.city_id, sa.phone, count(sa.phone) over (PARTITION BY sa.city_id) AS count_phones_in_city, count(*) over (PARTITION BY sa.city_id) AS count_rows_in_city FROM store_address sa WHERE sa.city_id IN (1, 2, 6) ORDER BY sa.city_id, sa.phone NULLS LAST 

#	city_id	phone	count_phones_in_city	count_rows_in_city
1	1	7(495)312‒03‒08	2	2
2	1	7(495)312‒03‒08	2	2
3	2	7(812)700‒03‒03	1	2
4	2	NULL	1	2
5	6	NULL	0	2
6	6	NULL	0	2

NULL в PARTITION BY

В условиях WHERE два NULL значения считаются различными. Но при группировке строк PARTITION BY NULL значения считаются идентичными и объединяются в одну группу (как и при исключении повторяющихся строк DISTINCT ).

Для номера телефона выведем в скольки городах он используется:

SELECT sa.phone, sa.city_id, count(sa.city_id) over (PARTITION BY sa.phone) AS count_cities FROM store_address sa WHERE sa.city_id IN (1, 2, 6) ORDER BY sa.phone NULLS LAST, sa.city_id 

#	phone	city_id	count_cities
1	7(495)312‒03‒08	1	2
2	7(495)312‒03‒08	1	2
3	7(812)700‒03‒03	2	1
4	NULL	2	3
5	NULL	6	3
6	NULL	6	3

P.S. Если внимательно посмотреть на первые две строки результата

#	phone	city_id	count_cities
1	7(495)312‒03‒08	1	2
2	7(495)312‒03‒08	1	2

то видно, что город на самом деле один, а не два, как мы получили. Функция count(значение) считает количество заполненных значений, а не количество уникальных значений. Чтобы получить количество уникальных значений, хотелось бы воспользоваться count (DISTINCT значение) , но такая возможность в PostgreSQL не реализована 🙁

SELECT sa.phone, sa.city_id, count(DISTINCT sa.city_id) over (PARTITION BY sa.phone) AS count_cities FROM store_address sa WHERE sa.city_id IN (1, 2, 6) ORDER BY sa.phone NULLS LAST, sa.city_id 

error: DISTINCT is not implemented for window functions 

Сравнение с NULL¶

Любое сравнение произвольного значения с NULL возвращает NULL (неопределенное значение):

denis=# select coalesce((1 <> null)::varchar, ''); coalesce ---------- (1 row) denis=# select coalesce((1 = null)::varchar, ''); coalesce ---------- (1 row) denis=# select coalesce((1  null)::varchar, ''); coalesce ---------- (1 row) denis=# select coalesce((1 > null)::varchar, ''); coalesce ---------- (1 row) denis=# select coalesce((null = null)::varchar, ''); coalesce ---------- (1 row) denis=# select coalesce((null <> null)::varchar, ''); coalesce ---------- (1 row) 

Поэтому выражения/переменные, которые могут принимать неопределенные значения, надо осторожно использовать в сравнениях. Если в выражении попадётся неопределенное значение, то результат всего выражения может быть очень неожиданным.

Использование неопределенного значения в сложном сравнении с использование булевых операторов AND, OR, NOT тоже не несет в себе какого-нибудь позитива - хотя и результаты логических операция не противоречат болевой логики, но их сложно анализировать разработчикам.

denis=# select coalesce((null or true)::varchar, ''); coalesce ---------- true (1 row) denis=# select coalesce((null and true)::varchar, ''); coalesce ---------- (1 row) denis=# select coalesce((null and false)::varchar, ''); coalesce ---------- false (1 row) denis=# select coalesce((null or false)::varchar, ''); coalesce ---------- (1 row) denis=# select coalesce((not null)::varchar, ''); coalesce ---------- (1 row) 

Для сравнения на равенство двух значение, которые могут принимать неопределенные значения, можно использовать конструкцию IS DISTINCT FROM, которая возвращает True, если значения отличаются друг от друга:

denis=# select coalesce((null is distinct from null)::varchar, ''); coalesce ---------- false (1 row) denis=# select coalesce((null is distinct from 1)::varchar, ''); coalesce ---------- true (1 row) denis=# select coalesce((1 is distinct from 1)::varchar, ''); coalesce ---------- false (1 row) 

При использовании операторов is distinct from и is not distinct from в запросе индексы задействованы не будут.

denis=# explain select * from customers where id is not distinct from 222; QUERY PLAN ------------------------------------------------------------ Seq Scan on customers (cost=0.00..803.00 rows=1 width=17) Filter: (NOT (id IS DISTINCT FROM 222)) (2 rows) 

denis=# explain select * from customers where id = 222; QUERY PLAN -------------------------------------------------------------------------------- Index Scan using customers_pkey on customers (cost=0.00..8.28 rows=1 width=17) Index Cond: (id = 222) (2 rows) 

Это частично решает проблему сравнения неопределенных значений. Однако осталась проблема сравнений на больше-меньше. Это можно решить через функцию COALESCE:

denis=# select coalesce(1  null, false); coalesce ---------- f (1 row) denis=# select coalesce(2 > null, true); coalesce ---------- t (1 row) 

При разработке разработке хранимых процедур на pl/pgsql вышеописанная проблема так же сохраняется, но не так остро. Хотя остаётся не менее коварной:

DECLARE _str text; _int int4; BEGIN _str := NULL; _int := NULL; IF _str <> '' THEN RAISE NOTICE 'str: True'; ELSE RAISE NOTICE 'str: False'; END IF; IF _int > 0 THEN RAISE NOTICE 'int: True'; ELSE RAISE NOTICE 'int: False'; END IF; IF NULL THEN RAISE NOTICE 'NULL: True'; ELSE RAISE NOTICE 'NULL: False'; END IF; RAISE NOTICE 'end'; END 

NOTICE: str: False NOTICE: int: False NOTICE: NULL: False NOTICE: end

Неопределенное значение результата операции сравнения с NULL интерпретируется конструкцией IF NULL THEN ELSE END как False. Поэтому при простом сравнении переменной (которая может принимать NULL) с какой-либо константой можно проверки на NULL не делать:

IF _var IS NOT NULL AND _var > 0 THEN -- можно заменить на IF _var > 0 THEN IF _var IS NOT NULL AND _var = 'token' THEN -- можно за менить на IF _var = 'token' THEN . 

В общем, будьте внимательны.

Учимся применять оконные функции

Оконные функции — это мощнейший инструмент аналитика, который с легкостью помогает решать множество задач.

Если вам нужно произвести вычисление над заданным набором строк, объединенных каким-то одним признаком, например идентификатором клиента, вам на помощь придут именно они.

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

Принцип работы

У вас может возникнуть вопрос – «Что значит оконные?»

При обычном запросе, все множество строк обрабатывается как бы единым «цельным куском», для которого считаются агрегаты. А при использовании оконных функций, запрос делится на части (окна) и уже для каждой из отдельных частей считаются свои агрегаты.

Синтаксис

Окно определяется с помощью обязательной инструкции OVER(). Давайте рассмотрим синтаксис этой инструкции:

SELECT Название функции (столбец для вычислений) OVER ( PARTITION BY столбец для группировки ORDER BY столбец для сортировки ROWS или RANGE выражение для ограничения строк в пределах группы )

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

OVER()

Откроем окно при помощи OVER() и просуммируем столбец «Conversions»:

SELECT Date , Medium , Conversions , SUM(Conversions) OVER() AS 'Sum' FROM Orders

Мы использовали инструкцию OVER() без предложений. В таком варианте окном будет весь набор данных и никакая сортировка не применяется. Появился новый столбец «Sum» и для каждой строки выводится одно и то же значение 14. Это сквозная сумма всех значений колонки «Conversions».

PARTITION BY

Теперь применим инструкцию PARTITION BY, которая определяет столбец, по которому будет производиться группировка и является ключевой в разделении набора строк на окна:

SELECT Date , Medium , Conversions , SUM(Conversions) OVER(PARTITION BY Date) AS 'Sum' FROM Orders

Инструкция PARTITION BY сгруппировала строки по полю «Date». Теперь для каждой группы рассчитывается своя сумма значений столбца «Conversions».

ORDER BY

Попробуем отсортировать значения внутри окна при помощи ORDER BY:

SELECT Date , Medium , Conversions , SUM(Conversions) OVER(PARTITION BY Date ORDER BY Medium) AS 'Sum' FROM Orders

К предложению PARTITION BY добавилось ORDER BY по полю «Medium». Таким образом мы указали, что хотим видеть сумму не всех значений в окне, а для каждого значения «Conversions» сумму со всеми предыдущими. То есть мы посчитали нарастающий итог.

ROWS или RANGE

Инструкция ROWS позволяет ограничить строки в окне, указывая фиксированное количество строк, предшествующих или следующих за текущей.

Инструкция RANGE, в отличие от ROWS, работает не со строками, а с диапазоном строк в инструкции ORDER BY. То есть под одной строкой для RANGE могут пониматься несколько физических строк одинаковых по рангу.

Обе инструкции ROWS и RANGE всегда используются вместе с ORDER BY.

В выражении для ограничения строк ROWS или RANGE также можно использовать следующие ключевые слова:

• UNBOUNDED PRECEDING — указывает, что окно начинается с первой строки группы;
• UNBOUNDED FOLLOWING – с помощью данной инструкции можно указать, что окно заканчивается на последней строке группы;
• CURRENT ROW – инструкция указывает, что окно начинается или заканчивается на текущей строке;
• BETWEEN«граница окна» AND «граница окна» — указывает нижнюю и верхнюю границу окна;
• «Значение»PRECEDING – определяет число строк перед текущей строкой (не допускается в предложении RANGE).;
• «Значение»FOLLOWING — определяет число строк после текущей строки (не допускается в предложении RANGE).

Разберем на примере:

SELECT Date , Medium , Conversions , SUM(Conversions) OVER(PARTITION BY Date ORDER BY Conversions ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS 'Sum' FROM Orders

В данном случае сумма рассчитывается по текущей и следующей ячейке в окне. А последняя строка в окне имеет то же значение, что и столбец «Conversions», потому что больше не с чем складывать.

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

Виды функций

Оконные функции можно подразделить на следующие группы:

• Агрегатные функции;
• Ранжирующие функции;
• Функции смещения;
• Аналитические функции.

В одной инструкции SELECT с одним предложением FROM можно использовать сразу несколько оконных функций. Давайте подробно разберем каждую группу и пройдемся по основным функциям.

Агрегатные функции

Агрегатные функции – это функции, которые выполняют на наборе данных арифметические вычисления и возвращают итоговое значение.

• SUM – возвращает сумму значений в столбце;
• COUNT — вычисляет количество значений в столбце (значения NULL не учитываются);
• AVG — определяет среднее значение в столбце;
• MAX — определяет максимальное значение в столбце;
• MIN — определяет минимальное значение в столбце.

Пример использования агрегатных функций с оконной инструкцией OVER:

SELECT Date , Medium , Conversions , SUM(Conversions) OVER(PARTITION BY Date) AS 'Sum' , COUNT(Conversions) OVER(PARTITION BY Date) AS 'Count' , AVG(Conversions) OVER(PARTITION BY Date) AS 'Avg' , MAX(Conversions) OVER(PARTITION BY Date) AS 'Max' , MIN(Conversions) OVER(PARTITION BY Date) AS 'Min' FROM Orders

Ранжирующие функции

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

• ROW_NUMBER – функция возвращает номер строки и используется для нумерации;
• RANK — функция возвращает ранг каждой строки. В данном случае значения уже анализируются и, в случае нахождения одинаковых, возвращает одинаковый ранг с пропуском следующего значения;
• DENSE_RANK — функция возвращает ранг каждой строки. Но в отличие от функции RANK, она для одинаковых значений возвращает ранг, не пропуская следующий;
• NTILE – это функция, которая позволяет определить к какой группе относится текущая строка. Количество групп задается в скобках.

SELECT Date , Medium , Conversions , ROW_NUMBER() OVER(PARTITION BY Date ORDER BY Conversions) AS 'Row_number' , RANK() OVER(PARTITION BY Date ORDER BY Conversions) AS 'Rank' , DENSE_RANK() OVER(PARTITION BY Date ORDER BY Conversions) AS 'Dense_Rank' , NTILE(3) OVER(PARTITION BY Date ORDER BY Conversions) AS 'Ntile' FROM Orders

Функции смещения

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

• LAG илиLEAD – функция LAG обращается к данным из предыдущей строки окна, а LEAD к данным из следующей строки. Функцию можно использовать для того, чтобы сравнивать текущее значение строки с предыдущим или следующим. Имеет три параметра: столбец, значение которого необходимо вернуть, количество строк для смещения (по умолчанию 1), значение, которое необходимо вернуть если после смещения возвращается значение NULL;
• FIRST_VALUE или LAST_VALUE — с помощью функции можно получить первое и последнее значение в окне. В качестве параметра принимает столбец, значение которого необходимо вернуть.

SELECT Date , Medium , Conversions , LAG(Conversions) OVER(PARTITION BY Date ORDER BY Date) AS 'Lag' , LEAD(Conversions) OVER(PARTITION BY Date ORDER BY Date) AS 'Lead' , FIRST_VALUE(Conversions) OVER(PARTITION BY Date ORDER BY Date) AS 'First_Value' , LAST_VALUE(Conversions) OVER(PARTITION BY Date ORDER BY Date) AS 'Last_Value' FROM Orders

Аналитические функции

Аналитические функции — это функции которые возвращают информацию о распределении данных и используются для статистического анализа.

• CUME_DIST — вычисляет интегральное распределение (относительное положение) значений в окне;
• PERCENT_RANK — вычисляет относительный ранг строки в окне;
• PERCENTILE_CONT — вычисляет процентиль на основе постоянного распределения значения столбца. В качестве параметра принимает процентиль, который необходимо вычислить (в этой статье я рассказываю как посчитать медиану, благодаря этой функции);
• PERCENTILE_DISC — вычисляет определенный процентиль для отсортированных значений в наборе данных. В качестве параметра принимает процентиль, который необходимо вычислить.

Важно! У функций PERCENTILE_CONT и PERCENTILE_DISC, столбец, по которому будет происходить сортировка, указывается с помощью ключевого слова WITHIN GROUP.

SELECT Date , Medium , Conversions , CUME_DIST() OVER(PARTITION BY Date ORDER BY Conversions) AS 'Cume_Dist' , PERCENT_RANK() OVER(PARTITION BY Date ORDER BY Conversions) AS 'Percent_Rank' , PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Conversions) OVER(PARTITION BY Date) AS 'Percentile_Cont' , PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Conversions) OVER(PARTITION BY Date) AS 'Percentile_Disc' FROM Orders

Кейс. Модели атрибуции

Благодаря модели атрибуции можно обоснованно оценить вклад каждого канала в достижение конверсии. Давайте попробуем посчитать две разных модели атрибуции с помощью оконных функций.

У нас есть таблица с id посетителя (им может быть Client ID, номер телефона и тп.), датами и количеством посещений сайта, а также с информацией о достигнутых конверсиях.

Первый клик

В Google Analytics стандартной моделью атрибуции является последний непрямой клик. И в данном случае 100% ценности конверсии присваивается последнему каналу в цепочке взаимодействий.

Попробуем посчитать модель по первому взаимодействию, когда 100% ценности конверсии присваивается первому каналу в цепочке при помощи функции FIRST_VALUE.

SELECT Date , Client_ID , Medium , FIRST_VALUE(Medium) OVER(PARTITION BY Client_ID ORDER BY Date) AS 'First_Click' , Sessions , Conversions FROM Orders

Рядом со столбцом «Medium» появился новый столбец «First_Click», в котором указан канал в первый раз приведший посетителя к нам на сайт и вся ценность зачтена данному каналу.

Произведем агрегацию и получим отчет.

WITH First AS ( SELECT Date , Client_ID , Medium , FIRST_VALUE(Medium) OVER(PARTITION BY Client_ID ORDER BY Date) AS 'First_Click' , Sessions , Conversions FROM Orders ) SELECT First_Click , SUM(Conversions) AS 'Conversions' FROM First GROUP BY First_Click

С учетом давности взаимодействий

В этом случае работает правило: чем ближе к конверсии находится точка взаимодействия, тем более ценной она считается. Попробуем рассчитать эту модель при помощи функции DENSE_RANK.

SELECT Date , Client_ID , Medium -- Присваиваем ранг в зависимости от близости к дате конверсии , DENSE_RANK() OVER(PARTITION BY Client_ID ORDER BY Date) AS 'Ranks' , Sessions , Conversions FROM Orders

Рядом со столбцом «Medium» появился новый столбец «Ranks», в котором указан ранг каждой строки в зависимости от близости к дате конверсии.

Теперь используем этот запрос для того, чтобы распределить ценность равную 1 (100%) по всем точкам на пути к конверсии.

SELECT Date , Client_ID , Medium -- Делим ранг определенной строки на сумму рангов по пользователю , ROUND(CAST(DENSE_RANK() OVER(PARTITION BY Client_ID ORDER BY Date) AS FLOAT) / CAST(SUM(ranks) OVER(PARTITION BY Client_ID) AS FLOAT), 2) AS 'Time_Decay' , Sessions , Conversions FROM ( SELECT Date , Client_ID , Medium -- Присваиваем ранг в зависимости от близости к дате конверсии , DENSE_RANK() OVER(PARTITION BY Client_ID ORDER BY Date) AS 'Ranks' , Sessions , Conversions FROM Orders ) rank_table

Рядом со столбцом «Medium» появился новый столбец «Time_Decay» с распределенной ценностью.

И теперь, если сделать агрегацию, можно увидеть как распределилась ценность по каналам.

WITH Ranks AS ( SELECT Date , Client_ID , Medium -- Делим ранг определенной строки на сумму рангов по пользователю , ROUND(CAST(DENSE_RANK() OVER(PARTITION BY Client_ID ORDER BY Date) AS FLOAT) / CAST(SUM(ranks) OVER(PARTITION BY Client_ID) AS FLOAT), 2) AS 'Time_Decay' , Sessions , Conversions FROM ( SELECT Date , Client_ID , Medium -- Присваиваем ранг в зависимости от близости к дате конверсии , DENSE_RANK() OVER(PARTITION BY Client_ID ORDER BY Date) AS 'Ranks' , Sessions , Conversions FROM Orders ) rank_table ) SELECT Medium , SUM(Time_Decay) AS 'Value' , SUM(Conversions) AS 'Conversions' FROM Ranks GROUP BY Medium ORDER BY Value DESC

Из получившегося отчета видно, что самым весомым каналом является канал «cpc», а канал «cpa», который был бы исключен при применении стандартной модели атрибуции, тоже получил свою долю при распределении ценности.

Полезные ссылки:

• SELECT — предложение OVER (Transact-SQL)
• Как работать с оконными функциями в Google BigQuery — подробное руководство
• Модель атрибуции на основе онлайн/офлайн данных в Google BigQuery