Главная » Статьи » Access » Запросы

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

Рано или поздно, но любой разработчик или профессиональный аналитик сталкивается с ситуацией, что проект перерастает возможности Excel и необходимо часть работы переложить с электронной таблицы на реляционную базу данных. В случае MS Office это, понятное дело, - MS Access. В виду этого на моём сайте будут появляться материалы и об Access.

Начну с малоизвестных широкой публике примеров использования доменных агрегатных функциях в запросах, при помощи которых можно делать интересные вещи. Данный материал предназначен пользователям, которые уже освоили основные виды запросов в MS Access. Сегодня обсудим 2 задачи:

  1. Подсчёт процентной величины в записях запроса относительно сумманой величины по всему набору данных

  2. Подсчёт в запросе какой-либо величины нарастающим итогом

Термины

Доменные агрегаты - это функции, такие как: Dsum, Dcount, Davg, Dmin, Dmax, Dfirst, Dlast, DLookup и некоторые другие. Данные функции производят какую-либо групповую операцию над всем набором данных (доменом) и возвращают результат в виде числа. Например, вызов такой функции DSUM("[Amount]", "Sales") вернёт нам итоговоую сумму по полю Amount в таблице Sales.

Подсчёт процентной величины

В этом примере мы подсчитываем сумму проданного товара (поле [Count]) по каждому коду продукта (поле [ProductCode]), а также процентную величину, которую занимает сумма по каждому коду относительно суммы по всем кодам продуктов.

Запрос в конструкторе выглядит следующим образом:

SQL оператор

можно и так - без промежуточного поля [SCount]

Обратите внимание, что конструкция DSUM будет возвращать в каждую запись набора одно и тоже число

Подсчёт величины нарастающим итогом

Здесь мы в поле [RT] подсчитываем нарастающим итогом количество заказов с группировкой по дате заказа (поле [OrderDate]). То есть каждый последующий день включает в себя количество заказов за эту дату плюс все предыдущие даты.

Запрос в конструкторе:

SQL оператор

Комментарии:

  1. Обратите внимание на то, как офомлен третий параметр в функции Dcount. Это выстраданный синтаксис, так как функция Dcount работает с датами только в стандартном формате вида #mm/dd/yyyy#.

  2. В англоязычных книгах рекомендуют следующий формат DCount("[OrderID]","Orders","[OrderDate] <= #" & [OrderDate] & "#"), но он не работает с нашими региональными настройками, поэтому - только так, как показано.

  3. Трюк с нарастающим итогом, как не трудно догадаться, кроется в знаке меньше или равно и динамическим формированием условия выборки.

Категория: Запросы | Добавил: dsb75 (26.11.2014) | Автор: Батьянов Денис E W
Просмотров: 2462 | Комментарии: 6 | Теги: dcount, domain agregate function, Query, dsum | Рейтинг: 5.0/1
Всего комментариев: 6
5 100tsky   (04.12.2014 12:33)
да, попробуйте, плиз, интересно ваше мнение

0
6 dsb75   (04.12.2014 15:51)
Честно говоря, я не большой специалист в SQL, но думаю, что ваш способ более затратный в плане вычислительных ресурсов, т.к. перемножить таблицы друг на друга стоит дорого.

3 100tsky   (04.12.2014 06:44)
Добрый день!

спасибо за материалы

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

SELECT DimDate_microLight.Datekey, facts.счет, Sum(facts.сумма) AS [Sum-сумма]
FROM DimDate_microLight, facts
WHERE (((facts.дата)<=[DimDate_microLight]![Datekey]) AND ((DimDate_microLight.Datekey)<=(SELECT Max(facts.дата) FROM [facts])))
GROUP BY DimDate_microLight.Datekey, facts.счет
ORDER BY facts.счет;

0
4 dsb75   (04.12.2014 12:29)
Хм... таблицы не связаны?

1 дэн   (01.12.2014 14:57)
Спасибо. Искал нарастающий итог. Именно в конструкторе, а не через программирование. И вот уже перестав искать случайно наткнулся на ваш сайт, читая про диаграмму Ганта на другом)

0
2 dsb75   (01.12.2014 14:59)
smile

Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Яндекс.Метрика