Евгений Каратаев
В этой статье рассмотрим техническую часть группировки данных. В качестве базы данных выберем СУБД Cache', поскольку в ней существует возможность самостоятельно использовать собственные структуры данных. Из общих слов на тему "зачем" можно сказать, что типа такие задачи возникают при составлении отчетов, что это очень важно, не всегда понятно, и прочее. Все вопросы на тему "зачем" в дальнейшем будем опускать и займемся вопросом "как". А именно, как сделать так, чтобы работало, работало хорошо и чтобы было понятно, какие возможности предоставляет техника группирования.
Операция группировки в базах SQL-типа объявляется опцией GROUP BY и зачастую сопровождается опцией ORDER BY. Те, кто имел дело с языком SQL, наверняка примерно представляют, что это такое и к чему приводит. Те же, кто не использует язык SQL, имеют с одной стороны отсутствие простого декларативного объявления своих намерений, и, с другой стороны, гораздо большую гибкость и могущество, не ограниченные ничем и никакими реализациями и их магическими ограничениями. Будем следовать второму варианту - ручное программирование операции группировки, и рассмотрим виды группирования и их особенности, плюсы и минусы.
Отдадим должное методологии и опишем, в чем заключается операция группирования. Группировка в общих словах - это операция выборки данных в таком виде, в котором значения колонок рассматриваются в качестве критерия объединения строк - строки с одинаковыми значениями в группирующих колонках объединяются в одну строку.
Положим, что у нас есть набор исходных данных, на котором мы можем провести демонстрацию. В качестве примера и в связи с приближающимся новым годом выберем условную задачу "учет новогодних елочных игрушек". Положим, что в нашем распоряжении есть несколько партий новогодних игрушек, которые мы различаем по фигуре, по цвету и в каждой партии есть некоторое количество одинаковых игрушек.
Создадим тестовые данные скриптом вида:
create(n)
s:'$d(n) n=100
s:(n<1) n=-n
k ^group
n i,color,colors,figure,figures,count
s colors="красный~золотой~синий~зеленый~серебряный~желтый"
s figures="шарик~шишка~снежинка~белка~лебедь~рыбка"
f i=1:1:n d
. s color=$p(colors,"~",$r(6)+1)
. s figure=$p(figures,"~",$r(6)+1)
. s count=$r(10)+1
. s ^group(i)=color_"~"_figure_"~"_count
q
Здесь i - это некий условный номер партии. При группировании по полям цвет и фигура часть строк с их одинаковыми значениями объединяются в одну строку: если были строки
красный шарик 10
красный шарик 8
синий шарик 5
синий шарик 15
То при группировании мы должны получить
красный шарик 10
8
синий шарик 5
15
То есть из четырех исходных получили две выходные, причем в выходных строках в одну ячейку попали от одного до нескольких значений (количество игрушек в партии). Формально говоря, мы можем сделать с ними что хотим, но поскольку речь ведем о группировке, то в группировке принято из этих нескольких значений, попадающих в одну ячейку, составлять одно значение и приводить таким образом, выходные данные к классическому определению таблицы с атомарными значениями в каждой ячейке. Характер манипулирования такими наборами с целью получения одного значения называется функцией группирования. Наиболее часто встречаются самые простейшие - вроде банального сложения в столбик или вычисления их количества. В более сложных случаях значения, попавшие в одну ячейку, могут быть отсортированы по выбранному критерию, например, по дате получения партии, из которой было взято это значение и в совокупности с датой получения партии может быть получена например средняя скорость поступления таких изделий. Вообще говоря, эти функции группирования составляют совершенно отдельный интереснейший для прикладных специалистов класс задач, находящийся на стыке задач класса OLAP и Data Mining. В этой статье мы опустим их разнообразие и будем пользоваться только простейшей функцией - сложение в столбик, которой в SQL соответствует функций SUM. В приведенном выше примере запрос на SQL выглядел бы примерно как
select color, figure, SUM(count)
from NewYearToys
group by color, figure
Обычно совместно с группировкой используется операция сортировки. О ней мы скажем отдельно позднее. Надеюсь, общетеоретические сведения о группировке, приведенные выше, должны оказаться достаточными для ее технической реализации.
Итак, группировка может быть классифицирована по типу выборки данных и по ширине группировки. По типу выборки данных группировка делится на группировку с ориентацией на выборку с помощью функции $ORDER и с ориентацией на выборку с помощью функции $QUERY. По ширине группировки деление идет на нормальную и широкую.
Будем использовать данные, сгенерированные в вышеприведенном скрипте и рассмотрим как именно технически выполнить группирование. Обратим внимание на структуру выходных данных и заметим, что сочетание группирующих полей для каждой строки образует уникальное значение. Следовательно, в иерархических базах данных это сочетание должно стоять слева от знака равенства:
переменная( группирующее поле 1 ...
группирующее поле 2 ...
группирующее поле N ) = набор негруппирующих полей
Здесь под таинственными символами и обозначены различия типов группировки - в случае использования функции $ORDER используем конкатенацию значений группирующих полей, в случае использования функции $QUERY используем обычные запятые, рассматривая значения группирующих полей в качестве значений индексов соответствующего уровня.
Выполним группировку для функции $QUERY:
GroupQ()
; group to use $QUERY function
; use SUM function
k group
n i,color,figure,count
s i=""
f s i=$o(^group(i)) q:i="" d
. s color=$p(^group(i),"~",1)
. s figure=$p(^group(i),"~",2)
. s count=$p(^group(i),"~",3)
. s group(color,figure)=count+$G(group(color,figure),0)
q
Здесь выполняется проход по исходным данным, для наглядности значения полей сохраняются в отдельных переменных, после чего выполняется сложение. Функция $G используется для случая, если это сложение выполняется первый раз. Вместо сложения можем использовать любую иную функцию группирования, но в нашем примере будем пользоваться для простоты только одним сложением в столбик. После того, как данные сгруппированы в виде
group(color,figure)=SUM(count)
мы можем их получить одним проходом с помощью функции $QUERY:
WriteGroupedQ()
d QroupQ()
n color,figure,count,cf
s cf="group"
f s cf=$Q(@cf) q:cf="" d
. s color=$qs(cf,1)
. s figure=$qs(cf,2)
. s count=@cf
. w color,?15,figure,?30,count,!
q
Здесь значения полей получаются с помощью функции $QSUBSCRIPT. В случае использования этого типа группировки мы можем использовать несколько полей группирования и все равно сможем получить результат одним проходом. В целях создания более-менее формализованной обобщенной функции мы можем использовать номера в аргументах $QS. Если их получать из формальной спецификации запроса, то нет необходимости организовывать вложенные циклы прохода по уровням индексов.
Рассмотрим парный вышеприведенному метод группирования, ориентированный на использование функции $ORDER:
GroupO()
; group to use $ORDER function
; use SUM function
k group
n i,color,figure,count
s i=""
f s i=$O(^group(i)) q:i="" d
. s color=$p(^group(i),"~",1)
. s figure=$p(^group(i),"~",2)
. s count=$p(^group(i),"~",3)
. s group(color_$C(10)_figure)=
count+$G(group(color_$C(10)_figure),0)
q
Здесь результат получается в виде переменной с одним значением индекса, в котором с помощью разделителей используется символ $C(10). Для получения результата группировки можем использовать также только один проход, но с использованием функции $ORDER:
WriteGroupedO()
d GroupO()
n color,figure,count,cf
s cf=""
f s cf=$O(group(cf)) q:cf="" d
. s color=$P(cf,$C(10),1)
. s figure=$P(cf,$C(10),2)
. s count=group(cf)
. w color,?15,figure,?30,count,!
q
Здесь мы также можем составить обобщенную функцию группировки, если получим номера полей из формального запроса и подставим их в аргумент функции $PIECE.
В обоих типах группировки в правой части может стоять не одно значение негруппирующего поля, а несколько. Их можно хранить как в формате с разделителями, так и в списочном виде. В приведенном примере использовалось только одно негруппирующее поле, поэтому в случае если их несколько, код следует соответственно подправить.
Отметим плюсы и минусы обоих методов группирования. В первом случае (ориентация на $QUERY) результат выдается в отсортированном виде, и порядок сортировки является индексным порядком. Каких-либо дополнительных пересортировок уже не требуется. При этом следует помнить, что операция $QS может занять больше времени, чем $P во втором случае. К тому же обязательно следует скорректировать код для случая получения в качестве значения поля пустой строки. Например, всегда дополнять строку пробелом при группировании и удаления этого пробела при выдаче результата. Во втором случае, вообще говоря, отсортированность результата не гарантируется и определяется выбранным символом - разделителем. Если он меньше пробела, то результат будет отсортирован. И, так же как в первом случае, следует дополнять индексное значение неким символом на случай получения группировки только по одному полю и при возможности получения в качестве значения поля пустой строки.
В случае использования групировки, ориентированной на функцию $ORDER, результат, конечно, будет неким образом отсортирован, но результат врядли будет удовлетворительным, поскольку будет применяться индексная сортировка к агрегату полей, что является строковой сортировкой. В случае использования нестроковых (числовых) значений полей следует приводить их значения к строкам таким образом, чтобы сортировка проводилась в правильном порядке, соответствующем типу данных. Например, в случае использования целых чисел их следует заменять примерно как: число 123 заменяем на строку "+00000123". То есть во-первых добавляем символ знака, во-вторых дополняем нулями до некоторой выбранной длины. В случае использования дробных чисел ситуация усложняется - следует в строку вносить символ знака числа, десятичный символ, дробную часть, знак и величину порядка. Причем расположить эти части следует в порядке, обеспечивающем именно строковую сортировку. После проведения группировки с такой сортировкой в функции визуализации также следует провести соответствующую коррекцию данных, чтобы убрать нагромождение дополняющих нулей.