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

Откуда:
Сообщений: 245
База занимает много места и продолжает постоянно увеличиваться. Практически всё занимает одна таблица:


Time Address Object
2018-08-15 01:12:20.000 26 3
2018-08-21 11:15:21.000 18 4
2018-08-20 07:49:08.000 2 2
2018-08-20 07:49:27.000 2 2
2018-08-20 07:49:52.000 2 2
2018-07-08 10:32:36.000 16 3
2018-08-16 14:15:13.000 40 4
2018-08-12 10:11:57.000 37 3
2018-08-19 06:31:47.000 11 4
2018-06-14 15:23:13.000 27 3
2018-06-14 15:23:42.000 27 3
2018-08-17 21:21:30.000 12 4
2018-08-20 13:07:14.000 24 2
2018-08-12 02:34:00.000 17 3
2018-08-03 17:53:15.000 22 3
2018-08-21 17:41:53.000 14 2
2018-08-14 20:11:47.000 17 4
2018-08-14 20:11:55.000 17 4
2018-08-13 19:19:07.000 7 2

Как можно оставить только одну запись для минуты с одинаковым Address и Object.
Например:

2018-08-20 07:49:08.000 2 2
2018-08-20 07:49:27.000 2 2
2018-08-20 07:49:52.000 2 2
2018-06-14 15:23:13.000 27 3
2018-06-14 15:23:42.000 27 3
2018-08-14 20:11:47.000 17 4
2018-08-14 20:11:55.000 17 4

Чтоб осталось только:

2018-08-20 07:49:08.000 2 2
2018-06-14 15:23:13.000 27 3
2018-08-14 20:11:47.000 17 4
23 авг 18, 09:41    [21651598]     Ответить | Цитировать Сообщить модератору
 Re: Уменьшить размер БД  [new]
LameUser
Member

Откуда:
Сообщений: 2028
alikon1
База занимает много места и продолжает постоянно увеличиваться. Практически всё занимает одна таблица:


Time Address Object
2018-08-15 01:12:20.000 26 3
2018-08-21 11:15:21.000 18 4
2018-08-20 07:49:08.000 2 2
2018-08-20 07:49:27.000 2 2
2018-08-20 07:49:52.000 2 2
2018-07-08 10:32:36.000 16 3
2018-08-16 14:15:13.000 40 4
2018-08-12 10:11:57.000 37 3
2018-08-19 06:31:47.000 11 4
2018-06-14 15:23:13.000 27 3
2018-06-14 15:23:42.000 27 3
2018-08-17 21:21:30.000 12 4
2018-08-20 13:07:14.000 24 2
2018-08-12 02:34:00.000 17 3
2018-08-03 17:53:15.000 22 3
2018-08-21 17:41:53.000 14 2
2018-08-14 20:11:47.000 17 4
2018-08-14 20:11:55.000 17 4
2018-08-13 19:19:07.000 7 2

Как можно оставить только одну запись для минуты с одинаковым Address и Object.
Например:

2018-08-20 07:49:08.000 2 2
2018-08-20 07:49:27.000 2 2
2018-08-20 07:49:52.000 2 2
2018-06-14 15:23:13.000 27 3
2018-06-14 15:23:42.000 27 3
2018-08-14 20:11:47.000 17 4
2018-08-14 20:11:55.000 17 4

Чтоб осталось только:

2018-08-20 07:49:08.000 2 2
2018-06-14 15:23:13.000 27 3
2018-08-14 20:11:47.000 17 4


сгруппируйте по адресу, объекту, дате, часу и минуте, в рамках группы выберите минимальный time, address, object.
23 авг 18, 09:48    [21651610]     Ответить | Цитировать Сообщить модератору
 Re: Уменьшить размер БД  [new]
aleksrov
Member

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

Сгрупировать вытащив id нужной записи в группе, остальное грохнуть
23 авг 18, 09:50    [21651613]     Ответить | Цитировать Сообщить модератору
 Re: Уменьшить размер БД  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
alikon1
Как можно оставить только одну запись для минуты с одинаковым Address и Object.
Просто сделайте distinct, только в запросе сделайте округление времени до минуты (например, сделав CONVERT(SMALLDATETIME, ...))
LameUser
сгруппируйте по адресу, объекту, дате, часу и минуте, в рамках группы выберите минимальный time, address, object.
С минимальными не надо, если Address или Object отличаются, ТС нужно их вывести.
23 авг 18, 09:52    [21651616]     Ответить | Цитировать Сообщить модератору
 Re: Уменьшить размер БД  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31371
alexeyvg
alikon1
Как можно оставить только одну запись для минуты с одинаковым Address и Object.
Просто сделайте distinct, только в запросе сделайте округление времени до минуты (например, сделав CONVERT(SMALLDATETIME, ...))
А, или время нужно оставить без округления, например, минимальное?

Тогда можно сделать группировку по Address и Object, и взять MIN(Time)
23 авг 18, 09:54    [21651619]     Ответить | Цитировать Сообщить модератору
 Re: Уменьшить размер БД  [new]
TaPaK
Member

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

DECLARE @T Table (a datetime, b int, c int)
INSERT INTO @T VALUES
('2018-08-15 01:12:20.000'		,26	,3),
('2018-08-21 11:15:21.000'		,18	,4),
('2018-08-20 07:49:08.000'		,2	,2),
('2018-08-20 07:49:27.000'		,2	,2),
('2018-08-20 07:49:52.000'		,2	,2),
('2018-07-08 10:32:36.000'		,16	,3),
('2018-08-16 14:15:13.000'		,40	,4)

;WITH x AS 
(
	SELECT 
	[R] = ROW_NUMBER() OVER (PARTITION BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a),0),b,c ORDER BY a,b,c) 
	FROM @T 
) 
DELETE FROM x
WHERE 
 [R] <> 1


ps CONVERT(SMALLDATETIME, ...)) округляет и в большую сторону, можно получить лютую хрень
23 авг 18, 10:03    [21651631]     Ответить | Цитировать Сообщить модератору
 Re: Уменьшить размер БД  [new]
Руслан Дамирович
Member

Откуда: Резиновая нерезиновая
Сообщений: 940
Сдается мне, ТСу просто нужно удалить дубликаты только до следующего изменения этих значений.
DECLARE
  @t TABLE ( 
    [time] DATETIME2(0),
    [address] INT,
    [object] INT )
;
INSERT
INTO
  @t
VALUES
  ( '2018-08-15 01:12:20',  26,	3 ),
  ( '2018-08-21 11:15:21',  18,	4 ),
  ( '2018-08-20 07:49:08',   2,	2 ),
  ( '2018-08-20 07:49:27',   2,	2 ),
  ( '2018-08-20 07:49:52',   2,	2 ),
  ( '2018-07-08 10:32:36',  16,	3 ),
  ( '2018-08-16 14:15:13',  40,	4 ),
  ( '2018-08-12 10:11:57',  37,	3 ),
  ( '2018-08-19 06:31:47',  11,	4 ),
  ( '2018-06-14 15:23:13',  27,	3 ),
  ( '2018-06-14 15:23:42',  27,	3 ),
  ( '2018-08-17 21:21:30',  12,	4 ),
  ( '2018-08-20 13:07:14',  24,	2 ),
  ( '2018-08-12 02:34:00',  17,	3 ),
  ( '2018-08-03 17:53:15',  22,	3 ),
  ( '2018-08-21 17:41:53',  14,	2 ),
  ( '2018-08-14 20:11:47',  17,	4 ),
  ( '2018-08-14 20:11:55',  17,	4 ),
  ( '2018-08-13 19:19:07',   7,	2 )
;
WITH
cte AS (
  SELECT
    [time],
    [address],
    [object],
    [gr] = ROW_NUMBER() OVER ( ORDER BY [time] ) 
         - ROW_NUMBER() OVER ( PARTITION BY [address], [object] ORDER BY [time] )
  FROM
    @t
)
SELECT
  [time] = MIN( [time] ),
  [address],
  [object]
FROM
  cte
GROUP BY
  [address],
  [object],
  [gr]
ORDER BY
  1
;
23 авг 18, 10:03    [21651632]     Ответить | Цитировать Сообщить модератору
 Re: Уменьшить размер БД  [new]
alikon1
Member

Откуда:
Сообщений: 245
Время любое, лишь бы одна запись на минуту. Можно и запись на 90, 120 сек., но с минутой кажется проще сделать. Знания минимальные по это вопросу, поэтому и займет много времени вникать. Просто ставили приборы, которые уведомления слали каждую секунду. Сами приборы перенастроили сейчас на нормальный интервал. А как базу вычистить от того что успело за месяц набежать. Чтение этих данных вместо нескольких секунд занимает минуты.
23 авг 18, 10:06    [21651635]     Ответить | Цитировать Сообщить модератору
 Re: Уменьшить размер БД  [new]
LameUser
Member

Откуда:
Сообщений: 2028
TaPaK
LameUser,

DECLARE @T Table (a datetime, b int, c int)
INSERT INTO @T VALUES
('2018-08-15 01:12:20.000'		,26	,3),
('2018-08-21 11:15:21.000'		,18	,4),
('2018-08-20 07:49:08.000'		,2	,2),
('2018-08-20 07:49:27.000'		,2	,2),
('2018-08-20 07:49:52.000'		,2	,2),
('2018-07-08 10:32:36.000'		,16	,3),
('2018-08-16 14:15:13.000'		,40	,4)

;WITH x AS 
(
	SELECT 
	[R] = ROW_NUMBER() OVER (PARTITION BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a),0),b,c ORDER BY a,b,c) 
	FROM @T 
) 
DELETE FROM x
WHERE 
 [R] <> 1


ps CONVERT(SMALLDATETIME, ...)) округляет и в большую сторону, можно получить лютую хрень


можно и через ROW_NUMBER(), вопрос задавал не я - тс :)

Мне больше интересно - тс утверждает, что таблица очень большая.
Какой вариант будет работать быстрее - ваш с оконной функцией (row_number()) или мой с группировкой?

+


DECLARE @T Table (a datetime, b int, c int)
INSERT INTO @T VALUES
('2018-08-15 01:12:20.000'		,26	,3),
('2018-08-21 11:15:21.000'		,18	,4),
('2018-08-20 07:49:08.000'		,2	,2),
('2018-08-20 07:49:27.000'		,2	,2),
('2018-08-20 07:49:52.000'		,2	,2),
('2018-07-08 10:32:36.000'		,16	,3),
('2018-08-16 14:15:13.000'		,40	,4)


delete t
from @t t
left join 
	(
		select b,c, min(a) a
		from @T
		group by b,c, datepart(hour, a), datepart(minute, a)
	) v on t.b = v.b and t.c = v.c and t.a = v.a
where v.b is null

23 авг 18, 11:59    [21651815]     Ответить | Цитировать Сообщить модератору
 Re: Уменьшить размер БД  [new]
TaPaK
Member

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

проверьте, мы не против
23 авг 18, 12:03    [21651821]     Ответить | Цитировать Сообщить модератору
 Re: Уменьшить размер БД  [new]
LameUser
Member

Откуда:
Сообщений: 2028
TaPaK
LameUser,

проверьте, мы не против


Еще раз: вопрос задавал не я -> у меня нет возможности проверить.
23 авг 18, 12:15    [21651841]     Ответить | Цитировать Сообщить модератору
 Re: Уменьшить размер БД  [new]
TaPaK
Member

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

проверьте, мы не против


Еще раз: вопрос задавал не я -> у меня нет возможности проверить.

какой ленивый и одновременно любознательный...
23 авг 18, 12:20    [21651854]     Ответить | Цитировать Сообщить модератору
 Re: Уменьшить размер БД  [new]
alikon1
Member

Откуда:
Сообщений: 245
TaPaK
LameUser,

DECLARE @T Table (a datetime, b int, c int)
INSERT INTO @T VALUES
('2018-08-15 01:12:20.000'		,26	,3),
('2018-08-21 11:15:21.000'		,18	,4),
('2018-08-20 07:49:08.000'		,2	,2),
('2018-08-20 07:49:27.000'		,2	,2),
('2018-08-20 07:49:52.000'		,2	,2),
('2018-07-08 10:32:36.000'		,16	,3),
('2018-08-16 14:15:13.000'		,40	,4)

;WITH x AS 
(
	SELECT 
	[R] = ROW_NUMBER() OVER (PARTITION BY DATEADD(MINUTE, DATEDIFF(MINUTE, 0, a),0),b,c ORDER BY a,b,c) 
	FROM @T 
) 
DELETE FROM x
WHERE 
 [R] <> 1


ps CONVERT(SMALLDATETIME, ...)) округляет и в большую сторону, можно получить лютую хрень


Спасибо. Первый вариант попробовал, минут за 10 примерно отработал, удалило 3-3,5 млн. Может 6 млн. записей это только для меня много, думал из-за этого ПО тормозит. Но фактически это не повлияло на скорость работы. Прочитал еще про индексы. Выбрал "Перестроить" и стало работать почти мгновенно.
23 авг 18, 15:36    [21652175]     Ответить | Цитировать Сообщить модератору
 Re: Уменьшить размер БД  [new]
256k
Member

Откуда: с.Торчилово, Псковская обл.
Сообщений: 437
LameUser
TaPaK
LameUser,

проверьте, мы не против


Еще раз: вопрос задавал не я -> у меня нет возможности проверить.


Еще раз: проверьте, мы не против!
23 авг 18, 16:05    [21652220]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить