Как в запросе с группировкой получить значение без использования групповых операций по одному из полей?

Приветствую.

По причине большого количества строк в таблице excel решил перейти на access, для подготовки данных с последующей загрузкой результатов в excel. По сути одноразовая задача. Но казалось бы в простом запросе для меня возник нереальный затык, над которым я бьюсь несколько дней и пребываю в шоке от того, что не могу нагуглить ответ на этот вопрос.

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

Казалось бы всё просто. Я создал запрос с группировкой по товару, вид операции "продажа", min по цене. Всё хорошо, я получаю результат. Для каждого товара найдена минимальная цена продажи. Но вот проблема, а как же теперь к найденной строке прицепить какой магазин и в каком количестве продаёт? При добавлении таких полей в запрос с группировкой, требуется указать групповую операцию, а мне нужно отобразить исходное значение магазина и количество в продаваемой партии для найденной цены. Ни одно из условий группировки не подходит для этой задачи, возможно кроме выражения, но поиски какое же выражение использовать для моей цели, так же не привели к успеху.

Возможно эту задачу можно решить запросом без группировки? Буду благодарен любым подсказкам.

0

Примерный запрос в SQL. Без выложенной Вашей БД может быть только структурно

1

mobile, спасибо за ответ. Я так понимаю это запрос с под запросами? Попробовал этот код, результат формировался очень долго и итогов я так и не дождался. Видимо я что-то упустил в формулировании задачи. Попробую составить упрощённый файл mdb.

0

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

Вложения

Анализ торговли.rar (23.2 Кб, 12 просмотров)

0

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

0

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

Вот смотри в твоем примере "Наибольшая цена продажи" Смартфон в разных ценах в 7 экземплярах. И какой из них выбрать? Какой алгоритм отбора? Даже по макс.цене есть 2 записи. Какую их них выбрать? Принцип какой отбора?

0

Сообщение от odbcsql Вот смотри в твоем примере "Наибольшая цена продажи" Смартфон в разных ценах в 7 экземплярах. И какой из них выбрать? Какой алгоритм отбора? Даже по макс.цене есть 2 записи. Какую их них выбрать? Принцип какой отбора?

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

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

0

Allexxandr, смотрите вложение. На форме Form1 выбираете тип объявления (продажа/покупка), показатель (макс/мин) и нажимаете большую кнопку. Для почти 65 тысяч записей время работы меньше секунды.
Что сделано:
1. В т.Объявления заданы индексы на поля кода товара, цены и количества
2. Создана вспомогательная таблица Temp куда грузятся макс или мин цены и количество для заданного типа объявления и кода товара. Там тоже созданы индексы.
Без индексов будет работать в 100 раз медленнее.

Для удобства, а также заполнения таблицы Temp, создана форма Form1. На ней, помимо полей со списком и кнопки, также 2 поля где показывается количество записей в исходной таблице и время работы до вывода данных.
Также создана форма MCena для вывода данных. Можно было бы конечно использовать запрос, но в нем труднее менять надписи и тогда сложнее ориентироваться. Форма MCena выходит автоматом после нажатия кнопки.

Таблицы Объявления, Магазины и товары сейчас забиты мусором. Я программно вставлял туда данные для проверки скорости.

Да, имейте в виду, что в Вашей БД не была присоединена библиотека DAO. Я вставил ее в референсах. Без этой библиотеки работать не будет

Вложения

Анализ торговли.rar (1.15 Мб, 17 просмотров)

1

Сообщение от mobile Allexxandr, смотрите вложение. На форме Form1 выбираете тип объявления (продажа/покупка), показатель (макс/мин) и нажимаете большую кнопку. Для почти 65 тысяч записей время работы меньше секунды...

Спасибо! Буду смотреть. (Насчёт индексов я позже подумал, у объявлений ведь уникальные коды есть).

Добавлено через 3 часа 21 минуту
mobile, огромное спасибо за титанический труд!

В файле немного разобрался. В основной базе пока не тестировал, нужно ещё перенести данные в неё с изменением имён полей на исходные.
Правда такой расширенный функционал с формами и макросом VBA совместно с запросом SQL усложняет понимание и дальнейшее редактирование.
Для моих целей вместо одного изменяемого запроса, было бы достаточно два постоянных запроса:
-- минимальная продажа (MinSell) и
--максимальная покупка (MaxBuy).

Результаты этих запросов будут связаны c листами в excel, в которых будет происходить дальнейшая обработка информации.

Как я понимаю для реализации такого варианта мне нужно по аналогии создать две временных таблицы (temp) и под каждую переделать макрос, который будет запускаться при выполнении запроса? Или есть способ проще?

Добавлено через 1 час 28 минут
В основной базе запустить запрос не получилось так как нет библиотеки DAO.
Я перенёс листы с данными в Ваш файл. В среднем в файле более миллиона строк (1 051 000).
Проверял скорости обработки с разным количеством строк.
--142 тысячи за 3 секунды,
--525 тысяч за 20 секунд,
--746 тысяч за 32 секунды,
--а вот требуемое количество более миллиона обрабатывается очень долго, за 10 минут полоска выполнения запроса заполняется до 5%, далее у меня не хватает терпения ждать, но по расчётам получается 200 минут, что очень долго.

Подскажите, пожалуйста, в чём может быть причина?
Почему до 750 тысяч строк запрос выполняется быстро, а более миллиона строк очень долго?

0

Причин может быть много:
- мало оперативной памяти
- во время работы программы открыто много других приложений, съедающих память
- не хватает места на диске
- мал дисковый кэш
- злобный антивирус
- размер БД вместе с вспомогательными таблицами приближается к 2 ГБ
- перед выполнением большой таблицы выполнялись многократные удаления большого количества данных и накопился мусор. Сжатие БД перед запуском большой таблицы поможет в этом случае.

2

Сообщение от mobile Причин может быть много...

Проверил.
Оперативки 8 ГБ.
В момент выполнения ничего особенного поедающего память не запущено.
Свободного места на диске 300Гб,
размер файла 180 Мб,
антивирус отключал.
Эффекта не было.

В момент выполнения запроса занято всего 25% ресурсов процессора и всего 55Мб оперативной памяти.

Может быть эта проблема как-то связана с подключаемой библиотекой? Не может ли быть в ней ограничения на количество обрабатываемых строк? Например как в последней версии excel 2016 не более 1 048 000 строк?

0

Сообщение от Allexxandr Как я понимаю для реализации такого варианта мне нужно по аналогии создать две временных таблицы (temp) и под каждую переделать макрос, который будет запускаться при выполнении запроса? Или есть способ проще?

Способ проще реализован УЖЕ. Постановкой "Для моих целей вместо одного изменяемого запроса, было бы достаточно два постоянных запроса" вы не упрощаете задачу, а усложняете: постоянные запросы в этой задаче все равно невозможны, так что просто хотите вместо одного изменяемого запроса - два изменяемых.

Добавлено через 10 минут

Сообщение от Allexxandr --746 тысяч за 32 секунды, --а вот требуемое количество более миллиона обрабатывается очень долго, за 10 минут полоска выполнения запроса заполняется до 5%, далее у меня не хватает терпения ждать, но по расчётам получается 200 минут, что очень долго.

Из текста запроса MCena удалите текст - в этом запросе операция группировки не изменит результирующий набо данных, но очень замедлит его выполнение.

1

texnik-san, спасибо за участие в обсуждении. Как я уже понял, реализовать задачу одним запросом не возможно. Мне нужны два запроса без использования формы ввода, так как к книге excel для определенного листа будет импорт данных из своего запроса access. Первый запрос нужен типа таблицы temp (не обязательно ведь его создавать в виде таблицы?), а второй и третий будут в свою очередь оперировать с этим запросом temp, и опираясь на файл, который сделал mobile, попробую создать запросы, чтобы "подтягивать" к строкам в temp, соответствующие поля из связанных таблиц.

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

Если убрать операцию группировки, весь смысл пропадает, так как вместо одной строки по одному товару с максимальной или минимальной ценой, выдаётся много строк с одинаковыми ценами, но разными магазинами. И Выполнение запроса это изменение не ускорило.

0

Сообщение от Allexxandr Первый запрос нужен типа таблицы temp (не обязательно ведь его создавать в виде таблицы?)

Вариант с запросом БЕЗ создания таблицы вы уже опробовали раньше. Было медленно.

Сохранение резальтатов вложенного запроса во временной таблице - это способ ускорить работу основного запроса.

Добавлено через 4 минуты

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

Мы, видимо, говорим о разных запросах. Я о том, который в базе, приложенной к этому посту

Сообщение от mobile Allexxandr, смотрите вложение.

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

Добавлено через 5 минут

Сообщение от Allexxandr так как к книге excel для определенного листа будет импорт данных из своего запроса access.

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

Тормоза при миллионе записей возникают потому, что аксес в принципе и не расчитан на работу с такими объемами. Это облегченная СУБД и ее потолок - пара сотен тысяч записей. Больше она теоретичеки может, а практически - вы сами видите.

Добавлено через 31 минуту
Вообще, что я порекомендовала бы для ускорения в вашем случае:

1. Разделить таблицу объявлений на две - в одной только объявления "покупают", в другой - только "продают". Все равно вы ищете всегда только в одном из подмножества.
Если же это не подходит - то хотя бы исправьте тип поля на числовой или логический (например, -1 продают, 0 покупают) и как самый минимум - индекируйте это поле!!! У вас по нему выполняется отбор, а поле тектовое и индекса нет.

1

Allexxandr, попробовал сгенерировать 2 миллиона записей, точнее 1 975 700, со случайными ценами и количеством, а также сгенерированной тысяче магазинов. Время исполнения 6 секунд. Так что причина не в количестве записей. Поскольку ресурсы Вашего компа значительны и не препятствуют работе (хотя о дисковом кэше Вы не упомянули и также не сказали о сжатии БД), то предположительно причина медленной работы в структуре и характере самих данных, полученных из Excel.

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

1

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

3. И вот только при выполнении пп.1 и 2 станет возможным более оптимальное решение (см. файл). Таблицу на две я делить пока не стала, создала логическое поле "продают" (чтобы не путать со старым текстовым полем "покупают") - в нем значение "истина" (оно же -1) соответствует продаже, а "ложь" (оно же 0) - покупке.

Вложения

Анализ торговли.rar (52.2 Кб, 7 просмотров)

1

mobile, texnik-san, спасибо, буду разбираться дальше. Видимо проблема возникла у меня из -за переноса в основную базу, а там и поля и таблицы иначе называются, может быть я где то, что-то не так переименовал.
Это я в базе для примера и листы и поля назвал по русски, чтобы было нагляднее. Как говорится: хотел как лучше, а получилось как всегда

0

texnik-san, перенёс запросы в свою базу, переименовал, при вызове зависает, при отмене сообщает что "данный подчинённый запрос должен возвращать не более одной записи", видимо не осилю я правильное переименование...

Во вложении база с оригинальными именами таблиц и полей. Там же примеры запросов на максимальную покупку (MaxBuyGroup). Задача "подцепить" к этому запросу поля из связанной таблицы (пример MaxBuyNeed). для того чтобы соблюсти размеры пришлось удалить основную часть информации по товару, из-за этого запрос MaxBuyNeed ничего не выдаёт, так как не находит соответствий.

Вложения

Base.rar (169.2 Кб, 4 просмотров)

0

Allexxandr,

Ваши запросы вам УЖЕ рассказали, как привязать. Этого я не улучшу.

МОИ запросы - ну, я попробовала угадать, какому полю в предыдущей базе соответствует что из выложенной вами сейчас, но не все поддается логике.
Скажем, я думала, что
--таблица invTypes содержит список товаров,
--а поле type таблицы Orders содержит эти коды этих товаров (так было вполне логично подумать, глядя на вашу схему данных)

совпадений по кодам в этих таблицах нету ни одного. Если бы совпадения были - запрос бы работал.

Вложения

Base1.rar (184.4 Кб, 5 просмотров)

1

Сообщение от Allexxandr запрос MaxBuyNeed ничего не выдаёт, так как не находит соответствий.

Сделайте запрос на создание таблицы, связанной со всеми остальными как в MaxBuyNeed. Для максимальной представительности надо новую таблицу "перемешать" случайным образом, чтобы равномерно представить все возможные сочетания. Я предположил, что для этой цели подойдет счетчик, поле Код, но не исключено, что есть лучшие варианты. Эту таблицу перешлите.
Если Вы также удаляли информацию из справочников, то запрос на создание надо делать с усеченными справочниками

1