Сводные таблицы в SQL

Сводная таблица – один из самых базисных видов аналитики. Почти все считают, что сделать её средствами SQL нереально. Естественно же, это не так.

Представим, у нас есть таблица с данными закупок нескольких видов продуктов (Product 1, 2, 3, 4) у различных поставщиков (A, B, C):

Обычная задачка – найти размер закупок по поставщикам и товарам, т.е. выстроить сводную таблицу. Юзеры MS Excel привыкли получать такую аналитику практически парой кликов:

В SQL это не так стремительно, но большая часть решений элементарны.

1. Оператор CASE и аналоги

Самый обычной и тривиальный метод получения сводной таблицы – это хардкод с внедрением оператора CASE . К примеру, для поставщика А можно вычислить размер поставок как sum(case when t.supplier = ‘A’ then t.volume end ). Чтоб получить размер поставок для различных продуктов довольно просто добавить группировку по полю product :

Если добавить else 0 , то для продуктов, по которым не было поставок, заместо null будут выведены нули:

Если продублировать код для всех поставщиков (которых у нас три — A, B, C), мы получим нужную нам сводную таблицу:

В неё можно добавить результат по строчкам (как обыденную сумму, т.е. sum(t.volume) ):

Не составит труда добавить и результат по столбцам. Для этого нужен употреблять оператор ROLLUP , который дозволит добавить суммирующую строчку. В большинстве СУБД употребляется синтаксис rollup(t.product) , хотя время от времени доступен и другой t.product with rollup (к примеру, SQL Server).

Итог можно создать ещё привлекательнее, заменив NULL на свою подпись итога. Для этого можно употреблять функцию coalesce() : coalesce(t.product, ‘total_sum’) , либо же хоть какой специфичный для определенной СУБД аналог (к примеру, nvl() в Oracle). Итог будет последующим:

Если ваша СУБД так стара, что не поддерживает rollup, – придётся употреблять костыли. К примеру, так:

Можно (но навряд ли стоит) употреблять какую-либо из вендоро-специфичных функций заместо обычного CASE . К примеру, в PostgreSQL и SQLite доступен оператор FILTER :

Изюминка FILTER в том, что он является частью эталона (SQL:2003), но практически поддерживается лишь в PostgreSQL и SQLite.

В остальных СУБД есть ряд эквивалентов CASE, не предусмотренных эталоном: IF в MySQL, DECODE в Oracle, IIF в SQL Server 2012+, и т.д. Почти всегда их внедрение не несёт никаких преимуществ, только усложняя поддержку кода в дальнейшем.

2. Внедрение PIVOT (SQL Server и Oracle)

Описанный чуть повыше подход тяжело именовать прекрасным. Как минимум, охото не дублировать код для всякого поставщика, а просто их перечислить. Создать это дозволяет разворот (PIVOT) таблицы, доступный в в SQL Server и Oracle. Хотя этот оператор не предусмотрен эталоном SQL, обе СУБД дают схожий синтаксис.

Для начала нам нужна таблица с агрегированной статистикой, которую мы «развернём». Чудилось бы, для этого довольно взять суммы по товару и провайдеру:

И этого будет довольно – если нам необходимы итоги лишь по товарам и по провайдерам. Если же мы желаем получить все вероятные итоги, нужно избрать все вероятные сочетания продукта и провайдера, в том числе такие где продукт либо провайдер NULL :

Этот запрос можно значительно упростить, используя оператор CUBE :

Если мы желаем получить подпись итогов как ‘total_sum’ заместо NULL запрос нужно мало откорректировать:

К такому результату уже можно использовать PIVOT:

Тут мы «поворачиваем» таблицу из прошедшего запроса, используя агрегатную функцию суммы sum(agg) . При всем этом заглавия столбцов мы берём из поля supplier , а при помощи in («A», «B», «C», «total_sum») указываем какие непосредственно поставщики должны быть выведены ( total_sum отвечает за столбец с итогами по строчкам).

3. Common table expression

В принципе, для «поворота» таблицы нам не нужен оператор PIVOT как такой. Этот запрос можно просто переписать, используя обычный синтаксис — комбинацию CTE (common table expression) и соединений. Для этого будем употреблять этот же запрос, что и для PIVOTа:

Из результатов, приобретенных в cte нам нужны лишь неповторимые значения продуктов:

… к которым можно попеременно присоединять размер закупок для всякого раздельно взятого поставщика:

Тут мы используем левое соединение т.к. у поставщика может не быть поставок по неким продуктам.

Окончательный запрос будет смотреться таковым образом:

Естественно, таковой запрос — это proof-of-concept, потому смотрится он достаточно экзотично.

4. Функция CROSSTAB (PostgreSQL)

В PostgreSQL доступна функция CROSSTAB , которая приблизительно эквивалентна PIVOT в SQL Server либо Oracle. Для работы с ней нужно расширение tablefunc :

CROSSTAB воспринимает в качестве основного аргумента запрос как text sql . Он будет фактически этим же, что и для PIVOT , но с неотклонимым внедрением сортировки:

В отличие от PIVOT, для «разворота» таблицы нам нужно указывать не только лишь наименования столбцов, да и типы данных. К примеру, так: «product» varchar, «A» bigint, «B» bigint, «C» bigint, «total_sum» bigint .

Ещё один аспект заключается в том, что CROSSTAB заполняет строчки слева вправо, игнорируя NULL-овые значения. К примеру, таковой запрос:

… вернёт совершенно не то, что мы желаем:

Как можно увидеть, там, где были NULL-овые значения, всё «съехало» на лево. К примеру, в первой строке для Product1 результат по строке оказался в столбце для поставщика С, а поставки С — в столбце поставщика В (для которого поставок не было). Корректно проставлены данные лишь для Product3 т.к. для этого продукта у всех поставщиков были значения. Другими словами, если б у нас не было NULL-овых значений, запрос был бы корректным и возвратил подходящий итог.

Чтоб не сталкиваться с таковым поведением CROSSTAB необходимо употреблять вариант функции с 2-мя параметрами. 2-ой параметр должен содержать запрос, выводящий перечень всех столбцов в итоге. В нашем случае это все наименования поставщиков из таблицы + «total_sum» для итогов:

… а полный запрос будет смотреться так:

5. Динамический SQL (на примере SQL Server)

Запрос с PIVOT либо CROSSTAB уже функциональнее, чем изначальный с CASE (либо CTE), но наименования поставщиков все ещё нужно заносить вручную. Но что созодать, если поставщиков много? Либо если их перечень часто обновляется? Хотелось бы выбирать их автоматом как как select distinct supplier from test_supply (либо же из словаря, если он есть).

Тут незапятнанного SQL недостаточно. Он предполагает статическую типизацию: для сотворения плана запроса СУБД необходимо заблаговременно указать число столбцов. Потому, к примеру, синтаксис PIVOT не дозволяет употреблять подзапрос. Но это ограничение просто обойти при помощи динамического SQL! Для этого наименования столбцов нужно конвертировать в строчку формата «элемент_1», «элемент_2», …, «элемент_n» , и употреблять их в запросе.

К примеру, в SQL Server мы можем употреблять STUFF для получения таковой строчки

… а потом включить её в окончательный запрос:

Динамический SQL полностью можно применить и к самому первому решению с CASE . К примеру, так:

Тут употребляется цикл для итерации по легкодоступным поставщикам в таблице test_supply (можно поменять на словарь, если он есть), опосля что формируется соответственный кусочек запроса:

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

Итого: как мы узнали, сводную таблицу можно просто сделать средствами SQL. Наиболее того, это можно обилием различных способов — довольно только избрать лучший для вашей СУБД.

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

Сводная таблица в Excel — это мощный инструмент для анализа данных, который поможет для вас стремительно:

• Приготовить данные для отчетов;
• Высчитать разные характеристики;
• Сгруппировать данные;
• Отфильтровать и проанализировать интересующие характеристики.

Также сберечь для вас массу времени.

Из данной статьи вы узнаете:

• Как создать сводную таблицу;
• Как при помощи сводной таблицы сгруппировать временные ряды и оценить данные в динамике по годам, кварталам, месяцам, денькам.
• Как высчитать прогноз при помощи сводной таблицы и Forecast4AC PRO;

Для начала научимся созодать сводные таблицы.

Для того, чтоб создать сводную таблицу, нам нужно выстроить данные в виде обычной таблицы. В любом столбце должен быть 1 анализируемый параметр. К примеру, у нас 3 столбца:

• Дата
• Продукт
• Реализации в руб.

И в каждой строке 3-м параметра соединены меж собой, т.е. к примеру, 01.02.2010 года Продукт 1 продали на 422 656 руб.

Опосля того, как вы подготовили данные для сводной таблицы, устанавливаем курсор в 1-ый столбец в первую ячейку обычной таблицы, дальше заходим в меню «Вставка» и жмем клавишу «Сводная таблица»

Покажется диалоговое окно, в каком:

• вы сможете сходу надавить клавишу «ОК», и сводная таблица выведется в отдельный лист.
• а сможете настроить характеристики вывода данных сводной таблицы:

1. Спектр с данными, которые будут выведены в сводную таблицу;
2. Куда вывести сводную (в новейший лист либо на имеющийся (если изберите на имеющийся, то нужно будет указать ячейку, в которую вы желаете поместить сводную таблицу)).

Жмем «ОК», сводная таблица готова и выведена в новейший лист. Назовем лист «Сводная».

• В правой части листа вы увидите поля и области, с которыми вы можете работать. Поля вы сможете перетащить в области и они выведутся в сводную таблицу на лист.
• В левой части листа сводная таблица.

Сейчас, зажимаем левой клавишей мыши поле «Продукт» — перетаскиваем его в «Заглавие строк», поле «Реализации в руб.» — в «Значения» в сводной таблице. Таковым образом мы получили сумму продаж по товарам за весь период:

Группировка и фильтрация временных рядов в сводной таблице

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

Для этого перебегаем в лист «Данные», и опосля даты вставляем 3 пустых столбца. Выделяем столбец «Продукт» и жмем «Вставить».

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

Вставленные столбцы называем «Год», «Месяц», «Год-Месяц».

Сейчас в любой из этих столбцов добавляем подобающую формулу для получения интересующего параметра времени:

• В столбец «Год» добавляем формулу =ГОД(со ссылкой на дату);
• В столбец «Месяц» добавляем формулу =МЕСЯЦ(со ссылкой на дату);
• В столбец «Год — Месяц» добавляем формулу =СЦЕПИТЬ(ссылка на год;» «;ссылка на месяц).

Получаем 3 столбца с годом, месяцем и годом и месяцем:

Сейчас перебегаем в лист «Сводная», устанавливаем курсор на сводную таблицу, вызываем правой клавишей мыши меню и жмем клавишу «Обновить». Опосля обновления в перечне полей у нас возникают новейшие поля сводной таблицы «Год», «Месяц», «Год — месяц», которые мы добавили в ординарную таблицу с данными:

Сейчас давайте проанализируем реализации по годам.

Для этого поле «Год» мы перетаскиваем в «заглавие столбцов» сводной таблицы. Получаем таблицу с продажами по товарам по годам:

Сейчас мы желаем еще наиболее поглубже «опуститься» на уровень месяцев и проанализировать реализации по годам и по месяцам. Для этого в «заглавие столбцов» перетаскиваем поле «месяц» под год:

Для анализа динамики месяцев по годам, можем месяцы переместить в область сводной «Заглавие строк» и получить последующий вид сводной таблицы:

В данном представлении сводной таблицы мы лицезреем:

• реализации по любому товару в сумме за целый год (строчка с заглавием продукта);
• наиболее тщательно реализации по любому товару в любом месяце в динамике за 4 года.

Последующая задачка, мы желаем убрать из анализа реализации за некий месяц (к примеру, октябрь 2012 года), т.к. данные о продажах у нас еще не за полный месяц.
Для этого в область сводной «Фильтр отчета» перетащим «Год — месяц»

Жмем на показавшейся над сводной фильтр и ставим галочку «Выделить несколько частей». Потом в перечне с возрастом и номерами месяцев снимаем галочку с 2012 10 и жмем ОК.

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

Расчет проноза при помощи сводной таблицы и Forecast4AC PRO

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

Для того чтоб отключить итоги в сводной таблице устанавливаем курсор на столбец «Общий результат» и жмем на клавишу «Удалить общий результат». Результат из сводной теряется.

Для расчета прогноза при помощи Forecast4AC PRO устанавливаем курсор в 1 января 2009 года

и жмем клавишу «График Модель прогноза» в меню Forecast4AC PRO

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

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

Четких для вас прогнозов!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

• Novo Forecast Lite — автоматический расчет прогноза в Excel .
• 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
• Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте способности платных решений:

• Novo Forecast PRO — прогнозирование в Excel для огромных массивов данных.

Получите 10 советов по увеличению точности прогнозов до 90% и выше.

Сводная таблица Excel: как сделать и работать? Работа со сводными таблицами Excel

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

Почему конкретно новейшие версии?

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

По-прежнему, конкретно Excel является 2-ой по востребованности программкой, которая дозволяет не только лишь создавать обыкновенные таблицы, но даже создавать достаточно сложные базы данных. Как и в остальные составляющие, в него была добавлена клавиша «Кабинет», нажав на которую вы сможете сохранить документ в требуемом для вас формате, поменять его либо указать требуемые меры по его защите. Был существенно обновлен модуль защиты документа.

Что все-таки касается более важных конфигураций в Excel, то необходимо подчеркнуть исправление огромного количества ошибок в формулах, из-за что в прошедших версиях часто появлялись достаточно тяжелейщие ошибки при расчетах.

Необходимо сказать, что новенькая версия пакета — это не попросту набор офисных приложений, да и мощный инструмент, пригодный для решения даже весьма сложных задач. Как вы могли осознать, сейчас нами будет рассмотрена сводная таблица Excel.

Что же все-таки это такое и для что она нужна?

Более весомым конфигурацией в новейших версиях «Кабинета» является их на сто процентов перекроенный интерфейс, который был назван создателями Ribbon (лента). В лентах все 1500 установок комфортно сгруппированы по категориям, а поэтому для вас не придется их длительно находить. Чтоб на сто процентов соответствовать сиим характеристикам, создатели добавили в Excel к тому же усовершенствованные сводные таблицы.

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

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

Создаем подходящий документ

Потому что сделать сводную таблицу в Excel? Сначала необходимо составить обычной документ, в который мы вносим данные для их следующего анализа. На один столбец должен приходиться один параметр. Возьмем простой пример:

• Время реализации.
• Проданный продукт.
• Стоимость всего сбыта.

Таковым образом, меж всеми параметрами в любом определенном столбце появляется связь: представим, что кроссовки были проданы в 9 часов утра, при этом прибыль составила n-рублей.

Форматирование таблицы

• Если вы сходу же нажмете на клавишу «ОК», то сводная таблица Excel сходу же будет выведена на отдельный лист.
• Вполне настроить выведение.

В крайнем случае создатели дают нам возможность найти спектр ячеек, в который будет выводиться разыскиваемая информация. Опосля этого юзер должен найти, где непосредственно будет сотворена новенькая таблица: на уже существующем либо же на вновь сделанном листе. Нажав «ОК», вы здесь же увидите впереди себя готовую таблицу. На этом создание сводных таблиц в Excel фактически закончено.

В правой части листа размещены те области, с которыми для вас предстоит работать. Поля можно перетаскивать в отдельные области, опосля что данные из их будут отображены в таблице. Соответственно, в левой части рабочей области будет размещена сама сводная таблица.

Зажав при нажатой левой кнопочке мыши поле «Продукт», отправляем его в «Заглавие строк», а пункт «Сумма всех продаж» таковым же образом переправляем в «Значения» (в левой части листа). Вот так можно получить сумму продаж за весь анализируемый период.

Создание и группировка временных рядов

Чтоб проанализировать реализации по определенным временным отрезкам, нужно вставить надлежащие пункты в саму таблицу. Для этого нужно перейти в лист «Данные» и опосля этого вставить три новейших столбика сходу опосля даты. Выделяем столбец с заглавием продукта, опосля что нажимаем на клавишу «Вставить».

Весьма принципиально, чтобы все вновь создаваемые столбцы находились снутри уже имеющейся таблицы с начальными данными. В этом случае создание сводных таблиц в Excel поновой не будет нужно. Вы просто будете добавлять новейшие поля с требуемыми для вас данными.

Применяемые формулы

• В «Годичный» вставляем формулу вида: «=ГОД» (ссылаясь при всем этом на дату).
• Месяц необходимо дополнить выражением: «=МЕСЯЦ» (также со ссылкой на дату).
• В 3-ий столбик вставляем формулу вида: «=СЦЕПИТЬ» (ссылаясь при всем этом на год и на месяц).

Соответственно, получаем три столбика со всеми начальными данными. На данный момент нужно перейти в меню «Сводная», щелкаем по хоть какому вольному месту в таблице правой кнопкой мыши, опосля что в открывшемся контекстном меню избираем пункт «Обновить». Направьте внимание! Пристально пишите формулы в сводных таблицах Excel, потому что неважно какая ошибка приведет к некорректности прогноза.

Как проводить анализ продаж по годам?

Опять зажимаем левой кнопкой мыши пункт «Год», опосля что тащим его в «Заглавие столбцов», опосля что сводная таблица начинает показывать результаты реализации определенного вида продукта по всем годам. А если нужно так же проанализировать месячный сбыт? Буквально так же зажимаем ЛКМ «Месяц», перетаскивая его под годичный столбец.

Чтоб получить приятную динамику месячных конфигураций продаж по годам, поле «Месяц» необходимо перетащить прямо в годичный столбец, ранее уже перемещенный в «Заглавие столбцов». Выполнив эту операцию, вы увидите таблицу в последующем представлении:

• Сумма продаж определенного продукта за весь год.
• Динамику продаж всякого из их по годам.

Убираем данные из выдачи

Представим, что из выдачи нам необходимо убрать данные по октябрю, потому что у нас нет полной статистики по нему. В сводной таблице отыскиваем «Фильтр отчета», опосля что перетаскиваем на него «Год – месяц».

Над таблицей возникает фильтр, в каком необходимо поставить флаг напротив пт «Выделить несколько частей». Опосля этого в показавшемся перечне снимаем флаг с октября, опосля что жмем на «ОК».

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

Рассчитываем прогнозы

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

Чтоб создать это, ставим курсор в поле «Общий результат», опосля что кликаем по кнопочке «Удалить общий результат». Опосля этого исполняем автоматическое обновление сводной таблицы Excel методом, описанным нами выше (нажимаем на правую кнопку мыши, избираем «Обновить»).

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

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

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

В этом нам поможет функция «Слияние». Эта программка дозволяет автоматом соединить текст документа и нужные адреса, имена либо другую нужную информацию для сотворения персонализированных прогнозов, что в нашем случае и требуется.

Функция слияния в поздних версиях «Кабинета» фактически не поменялась в сопоставлении с Office 2003. Тем не наименее запросы к базам данных для выбора источника данных могут производиться с применением Microsoft Query. Чтоб получить данные сводной таблицы Excel, нужно наличие 2-ух объектов:

• Главный документ, содержащий подходящую для вас информацию, также поля, указывающие программке Excel, какие данные следует вставлять в документ. Основная таблица является обычным документом табличного микропроцессора, но нужно указать в окне диалога Слияние (Mail Merge Helper), что конкретно он должен употребляться как главный.
• Источник данных, содержащий ту информацию, которая нужна для составления прогноза. Можно сделать новейшую таблицу тем методом, который мы указывали выше.

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

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

Применяемые группы

Когда все готово для сотворения составного документа, окно диалога Merge предоставляет юзеру возможность выявить и убрать отдельные ошибки до их возникновения. Когда все испытано, то можно смело нажимать на клавишу «Соединить». Если же слияние обязано выполняться через электрическую почту, то выйдет запрос на установку почтовой программки (если у вас она не установлена).

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

Совершенно, Excel (работа со сводными таблицами в каком нами рассматривается) дозволяет не только лишь проводить сравнимо сложные анализы покупательской возможности, да и создавать на их базе прогнозы.