|
|
практическая. Документ DOCX. Трюки с запросами, использующими агрегатные доменные функции
Трюки с запросами, использующими агрегатные доменные функции
Рано или поздно, но любой разработчик или профессиональный аналитик сталкивается с ситуацией, что проект перерастает возможности Excel и необходимо часть работы переложить с электронной таблицы на реляционную базу данных. В случае MS Office это, понятное дело, - MS Access. В виду этого на моём сайте будут появляться материалы и об Access.
Начну с малоизвестных широкой публике примеров использования доменных агрегатных функциях в запросах, при помощи которых можно делать интересные вещи. Данный материал предназначен пользователям, которые уже освоили основные виды запросов в MS Access. Сегодня обсудим 2 задачи:
Подсчёт процентной величины в записях запроса относительно суммарной величины по всему набору данных
Подсчёт в запросе какой-либо величины нарастающим итогом
ТЕРМИНЫ
Доменные агрегаты - это функции, такие как: Dsum, Dcount, Davg, Dmin, Dmax, Dfirst, Dlast, DLookup и некоторые другие. Данные функции производят какую-либо групповую операцию над всем набором данных (доменом) и возвращают результат в виде числа. Например, вызов такой функции DSUM("[Amount]", "Sales") вернёт нам итоговоую сумму по полю Amount в таблице Sales.
ПОДСЧЁТ ПРОЦЕНТНОЙ ВЕЛИЧИНЫ
В этом примере мы подсчитываем сумму проданного товара (поле [Count]) по каждому коду продукта (поле [ProductCode]), а также процентную величину, которую занимает сумма по каждому коду относительно суммы по всем кодам продуктов.
Запрос в конструкторе выглядит следующим образом:
SQL оператор
?
1
2
3
4
5
|
SELECT OrdRows.ProductCode, Sum(OrdRows.Count) AS SCount,
[SCount]/DSum("[Count]","OrdRows") AS Percentage
FROM OrdRows
GROUP BY OrdRows.ProductCode
ORDER BY OrdRows.ProductCode;
|
можно и так - без промежуточного поля [SCount]
?
1
2
3
4
5
|
SELECT OrdRows.ProductCode,
Sum(OrdRows.Count)/DSum("[Count]","OrdRows") AS Percentage
FROM OrdRows
GROUP BY OrdRows.ProductCode
ORDER BY OrdRows.ProductCode;
|
Обратите внимание, что конструкция DSUM будет возвращать в каждую запись набора одно и тоже число
ПОДСЧЁТ ВЕЛИЧИНЫ НАРАСТАЮЩИМ ИТОГОМ
Здесь мы в поле [RT] подсчитываем нарастающим итогом количество заказов с группировкой по дате заказа (поле [OrderDate]). То есть каждый последующий день включает в себя количество заказов за эту дату плюс все предыдущие даты.
Запрос в конструкторе:
SQL оператор
?
1
2
3
4
5
6
|
SELECT Orders.OrderDate,
DCount("[OrderID]","Orders",
"[OrderDate] <= #" & Format([OrderDate],"mm\/dd\/yyyy") & "#") AS RT
FROM Orders
GROUP BY Orders.OrderDate
ORDER BY Orders.OrderDate;
|
Комментарии:
Обратите внимание на то, как офомлен третий параметр в функции Dcount. Это выстраданный синтаксис, так как функция Dcount работает с датами только в стандартном формате вида #mm/dd/yyyy#.
В англоязычных книгах рекомендуют следующий формат DCount("[OrderID]","Orders","[OrderDate] <= #" & [OrderDate] & "#"), но он не работает с нашими региональными настройками, поэтому - только так, как показано.
Трюк с нарастающим итогом, как не трудно догадаться, кроется в знаке меньше или равно и динамическим формированием условия выборки.
| |
|
|
Скачать 81.8 Kb.