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

Откуда: Moscow
Сообщений: 179
Вот прочитал тут на форуме про кросстаблицы, и что-то мне все так некрасиво показалось ;-)
Попробуйте вариант получше, безо васяких временных таблиц и тому подобного, и работает мгновенно и по сути алгоритма - ну куда еще быстрей...
Надеюсь без логических ошибок, проверяйте :-)

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER procedure [dbo].[crossTab](
  @groupRowExpressionsWithAliases varchar(8000), /*список вертикальных ключевых выражений/полей с алиасами*/
  @groupRowAliases varchar(8000), /*список алиасов вертикальных ключевых выражений/полей*/
  @groupColExpression varchar(8000) /*горизонтальное ключевое выражение/поле без алиаса*/, 
  @groupColFieldAlias varchar(8000) /*алиас горизонтального ключевого выражение/поле*/, 
  @sourceAggregateExpression varchar(8000), /*аггрегирующее выражение для получения итогового поля*/
  @prefixForResultColumnAliases varchar(8000), /*префикс имен горизонтальных колонок 
                                                 в результирующем датасете*/
	@fromSection varchar(8000), /*любое допустимое для from выражение*/
	@whereSection varchar(8000), /*любое условие*/
	@groupBySection varchar(8000), /*список ключевых выражений/полей (вертикальных и горизонтального) 
                                   для группировки в исходном запросе*/
  @crossAggregateFunctionExpression varchar(8000) /*аггрегирующая функция (или выражение без скобок) 
                        для окончательного аггрегирования/складывания итогов по совпадающим ключевым полям*/
)
as
/* ******* ПОЯСНЕНИЯ ********
по генератору кросстаблицы. 

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

К примеру, результат вашего исходного запроса вида

select 
  <список ключевых выражений/полей, разворачиваемых вертикально>, 
  <ключевое выражение/поле, разворачиваемое горизонтально>,
  <выражение/поле итогов>
from
  <ваши исходные данные>
where
  <условие отбора>
group by
  <группировка для получения итогов по ключевым выражениям/полям>

преобразуется в запрос, возвращающий кросстаблицу:

select
  <список алиасов исходных ключевых выражений/полей, разворачиваемых вертикально>,
  <список результирующих горизонтальных полей col_1,  col_2,  ...,  col_N>
from 
  (<ваш исходный запрос>) t
group by 
  <список алиасов ключевых выражений/полей, разворачиваемых вертикально>,
order by 
  <список алиасов ключевых выражений/полей, разворачиваемых вертикально>,

Главная суть - использование функции dense_rank для соотнесения значений
выражения/поля, разворачиваемого горизонтально, с номерами развернутых колонок

В даной версии:
- работает мгновенно:
  - ни каких временных таблиц
  - всего два очень быстрых запроса
  1-й запрос - подсчет числа уникальных значений, выражения/поля, разворачиваемого вертикально
               подсчет - исключительно по вашему исходному запросу
  2-й запрос - простая и быстрая группировка по вашему исходному запросу
- допускается любое аггрегирующее выражение в вашем исходном запросе для получения итогового поля,
- любой тип итогового поля, допустимый для окончательного аггрегирования/складывания 
  итогов по совпадающим ключевым полям
- любая аггрегирующая функция (или выражение без скобок) для окончательного аггрегирования/складывания 
  итогов по совпадающим ключевым полям
- любые типы ключевых полей (строки/колонки), 
- ни каких преобразований типа cast или convert ни с одним из полей исходных данных
- можно развить алгоритм, допустив несколько горизонтальных колонок и итоговых полей

*/
declare 
        @SQL varchar(max),
        @InnerTableAlias varchar(20),
        @AggregateAlias varchar(20),
        @RankAlias varchar(20),
        @count_fields smallint,
        @field_num smallint,
        @CrLf char(2)
begin 
  -- считаем количество результирующих колонок - работает мгновенно
  exec ('declare tempCur cursor for select count(distinct ' + @groupColExpression 
  + ') from ' + @fromSection + ' where ' + @whereSection)
  open tempCur
  fetch next from tempCur into @count_fields
  close tempCur
  deallocate tempCur 
  -- посчитали количество колонок

  -- генерим алиасы, чтобы они не спутались с теми, что могут встретится в исходном запросе
  -- алиас самого вложенного запроса
  set @InnerTableAlias = 'InnerTableAlias_' + ltrim(str(cast(rand() * 1000000 as int))) + '_'
  -- алиас аггрегирующего выражения/поля во вложенном запросе                                  
  set @AggregateAlias = 'AggregateAlias_' + ltrim(str(cast(rand() * 1000000 as int))) + '_'
  -- алиас поля ранга во вложенном запросе
  set @RankAlias = 'RankAlias_' + ltrim(str(cast(rand() * 1000000 as int))) + '_'

  -- генерим начало внешнего запроса со списком горизонтальных колонок и выражений для их подсчета
  set @CrLf = char(13) + char(10)
  set @SQL = 'select ' + @groupRowAliases
  set @field_num = 1
  while @field_num <= @count_fields
  begin
    set @SQL = @SQL + @CrLf + ', ' + @crossAggregateFunctionExpression 
      + '(case ' + /*@InnerTableAlias + '.' + */@RankAlias 
        + ' when ' + ltrim(str(@field_num)) 
        + ' then ' + /*@InnerTableAlias + '.' + */@AggregateAlias 
        + ' else 0 end) ' + @prefixForResultColumnAliases + '_' + ltrim(str(@field_num))
    set @field_num = @field_num + 1
  end
  --сгенерили начало

  --генерим исходный вложенный запрос, с соответсвующими алиасами
  set @SQL = @SQL + @CrLf + ' from (select ' + @groupRowExpressionsWithAliases + ', ' 
    + @groupColExpression + ' as ' + @groupColFieldAlias 
    + ', ' + @sourceAggregateExpression + ' as ' + @AggregateAlias
    + ', dense_rank() over(order by ' + @groupColExpression + ') as ' + @RankAlias
    + ' from ' + @fromSection + ' where ' + @whereSection 
    + ' group by ' + @groupBySection + ') ' + @InnerTableAlias
    + @CrLf + ' group by ' + @groupRowAliases
    + @CrLf + ' order by ' + @groupRowAliases
  --все готово
  print @SQL
  exec (@SQL)

end;

GO

--пример для таблицы Sales, которая из примера от Microsoft
exec crossTab 'year(s.date) as r_year, month(s.date) as r_month', 'r_year, r_month', 
  'datepart(day, s.date)', 'r_part', 
  'sum(s.amount)',
  'col_', 
  'mrs.Sales s', 
  'year(s.date) > 1987 and s.Amount > 1', 
  'year(s.date), month(s.date), datepart(day, s.date)',
  'sum'
26 фев 09, 06:29    [6861781]     Ответить | Цитировать Сообщить модератору
 Re: вариант получения кросстаблицы, получше тех, что встретил  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
А оператор PIVOT (начиная с 2005) ещё проще.
26 фев 09, 06:55    [6861792]     Ответить | Цитировать Сообщить модератору
 Re: вариант получения кросстаблицы, получше тех, что встретил  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

вариант в FAQ на выходе дает таблицу, с которой можно при необходимости
дальше что-то сделать на стороне сервера (что и зачем - вопрос, конечно,
интересный, но многим, помнится, хотелось именно этого).
ваш вариант просто возвращает набор данных клиенту.
вариант в FAQ позволяет дать столбцам осмысленные имена (а не просто col_1, col_2, ...).
ваш вариант такой возможности не дает.
это по поводу недоумения - зачем там временные таблицы.

судя по комментариям в FAQ, свое знакомство с ms sql вы начали с 2005-ого.
процедура, если мне память не изменяет, еще под 7.0 писалась. ключевого слова
pivot там еще не было. как (УВЫ!!!) не было и типа данных varchar(max) - именно для этого
там десяток переменных @sql объявляется и сложно все так.

ну и так, по мелочи:
в вашем варианте обязательно указывать какое-нибудь условие where.

вот это вот:
   -- считаем количество результирующих колонок - работает мгновенно
   exec ('declare tempCur cursor for select count(distinct ' + @groupColExpression
   + ') from ' + @fromSection + ' where ' + @whereSection)
   open tempCur
   fetch next from tempCur into @count_fields
   close tempCur
   deallocate tempCur
   -- посчитали количество колонок
гораздо проще можно сделать - с помощью sp_executesql с output-параметром - пример
можно посмотреть в FAQ.

Posted via ActualForum NNTP Server 1.4

26 фев 09, 08:53    [6861935]     Ответить | Цитировать Сообщить модератору
 Re: вариант получения кросстаблицы, получше тех, что встретил  [new]
sqllex
Member

Откуда: Kiev
Сообщений: 710
Кстати, может быть у кого-то в загашнике завался вариант с динамическим построением через PIVOT. Поделитесь, пожалуйста.

ЗЫ
В справке есть описание примеров через PIVOT в разделе Integration Services Objects and Concepts > Data Flow Elements > Integration Services Transformations >

Расширенный вариант от Pinal Dave (верчу PIVOT как хочу) есть на
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/
26 фев 09, 18:03    [6866368]     Ответить | Цитировать Сообщить модератору
 Re: вариант получения кросстаблицы, получше тех, что встретил  [new]
MaratSH
Member

Откуда: Moscow
Сообщений: 179
daw,

то, о чем в говорите, понятно. я ведь и имел ввиду, что это - пример,как НЕ использовать временные таблицы для целей подсчета числа колонок, поименовки колонок. Естественно, что поименовать колонки - элементарное дело, достаточно открыть курсор и с него же получить и количество и имена колонок.
А чтобы дать пользователю именно временную таблицу, достаточно в мой запрос добавить into.
Я ведь для чего пример привел - производительность решения и чистота данных. Во первых - нет преобразований ни каких, данные передаются пользователю в оригинальном виде, безо всяких convert и cast, во вторых - в тех примерах окончательная сборка тяжелее, и опять же, зависит от типа данных. А тут я могу свернуть таблицу на основании исходных данных типа varchar и чего угодно, лишь бы это поддерживалось group by,т.е. любым выражением поддерживаемым group by. И чуток переписав и упростив процедурпу можно за исходные данные использовать вообще просто запрос безо всяких group by. Кстати пример приблизительный, т.е. служит примером, как можно собирать такие таблицы в тех случаях, когда вы не готовы разворачивать многомегатонные итегрейшн сервисес с их PIVOT преобразованиями, как тут уже похвалится успели.
Короче, я к тому, что люди бились тут головой о стену разрабатывая алгоритмы, которые по сути оказывались дохленькими - то зависят от типа данных, то аггрегирующие функции предопределенные, то восьмитысячного варчара мало. К примеру, на счет варчаров 2000-м - я не думаю, что в 2000-м сервере было бы недостаточно 8 кило для запросов, ведь если у вас результирующая кросстаблица имеет очень большое число полей, то это уже бага архитекторы вашего приложения - ну скажите, кому надо глядеть на такую огромную таблицу? Зачем? Приведите пример? Сам набор исходных даных может быть большим, но для того чтобы посмотреть на 5-20 колонок данных зачем разворачивать трехсотколончатую таблицу и тянуть ее на клиента? Лучше уж реализовать механизм drilldown на клиете и/или сервере, ну и ктому вообще построить кубы и брать с них нужные срезы теми же системами подготовки отчетов, в которых разворот кросс-таблицы - простое и старое дело, о ктором задумваться не надо.
26 фев 09, 23:04    [6867303]     Ответить | Цитировать Сообщить модератору
 Re: вариант получения кросстаблицы, получше тех, что встретил  [new]
Dikiy Pelemen`
Member

Откуда:
Сообщений: 7
sqllex,
пользуйтес елсли еще надо, таблица из более 2500 столбцов и получаемых ста строк строиться тока в путь

DECLARE @cols AS nvarchar(MAX)
;
WITH tableCTE
AS
(SELECT id  FROM tableHeader)

SELECT @cols = ISNULL(@cols + ',[', '[') + CAST([id] AS nvarchar(10)) + ']'
FROM tableCTE
--сформировали строку с перечислением через рекурсию

DECLARE @sql AS nvarchar(MAX)

SET @sql = N'SELECT *
FROM (SELECT  список нужных полей 
      FROM tableData) as tablePrePivoting
  PIVOT(SUM(поле по которому агрегируем) FOR поле которое содержит значения по которым транспонируем IN(' + @cols + N')) AS tablePivoting'
PRINT @sql --посмотрим на этот кавайный запрос


EXEC  (@sql)
3 июл 09, 12:13    [7372678]     Ответить | Цитировать Сообщить модератору
 Re: вариант получения кросстаблицы, получше тех, что встретил  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
А чего вы все: "PIVOT, PIVOT!"?
Зачем он вообще нужен? Чтобы требовался сервер не ранее 2005-го? Давно этого не понимаю.
MaratSH
Короче, я к тому, что люди бились тут головой о стену разрабатывая алгоритмы, которые по сути оказывались дохленькими - то зависят от типа данных, то аггрегирующие функции предопределенные, то восьмитысячного варчара мало.
Это такая неявная хвастливость?
3 июл 09, 12:38    [7372870]     Ответить | Цитировать Сообщить модератору
 Re: вариант получения кросстаблицы, получше тех, что встретил  [new]
Dikiy Pelemen`
Member

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

Если есть желание то можете и с dbf работать, просто есть механизмы реалезующие кросстабы лучше и проще, да в 2000 сикле этого не было, но уже вышел 2008. У Вас есть детский совоЧЕК и хорошая штыковая лопата, чем вы предпочтете пользоваться???
А еще хотите тайну для PIVOT`а и UNPIVOT`а создаються планы запроса, если в курсе что это.
3 июл 09, 15:14    [7374034]     Ответить | Цитировать Сообщить модератору
 Re: вариант получения кросстаблицы, получше тех, что встретил  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Dikiy Pelemen`
А еще хотите тайну для PIVOT`а и UNPIVOT`а создаються планы запроса, если в курсе что это.
Не, я не в курсе!
А знаете ли Вы, что план создаётся для ЛЮБОГО запроса?
И план для запроса с пресловутым PIVOT получается таким же, как и при использовании привычного универсального CASE?

А про UNPIVOT я ничего не говорил. IMHO, классная штука.

Когда-то я только начал читать про новые возможности SQL2005,
и от PIVOT ожидал, что в нём предусмотрен какой-нибудь хитрый механизм
формирования неизвестного заранее количества колонок
(на основании какого-нибудь внешнего SELECTа, например).
Но когда дочитал до конца, был разочарован. В таком виде он просто не нужен,
ибо представляет собой просто другую запись суммирования CASEов.
Кто-то скажет: зато запись короче! Но разве это так уж важно?
3 июл 09, 16:36    [7374759]     Ответить | Цитировать Сообщить модератору
 Re: вариант получения кросстаблицы, получше тех, что встретил  [new]
Dikiy Pelemen`
Member

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

Ну, а я тоже не в курсе что Pivot являеться синтаксической оболочкой для столь вами и так то мной любимого case))), притом что чрез рекурсию можно было собрать и набор кейсов, но заметьте код ведь гораздо элегантней и читабельней)).
А вот стем что в Pivot`е у нас следущие FOR pivot_column IN ( <column_list>) ), я тоже обломался впервый раз, но собрать список колонок который мне нужен не такая уж и проблема.
Хотя возможно в недрах msdn и есть пример того как надо делать эту вещь правильно)
4 июл 09, 15:00    [7376744]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: вариант получения кросстаблицы, получше тех, что встретил  [new]
Arafat
Member

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

должно работать на 2000м скуле.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
alter PROCEDURE [dbo].[sp_CrossTab]
  @table       AS varchar(max),-- Таблица для построения crosstab отчета
  @onrows      AS varchar(max),-- Значение для группировки по строкам
  @onrowsalias AS varchar(max) = NULL,-- Псевдоним для группируемой колонки
  @oncols      AS varchar(max),-- Значение для группировки по колонкам
  @sumcol      AS varchar(max) = NULL -- Значение для суммирования
AS
SET NOCOUNT ON
DECLARE
  @sql AS varchar(max),
  @sql_1 AS varchar(max),
  @NEWLINE AS char(1), 
  @onrows_temp AS varchar(max)

SET @onrows_temp = @onrows;
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 varchar(8000) NOT NULL PRIMARY KEY)

DECLARE @keyssql AS varchar(max)
SET @keyssql =
  'INSERT INTO #keys ' +
  'SELECT DISTINCT CAST(' + @oncols + ' AS nvarchar(100)) ' +
  'FROM ' + @table

EXEC (@keyssql)

-- Шаг 3: Средняя часть строки SQL.
DECLARE @key AS varchar(max)
SELECT @key = MIN(keyvalue) FROM #keys
WHILE @key IS NOT NULL
BEGIN
  SET @sql = @sql + ',' + @NEWLINE +
    '  CASE (CAST(' + @oncols + ' AS nvarchar(100))) ' + @NEWLINE +
    '        WHEN N''' + @key + ''' THEN isnull(' + 
						CASE
                          WHEN @sumcol = '' THEN '1'
                          ELSE @sumcol
                        END +
    ','''')        ELSE '''''    + @NEWLINE +
    '      END AS [' + @key+']'
  SET @onrows = @onrows + ',' + '['+  @key +']'
  SELECT @key = MIN(keyvalue) FROM #keys
  WHERE keyvalue > @key
END

-- Шаг 4: Конец строки SQL.
SET @sql =  @sql + @NEWLINE +
  'INTO #temp '+
  'FROM ' + @table      + @NEWLINE +
  'ORDER BY ' + @onrows     + @NEWLINE 


SET @sql_1 =
  'select '+ @onrows_temp +' ' + @NEWLINE +
  CASE
    WHEN @onrowsalias IS NOT NULL THEN ' AS ' + @onrowsalias
    ELSE ''
  END

SELECT @key = MIN(keyvalue) FROM #keys
WHILE @key IS NOT NULL
BEGIN
  SET @sql_1 = @sql_1 + 
    ',(SELECT STUFF((SELECT '''' +[' + @key + '] from #temp t2 where t1.'+@onrows_temp+'=t2.'+@onrows_temp+' FOR XML PATH('''') ),1,0,'''')) as [' + @key + ']'
  SELECT @key = MIN(keyvalue) FROM #keys
  WHERE keyvalue > @key
END

SET @sql_1 =  @sql_1 + ' '+ ' FROM #temp t1 GROUP BY '+@onrows_temp

--PRINT @sql  + @NEWLINE + @sql_1-- для отладки
EXEC (@sql + @NEWLINE + @sql_1)

SET NOCOUNT OFF
6 сен 11, 19:04    [11237275]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить