Как я могу превратить набор строк в агрегированные столбцы БЕЗ использования сводки в SQL Server 2005?

Вот сценарий: у меня есть таблица, в которой записаны user_id, module_id и дата/время просмотра модуля.

eg.

Table: Log
------------------------------
User_ID  Module_ID   Date
------------------------------
1       red         2001-01-01
1       green       2001-01-02
1       blue        2001-01-03
2       green       2001-01-04
2       blue        2001-01-05
1       red         2001-01-06
1       blue        2001-01-07
3       blue        2001-01-08
3       green       2001-01-09
3       red         2001-01-10
3       green       2001-01-11
4       white       2001-01-12

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

eg.

---------------------------------
User_ID  red green   blue    white
---------------------------------
1       2   1       2       0
2       0   1       1       0
3       1   2       1       0
4       0   0       0       1

Сначала я думал, что смогу сделать это с помощью PIVOT, но без кубиков; база данных представляет собой преобразованную базу данных SQL Server 2000, работающую в SQL Server 2005. Я не могу изменить уровень совместимости, поэтому свод отсутствует.

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

Как я могу это сделать?


sql sql-server pivot sql-server-2005
person cdeszaq    schedule 27.04.2010    source источник

Ответы (4)


arrow_upward
7
arrow_downward

PIVOT можно смоделировать с помощью CASE и GROUP BY.

select
    [user_id],
    sum(case when [Module_ID] = 'red' then 1 else 0 end) as red,
    sum(case when [Module_ID] = 'green' then 1 else 0 end) as green,
    sum(case when [Module_ID] = 'blue' then 1 else 0 end) as blue,
    sum(case when [Module_ID] = 'white' then 1 else 0 end) as white
from [log]
group by
    [user_id]

Конечно, это не работает, если модули различаются (как указано в вопросе), но тогда у PIVOT та же проблема.

Динамическая генерация некоторых sql преодолевает эту проблему, но это решение немного пахнет!

declare @sql nvarchar(max)

set @sql = '
select
    [user_id],'

select @sql = @sql + '
    sum(case when [Module_ID] = ''' + replace([Module_ID], '''','''''') + ''' then 1 else 0 end) as [' + replace([Module_ID], '''','') + '],'
from (select distinct [Module_ID] from [log]) as moduleids

set @sql = substring(@sql,1,len(@sql)-1) + '
from [log]
group by
    [user_id]
'
print @sql
exec sp_executesql @sql

Обратите внимание, что это может быть уязвимо для sql-инъекций, если данным идентификатора модуля нельзя доверять.

person Daniel Renshaw    schedule 27.04.2010
comment
Есть ли способ сделать этот блок операторов sum/case динамическим? Установленные модули не фиксируются, поэтому дискретное перечисление модулей невозможно. - person cdeszaq; 27.04.2010
comment
Заметил это как раз перед тем, как ты написал. У PIVOT та же проблема — вам нужно заранее жестко закодировать нужные столбцы. - person Daniel Renshaw; 27.04.2010
comment
Существует динамический способ получения столбцов, показанный здесь: 2005/2344668#2344668" title="как мне преобразовать строки в столбцы в sql server 2005"> stackoverflow.com/questions/2344590/ но это не работает для меня из-за невозможности использовать сводку . Есть ли способ смешать их? - person cdeszaq; 27.04.2010
comment
Да, можно динамически генерировать sql, как в моем отредактированном ответе, но мне это не нравится! - person Daniel Renshaw; 27.04.2010

arrow_upward
0
arrow_downward

Используя MySQL, я сделал это:

  1. Скопировал ваши данные в Log_Table.sql
  2. create table Log (User_ID mediumint, Module_ID CHAR(5), dte CHAR(10));
  3. load data infile 'Log_Table.sql' INTO TABLE Log FIELDS TERMINATED BY ',';

  4. Вращаться:

    select User_ID AS 'USER',  sum(case
Module_ID WHEN 'red'   then 1 else 0
END) AS 'red', 

sum(case Module_ID WHEN 'green' then 1
else 0 END) AS 'green', 

sum(case Module_ID WHEN 'blue'  then 1
else 0 END) AS 'blue', 

sum(case Module_ID WHEN 'white' then 1
else 0 END) AS 'white'

from Log 

Group By User_ID;

> +------+------+-------+------+-------+ 
> | USER | red  | green | blue | white |
> +------+------+-------+------+-------+ 
> |    1 |    2 |     1 |    2 |     0 |
> |    2 |    0 |     1 |    1 |     0 |
> |    3 |    1 |     2 |    1 |     0 |
> |    4 |    0 |     0 |    0 |     1 |
> +------+------+-------+------+-------+ 
> 4 rows in set (0.00 sec)

Надеюсь это поможет.

person okaygo    schedule 27.04.2010

arrow_upward
-1
arrow_downward

Я считаю, что characteristic functions are what you want.

person Carl Manaster    schedule 27.04.2010

arrow_upward

arrow_downward

person    schedule
comment
Установленные модули не являются фиксированными, поэтому дискретное перечисление модулей невозможно. Есть ли динамический способ сделать это? - person cdeszaq; 27.04.2010