Гость
Целевая тема:
Создать новую тему:
Автор:
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих? / 25 сообщений из 35, страница 1 из 2
17.09.2016, 18:50
    #39310862
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
Привет! В таблице MySql хранятся закрашенные области (серый цвет), как координаты левого верхнего угла и правого нижнего, т.е. ЧЕТЫРЕ числа (х1,у1,х2,у2) и поле статус=ноль - нет закраски, статус=единица - закрашено.. Как запросом в базу MySql создать новую закрашенную область, если входящие условия - размеры? Т.е. условия такие, например:

п.1 начиная слева сверху найти свободную область размером 1х1 и соответственно записать в эту ячейку статус=1
п.2 начиная слева сверху найти свободную область размером 2х2 и соответственно записать в эти ЧЕТЫРЕ ячейки статус=1

То есть для п.1 ответ должен быть 0,0,1,1 (голубой квадратик) для п.2 = 6,0,8,2 (оранжевый квадратик)

Нюанс в том, что размер сетки 2000х2000, т.е. всего 4млн квадратиков.
Пока вижу два решения:
1. каким-то сложным запросом делать INSERT внутри таблицы используя SET MIN и тд, возможно зацикливание?
2. использовать доп. таблицу (приложил пример на втором рисунке), где перечислены все 4 млн. комбинации, через left join искать не совпадающие ячейки?. но тут коллизия - пока ищу по одной таблице, во вторую уже кто-то пишет...
Коллеги, подскажите, куда хотя бы двигаться в размышлениях?

Ниже пример SQL который хранит эти закрашенные серым области.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
CREATE TABLE `m_items` (
  `id` int(11) NOT NULL,
  `x1` smallint(4) NOT NULL,
  `y1` smallint(4) NOT NULL,
  `x2` smallint(4) NOT NULL,
  `y2` smallint(4) NOT NULL,
`status` tinyint(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `m_items` (`id`, `x1`, `y1`, `x2`, `y2`) VALUES
(1, 3, 0, 6, 4),
(2, 8, 0, 9, 2),
(3, 0, 1, 2, 2),
(4, 0, 3, 2, 5),
(5, 7, 3, 9, 7),
(6, 1, 6, 6, 7),
(7, 1, 8, 2, 10);
...
Рейтинг: 0 / 0
17.09.2016, 19:24
    #39310863
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
Олег Котейкини поле статус=ноль - нет закраски, статус=единица - закрашено.Долго искал в примере данных - не нашёл. Тыкни пальцем, что ли...

Олег Котейкинзапросом в базу MySql создать новую закрашенную область, если входящие условия - размерыНе вижу проблем. Конвертируй свой [censored] исходный формат данных во вменяемый Row-Col-Val - и запрос получится почти тривиальным.

Олег Котейкинразмер сетки 2000х2000
И так понятно, что выполнение запроса будет тяжёлым.
...
Рейтинг: 0 / 0
17.09.2016, 20:16
    #39310871
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
Поле статус озвучил для понимания. По сути область закрашена уже если есть строка с координатами - именно такая область и красится по координатам. Т.е. по входящим данным в виде двух чисел (размеры ШхВ) нужен INSERT ЧЕТЫРЕХ значений, при этом эти значения в комплексе не должны пересечься с уже существующими.
...
Рейтинг: 0 / 0
28.09.2016, 09:40
    #39316702
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
можно выбрать другую структуру хранения, может тогда проще будет составить скрипт, коллеги?
...
Рейтинг: 0 / 0
28.09.2016, 11:13
    #39316760
bochkov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
вот запрос, который создает возможные области для закраски
из него уже инсерт сделать можно
для него нужна опорная таблица crd, в которой колонка n содержит значения возможных координат
в вашем случае 2001 строка от 0 до 2000,
для блокировки клеток чтоб в них не красили
придется делать таблицу для всех клеток
state для не закрашенных = null
это для того чтоб не покрашенные клетки можно было блокировать с помощью SELECT FOR UPDATE
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
SELECT
  x1_crd.n AS x1,
  y1_crd.n AS y1,
  x2_crd.n AS x2,
  y2_crd.n AS y2
FROM crd x1_crd, crd x2_crd, crd y1_crd, crd y2_crd
WHERE x2_crd.n - x1_crd.n = ${size} AND y2_crd.n - y1_crd.n = ${size}
AND NOT EXISTS(SELECT m.id FROM m_items m WHERE m.x1 < x2_crd.n AND m.x2 > x1_crd.n AND m.y1 < y2_crd.n AND m.y2 > y1_crd.n LIMIT 1)
...
Рейтинг: 0 / 0
28.09.2016, 11:18
    #39316765
bochkov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
пожалуй в таблице 2000х2000 state не нужен только координаты клеток для блокировки
...
Рейтинг: 0 / 0
28.09.2016, 14:10
    #39316932
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
bochkov,
ого, спасибо, щас потестирую!

опорная таблица crd - там должно быть 2000 строк или все-таки 2000*2000=4000000 строк? т.еб ВСЕ возможные сочетания координат?
...
Рейтинг: 0 / 0
28.09.2016, 14:21
    #39316941
bochkov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
Олег Котейкинbochkov,
ого, спасибо, щас потестирую!

опорная таблица crd - там должно быть 2000 строк или все-таки 2000*2000=4000000 строк? т.еб ВСЕ возможные сочетания координат?
Должно быть 2001 строка,
в запросе таблица сама на себя умножается
...
Рейтинг: 0 / 0
28.09.2016, 14:42
    #39316970
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
bochkov,

что то уже минут 5 висит... ${size} - это искомый размер в виде числа?
...
Рейтинг: 0 / 0
28.09.2016, 14:46
    #39316975
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
bochkov,

а если не кубик а прямоугольник искать?
...
Рейтинг: 0 / 0
28.09.2016, 14:51
    #39316984
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
bochkov,

хотя вот вывело :
Отображение строк 0 - 24 (972196 всего, Запрос занял 0.8070 сек.)
Типа что-то нашло, т.е. запрос 0,8 сек? а что висело долго 5 минут?
...
Рейтинг: 0 / 0
28.09.2016, 15:03
    #39316998
bochkov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
первый size - ширина, второй - высота,
первый раз запрос- реальное время,
второй запрос - из кеша
чтоб сократить время надо создать индексы
по x1 y1 x2 y2 именно в таком порядке,
возможно primary key по полю n поможет
...
Рейтинг: 0 / 0
28.09.2016, 15:07
    #39317002
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
bochkov,

насчет size я основательно тормознул!!))) погода... разобрался!!!
по n индекс создал - теперь 0,5 сек

а по поводу "...по x1 y1 x2 y2 именно в таком порядке..." - т.е. составной индекс нужен? в той таблице уже есть индекс по id - первичный автоинкремент - он тут не помогает?

а вообще получилось очень отличный вариант, соседи со стаковерфлоу неделю вместе думали и такого скоростного не получилось создать! Премного Вам благодарен!

Щас индексы составить попробую и отпишусь тут
...
Рейтинг: 0 / 0
28.09.2016, 15:12
    #39317006
bochkov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
очень много извлекать ему приходится
добавь в запрос limit 1 по задаче ведь нужна одна доступная область
а чтобы поиск был слева или справа order by x1_crd.n
desc-справа налево , asc слева направо
order by y1_crd.n поиск сверху вниз
...
Рейтинг: 0 / 0
28.09.2016, 15:20
    #39317017
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
bochkov,

ну да, limit 1 поставил, он ищет столько же 0,5сек, вывод на экран тормозил просто когда без лимита было.
а без order by выводит крайнее слева сверху, как и требуется! точно нужен order by ? Сейчас с ним вот так получилось Отображение строк 0 - 0 (1 всего, Запрос занял 117.4495 сек.). Получается если много результатов , по сути щас миллилон, то сначала сортирует этот миллион а потом лимит?
...
Рейтинг: 0 / 0
28.09.2016, 15:22
    #39317025
bochkov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
могут ускорить запрос 2 составных ключа
x1 x2 и y1 y2
чтобы реальное время смотреть
надо добавить модификатор к селектор
sql_no_cache так по моему называется
не удобно с телефона проверять
...
Рейтинг: 0 / 0
28.09.2016, 15:26
    #39317033
bochkov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
order by нужен
без него mysql первое попавшие
вываливает
...
Рейтинг: 0 / 0
28.09.2016, 15:40
    #39317055
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
bochkov,

с сортировкой по x и y вообще сервер слетел)) #2006 - MySQL server has gone away
составной индекс добавил...
...
Рейтинг: 0 / 0
28.09.2016, 15:52
    #39317074
bochkov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
ну тут уж не знаю чем помочь
...
Рейтинг: 0 / 0
28.09.2016, 16:29
    #39317117
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
Чисто поржать...
Код: 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.
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.
mysql> -- Исходные данные
mysql> CREATE TABLE `m` (
    ->   `id` INT,
    ->   `x1` INT,
    ->   `y1` INT,
    ->   `x2` INT,
    ->   `y2` INT
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO `m` (`id`, `x1`, `y1`, `x2`, `y2`) VALUES
    -> (1, 3, 0, 6, 4),
    -> (2, 8, 0, 9, 2),
    -> (3, 0, 1, 2, 2),
    -> (4, 0, 3, 2, 5),
    -> (5, 7, 3, 9, 7),
    -> (6, 1, 6, 6, 7),
    -> (7, 1, 8, 2, 10);
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql>
mysql> -- Промежуточная таблица (но можно было добавить и поле в исходную)
mysql> CREATE TABLE p(id INT, rng GEOMETRY);
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO p (id,rng) SELECT id,ST_GeomFromText(CONCAT('Polygon((',x1,' ',y1,',',x1,' ',y2,',',x2,' ',y2,',',x2,' ',y1,',',x1,' ',y1,'))')) FROM m;
Query OK, 7 rows affected (0.00 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql>
mysql> -- Временная таблица, в реальном запросе не создаётся, используется как подзапрос
mysql> CREATE TEMPORARY TABLE x(x1 int, y1 int, p GEOMETRY);
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO x(x1, y1, p)
    -> SELECT x1,y1, ST_GeomFromText(CONCAT('Polygon((',x1+0.5,' ',y1+0.5,',',x1+0.5,' ',y1+1.5,',',x1+1.5,' ',y1+1.5,',',x1+1.5,' ',y1+0.5,',',x1+0.5,' ',y1+0.5,'))'))
    -> FROM (SELECT 0 x1 UNION ALL
    ->    SELECT 1 UNION ALL
    ->    SELECT 2 UNION ALL
    ->    SELECT 3 UNION ALL
    ->    SELECT 4 UNION ALL
    ->    SELECT 5 UNION ALL
    ->    SELECT 6 UNION ALL
    ->    SELECT 7 UNION ALL
    ->    SELECT 8
    ->    ) t1,
    ->   (SELECT 0 y1 UNION ALL
    ->    SELECT 1 UNION ALL
    ->    SELECT 2 UNION ALL
    ->    SELECT 3 UNION ALL
    ->    SELECT 4 UNION ALL
    ->    SELECT 5 UNION ALL
    ->    SELECT 6 UNION ALL
    ->    SELECT 7 UNION ALL
    ->    SELECT 8
    ->    ) t2
    -> ;
Query OK, 81 rows affected (0.01 sec)
Records: 81  Duplicates: 0  Warnings: 0

mysql>
mysql> -- Ну и получение всех мест, где помещается 2х2. Ежели много, есть LIMIT
mysql> SELECT x.x1,x.y1
    -> FROM p,x
    -> GROUP BY x.x1,x.y1
    -> HAVING SUM(MBRIntersects(p,rng)) = 0
    -> ;
+------+------+
| x1   | y1   |
+------+------+
|    2 |    4 |
|    2 |    7 |
|    2 |    8 |
|    3 |    4 |
|    3 |    7 |
|    3 |    8 |
|    4 |    4 |
|    4 |    7 |
|    4 |    8 |
|    5 |    4 |
|    5 |    7 |
|    5 |    8 |
|    6 |    0 |
|    6 |    1 |
|    6 |    7 |
|    6 |    8 |
|    7 |    7 |
|    7 |    8 |
|    8 |    7 |
|    8 |    8 |
+------+------+
20 rows in set (0.00 sec)
...
Рейтинг: 0 / 0
28.09.2016, 16:39
    #39317136
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
bochkov,

118 сек, и составной по 4 полям и два составных x1 x1 и y1 y2....

жаль-жаль, думал что все-таки выход есть - ан нет))

а без ORDER BY сервер выдает первое попавшее, но согласно индекса?
...
Рейтинг: 0 / 0
28.09.2016, 18:27
    #39317240
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
Akina,

общий размер сетки 2000х2000, где это видно в вашем примере? у вас для варианта сетки 10х10. на 10х10 можно и вручную искать, сидя рядом с сервером и получая от него запрос...))
...
Рейтинг: 0 / 0
28.09.2016, 18:33
    #39317243
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
Олег Котейкин , ты способен понять разницу между идеей и реализацией?
...
Рейтинг: 0 / 0
28.09.2016, 19:28
    #39317281
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
Akina,
наверное нет, простите старикашку. понять-простить.
...
Рейтинг: 0 / 0
28.09.2016, 22:23
    #39317351
bochkov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих?
таблица 2000x2000 эта таблица на 4млн записей
например назовём её grid
колонки x1 y1 x2 y2
таблица содержит описание клеток
в неё никто никогда не пишет
только читают с блокировкой
перед тем как вставить данные в m_items
и таким образом
1 получаем возможную для закраски область
2 читаем клетки for update из grid, в соответствии с этой областью
3 обновляем m_items
...
Рейтинг: 0 / 0
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Как сделать INSERT с значениями, которых нет в интервалах среди уже существующих? / 25 сообщений из 35, страница 1 из 2
Найденые пользователи ...
Разблокировать пользователей ...
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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