Cache': Как написать свою функцию агрегации для group by?

В некоторых случаях, при использовании выборки с группировкой (group by), хотелось бы использовать свой алгоритм агрегации значений. Как это сделать в Cache?

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

Для выполнения группирования нам нужно выполнить три операции:

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

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

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

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

Для операции конкатенации через запятую у меня получились вот такие функции:

ClassMethod ConcatStart() As %String [ SqlProc]
{
 ; вызывается в начале выборки
 k zzzconcat
 q ""
}

ClassMethod GetConcat(Vals As %String) 
  As %String [ SqlProc ]
{
 ; вызывается в конце выборки
 q $g(zzzconcat(Vals))
}

ClassMethod Concat(Vals As %String, Val As %String)
  As %String [ SqlProc ]
{
 ; вызывается при группировке для каждой строки
 n groupvalue
 s groupvalue=$g(zzzconcat(Vals))
 i groupvalue="" s zzzconcat(Vals)=Val
 e  s zzzconcat(Vals)=groupvalue_","_Val
 q 1
}

Функционал очистки можно вставить в функцию получения группированного значения, например:

ClassMethod GetConcat(Vals As %String) 
  As %String [ SqlProc ]
{
 ; вызывается в конце выборки
 n ret
 s ret=$g(zzzconcat(Vals))
 k zzzconcat(Vals)
 q ret
}

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

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

create table colors
 (
  fcolor varchar(80),
  fcount integer
 )

insert into colors( fcolor, fcount) values( 'red', 2)
insert into colors( fcolor, fcount) values( 'red', 4)
insert into colors( fcolor, fcount) values( 'red', 7)
insert into colors( fcolor, fcount) values( 'blue', 1)
insert into colors( fcolor, fcount) values( 'blue', 3)
insert into colors( fcolor, fcount) values( 'blue', 6)
insert into colors( fcolor, fcount) values( 'green', 2)
insert into colors( fcolor, fcount) values( 'green', 4)
insert into colors( fcolor, fcount) values( 'green', 8)

Обычные операции группировки выглядят так:

>>select count(*), fcolor
(1)>> from colors
(2)>> group by fcolor
(3)>>go
3       blue
3       green
3       red
>>

>>select sum(fcount), fcolor
(1)>>from colors
(2)>>group by fcolor
(3)>>go
10      blue
14      green
13      red
>>

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

>>select count(*), fcolor, 
  Expand_ConcatStart(), Expand_GetConcat(fcolor)
(1)>> from colors
(2)>> where Expand_Concat(fcolor,ID)=1
(3)>> group by fcolor
(4)>>go
3       blue            4,5,6
3       green           7,8,9
3       red             1,2,3
>>

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

ClassMethod ModGeomStart() As %String [ SqlProc]
{
 ; функция очищает начальные значения группировок
 k zzzgeom
 q ""
}

ClassMethod ModGeom(Vals As %String, Val As %String)
  As %String [ SqlProc ]
{
 ; функция накапливает суммы квадратов
 s zzzgeom(Vals)=Val**2+$g(zzzgeom(Vals),0)
 q 1
}

ClassMethod GetModGeom(Vals As %String)
  As %String [ SqlProc ]
{
 ; функция возвращает корень квадратный из суммы квадратов
 q $zsqr($g(zzzgeom(Vals)))
}

Проверим работу на тех же контрольных данных:

>>select count(*), fcolor, 
  Expand_ModGeomStart(), Expand_GetModGeom(fcolor)
(1)>> from colors
(2)>> where Expand_ModGeom(fcolor,fcount)=1
(3)>> group by fcolor
(4)>>go
3       blue            6.78232998312526814
3       green           9.165151389911680015
3       red             8.306623862918074855
>>

Эти функции не привязаны ни к какой таблице, поэтому я внес их к класс User.Expand из заметки Cache': Как развернуть значение параметра в несколько строк. Поэтому, получил полное имя хранимой функции с декорированием именем Expand.

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

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

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