Гость
Войти | Профиль | Очистить
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Удалить дублирующиеся строки, по условию / 11 сообщений из 11, страница 1 из 1
13.04.2021, 22:24
    #40062231
mynick
mynick
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удалить дублирующиеся строки, по условию
Код: plsql
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.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
-- Версия сервера: 5.5.49-

CREATE TABLE IF NOT EXISTS `text_score_history` (
`id` int(10) unsigned NOT NULL,
  `text_id` mediumint(8) unsigned NOT NULL,
  `score` tinyint(3) unsigned NOT NULL,
  `dt` date NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=38924992 DEFAULT CHARSET=cp1251;


INSERT INTO `text_score_history` (`id`, `text_id`, `score`, `dt`) VALUES
(9829614, 64, 151, '2019-08-29'),
(10131139, 64, 151, '2019-09-01'),
(10433130, 64, 151, '2019-09-15'),
(10736614, 64, 151, '2019-09-21'),
(11040882, 64, 150, '2019-09-29'),
(11345906, 64, 151, '2019-10-03'),
(11651367, 64, 150, '2019-10-16'),
(11958033, 64, 150, '2019-10-24'),
(12265474, 64, 150, '2019-11-07'),
(12574423, 64, 150, '2019-11-18'),
(12884920, 64, 150, '2019-12-04'),
(13198030, 64, 150, '2019-12-13'),
(13512332, 64, 150, '2019-12-21'),
(13827796, 64, 150, '2019-12-25'),
(14142451, 64, 150, '2019-12-28'),
(14457456, 64, 150, '2019-12-29'),
(14772716, 64, 149, '2019-12-29'),
(15088073, 64, 149, '2020-01-12'),
(15405320, 64, 150, '2020-01-18'),
(15723714, 64, 150, '2020-02-12'),
(16046080, 64, 150, '2020-02-16'),
(16369442, 64, 150, '2020-02-22'),
(16693755, 64, 150, '2020-02-22'),
(17018317, 64, 150, '2020-03-01'),
(17344326, 64, 150, '2020-03-06'),
(17671398, 64, 149, '2020-03-07'),
(17998778, 64, 149, '2020-03-07'),
(18326273, 64, 148, '2020-03-15'),
(18655407, 64, 148, '2020-03-27'),
(18987677, 64, 147, '2020-03-29'),
(19320576, 64, 148, '2020-03-30'),
(19653863, 64, 147, '2020-04-01'),
(19987825, 64, 149, '2020-04-03'),
(20322566, 64, 149, '2020-04-03'),
(20657307, 64, 151, '2020-04-04'),
(20992439, 64, 150, '2020-04-05'),
(21328000, 64, 150, '2020-04-06'),
(21664050, 64, 150, '2020-04-07'),
(22000585, 64, 151, '2020-04-07'),
(22337120, 64, 151, '2020-05-01'),
(22682112, 64, 150, '2020-05-31'),
(23034364, 64, 150, '2020-05-31'),
(23386616, 64, 150, '2020-06-01'),
(23739124, 64, 150, '2020-06-02'),
(24092001, 64, 149, '2020-06-27'),
(24449795, 64, 147, '2020-07-20'),
(24811512, 64, 147, '2020-08-07'),
(25176081, 64, 147, '2020-08-07'),
(25540810, 64, 148, '2020-08-29'),
(25907929, 64, 148, '2020-09-27'),
(26277790, 64, 148, '2020-09-28'),
(26647651, 64, 148, '2020-09-28'),
(27017517, 64, 148, '2020-10-04'),
(27388090, 64, 148, '2020-10-04'),
(27758739, 64, 148, '2020-10-22'),
(28130834, 64, 150, '2020-10-22'),
(28502929, 64, 149, '2020-10-26'),
(28875546, 64, 147, '2020-11-17'),
(29250275, 64, 147, '2020-11-18'),
(29625465, 64, 147, '2020-12-22'),
(30004182, 64, 147, '2020-12-23'),
(30383159, 64, 127, '2020-12-28'),
(30762836, 64, 147, '2020-12-29'),
(31142805, 64, 127, '2021-01-05'),
(31523713, 64, 127, '2021-01-05'),
(31904621, 64, 146, '2021-01-06'),
(32285824, 64, 146, '2021-01-16'),
(32668794, 64, 146, '2021-01-17'),
(33052062, 64, 146, '2021-01-18'),
(33435656, 64, 146, '2021-01-19'),
(33819696, 64, 145, '2021-02-12'),
(34207954, 64, 145, '2021-02-13'),
(34596477, 64, 145, '2021-02-14'),
(34985248, 64, 145, '2021-02-22'),
(35375429, 64, 145, '2021-02-23'),
(35765988, 64, 145, '2021-02-24'),
(36156895, 64, 145, '2021-03-05'),
(36549463, 64, 145, '2021-03-06'),
(36942379, 64, 145, '2021-03-07'),
(37335695, 64, 147, '2021-03-26'),
(37732820, 64, 147, '2021-03-27'),
(38130218, 64, 147, '2021-03-27'),
(38527616, 64, 147, '2021-03-27');

ALTER TABLE `text_score_history`
ALTER TABLE `text_score_history`
MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=38924992;



1. Есть таблица с оценками статей (0...255).

Нужно оставить в таблице только те строки, где были изменения оценки score . Остальные строки удалить.

Было так:
Код: plaintext
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.
 (9829614, 64, 151, '2019-08-29'),
(10131139, 64, 151, '2019-09-01'),
(10433130, 64, 151, '2019-09-15'),
(10736614, 64, 151, '2019-09-21'),
(11040882, 64, 150, '2019-09-29'),
(11345906, 64, 151, '2019-10-03'),
(11651367, 64, 150, '2019-10-16'),
(11958033, 64, 150, '2019-10-24'),
(12265474, 64, 150, '2019-11-07'),
(12574423, 64, 150, '2019-11-18'),
(12884920, 64, 150, '2019-12-04'),
(13198030, 64, 150, '2019-12-13'),
(13512332, 64, 150, '2019-12-21'),
(13827796, 64, 150, '2019-12-25'),
(14142451, 64, 150, '2019-12-28'),
(14457456, 64, 150, '2019-12-29'),
(14772716, 64, 149, '2019-12-29'),
(15088073, 64, 149, '2020-01-12'),
(15405320, 64, 150, '2020-01-18'),
(15723714, 64, 150, '2020-02-12'),
(16046080, 64, 150, '2020-02-16'),
(16369442, 64, 150, '2020-02-22'),
(16693755, 64, 150, '2020-02-22'),
(17018317, 64, 150, '2020-03-01'),
(17344326, 64, 150, '2020-03-06'),
(17671398, 64, 149, '2020-03-07'),

Нужно так:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
 (9829614, 64,  151 , '2019-08-29'),
(11040882, 64,  150 , '2019-09-29'),
(11345906, 64,  151 , '2019-10-03'),
(11651367, 64,  150 , '2019-10-16'),
(14772716, 64,  149 , '2019-12-29'),
(15405320, 64,  150 , '2020-01-18'),
(17671398, 64,  149 , '2020-03-07'),


2. Я записываю текущие оценки в историю оценок text_score_history - следующим запросом:
INSERT INTO text_score_history (text_id, score, dt) SELECT id, score, NOW() FROM texts
(в таблице texts - около 400.000 записей)

Можно ли как-то быстро добавлять только изменившиеся оценки, т.е. добавлять когда:
text_score_history.score (для последней записи) <>texts.score
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.04.2021, 23:05
    #40062241
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удалить дублирующиеся строки, по условию
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
DELETE t2.*
FROM text_score_history t1
JOIN text_score_history t2 ON t1.text_id = t2.text_id
                          AND t1.score = t2.score
                          AND t1.dt < t2.dt
LEFT JOIN text_score_history t3 ON t1.text_id = t3.text_id
                               AND t1.dt < t3.dt
                               AND t3.dt < t2.dt
WHERE t3.id IS NULL;


fiddle

mynick
Было так

Вот какого [censored] это "было" (и соответственно последующее "нужно") не соответствует выложенному коду, а?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
13.04.2021, 23:07
    #40062242
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удалить дублирующиеся строки, по условию
mynick
Можно ли как-то быстро добавлять только изменившиеся оценки, т.е. добавлять когда:
text_score_history.score (для последней записи) <>texts.score
Можно. Рисуйте соответствующий триггер. Ну и используйте INSERT IGNORE.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.04.2021, 12:36
    #40062400
mynick
mynick
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удалить дублирующиеся строки, по условию
Akina

fiddle


Огромное спасибо. Пример реально работает.
На реальных данных (40 млн записей) - уже почти час выполняется запрос.


Akina
Вот какого [censored] это "было" (и соответственно последующее "нужно") не соответствует выложенному коду, а?

Пытался показать пример (что примерно есть и что нужно) и урезать данные, чтобы не раздувать пост простым копированием.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.04.2021, 14:33
    #40062448
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удалить дублирующиеся строки, по условию
mynick
На реальных данных (40 млн записей) - уже почти час выполняется запрос.
И ещё будет пыхтеть до-о-лго... А сколько соответствующий SELECT на реальных данных выполняется - не смотрел? А то при удалении такими комплексными запросами куда как разумнее сперва выгрести во временную таблицу те записи, которые надо удалить (ну, само собой, только их id) - и потом уже их прибить.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.04.2021, 14:53
    #40062457
mynick
mynick
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удалить дублирующиеся строки, по условию
Akina,
Т.к. выполнялось почти 3 часа, прибил процесс. И в надежде на увеличение скорости - добавил отдельные индексы для dt и score и запустил заново.

Попробую запустить select.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.04.2021, 15:09
    #40062461
mynick
mynick
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удалить дублирующиеся строки, по условию
mynick,

select по данным все еще выполняется (>15 минут).
А если сделать это 5 частями и добавив к WHERE t3.id IS NULL and (t1.text_id<=100000), (t1.text_id>100000 and t1.text_id<=200000 и т.д.), будет заметно быстрее?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.04.2021, 15:23
    #40062471
mynick
mynick
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удалить дублирующиеся строки, по условию
с WHERE t3.id IS NULL and (t1.text_id<=10000) - выполняется 4 минуты.

Единственное, заметил, что если одинаковые dt и score, пример:
(11345906, 64, 150 , ' 2019-10-03 ')
(11651367, 64, 150 , ' 2019-10-03 ')
данные не удаляются.

Я этот момент не учел в коде первого поста :(
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.04.2021, 15:42
    #40062480
mynick
mynick
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удалить дублирующиеся строки, по условию
В https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=54f088c2b84f564fd39cd28e820c67ed

После:
(11040882, 64, 150, '2019-09-29'),
Добавил строку:
(11040883, 64, 150, '2019-09-29'),

Видно сейчас в результатах
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.04.2021, 17:20
    #40062524
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удалить дублирующиеся строки, по условию
Значит, надо сравнивать не dt, а соотв. id. Всего-то.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.04.2021, 21:19
    #40062591
mynick
mynick
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Удалить дублирующиеся строки, по условию
Akina, огромное спасибо. Все работает.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Удалить дублирующиеся строки, по условию / 11 сообщений из 11, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 267ms
Закрыть
start [/forum/topic.php?fid=47&tablet=1&tid=1828107]:
 0ms
get settings:
 9ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 151ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 53ms
get tp. blocked users:
 2ms
others: 16ms
total:  267ms
x
x
Закрыть


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