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

Откуда:
Сообщений: 329
Есть таблица до 1 млрд строк. Данные в них повторяются.
create table MySrcData 
(
   [PKField1] varchar(30) not null,
   [PKField2] varchar(30) not null,
   [PKField3] varchar(30) not null,
   [BegTime] datetime not null,
   [EndTime] datetime not null,
   [Field1] varchar(30),
   [Field2] varchar(30),
   [Field3] varchar(30),
   [Field4] varchar(30),
)

Требуется скопировать их в другую таблицу:
create table MyDstData
(
[PKField1] varchar(30) not null,
[PKField2] varchar(30) not null,
[PKField3] varchar(30) not null,
[BegTime] datetime not null,
[EndTime] datetime not null,
[Field1] varchar(30),
[Field2] varchar(30),
[Field3] varchar(30),
[Field4] varchar(30),
primary key clustered ([PKField1],[PKField2],[PKField3])
)
сгруппировав по [PKField1],[PKField2],[PKField3]. Если с [BegTime]/[EndTime] всё понятно: min() и max(),
то в [Field1],[Field2],[Field3],[Field4] нужно сложить самые свежие данные (где [EndTime] = max([EndTime])).
Как это правильно сделать?
6 дек 16, 14:10    [19972370]     Ответить | Цитировать Сообщить модератору
 Re: Primary Key и группировка данных  [new]
=Сергей=
Member

Откуда:
Сообщений: 329
сделал навскидку так, покритикуйте плиз
select
     B.[Field1]
   ,B.[Field2]
   ,B.[Field3]
   ,B.[BegTime]
   ,B.[EndTime]
   ,A.[Field1]
   ,A.[Field2]
   ,A.[Field3]
   ,A.[Field4]
from [MySrcData] A
join
(
   select
        [Field1]
      ,[Field2]
      ,[Field3]
      ,[BegTime] = min([BegTime])
      ,[EndTime] = max([EndTime])
   from [MySrcData]
   group by [Field1],[Field2],[Field3]

) B on A.[Field1] = B.[Field1] and A.[Field2] = B.[Field2] and A.[Field3] = B.[Field3] and A.[EndTime] = B.[EndTime]
6 дек 16, 14:30    [19972491]     Ответить | Цитировать Сообщить модератору
 Re: Primary Key и группировка данных  [new]
Sybex
Member

Откуда: Moscow
Сообщений: 113
Можно вот так сделать, но про производительность судить не берусь
;WITH MySrcData ([PKField1], [PKField2], [PKField3], [BegTime], [EndTime], [Field1], [Field2], [Field3], [Field4])
AS
(
    SELECT '1', '2', '3', CAST('2016-12-04T14:23:00' AS datetime), CAST('2016-12-06T14:24:00' AS datetime), 'F1', 'F2', 'F3', 'F4'
    UNION ALL
    SELECT '1', '2', '3', CAST('2016-12-04T14:24:00' AS datetime), CAST('2016-12-06T14:25:00' AS datetime), 'F1_new', 'F2_new', 'F3_new', 'F4_new'
    UNION ALL
    SELECT '1', '2', '4', CAST('2016-12-05T10:20:00' AS datetime), CAST('2016-12-05T11:23:00' AS datetime), 'F1', 'F2', 'F3', 'F4'
    UNION ALL
    SELECT '1', '2', '4', CAST('2016-12-05T12:20:00' AS datetime), CAST('2016-12-05T12:20:00' AS datetime), 'F1_new', 'F2_new', 'F3_new', 'F4_new'
)
,CTE
AS
(
    SELECT *
          ,RANK() OVER (PARTITION BY [PKField1], [PKField2], [PKField3] ORDER BY [EndTime] DESC) AS [R]
    FROM MySrcData
)
SELECT *
  FROM CTE
 WHERE R = 1
6 дек 16, 14:33    [19972527]     Ответить | Цитировать Сообщить модератору
 Re: Primary Key и группировка данных  [new]
Sybex
Member

Откуда: Moscow
Сообщений: 113
В вашем случае таблица будет читаться целиком два раза - в моём один.
6 дек 16, 14:37    [19972563]     Ответить | Цитировать Сообщить модератору
 Re: Primary Key и группировка данных  [new]
Sybex
Member

Откуда: Moscow
Сообщений: 113
Стоп! А по каким полям Вы группируете в своём запросе?
В коде у Вас:
=Сергей=
   group by [Field1],[Field2],[Field3]

А в стартовом сообщении:
=Сергей=
сгруппировав по [PKField1],[PKField2],[PKField3].
6 дек 16, 14:48    [19972661]     Ответить | Цитировать Сообщить модератору
 Re: Primary Key и группировка данных  [new]
=Сергей=
Member

Откуда:
Сообщений: 329
Sybex
Стоп! А по каким полям Вы группируете в своём запросе?

Простите, конечно же [PKField1],[PKField2],[PKField3]

Спасибо большое!
6 дек 16, 14:51    [19972684]     Ответить | Цитировать Сообщить модератору
 Re: Primary Key и группировка данных  [new]
o-o
Guest
Sybex
Можно вот так сделать, но про производительность судить не берусь
;WITH MySrcData ([PKField1], [PKField2], [PKField3], [BegTime], [EndTime], [Field1], [Field2], [Field3], [Field4])
AS
(
    SELECT '1', '2', '3', CAST('2016-12-04T14:23:00' AS datetime), CAST('2016-12-06T14:24:00' AS datetime), 'F1', 'F2', 'F3', 'F4'
    UNION ALL
    SELECT '1', '2', '3', CAST('2016-12-04T14:24:00' AS datetime), CAST('2016-12-06T14:25:00' AS datetime), 'F1_new', 'F2_new', 'F3_new', 'F4_new'
    UNION ALL
    SELECT '1', '2', '4', CAST('2016-12-05T10:20:00' AS datetime), CAST('2016-12-05T11:23:00' AS datetime), 'F1', 'F2', 'F3', 'F4'
    UNION ALL
    SELECT '1', '2', '4', CAST('2016-12-05T12:20:00' AS datetime), CAST('2016-12-05T12:20:00' AS datetime), 'F1_new', 'F2_new', 'F3_new', 'F4_new'
)
,CTE
AS
(
    SELECT *
          ,RANK() OVER (PARTITION BY [PKField1], [PKField2], [PKField3] ORDER BY [EndTime] DESC) AS [R]
    FROM MySrcData
)
SELECT *
  FROM CTE
 WHERE R = 1

хотя бы уж row_number(), какой rank(),
он же одинаков у дублей
6 дек 16, 14:54    [19972714]     Ответить | Цитировать Сообщить модератору
 Re: Primary Key и группировка данных  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
o-o,

PK жешь
6 дек 16, 14:57    [19972734]     Ответить | Цитировать Сообщить модератору
 Re: Primary Key и группировка данных  [new]
Sybex
Member

Откуда: Moscow
Сообщений: 113
TaPaK
o-o,

PK жешь

Нет, o-o прав! Там ROW_NUMBER должно быть. PK там ещё нет, как раз группировка идёт по этим полям. Это в новой таблице там PK будет. Просто меня тут отвлекли, я фигню и сморозил. ТС, поменяй RANK на ROW_NUMBER.
6 дек 16, 15:02    [19972772]     Ответить | Цитировать Сообщить модератору
 Re: Primary Key и группировка данных  [new]
=Сергей=
Member

Откуда:
Сообщений: 329
Всем большое спасибо!
6 дек 16, 15:07    [19972809]     Ответить | Цитировать Сообщить модератору
 Re: Primary Key и группировка данных  [new]
AmKad
Member

Откуда:
Сообщений: 5211
WITH MySrcData ([PKField1], [PKField2], [PKField3], [BegTime], [EndTime], [Field1], [Field2], [Field3], [Field4])
AS
(
    SELECT '1', '2', '3', CAST('2016-12-04T14:23:00' AS datetime), CAST('2016-12-06T14:24:00' AS datetime), 'F1', 'F2', 'F3', 'F4'
    UNION ALL
    SELECT '1', '2', '3', CAST('2016-12-04T14:24:00' AS datetime), CAST('2016-12-06T14:25:00' AS datetime), 'F1_new', 'F2_new', 'F3_new', 'F4_new'
    UNION ALL
    SELECT '1', '2', '4', CAST('2016-12-05T10:20:00' AS datetime), CAST('2016-12-05T11:23:00' AS datetime), 'F1', 'F2', 'F3', 'F4'
    UNION ALL
    SELECT '1', '2', '4', CAST('2016-12-05T12:20:00' AS datetime), CAST('2016-12-05T12:20:00' AS datetime), 'F1_new', 'F2_new', 'F3_new', 'F4_new'
)
select top 1 with ties *
from MySrcData
order by row_number() OVER (PARTITION BY [PKField1], [PKField2], [PKField3] ORDER BY [EndTime] DESC);
6 дек 16, 15:13    [19972860]     Ответить | Цитировать Сообщить модератору
 Re: Primary Key и группировка данных  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
AmKad
WITH MySrcData ([PKField1], [PKField2], [PKField3], [BegTime], [EndTime], [Field1], [Field2], [Field3], [Field4])
AS
(
    SELECT '1', '2', '3', CAST('2016-12-04T14:23:00' AS datetime), CAST('2016-12-06T14:24:00' AS datetime), 'F1', 'F2', 'F3', 'F4'
    UNION ALL
    SELECT '1', '2', '3', CAST('2016-12-04T14:24:00' AS datetime), CAST('2016-12-06T14:25:00' AS datetime), 'F1_new', 'F2_new', 'F3_new', 'F4_new'
    UNION ALL
    SELECT '1', '2', '4', CAST('2016-12-05T10:20:00' AS datetime), CAST('2016-12-05T11:23:00' AS datetime), 'F1', 'F2', 'F3', 'F4'
    UNION ALL
    SELECT '1', '2', '4', CAST('2016-12-05T12:20:00' AS datetime), CAST('2016-12-05T12:20:00' AS datetime), 'F1_new', 'F2_new', 'F3_new', 'F4_new'
)
select top 1 with ties *
from MySrcData
order by row_number() OVER (PARTITION BY [PKField1], [PKField2], [PKField3] ORDER BY [EndTime] DESC);


=Сергей=, никогда так не делайте.
6 дек 16, 15:58    [19973177]     Ответить | Цитировать Сообщить модератору
 Re: Primary Key и группировка данных  [new]
AmKad
Member

Откуда:
Сообщений: 5211
invm
=Сергей=, никогда так не делайте.
Почему же?
6 дек 16, 16:05    [19973217]     Ответить | Цитировать Сообщить модератору
 Re: Primary Key и группировка данных  [new]
invm
Member

Откуда: Москва
Сообщений: 9406
AmKad
Почему же?
Потому что в вашем варианте миллиард записей сортируется по [PKField1], [PKField2], [PKField3], [EndTime] DESC, а потом еще раз по row_number().
6 дек 16, 16:09    [19973232]     Ответить | Цитировать Сообщить модератору
 Re: Primary Key и группировка данных  [new]
AmKad
Member

Откуда:
Сообщений: 5211
invm
AmKad
Почему же?
Потому что в вашем варианте миллиард записей сортируется по [PKField1], [PKField2], [PKField3], [EndTime] DESC, а потом еще раз по row_number().
Ну да, причем оптимизатор для данного запроса показывает в плане 2 сортировки по 50% каждая. Хотя во втором случае мог бы ее упростить, так как фактически задача сводится не к сортировке, а к поиску минимального. Хотя в любом случае, даже после такой оптимизации решение все равно проиграет варианту с явным предикатом where r=1.
6 дек 16, 16:18    [19973272]     Ответить | Цитировать Сообщить модератору
 Re: Primary Key и группировка данных  [new]
=Сергей=
Member

Откуда:
Сообщений: 329
invm
никогда так не делайте.

а как было бы лучше?
7 дек 16, 19:44    [19977968]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить