Сложение данных из символьных полей в запросе с группировкой (аналог функции Sum()

Слияние полей из разных строк запроса.
Автор Юрий Шерман
16.01.2002 г.
Как соединить данные полей из разных строк запроса в одно поле? Вашему вниманию предлагается оптимальное решение.
Автор: Юрий Шерман
www.tour-soft.com

Слияние полей из разных строк запроса. Новая версия.

Для полей из разных строк запроса в SQL имеется целый ряд групповых функций: Sum, Min, Max и т.д. Однако если Вам надо объединить поля из разных строк, разделяя их, например, запятой, то такой групповой операции Вы не найдете. Придется писать собственное решение.

Постановка задачи.
Пусть у Вас имеется Tab1 (таблица или запрос) с полями: ID и Fam.
ID - групповой номер, он может повторяться, но не пуст (для упрощения).
Fam - поле с фамилией.

Требуется создать запрос TabUnion с двумя полями:
- ID, значения которого взяты из Tab1, но не повторяются;
- FamUnion, которое состоит из списка фамилий через запятую.
Список должен состоять из фамилий с одним ID.

Рассмотрим решение задачи от самого простого случая до самого общего.

Случай 1. Имеются упрощающие предположения.
Предположение 1: Tab1 должна быть таблицей, отсортированной по ID.
Предположение 2: в Tab1 должно быть не менее двух различных значений ID.
Предположение 3: пустых фамилий нет.

Текст запроса TabUnion1:
Select ID, Last(UnionStr1(ID,Fam)) AS FamUnion FROM Tab1
GROUP BY ID;

Описание функции UnionStr1:

Public Function UnionStr1(ID, Fam)
Static IDOld, FamUnion
If IDOld <> ID Then
IDOld = ID
FamUnion = Null
End If

FamUnion = (FamUnion + ", ") & Fam
UnionStr1 = FamUnion
End Function

Оценка скорости: время обработки пропорционально C*n, где C – константа, n – количество записей в Tab1. Очевидно, что оценку улучшить нельзя. То есть метод оптимален.

Случай 2. Отличается от случая 1 снятием предположения 2.
То есть, допускается случай, когда все записи Tab1 имеют один ID.
Тогда приходится заниматься установкой начального значения IDOld.

Текст запроса TabUnion2:
Select ID, Last(UnionStr2(ID,Fam)) AS FamUnion
FROM Tab1
WHERE IsEmpty(UnionStr2())
GROUP BY ID;

Описание функции UnionStr2:

Public Function UnionStr2(Optional ID, Optional Fam)
Static IDOld, FamUnion
If IsMissing(ID) Then
IDOld = Empty
Exit Function
End If
If IDOld <> ID Then
IDOld = ID
FamUnion = Null
End If
FamUnion = (FamUnion + ", ") & Fam
UnionStr2 = FamUnion
End Function

Оценка скорости: такая же, как и в случае 1.
Замечание: чтобы снять предположение о не пустых фамилиях можно (чтобы избежать лишних запятых) использовать в более тяжеловесную конструкцию.
В функциях место строки:
FamUnion = (FamUnion + ", ") & Fam
следует писать:
FamUnion = IIf (IsNull(FamUnion), Fam, FamUnion & (", " + Fam))

Случай 3. Tab1 таблица или запрос. Предположений о сортированности по ID и о пустых фамилиях нет.
Предположение: нет повторяющихся пар: ID, Fam.

В этом случае необходима промежуточная сортировка Tab1 по ID.

Текст промежуточного запроса Tab1Sort для сортировки Tab1:
Select DISTINCT ID, Fam FROM Tab1 ORDER BY ID, Fam;

Текст запроса TabUnion3:
Select ID, Last(UnionStr2(ID,Fam)) AS FamUnion
FROM Tab1Sort
WHERE IsEmpty(UnionStr2())
GROUP BY ID;

Здесь применяется та же функция, что и в случае 2.

Оценка скорости: оценка ухудшается из-за применения сортировки. Известно, что сортировку быстрее, чем C*n*log(n) сделать нельзя. Обычно используют сортировку методом Шелла, которая имеет оценку C*n*log(n)^2. Эта же оценка является оценкой быстродействия в данном случае. Быстрее, очевидно, сделать нельзя.
Замечание: в промежуточной сортировке используется предикат DISTINCT. Он превращает запрос Tab1Sort в статический. Без этого метод неработоспособен. Но этот же предикат удаляет повторяющиеся пары ID, Fam. Поэтому необходимо указанное выше предположение.

Случай 4. Tab1 таблица или запрос. Никаких дополнительных предположений нет.

Для применения метода слияния из случая 3 необходимо все строки Tab1 сделать уникальными.
Для этого используем нумерацию строк запроса, изложенную в статье «Нумерация строк запроса. Новый подход».

Текст промежуточного запроса Tab1Num для нумерации Tab1:
Select DISTINCT Numeration(ID) As Num, ID, Fam
FROM Tab1
WHERE Numeration()=0;

Описание функции Numeration дано в статье, указанной выше.

Текст промежуточного запроса Tab1SortNum для сортировки Tab1Num:
Select DISTINCT ID, Fam, Num FROM Tab1Num ORDER BY ID, Fam;

И, наконец, текст запроса TabUnion4:
Select ID, Last(UnionStr2(ID,Fam)) AS FamUnion
FROM Tab1SortNum
WHERE IsEmpty(UnionStr2())
GROUP BY ID;

Здесь применяется та же функция, что и в случае 2.

Оценка скорости: оценка скорости та же, что и в случае 3, так как нумерация имеет оценку C*n.

Решение поставленной задачи завершено.

Другой вариант постановки задачи.
Предположения о Tab1 остаются прежними.
Теперь требуется заполнить пустую таблицу Tab2, содержащую поля ID и FamUnion значениями, описанными в основной задаче.

Метод 1. Очевидный.
Формируем запрос TabUnion одним из способов, указанных выше.
Затем записываем его результат в Tab2.

Метод 2. Заполнение таблицы Tab2 с помощью Recordset.
Это можно сделать с помощью следующей функции:

Public Function UnionStr3()
Dim R As Recordset, W As Recordset
Dim IDOld, FamUnion

Set R = CurrentDb.OpenRecordset("SELECT * FROM Tab1 ORDER BY ID,Fam")
If R.EOF Then Exit Function

Set W = CurrentDb.OpenRecordset("Tab2")

Do
NewID:
IDOld = R!ID
FamUnion = Null

Union:
FamUnion = (FamUnion + ", ") & R!Fam
R.MoveNext

If R.EOF Then GoTo WriteUnion
If IDOld <> R!ID Then

WriteUnion:
W.AddNew
W!ID = IDOld
W!FamUnion = FamUnion
W.Update
Else
GoTo Union
End If
If Not R.EOF Then GoTo NewID
Exit Do
Loop
End Function

Область применения: в том случае, когда кроме составления списка нужно сделать еще какие-нибудь нестандартные операции.
Недостаток: по факту работает значительно медленнее метода 1.


Метод 3. Заполнение таблицы при помощи запросов (по Митину).
Ссылка: http://c85.cemi.rssi.ru/Access... ?QID=14147

Оригинальный и неочевидный метод.
Выполняется с помощью двух запросов.
1. Запись в Tab2 уникальных ID без фамилий.
Текст запроса WriteID:
INSERT INTO Tab2 (ID) Select DISTINCT ID FROM Tab1;

2. Запись списков фамилий.
Текст запроса TabUnion5:
UPDATE Tab2 INNER JOIN Tab1 On Tab2.ID = Tab1.ID
Set Tab2.FamUnion = ([Tab2].[FamUnion]+", ") & [Tab1].[Fam];

Оценка скорости: по-видимому, работает быстрее метода 1 за счет отсутствия вызова специальных функций.
Недостаток: "Однако в T-SQL это непрокатывает" (Митин).


Общий недостаток всех методов: при большом объеме данных все методы заполнения Tab2 работают достаточно долго.
Если результат (список фамилий) используется часто, то лучше Tab2 сделать постоянной таблицей в базе общего доступа. Далее корректировать ее при изменении Tab1. Так как одиночная корректировка Tab1 меняет только один список в Tab2, то при этом объем работы будет мал и выполнится быстро.

Реализация для базы Access: в форме, в которой можно изменить Tab1 следует на события создания/измения/удаления записи молча проводить синхронные изменения в Tab2.

Собственно такую же рекомендацию для MS SQL дает и Митин: "Я в общем то для обеспечения максимальной производительности остановился на решении, когда на триггерах поддерживается перечень упаковок в основной табл. при изменении записей в табл. с упаковками."