Sql перекрестный запрос пример: Перекрестные запросы SQL или кросс табличные выражения | Info-Comp.ru
Содержание
Перекрестные запросы SQL или кросс табличные выражения | Info-Comp.ru
Продолжаем осваивать язык запросов SQL и сегодня мы с Вами займемся изучением так называемых перекрестных запросов или их также называют кросс табличные выражения. Иногда можно встретить такое название как транспонирование таблицы (например, в Excel), но смысл во всех этих названиях один и тот же и сейчас в нем мы будем разбираться.
Для начала напомню, что это у нас уже третья статья по SQL. В предыдущих статьях мы рассмотрели:
- Основы языка запросов SQL – оператор SELECT;
- Строковые функции SQL – примеры использования.
А сегодня, как было уже сказано, мы займемся изучением перекрестных запросов. Для чего же нужны эти перекрестные запросы? Чтобы помочь Вам ответить на этот вопрос, я приведу пример, чтобы Вы могли наглядно увидеть применение этих перекрестных запросов.
Допустим, у Вас есть таблица следующего вида (колонки называются god, chislo, tovar, сама таблица называется tabl).
| Год | Число продаж | Товар |
| 2009 | 5 | Монитор |
| 2010 | 3 | Принтер |
| 2011 | 4 | Сканер |
| 2009 | 5 | Принтер |
| 2010 | 8 | Сканер |
| 2011 | 6 | Монитор |
| 2009 | 5 | Сканер |
| 2010 | 4 | Монитор |
| 2011 | 7 | Принтер |
Вы хотите наглядно посмотреть в каком году, сколько было продаж того или иного товара, согласитесь здесь это не наглядно, да и сама таблица маленькая, если была бы большая, то вообще ничего не понятно было. Данную задачу можно выполнить как раз с помощью перекрестного запроса, т.е. года с группировкой будут по горизонтали, по вертикали будут товары, а на пересечение товара и года будет стоять число продаж. Перекрестный запрос является разновидностью группировки, правда своеобразной.
Пример перекрестного запроса с использованием CASE WHEN
Существует несколько способов реализовать перекрестный запрос. Например, следующий, он подойдет практически для любой версии СУБД. Для нашей с Вами тестовой задачи запрос будет выглядеть так:
SELECT tovar,
SUM (CASE god WHEN '2009' THEN chislo ELSE 0 END) AS "2009",
SUM (CASE god WHEN '2010' THEN chislo ELSE 0 END) AS "2010",
SUM (CASE god WHEN '2011' THEN chislo ELSE 0 END) AS "2011"
FROM tabl
GROUP BY tovar
В результате вы получите следующий вывод данных:
| tovar | 2009 | 2010 | 2011 |
| Сканер | 5 | 8 | 4 |
| Монитор | 5 | 4 | 6 |
| Принтер | 5 | 3 | 7 |
Сравните исходную нашу таблицу и результат этого запроса, по моему сейчас более наглядно видно, сколько продаж в каком году было сделано.
Примечание! Сразу скажу, что все перечисленные в данной статье запросы вы сможете применить, только если Вы заранее знаете, сколько у Вас будет выводиться столбцов (у нас это «года» и их всего 3, если было 4, то нам пришлось бы добавлять в запрос еще одну строку и так далее), т.е. только фиксированное количество столбцов, которое Вам заранее известно. А если Вы не знаете, сколько необходимо выводить лет или их количество будет постоянно меняться, Вам уже нужно будет писать динамически расширяемый перекрестный запрос с помощью специальных процедур. В данном уроке такой способ мы рассматривать не будем.
Пример перекрестного запроса с использованием оператора PIVOT
Переходим к следующему способу, который появился с выходом Microsoft SQL Server 2005. В более ранних версиях этот способ применить нельзя, так как там отсутствовал оператор PIVOT. С использованием этого способа запрос будет выглядеть следующим образом, и результат будет тот же самый:
SELECT tovar, [2009], [2010], [2011] FROM tabl PIVOT(SUM (chislo) FOR god IN ([2009], [2010], [2011])) AS mytabl
Как видите этот запрос уже немного покороче, но у него своеобразный синтаксис (если конкретней, то у оператора PIVOT).
Если Вы работаете в Access, то там вообще все просто, это можно реализовать стандартными средствами Access, даже если Вы не знаете SQL. Просто нажмите «Создать новый запрос» и выберете «Перекрестный запрос». После создания запроса можете открыть запрос в режиме конструктора и перейти в режим SQL, где Вы сможете увидеть сам запрос на SQL.
На сегодня о перекрестных запросах я думаю достаточно. Продолжим изучение SQL в следующих материалах.
SQL.RU | Перекрестные запросы в Т-SQL
Перекрестные запросы (Crosstab Query) являются еще одной специфической разновидностью запросов на выборку. Предназначены они для более глубокого анализа информации, хранящейся в таблицах.
Ключевым словом SQL-оператора перекрестного запроса, задающим его тип, является слово TRANSFORM (преобразовать). Это подразумевает, что значения одного из столбцов (полей) выборки, будут преобразованы в названия столбцов итоговой выборки.
Результаты перекрестного запроса группируются по двум наборам данных, один из которых расположен в левом столбце (столбцах) таблицы, а второй — в верхней строке.
В остальном пространстве таблицы отображаются результаты статистических расчетов (Sum, Count и т.д.), выполненных над данными трансформированного поля.
Для демонстрации создадим тестовую таблицу:
CREATE TABLE Sales ( SaleID int IDENTITY PRIMARY KEY CLUSTERED, ClientID int, Date datetime, Amount money ) insert Sales values(1,'20010401', 15.48) insert Sales values(1,'20020302', 134.01) insert Sales values(1,'20031003', 2346.03) insert Sales values(2,'20030203', 754.88) insert Sales values(3,'20010301', 73.07) insert Sales values(3,'20030402', 734.46) insert Sales values(4,'20010301', 1567.10) insert Sales values(4,'20020404', 6575.70) insert Sales values(4,'20030307', 6575.77) insert Sales values(4,'20030309', 6575.37) insert Sales values(5,'20011201', 1975.73) insert Sales values(5,'20030306', 178965.63) insert Sales values(6,'20020103', 16785.34) insert Sales values(6,'20030304', 1705.44) GO
44)
GOДо версии SQL Server 2005, в котором появился оператор PIVOT, перекрестные запросы выполнялись через оператор CASE (BOL->Cross-Tab Reports):
SELECT
MONTH(Date) AS SaleMonth,
SUM(CASE YEAR(Date)
WHEN 2001 THEN Amount
ELSE 0
END) AS [2001],
SUM(CASE YEAR(Date)
WHEN 2002 THEN Amount
ELSE 0
END) AS [2002],
SUM(CASE YEAR(Date)
WHEN 2003 THEN Amount
ELSE 0
END) AS [2003]
FROM Sales
GROUP BY MONTH(Date)
ORDER BY MONTH(Date)
GOРезультат выполнения запросa:
SaleMonth 2001 2002 2003
———— ——————— ——————— ———————
1 .0000 16785.3400 .0000
2 .0000 .0000 754.
8800
3 1640.1700 134.0100 193822.2100
4 15.4800 6575.7000 734.4600
10 .0000 .0000 2346.0300
12 1975.7300 .0000 .0000
(6 row(s) affected)
Теперь тот же запрос, но уже с использованием оператора PIVOT (версия SQL Server 2005 и выше):
SELECT * FROM (SELECT YEAR(Date) y, MONTH(Date) SaleMonth, Amount FROM Sales) as s PIVOT (SUM(Amount) for y in ([2001], [2002], [2003])) pv
Всё бы хорошо, когда мы за ранее знаем кол-во лет, но что делать, если мы не имеем такой информации на входе, либо нужно, чтобы код был более универсальным и при добавлении записей с более старшей датой код был «рабочим». Выход-динамический перекрестный запрос (Dynamic Crosstab Queries). Усложним наши тестовые данные добавив хотя бы одну запись с другим годом:
insert Sales values(1,'20010401', 15.48)
Очень интересное решение этой задачи предложил Ицик Бен-Ган (http://am.
rusimport.ru/MsAccess/topic.aspx?id=285):
CREATE PROCEDURE sp_CrossTab
@table AS sysname,-- Таблица для построения crosstab отчета
@onrows AS nvarchar(128),-- Значение для группировки по строкам
@onrowsalias AS sysname = NULL,-- Псевдоним для группируемой колонки
@oncols AS nvarchar(128),-- Значение для группировки по колонкам
@sumcol AS sysname = NULL -- Значение для суммирования
AS
SET NOCOUNT ON
DECLARE
@sql AS varchar(8000),
@NEWLINE AS char(1)
SET @NEWLINE = CHAR(10)
-- Шаг 1: начало строки SQL.
SET @sql =
'SELECT' + @NEWLINE +
' ' + @onrows +
CASE
WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias
ELSE ''
END
-- Шаг 2: Хранение ключей во временной таблице.
CREATE TABLE #keys(keyvalue nvarchar(100) NOT NULL PRIMARY KEY)
DECLARE @keyssql AS varchar(1000)
SET @keyssql =
'INSERT INTO #keys ' +
'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +
'FROM ' + @table
EXEC (@keyssql)
-- Шаг 3: Средняя часть строки SQL.
DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keys
WHILE @key IS NOT NULL
BEGIN
SET @sql = @sql + ',' + @NEWLINE +
' SUM(CASE CAST(' + @oncols +
' AS nvarchar(100))' + @NEWLINE +
' WHEN N''' + @key +
''' THEN ' + CASE
WHEN @sumcol IS NULL THEN '1'
ELSE @sumcol
END + @NEWLINE +
' ELSE 0' + @NEWLINE +
' END) AS [' + @key+']'
SELECT @key = MIN(keyvalue) FROM #keys
WHERE keyvalue > @key
END
-- Шаг 4: Конец строки SQL.
SET @sql = @sql + @NEWLINE +
'FROM ' + @table + @NEWLINE +
'GROUP BY ' + @onrows + @NEWLINE +
'ORDER BY ' + @onrows
SET NOCOUNT OFF
PRINT @sql + @NEWLINE-- для отладки
EXEC (@sql)
GO
DECLARE @key AS nvarchar(100)
SELECT @key = MIN(keyvalue) FROM #keys
WHILE @key IS NOT NULL
BEGIN
SET @sql = @sql + ',' + @NEWLINE +
' SUM(CASE CAST(' + @oncols +
' AS nvarchar(100))' + @NEWLINE +
' WHEN N''' + @key +
''' THEN ' + CASE
WHEN @sumcol IS NULL THEN '1'
ELSE @sumcol
END + @NEWLINE +
' ELSE 0' + @NEWLINE +
' END) AS [' + @key+']'
SELECT @key = MIN(keyvalue) FROM #keys
WHERE keyvalue > @key
END
-- Шаг 4: Конец строки SQL.
SET @sql = @sql + @NEWLINE +
'FROM ' + @table + @NEWLINE +
'GROUP BY ' + @onrows + @NEWLINE +
'ORDER BY ' + @onrows
SET NOCOUNT OFF
PRINT @sql + @NEWLINE-- для отладки
EXEC (@sql)
GOВызов этой процедуры:
EXEC sp_CrossTab @table = 'Sales', @onrows = 'MONTH(Date)', @onrowsalias = 'SaleMonth', @oncols = 'YEAR(Date)', @sumcol = 'Amount' GO
Но хотелось бы немного упростить эту процедуру с помощью оператора PIVOT и вот, что получилось:
CREATE PROCEDURE sp_CrossTab_PIVOT
@table AS sysname,-- Таблица для построения crosstab отчета
@onrows AS nvarchar(128),-- Значение для группировки по строкам
@onrowsalias AS sysname = NULL,-- Псевдоним для группируемой колонки
@oncols AS nvarchar(128),-- Значение для группировки по колонкам
@sumcol AS sysname = NULL -- Значение для суммирования
AS
SET NOCOUNT ON
DECLARE
@sql AS nvarchar (max),
@case AS varchar(1000)
SET @case=''
SELECT @sql='
SELECT @case=@case+''[''+CONVERT(VARCHAR, '+@oncols+')+''], '''+
' FROM '+@table+' GROUP BY '+@oncols+' ORDER BY '+@oncols
EXEC sp_executesql @sql,N'@case varchar(1000) out', @case=@case out
SET @case=LEFT(@case, LEN(@case)-1)
SELECT @sql='SELECT * FROM (
SELECT '+@oncols+' y, '+@onrows+' '+@onrowsalias+', '+@sumcol+' FROM '+@table+
') as s
PIVOT
(SUM ('+@sumcol+') for y in ('+@case+')) as pv'
PRINT @sql -- для отладки
EXECUTE (@sql)Вызов моей процедуры идентичен вызову предыдущей процедуры:
EXEC sp_CrossTab_PIVOT @table = 'Sales', @onrows = 'MONTH(Date)', @onrowsalias = 'SaleMonth', @oncols = 'YEAR(Date)', @sumcol = 'Amount' GO
Ещё одно решение этой задачи можно найти в FAQ на сайте SQL.
RU (http://www.sql.ru/faq/faq_topic.aspx?fid=358)
SQL CROSS JOIN с примерами
В этой статье мы изучим концепцию SQL CROSS JOIN и подкрепим наши знания простыми примерами, которые объясняются иллюстрациями.
Введение
CROSS JOIN используется для создания парной комбинации каждой строки первой таблицы с каждой строкой второй таблицы. Этот тип соединения также известен как декартово соединение.
Предположим, что мы сидим в кофейне и решили заказать завтрак. Вскоре мы посмотрим на меню и начнем думать, какое сочетание еды и напитков может быть вкуснее. Наш мозг получит этот сигнал и начнет генерировать все комбинации еды и питья.
На следующем изображении показаны все комбинации меню, которые может генерировать наш мозг. SQL CROSS JOIN работает аналогично этому механизму, поскольку создает все парные комбинации строк таблиц, которые будут объединены.
«Пожалуйста, не волнуйтесь, даже если вы чувствуете себя немного голодным сейчас, вы можете есть все, что захотите, после прочтения нашей статьи».
Основная идея CROSS JOIN заключается в том, что она возвращает декартово произведение соединенных таблиц. В следующем совете мы кратко объясним декартово произведение;
Совет: Что такое декартово произведение?
Декартово произведение — это операция умножения в теории множеств, порождающая все упорядоченные пары заданных множеств. Предположим, что A — это множество, а элементами являются {a,b}, а B — это множество, а элементами являются {1,2,3}. Декартово произведение этих двух A и B обозначается AxB, и результат будет примерно следующим.
AxB = {(а, 1), (а, 2), (а, 3), (б, 1), (б, 2), (б, 3)}
Синтаксис
Синтаксис CROSS JOIN в SQL будет выглядеть следующим образом:
SELECT ColumnName_1, ColumnName_2, ColumnName_N FROM [Table_1] CROSS JOIN [Table_2] Или мы можем использовать следующий синтаксис вместо предыдущего.
Набор результатов не меняется ни для одного из этих синтаксисов. Кроме того, мы должны отметить один момент, касающийся CROSS JOIN. В отличие от INNER JOIN, LEFT JOIN и FULL OUTER JOIN, CROSS JOIN не требует условия соединения. Пример перекрестного соединения SQL:В этом примере мы снова рассмотрим пример меню завтрака, о котором мы упоминали в предыдущей части статьи. Во-первых, мы создадим таблицы с двумя примерами, которые будут содержать названия напитков и блюд. После этого мы заполним их некоторыми образцами данных. С помощью следующего запроса мы выполним эти два шага:
Следующий запрос соединит таблицу Meals and Drinks с помощью ключевого слова CROSS JOIN , и мы получим все парные комбинации названий блюд и напитков.
Изображение ниже иллюстрирует принцип работы CROSS JOIN. В то же время мы можем использовать следующий запрос, чтобы получить тот же набор результатов с альтернативным синтаксисом без CROSS JOIN.
Совет: Количество строк набора результатов будет равно произведению количества строк таблиц, которые будут объединены. В примере с меню завтрака количество строк в таблице «Еда» равно 3, а количество строк в таблице «Напитки» равно 3, поэтому количество строк в наборе результатов можно найти с помощью следующего вычисления. 3 (Количество строк в таблице питания) x 3 (Количество строк напитков в напитках) = (Результаты ряда строк Функция CONCAT_WS поможет объединить выражения столбцов.
SQL CROSS JOIN и вопросы производительностиЗапросы SQL, содержащие ключевое слово CROSS JOIN, могут быть очень дорогостоящими. Мы пытаемся сказать, что эти запросы могут потреблять больше ресурсов и вызывать проблемы с производительностью. Для следующего запроса мы проанализируем план выполнения с помощью ApexSQL Plan. В сгенерированном фактическом плане выполнения мы увидим оператор вложенных циклов, и когда мы наведем указатель мыши на этот оператор, появится всплывающее окно с подробностями. В этом окне нам бросается в глаза предупреждающее сообщение. Сообщение «No Join Predicate» указывает, что этот запрос может столкнуться с проблемами производительности. Совет: ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ может быть реализовано только с вложенными циклами, поэтому следующие запросы вернут ошибку, если мы заставим оптимизатор запросов использовать другие типы соединений.
Заключение В этой статье мы подробно изучили основы SQL CROSS JOIN, а также упомянули соображения производительности CROSS JOIN.
Esat Erkec Esat Erkec — специалист по SQL Server, который начал свою карьеру более 8 лет назад в качестве разработчика программного обеспечения. Он является сертифицированным экспертом по решениям Microsoft для SQL Server. Большая часть его карьеры была посвящена администрированию и разработке баз данных SQL Server. Его текущие интересы связаны с администрированием баз данных и бизнес-аналитикой. Вы можете найти его на LinkedIn. Просмотреть все сообщения Эсата Эркеца Последние сообщения от Esat Erkec (посмотреть все) MySQL CROSS JOIN Keyword ❮ Предыдущий Ключевое слово SQL CROSS JOIN Ключевое слово Синтаксис CROSS JOIN SELECT имя_столбца Примечание: Демонстрационная база данныхВ этом руководстве мы будем использовать известную учебную базу данных Northwind. Ниже представлена выборка из таблицы «Клиенты»:
И выбор из таблицы «Заказы»:
MySQL CROSS JOIN ПримерСледующая инструкция SQL выбирает всех клиентов и все заказы: Пример SELECT Customers. Попробуйте сами » Примечание: Ключевое слово Если добавить предложение Пример SELECT Customers.CustomerName, Orders.OrderID Попробуйте сами » ❮ Предыдущая ВЫБОР ЦВЕТАЛучшие учебникиУчебник по HTML 900 Справочник Основные примерыПримеры HTML FORUM | |
Этот синтаксис не включает ключевое слово CROSS JOIN; только мы будем размещать таблицы, которые будут объединены после предложения FROM и разделены запятой.
Таким образом, мы можем создать более значимый набор результатов меню завтрака.
По этой причине оптимизатор запросов предупреждает нас об этой потенциальной проблеме. Вкратце, когда мы решаем использовать CROSS JOIN в каком-либо запросе, мы должны учитывать количество таблиц, которые будут объединены. Например, когда мы выполняем ПЕРЕКРЕСТНОЕ СОЕДИНЕНИЕ двух таблиц, и если первая содержит 1000 строк, а вторая — 1000 строк, количество строк в результирующем наборе будет равно 1 000 000 строк.
Когда CROSS JOIN используется для таблиц с большим количеством строк, это может негативно сказаться на производительности.
Ф.
CustomerName, Orders.OrderID 
CSS 