Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Дополнительная группировка данных в Pivot / 8 сообщений из 8, страница 1 из 1
21.01.2020, 17:59
    #39916901
heso
heso
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнительная группировка данных в Pivot
Добрый день,
Просьба помочь, как из таблицы вида

id_param id_objectabcdefg11110NULLNULLNULLNULLNULLNULL12130NULLNULLNULLNULLNULLNULL32NULLNULL180NULLNULLNULLNULL42NULLNULLNULL150NULLNULLNULL
Получить выходную таблицу вида:
id_param id_objectabcdefg11110NULLNULLNULLNULLNULLNULL12130NULL180150NULLNULLNULL

Т.е сгруппировать данные по id_object

Создание:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
CREATE TABLE params
(
    id_param smallint PRIMARY KEY,
    name varchar(50) NOT NULL
)

CREATE TABLE objects_params
(
    id_object int,
    id_param smallint NOT NULL,
    cdate smalldatetime,
    value int
)

INSERT INTO dbo.params (id_param, name)
VALUES (1, 'a'), (2, 'b'), (3, 'c'), (4, 'd'), 
       (5, 'e'), (6, 'f'), (7, 'g')

INSERT INTO dbo.objects_params (id_object, id_param, cdate, value)
VALUES (1, 1, '20191206',NULL), (1, 2,'20191212', 100), (1, 1, '20191201', 110),
       (2, 4, '20191211',120), (2, 1,'20190101', 130), (2, 3, '20191212', 140),
       (2, 4, '20191111',150), (2, 3,'20190201', 160), (2, 3, '20190312', 170),
       (2, 3, '20191201', 175),(2, 3, '20191202', 180), (2, 3, '20191203', 185),
       (2, 3, '20191204', 190)



Скрипт:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
DECLARE @userdate VARCHAR(MAX)
SET @userdate='20191202';

DECLARE @names as VARCHAR(MAX)
SELECT @names =
COALESCE(@names + ', ','') + QUOTENAME(name)
FROM
   (SELECT name
    FROM params 
   ) AS B;

DECLARE @SQL as VARCHAR(MAX)
SET @SQL = 
'WITH op as ( 
      SELECT op.id_object, op.id_param, name, op.cdate, op.value,
             ROW_NUMBER() OVER (PARTITION BY op.id_object, op.id_param ORDER BY op.cdate DESC) as seqnum
      FROM objects_params op JOIN
           params p
           ON op.id_param = p.id_param
		
      WHERE op.cdate <='''+ @userdate +'''
     )

SELECT id_param, id_object, ' + @names + '
FROM (
    select *
    from op
    where seqnum=1
    ) as tbl
pivot 
(
    max(value) for name in (' + @names + ')
) piv'

execute(@SQL)



P.s.
на SO помогли, но не до конца.

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.01.2020, 18:09
    #39916904
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнительная группировка данных в Pivot
heso
Просьба помочь, как из таблицы вида

id_param id_objectabcdefg11110NULLNULLNULLNULLNULLNULL12130NULLNULLNULLNULLNULLNULL32NULLNULL180NULLNULLNULLNULL42NULLNULLNULL150NULLNULLNULL

Получить выходную таблицу вида:
id_param id_objectabcdefg11110NULLNULLNULLNULLNULLNULL12130NULL180150NULLNULLNULL

Т.е сгруппировать данные по id_objectА в чём проблема? груп бай по полю id_object, и макс по остальным (возможно, мин по id_param, если не устраивает макс - вы не указали критерий).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.01.2020, 18:15
    #39916908
heso
heso
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнительная группировка данных в Pivot
alexeyvg,

Когда я пытаюсь добавить group by id_object в данный блок:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
SELECT id_param, id_object, ' + @names + '
FROM (
    select *
    from op
    where seqnum=1
	group by id_object
    ) as tbl
pivot 
(
    max(value) for name in (' + @names + ')
	 
) piv'

execute(@SQL)



То выдает ошибку "Столбец "op.id_param" недопустим в списке выбора, поскольку он не содержится ни в агрегатной функции, ни в предложении GROUP BY."
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.01.2020, 18:24
    #39916914
alexeyvg
alexeyvg
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнительная группировка данных в Pivot
heso
Когда я пытаюсь добавить group by id_object в данный блок:
Нужно этот блок сделать подзапросом, а в верхнем запросе уже будет group by id_object
И не забыть сделать ещё одну переменную, как @names, только с описанием MAX(поле) as поле
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.01.2020, 18:43
    #39916926
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнительная группировка данных в Pivot
heso,

Примерно так
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
SET @SQL = 
'WITH op as ( 
      SELECT op.id_object, op.id_param, name, op.cdate, op.value,
             ROW_NUMBER() OVER (PARTITION BY op.id_object, op.id_param ORDER BY op.cdate DESC) as seqnum
      FROM objects_params op JOIN
           params p
           ON op.id_param = p.id_param
		
      WHERE op.cdate <='''+ @userdate +'''
     )--select * from op

SELECT id_param, id_object, ' + @names + '
FROM (
    select id_object, value, name, min(id_param) over (partition by id_object) as id_param
    from op
    where seqnum=1
    ) as tbl
pivot 
(
    max(value) for name in (' + @names + ')
) piv'


Можно будет уточнить, когда расскажите каким должен быть id_param в результате.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.01.2020, 19:08
    #39916942
heso
heso
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнительная группировка данных в Pivot
invm,

Спасибо, работает.
В данном случае id_param Не имеет значения. В итоговой таблице его можно отбросить. (т.е SELECT id_param, id_object, ' + @names + ').
Пошел курить мануалы, чтобы понять, каким образом в данном примере работает MIN() OVER (PARTITION BY ()).

alexeyvg,
попробовал..может слишком в лоб:
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
DECLARE @userdate VARCHAR(MAX)
SET @userdate='20191202';

DECLARE @names as VARCHAR(MAX)
SELECT @names =
COALESCE(@names + ', ','') + QUOTENAME(name)
FROM
   (SELECT name
    FROM params 
   ) AS B;

DECLARE @SQL as VARCHAR(MAX)
SET @SQL = 
'WITH op as ( 
      SELECT op.id_object, op.id_param, name, op.cdate, op.value,
             ROW_NUMBER() OVER (PARTITION BY op.id_object, op.id_param ORDER BY op.cdate DESC) as seqnum
      FROM objects_params op JOIN
           params p
           ON op.id_param = p.id_param
		
      WHERE op.cdate <='''+ @userdate +'''
     )
SELECT *
FROM (
	SELECT id_param, id_object, ' + @names + '
	FROM (
		select *
		from op
		where seqnum=1
		) as tbl
	pivot 
	(
		max(value) for name in (' + @names + ')
	) piv) try1
Group by id_object'

execute(@SQL)



Выскакивает аналогичная ошибка.
Не очень понял по поводу создания еще одной переменной. Для каких целей?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.01.2020, 19:11
    #39916945
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнительная группировка данных в Pivot
heso
В итоговой таблице его можно отбросить
Тогда так
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
SET @SQL = 
'WITH op as ( 
      SELECT op.id_object, op.id_param, name, op.cdate, op.value,
             ROW_NUMBER() OVER (PARTITION BY op.id_object, op.id_param ORDER BY op.cdate DESC) as seqnum
      FROM objects_params op JOIN
           params p
           ON op.id_param = p.id_param
		
      WHERE op.cdate <='''+ @userdate +'''
     )
SELECT id_object, ' + @names + '
FROM (
    select id_object, value, name
    from op
    where seqnum=1
    ) as tbl
pivot 
(
    max(value) for name in (' + @names + ')
) piv'
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.01.2020, 19:24
    #39916956
heso
heso
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Дополнительная группировка данных в Pivot
invm,

Блин. все гениальное просто. спасибо большое.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Дополнительная группировка данных в Pivot / 8 сообщений из 8, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 473ms
Закрыть
start [/forum/topic.php?fid=46&tablet=1&tid=1686621]:
 0ms
get settings:
 9ms
get forum list:
 16ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 41ms
get topic data:
 14ms
get forum data:
 3ms
get page messages:
 51ms
get tp. blocked users:
 2ms
others: 329ms
total:  473ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]