Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Сводная таблица из нескольких книг с разгруппировкой, Необходимо сформулировать оптимальный сложный SQL запрос
 
#1
08.10.2015 13:40:43
Добрый день!

Перерыл интернет, решения не нашел.

Прикрепил наглядную модель.

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

Хочу построить сводную таблицу по всем сотрудникам, но при этом в источнике данных столбцы с названиями городов превращаются в два столбца:
1) Название города;
2) Сумма по городу;

Сейчас я смог сделать два решения

Решение 1: через 3 подключения

1-е и 2-е подключения собирают данные из файлов Вани и Васи в файл "Сводная" в соответствующие листы, при этом SQL запрос строится из нескольких подзапросов, которые переворачивают данные под нужный формат.

3-е подключение соединяет данные из двух листов этой же книги "Сводная" и строит по ним сводную таблицу.
Плюс данного решения - понятные SQL запросы.

Решение 2: через 1 подключение (сложное)

Берем SQL 1-го и 2-го подключения из Решения 1 и соединяем через UNION ALL
SQL получается не читаемым и громоздким

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

В данной модели это терпимо, так как столбцов мало.

Вопрос:

В реальности столбцов по городам 17 и сотрудников больше.

Использовать 2-е решение нереально, SQL будет не обосновано огромным.

Прошу Вас помочь составить одного SQL запроса таким образом, чтобы он выполнил пошаговые действия:
1) Сначала соединил все таблицы пользователей, результату подзапроса присвоим псевдоним tb1;
2) Потом по псевдониму tb1 совершил "разгруппировку" данных по всем городам, результату дадим псевдоним tb2;
3) После чего INNER JOIN по tb1.ID=tb2.ID;

Проблема вся в псевдонимах...
Я не могу сообразить как сделать SQL запрос оптимальным, и как добиться использования одного псевдонима для нескольких подзапросов, может все это надо обернуть в еще один SELECT?.

Элегантное решение этого вопроса будет полезно многим!)
Ведь для составления сводной таблицы по данным от разных пользователей, потребуется прописать всего одно подключение, это очень удобно и сильно облегчит процессы контроля и отчетностей.

Заранее спасибо за Ваши комментарии!)))
Изменено: vetrintsev - 08.10.2015 13:41:37
 
 
 
#2
08.10.2015 13:54:07
так а что сложного?
Код
select t1.*, t2.* from
(
select * from  table
) t1,
(select * from  table2
) t2
where t1.id=t2.id

ну и так далее
Изменено: B.Key - 08.10.2015 13:59:39
 
 
 
#3
08.10.2015 14:09:27
Вот запрос (убрал адрес к файлам для удобного восприятия) на рассматриваемой модели:
Код
SELECT tb1.Контрагент, tb2.Город, tb2.Сумма
FROM 
(
SELECT tb1.ID, tb1.Контрагент 
FROM `Ваня.xlsx`.`Данные$` AS tb1
WHERE tb1.Контрагент
) as tb1 
INNER JOIN 
(
SELECT tb1.ID, 'Саратов' AS Город, tb1.Саратов AS Сумма
FROM `Ваня.xlsx`.`Данные$` AS tb1
WHERE tb1.Саратов
UNION ALL
SELECT tb1.ID, 'Балаково' AS Город, tb1.Балаково AS Сумма
FROM `Ваня.xlsx`.`Данные$` AS tb1
WHERE tb1.Балаково 
UNION ALL
SELECT tb1.ID, 'Энгельс' AS Город, tb1.Энгельс AS Сумма
FROM `Ваня.xlsx`.`Данные$` AS tb1
WHERE tb1.Энгельс 
) as tb2 ON tb1.ID = tb2.ID
UNION ALL
SELECT tb1.Контрагент, tb2.Город, tb2.Сумма
FROM 
(
SELECT tb1.ID, tb1.Контрагент 
FROM `Вася.xlsx`.`Данные$` AS tb1
WHERE tb1.Контрагент
) as tb1 
INNER JOIN 
(
SELECT tb1.ID, 'Саратов' AS Город, tb1.Саратов AS Сумма
FROM `Вася.xlsx`.`Данные$` AS tb1
WHERE tb1.Саратов
UNION ALL
SELECT tb1.ID, 'Балаково' AS Город, tb1.Балаково AS Сумма
FROM `Вася.xlsx`.`Данные$` AS tb1
WHERE tb1.Балаково 
UNION ALL
SELECT tb1.ID, 'Энгельс' AS Город, tb1.Энгельс AS Сумма
FROM `Вася.xlsx`.`Данные$` AS tb1
WHERE tb1.Энгельс 
) as tb2 ON tb1.ID = tb2.ID
Тут псевдонимы используются и этот запрос работает.Но здесь для каждой таблицы происходить разгруппировка по городам

Надо упростить запрос:

Т.е. должно получиться что-то вроде:
Код
SELECT tb1.Контрагент, tb2.Город, tb2.Сумма 
FROM 
(
SELECT tb0.ID, tb0.Контрагент 
FROM `Ваня.xlsx`.`Данные$` AS tb0
WHERE tb0.Контрагент
UNION ALL
SELECT tb0.ID, tb0.Контрагент 
FROM `Вася.xlsx`.`Данные$` AS tb0
WHERE tb0.Контрагент
) as tb1 
INNER JOIN 
(
SELECT tb1.ID, 'Саратов' AS Город, tb1.Саратов AS Сумма
FROM tb1
WHERE tb1.Саратов
UNION ALL
SELECT tb1.ID, 'Балаково' AS Город, tb1.Балаково AS Сумма
FROM tb1
WHERE tb1.Балаково 
UNION ALL
SELECT tb1.ID, 'Энгельс' AS Город, tb1.Энгельс AS Сумма
FROM tb1
WHERE tb1.Энгельс 
) as tb2 ON tb1.ID = tb2.ID
Здесь подзапросы второго подзапроса (после INNER JOIN), который делает разгруппировку по городам, используют псевдоним результатов первого подзапроса.

Как здесь надо обернуть tb1 и во что, чтобы подзапросам tb2 стала видна tb1?

PS: Спасибо за оперативную реакцию)
Изменено: vetrintsev - 08.10.2015 14:12:45
 
 
 
#4
08.10.2015 14:13:52
Вы бы лучше подумали над правильной структурой данных.
если город добавится будете еще поле добавлять?
Структура должна быть такой:

Код
CITY:
IdCity
NameCity

USERCOMPANY:
IdUserCompany
NameUserCompany

DATA:
IdData
IdUserCompany
IdCity
ValueData

---------------------
Тогда и запросы будут простые и анализировать просто.
Изменено: B.Key - 08.10.2015 14:14:18
 
 
 
#5
08.10.2015 14:14:11
Очень элегантно можно решить через надстройку PowerQuery. Если у вас это регулярная задача, то пользуйтесь, всё интуитивно получается.
  1. Создаем подключения к исходным данным.
  2. Добавляем таблицы одна к другой.
  3. Делаем UnPivot ("Отменить свертывание столбцов")
  4. Всё, готово, выгружаем на лист в Таблицу и по ней строим сводную.
вот как это выглядит http://joxi.ru/gV2VPMkTVEa1rv
Изменено: Максим Зеленский - 08.10.2015 14:17:41
F1 творит чудеса
 
 
 
#6
08.10.2015 14:19:47
Цитата
B.Key написал:
Вы бы лучше подумали над правильной структурой данных.
если город добавится будете еще поле добавлять?
Конечно, это первое что пришло мне в голову, но по ряду причин пришлось отказаться.
Города у нас добавляются раз в N лет, и да тогда его придется добавить в инструкцию SQL.
Вариант разносить города по строкам не удобен, одна строка - один заказ. Иначе сотрудники кашу создадут.
У нас есть уже и не одна централизованная БД, но она пока не позволяет отслеживать нужные показатели. Создавать еще одну, да даже в ACCESS, не преживется.
Люди опять начнут плодить у себя excel'файлы для личного контроля, я не хочу их заставлять дублировать данные в еще одну ИСУ.
В общем ищу решение для конкретного случая.
Спасибо за ответ.
 
 
 
#7
08.10.2015 14:23:10
Цитата
Максим Зеленский написал:
  1. Создаем подключения к исходным данным.
  2. Добавляем таблицы одна к другой.
  3. Делаем UnPivot ("Отменить свертывание столбцов")
  4. Всё, готово, выгружаем на лист в Таблицу и по ней строим сводную.
Если я один раз выполню эту процедуру, до дальнейшие обновления данных из файлов будут происходить автоматически (при открытие, по времени и т.д.)?
но спасибо, я не знал про такую надстройку.
 
 
 
#8
08.10.2015 14:25:51
Почему Вы не хотите хранить данные правильно, а представлять их в удобном для пользователя виде?
Хотя сами решайте, мое дело предложить правильное решение, а Вам выбирать и пользоваться тем что выбрали :).
------
Удачи Вам.
 
 
 
#9
08.10.2015 14:48:02
Цитата
B.Key написал:
Почему Вы не хотите хранить данные правильно, а представлять их в удобном для пользователя виде?
Я же написал, для конечного юзверя, расписывать затраты в городах не по столбцам а по строкам не представляется возможным.
Согласен, что это правильно с точки зрения структуры хранения информации, но excel это ведь не база данных.
 
 
 
#10
08.10.2015 14:54:24
Цитата
Максим Зеленский написал:
Очень элегантно можно решить через надстройку PowerQuery.
Оказывается эта надстройка только для 2010, и для 2007 нет такой надстройки... А в офисе стоят только 2007-е(
 
 
 
#11
08.10.2015 14:57:25
Увы, да, в 2010-13 это надстройка, в 2016 уже включена в Excel по умолчанию.
Можно еще через PowerPivot, но так же, с 2010...
F1 творит чудеса
 
 
 
#12
08.10.2015 16:33:48
А кто-нибудь знает, можно ли в этих подключения ODBC через excel driver в SQL писать инструкцию WITH?
https://msdn.microsoft.com/ru-ru/library/ms175972(v=sql.120).aspx

Такой запрос:
Код
WITH tb1 AS (
SELECT tb0.ID, tb0.Контрагент 
FROM `C:\Users\avetrintsev\Desktop\Запрос\Ваня.xlsx`.`Данные$` AS tb0
WHERE tb0.Контрагент
UNION ALL
SELECT tb0.ID, tb0.Контрагент 
FROM `C:\Users\avetrintsev\Desktop\Запрос\Вася.xlsx`.`Данные$` AS tb0
WHERE tb0.Контрагент
)
SELECT tb1.Контрагент, tb2.Город, tb2.Сумма
FROM tb1
INNER JOIN 
(
SELECT tb1.ID, 'Саратов' AS Город, tb1.Саратов AS Сумма
FROM tb1 
WHERE tb1.Саратов
UNION ALL
SELECT tb1.ID, 'Балаково' AS Город, tb1.Балаково AS Сумма
FROM tb1
WHERE tb1.Балаково 
UNION ALL
SELECT tb1.ID, 'Энгельс' AS Город, tb1.Энгельс AS Сумма
FROM tb1
WHERE tb1.Энгельс 
) as tb2 ON tb1.ID = tb2.ID

Выдал ошибку:
Цитата
[Microsoft][Драйвер ODBC Excel] Ошибочная инструкция SQL; Предполагалось DELETE, INSERT, PROCEDURE, SELECT или UPDATE.
Он просто не проглатывает WITH? или я где-то ошибку допустил в SQL запросе?
 
 
 
#13
08.10.2015 17:09:19
Так вы почитайте по ссылке, что привели, что это за язык и в каких средах и с какой версии он поддерживается :))
 
 
 
#14
09.10.2015 06:20:04
Доброе время суток
vetrintsev, а какой смысл использовать Inner Join? только для того, что бы в каждой операции Union All не употреблять вывод контрагента?
По моему проще будет так, коль нужно всё равно выводить каждый столбец суммы и названия города для неё.
Код
Select ID,[Контрагент],'Ваня' As Исполнитель,'Саратов' As Город,[Саратов] As Сумма
From [c:\1\Ваня.xlsx].[Данные$] Where [Саратов] Is Not Null
Union All
Select ID,[Контрагент],'Ваня' As Исполнитель,'Энгельс' As Город,[Энгельс] As Сумма
From [c:\1\Ваня.xlsx].[Данные$] Where [Энгельс] Is Not Null
Union All
Select ID,[Контрагент],'Ваня' As Исполнитель,'Балаково' As Город,[Балаково] As Сумма
From [c:\1\Ваня.xlsx].[Данные$] Where [Балаково] Is Not Null
Union All
Select ID,[Контрагент],'Вася' As Исполнитель,'Саратов' As Город,[Саратов] As Сумма
From [c:\1\Вася.xlsx].[Данные$] Where [Саратов] Is Not Null
Union All
Select ID,[Контрагент],'Вася' As Исполнитель,'Энгельс' As Город,[Энгельс] As Сумма
From [c:\1\Вася.xlsx].[Данные$] Where [Энгельс] Is Not Null
Union All
Select ID,[Контрагент],'Вася' As Исполнитель,'Балаково' As Город,[Балаково] As Сумма
From [c:\1\Вася.xlsx].[Данные$] Where [Балаково] Is Not Null
Изменено: Андрей VG - 09.10.2015 06:20:51
 
 
 
#15
09.10.2015 14:42:50
Цитата
Андрей VG написал:
По моему проще будет так, коль нужно всё равно выводить каждый столбец суммы и названия города для неё.
В оригинале много столбцов, через inner join запрос короче получается.
так тоже можно, но приходится для каждого файла прописывать разворот по каждому городу, а хотелось бы сначала выполнить подзапрос, объединив все файлы, и из него потом разернуть города... Жаль не работает конструкция WITH, с ней было бы проще :(.

А можно ли (если взять решение №1) как-нибудь соединить несколько диапазонов в одну простыню (конечную таблицу) без использования подключения (консолидация не подходит).
Только пробовать формулами стянуть через создание большого буфера строк (по 1000 на каждого сотрудника, например), больше никак?
и потом уже из этого буфера строить сводную таблицу...
 
 
 
#16
09.10.2015 14:58:46
а если "Балаково" кто нибудь напишет "Балоково" что делать будете :)
----------
Соберите программно данные, сделайте редизайн и затем сводную.
 
 
 
#17
09.10.2015 15:51:49
Цитата
B.Key написал:
а если "Балаково" кто нибудь напишет "Балоково" что делать будете
Столбы именованы заранее, я определяю какой сотрудник за какие регионы отвечает.

Цитата
B.Key написал:
Соберите программно данные, сделайте редизайн и затем сводную.
VBA Вы имеете ввиду?
Изменено: vetrintsev - 09.10.2015 15:52:05
 
 
 
#18
09.10.2015 16:07:46
Цитата
vetrintsev написал:
VBA Вы имеете ввиду?
Именно
 
 
 
#19
09.10.2015 16:22:17
Цитата
B.Key написал:
Именно
Так можно сделать все что угодно)
Пытаюсь отучить себя от VBA. Раньше много на нем писал, но сейчас понимаю, что просто не знал готовых решений в самом excel. Все приходит с опытом.
Хотя все федеральные отчеты связал только на VBA.
 
 
 
#20
09.10.2015 17:06:33
Цитата
vetrintsev написал:
а хотелось бы сначала выполнить подзапрос, объединив все файлы, и из него потом разернуть города..
Так соберите города сначала на отдельный лист запросом, а потом перестраивайте его данные запросом же на другом листе. Excel на "обновить всё", вроде, обновляет листы слева на право по отображению. Единственно, для Union нужно чтобы число столбцов объединяемых столбцов было равно, а в вашем случае ещё чтобы совпадали названия городов. Так что в случае чего нужно будет вводить фиктивные названия столбцов-городов с каким-нибудь значением, далее убираемым фильтром.
 
 
 
#21
09.10.2015 17:18:32
Цитата
Андрей VG написал:
Так соберите города сначала на отдельный лист запросом, а потом перестраивайте его данные запросом же на другом листе. Excel на "обновить всё", вроде, обновляет листы слева на право по отображению. Единственно, для Union нужно чтобы число столбцов объединяемых столбцов было равно, а в вашем случае ещё чтобы совпадали названия городов. Так что в случае чего нужно будет вводить фиктивные названия столбцов-городов с каким-нибудь значением, далее убираемым фильтром.
А как сделать подключение не по абсолютной ссылке, а в текущей книге, а то если файл скопировать, то он все равно последним подключением будет ссылаться на первый файл.
 
 
 
#22
10.10.2015 02:47:30
Доброе время суток
Распакуйте файлы архива в папку C:\1 Файл сводной - Сводка.xlsx. В нём на лист "Сводка" собираются данные с книг Вася и Ваня. Сам же он через запрос к нему служит источником для сводной. Обновляем сначала данные на листе "Сводка", затем сводную.

Успехов.
Изменено: Андрей VG - 10.10.2015 02:56:38 (Файл не приложился)
 
 
 
Страницы: 1
Наверх