Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Возможно ли Pivot с несколькими агрегатными функциями  [new]
Александра77
Member

Откуда:
Сообщений: 12
Надо и SUM и COUNT одновременно...
31 янв 17, 09:04    [20164526]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли Pivot с несколькими агрегатными функциями  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Александра77,
гугл "PIVOT on two or more fields in SQL Server" и тп
31 янв 17, 09:59    [20164709]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли Pivot с несколькими агрегатными функциями  [new]
iap
Member

Откуда: Москва
Сообщений: 46953
Александра77
Надо и SUM и COUNT одновременно...
Выбросить PIVOT куда подальше
и написать SUM(CASE WHEN ... THEN ... ELSE 0 END) и SUM(CASE WHEN ... THEN 1 ELSE 0 END)
31 янв 17, 11:00    [20165024]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли Pivot с несколькими агрегатными функциями  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4379
Александра77
Надо и SUM и COUNT одновременно...


не получиться. Партянку с CASE-ам придется расписывать.

Если такие задачи встречаются часто, смотрите в сторону OLAP-кубов. MDX и DAX раскладывают поля по горизонтали и вертикали легко.
31 янв 17, 15:18    [20166420]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли Pivot с несколькими агрегатными функциями  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
Александра77,

можно если вас конечно получившийся вид смущать не будет, так как скуль не клиент и как эксель многоуровневые шапки не длеает, так что приводите датасет к такому виду:
column1_sum, value
column1_count, value
column2_sum, value
column2_count, value
column3_sum, value
column3_count, value
и далее pivot
31 янв 17, 15:23    [20166454]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли Pivot с несколькими агрегатными функциями  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 774
a_voronin
Если такие задачи встречаются часто, смотрите в сторону OLAP-кубов.


Excel своими PivotTables очень неплохо справляется с обязанностями OLAP и может неплохо работать с достаточно большими простынями. Я в своё время делал широкую простыню View и скармливал Excel'ю, который юзер сам себе обновлял по требованию
31 янв 17, 15:33    [20166493]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли Pivot с несколькими агрегатными функциями  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Шыфл
a_voronin
Если такие задачи встречаются часто, смотрите в сторону OLAP-кубов.


Excel своими PivotTables очень неплохо справляется с обязанностями OLAP и может неплохо работать с достаточно большими простынями. Я в своё время делал широкую простыню View и скармливал Excel'ю, который юзер сам себе обновлял по требованию

что такое "достаточно большая простыня" с ограничением в 1кк строк?
31 янв 17, 15:35    [20166498]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли Pivot с несколькими агрегатными функциями  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 774
TaPaK,

а вот и нет, если использовать источник данных, то строк может быть намного больше, чем 1кк. Он их не хранит в виде строк, а оперирует именно как с множеством, которое можно двойным кликом drill-down к исходным данным.
Ну, по крайней мере в те времена, когда в Excel было всего 64к строк, я таким образом вынимал по ODBC 250к строк данных и сворачивал в Pivot. В левом нижнем углу прям счётчик бежал, когда данные обновлялись (у слову, довольно медленно).
31 янв 17, 15:46    [20166560]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли Pivot с несколькими агрегатными функциями  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Шыфл,

при этом надо закладывать, что вся это радость падает на плечи клиентских компьютеров(достаточно средних по большей части), а радость падает немалая как на оперативку, так и на ЦП
31 янв 17, 15:55    [20166611]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли Pivot с несколькими агрегатными функциями  [new]
iap
Member

Откуда: Москва
Сообщений: 46953
Александра77,

какой-то тут трёп ни о чём.
Сделайте хотя бы простой пример с данными (скрипт создания таблиц и INSERT тестовых данных в эти таблицы)
31 янв 17, 16:02    [20166649]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли Pivot с несколькими агрегатными функциями  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4379
Шыфл
a_voronin
Если такие задачи встречаются часто, смотрите в сторону OLAP-кубов.


Excel своими PivotTables очень неплохо справляется с обязанностями OLAP и может неплохо работать с достаточно большими простынями. Я в своё время делал широкую простыню View и скармливал Excel'ю, который юзер сам себе обновлял по требованию


До 5-10 миллионов -- с большим скрипом -- да. На 100 лимонов Excel он ляжет. Если вы собрались сказать, что Excel заменит SSAS, то я с вами сильно не соглашусь.
31 янв 17, 16:30    [20166826]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли Pivot с несколькими агрегатными функциями  [new]
лолл
Member

Откуда:
Сообщений: 450
a_voronin,

интересно, а зачем такие объемы возвращать клиенту? любой пользователь ляжет вместе с Экселем, пытаясь переварить столько информации :)
31 янв 17, 17:53    [20167203]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли Pivot с несколькими агрегатными функциями  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4379
лолл
a_voronin,

интересно, а зачем такие объемы возвращать клиенту? любой пользователь ляжет вместе с Экселем, пытаясь переварить столько информации :)


Речь идет не об объемах, возвращаемых клиенту. Речь идет об объемах, которые может переварить, праггрегировать и разложить по измерениям соответствующий движок. Если бы Excel мог это сделать, то я бы не работал там, где сейчас работаю.
31 янв 17, 18:05    [20167234]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли Pivot с несколькими агрегатными функциями  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30768
Александра77,

Одну выборку преобразовать в другую
31 янв 17, 20:02    [20167564]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли Pivot с несколькими агрегатными функциями  [new]
Александра77
Member

Откуда:
Сообщений: 12
Коллеги, всем спасибо!!!
Совет WarAnt очень подойдёт, подгоню выборку и переверну.
alexeyvg, ваш совет видела, все просто супер! Выборка исправлена и освоена новая команда ;)
31 янв 17, 22:50    [20167973]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли Pivot с несколькими агрегатными функциями  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 774
a_voronin
Шыфл
пропущено...
Excel своими PivotTables очень неплохо справляется с обязанностями OLAP и может неплохо работать с достаточно большими простынями.

До 5-10 миллионов -- с большим скрипом -- да. На 100 лимонов Excel он ляжет. Если вы собрались сказать, что Excel заменит SSAS, то я с вами сильно не соглашусь.


Я говорил, что Excel справляется с обязанностями OLAP - это примерно так, как Access справляется с обязанностями SQL сервера.

Вообще, для проверки скармил Excel'ю
 create view t
 as 
 select top 1000000000  rand(checksum(newid())) * 1000 a 
from 
master.sys.all_columns a cross join 
master.sys.all_columns b cross join 
master.sys.all_columns c


Грузил он медленно (сса 200к записей/с) , в процессе загрузки выжрал все имеющиеся 6,2GB памяти и 25% процессора(1 из 4 ядер, больше, видимо, не умеет), и упал - счётчик остановился на 536858230. Очевидно, памяти не хватило.
Тогда я уменьшил top до 200 000 000 но добавил колонок
 alter view t
 as 
 select top 200000000  
rand(checksum(newid())) * 1000 a, 
a.object_id, 
a.column_id 
from 
master.sys.all_columns a 
cross join master.sys.all_columns b  
cross join master.sys.all_columns c

Данные загрузились в 5GB, судя по счётчику, но при расчётах память опять упёрлась в 6GB и повисло.

Тогда топ уменьшился до 100 000 000, и данные влезли в 3,8GB. И ОНО ЗАРАБОТАЛО!
Т.е. конечно несчадно тормозило и любая операция с таблицей забивала память до 5GB, но работало, считало сумму, колличество, разбивало всё по объектам и колонкам - как надо быть! Несмотря на то, что размер сохранённого файла больше 1GB

Так что для целого класса задач, где исходных данных 150к-10кк OLAP можно заменить эксцелем. А если подключить элементарную серверную преагрегацию, то всё ещё веселее :)

a_voronin
Если бы Excel мог это сделать, то я бы не работал там, где сейчас работаю.

Я тоже сейчас работаю там, где строят OLAP кубы. Но раньше мне хватало Excel'я, поэтому испытываю к нему самые тёплые чувства
1 фев 17, 14:08    [20169631]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли Pivot с несколькими агрегатными функциями  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6794
Шыфл,

это настолько узкий пример для olap что аж не прилично
1 фев 17, 14:18    [20169688]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли Pivot с несколькими агрегатными функциями  [new]
Шыфл
Member

Откуда: Прага
Сообщений: 774
TaPaK
то настолько узкий пример для olap что аж не прилично


А что тут узко, кроме генерированых данных? Ну нет у меня под рукой 10кк бизнес-данных.

Ради вас добавил колонок, и снизил топ до 10кк
alter view t
 as 
 select top 10000000  
rand(checksum(newid())) * 1000 a, 
a.*
from 
master.sys.all_columns a 
cross join master.sys.all_columns b  
cross join master.sys.all_columns c

Файл уменьшился до 100Mb, всё почти летает - крути не хочу. Т.е. тем, у кого исходных данных меньше 1кк записей можно вообще не париться - делаешь простыню View со всеми справочниками, вставляешь YEAR, MONTH, DAY на даты и вуаля - практически полноценный мультиразмерный куб у пользователя в эксцеле, пусть балуется. И никакого SSAS и MDX
1 фев 17, 14:47    [20169807]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли Pivot с несколькими агрегатными функциями  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 30768
Шыфл
Так что для целого класса задач, где исходных данных 150к-10кк OLAP можно заменить эксцелем. А если подключить элементарную серверную преагрегацию, то всё ещё веселее :)

a_voronin
Если бы Excel мог это сделать, то я бы не работал там, где сейчас работаю.

Я тоже сейчас работаю там, где строят OLAP кубы. Но раньше мне хватало Excel'я, поэтому испытываю к нему самые тёплые чувства
Вообще для экселя некоторое время назад появилась надстройка Power Pivot.
Это превращает эксель в один из лучших, если не лучший, клиент для OLAP.
Как я понимаю, он вполне может и обрабатывать большие объёмы, т.е. это не просто "клиент визуализации".
Правда, я знаком с Power Pivot в теории, сам не применял.
1 фев 17, 15:02    [20169897]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Возможно ли Pivot с несколькими агрегатными функциями  [new]
И_Павел_С
Member

Откуда:
Сообщений: 44
Всем доброго времени!
Простите, что может не по теме, но у меня очень схожий вопрос.
Есть простой пример использования PIVOT, который показывают на учебной базе NorthWind
SELECT CompanyName, [Beverages], [Condiments], [Confections], [Dairy Products], [Grains/Cereals], [Meat/Poultry], [Produce], [Seafood]
FROM (
	  SELECT SUP.CompanyName, CAT.CategoryName AS [Категория товара], PR.UnitPrice
	  FROM Northwind..Suppliers AS SUP
	  INNER JOIN
		   Northwind..Products AS PR
	  ON SUP.SupplierID = PR.SupplierID
	  INNER JOIN
		  Northwind..Categories AS CAT
	  ON PR.CategoryID = CAT.CategoryID
) AS MyTab
pivot
	 (AVG(MyTab.UnitPrice) FOR [Категория товара] IN ([Beverages], [Condiments], [Confections], [Dairy Products], [Grains/Cereals], [Meat/Poultry], [Produce], [Seafood])) AS MyReport

Здесь мы видим, что транспонируемые столбцы взяты из колонки CategoryName таблицы Categories.
Теперь пожалуйста представьте, что у меня еще таблица, например CostCategory, с категориями по цене, и мне нужно получить по ним агрегацию в этом запросе.
То есть запрос по идее должен выглядеть приблизительно вот так
SELECT CompanyName, [Beverages], [Condiments], [Confections], [Dairy Products], [Grains/Cereals], [Meat/Poultry], [Produce], [Seafood], [LowCost], [MediumCost], [HighCost] -- Добавленные ценовые катеогрии
FROM (
	  SELECT SUP.CompanyName, CAT.CategoryName AS [Категория товара], PR.UnitPrice, CT.CostCategory AS [Ценовая категория]
	  FROM Northwind..Suppliers AS SUP
	  INNER JOIN
		   Northwind..Products AS PR
	  ON SUP.SupplierID = PR.SupplierID
	  INNER JOIN
		  Northwind..Categories AS CAT
	  ON PR.CategoryID = CAT.CategoryID
          INNER JOIN
		  Northwind..CostCategory AS CT -- добавленный JOIN
	  ON PR.CostCategoryID = CT.CostCategoryID
) AS MyTab
pivot
	 (AVG(MyTab.UnitPrice) FOR [Категория товара] IN ([Beverages], [Condiments], [Confections], [Dairy Products], [Grains/Cereals], [Meat/Poultry], [Produce], [Seafood]),
FOR [Ценовая категория] IN ([LowCost], [MediumCost], [HighCost]) -- вот собственно и главный вопрос. Можно ли в FOR перечислить столбцы?
) AS MyReport

Понятно, что в таком варианте идет синтаксическая ошибка, но возможно ли вообще такое?
11 окт 18, 18:46    [21701908]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли Pivot с несколькими агрегатными функциями  [new]
court
Member

Откуда:
Сообщений: 1956
И_Павел_С,

нужно, кроме "добавленный JOIN", - "добавленный UNION"
типа этого что-то
...
FROM (
	  SELECT SUP.CompanyName, CAT.CategoryName AS [Категория товара], PR.UnitPrice
	  FROM Northwind..Suppliers AS SUP
	  INNER JOIN
		   Northwind..Products AS PR
	  ON SUP.SupplierID = PR.SupplierID
	  INNER JOIN
		  Northwind..Categories AS CAT
	  ON PR.CategoryID = CAT.CategoryID

union all

	  SELECT SUP.CompanyName, CT.CostCategory  AS [Ценовая категория], PR.UnitPrice
	  FROM Northwind..Suppliers AS SUP
	  INNER JOIN
		   Northwind..Products AS PR
	  ON SUP.SupplierID = PR.SupplierID
	  INNER JOIN
		  Northwind..Categories AS CAT
	  ON PR.CategoryID = CAT.CategoryID
          INNER JOIN
		  Northwind..CostCategory AS CT -- добавленный JOIN
	  ON PR.CostCategoryID = CT.CostCategoryID
) AS MyTab
...
12 окт 18, 09:31    [21702189]     Ответить | Цитировать Сообщить модератору
 Re: Возможно ли Pivot с несколькими агрегатными функциями  [new]
И_Павел_С
Member

Откуда:
Сообщений: 44
court,
Спасибо большое за идею. Попробую ее применить. А я из ситуации вышел немного по другому, я просто создал две вьюхи, а потом переJOINил их.
12 окт 18, 12:32    [21702373]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить