| Tertium |
| Отправлено: 04.06.2005, 18:44 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
вот какая задачка. Всю башку сломал ибо в ней (башке) очень мало данных по SQL:)
есть таблица в MSSQL:
| CODE |
CREATE TABLE Interim_Stats (
id int IDENTITY (1, 1) NOT NULL ,
machine_id int NULL ,
currentIn int NULL ,
currentOut int NULL ,
last_query datetime NULL
)
|
это данные, скидываемые со всех машин сети, у каждой из которых есть machine_id и некоторые значения счётчиков in и out. Это происходит периодически каждую выбираемую единицу времени.
Таким образом, в базе в каждой строке могут быть разные и неуникальные значения, за исключением
Теперь вопрос:
надо одним запросом вынуть последние данные по каждой машине (уникальному machine_id), если известно, что записей с одним и тем же machine_id — полно.
Я решал так.
Запросом
| CODE |
select (machine_id, currentIn), max(last_query) from interim_stats group by machine_id
|
получаем кортеж, состоящий из действительно максимальных дат по каждому из machine_id.
Мне это в приливе идеализма показалось кусками строк, на которых для каждого machine_id максимален last_query. И захотелось получить _остальную_ часть этих строк:)
Но это, очевидно, не так. Так что решение зашло в тупик.
А хочется именно одним запросом, потому что иначе — это запрос для каждого machine_id, которых — туева хуча.
|
|
| ** avtoritet |
| Отправлено: 05.06.2005, 00:29 |
|
Не зарегистрирован
|
А если так:
select TOP1 (machine_id, currentIn), max(last_query) from interim_stats order by machine_id DESC;
Попробуй, и получишь, как я думаю, одну самую последнюю запись. |
|
| ** avtoritet |
| Отправлено: 05.06.2005, 01:12 |
|
Не зарегистрирован
|
лИЧНО мне вопрос не совсем понятен! Можно как-нибудь иначе сформирвать вопрос? Что занчить остальная часть этих строк? |
|
| Tertium |
| Отправлено: 05.06.2005, 13:53 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
остальная часть строк — это currentIn, где machine_id и last_query — те что получены.
Кстати, запрос когда писал, ошибся, так сказать выдал желаемое за действительное  :
| CODE |
select machine_id, max(last_query) from interim_stats group by machine_id
|
вот так работает, но выдаёт только последние даты по каждому из идов.
Надо же вот что:
- выделить группы по machine_id
- внутри каждой найти строку, старшую по last_query
- вывести для каждой группы найденную строку целиком
таким образом, получатся ПОСЛЕДНИЕ ДАННЫЕ ДЛЯ КАЖДОЙ МАШИНЫ!!
пока делается так. находится приведённым запросом пары ид-дата,
проходя по полученному ассоциативному массиву, затем ищются в таблице строки у которых ид=полученному[i], дата=полученной[i]. Так получается, что запрос, выполняющийся в цикле, максимально разгружен.
Первоначальный вариант был такой. Первым запросом находим distinct(machine_id) as mid, затем по каждому пишем такой запросище:
| CODE |
use gambling select currentIn,currentOut from stats where machine_id=mid[i] and last_query=(select max(last_query) from stats where machine_id=mid[i])
|
Тоже работает, но в три-четыре раза медленнее (запрос выполняемый в цикле силшком загружен — вложенный запрос, да ещё и с max).
вот. Пока я научился решать задачу в два прохода. Двумя спсобами даже. А вот одним запросом не могу.
Кстати, твой запрос сервер материт. Да и если б не материл, не то это
|
|
| ** avtoritet |
| Отправлено: 06.06.2005, 02:29 |
|
Не зарегистрирован
|
Слушай, будь добор, пришли саму базу данных, только если она не тяжелая ( ну не более 1 м)?
avtoritet@fromru.com |
|
| full_lamer |
| Отправлено: 06.06.2005, 08:12 |
|
Машинист паровоза
Группа: Участник
Сообщений: 225
|
2Tertium
я конечно дико извиняюсь, но что то я не пойму чего Вам нужно от базы? честно говоря я не совсем понял... нужно получить именно последнее обнавление для каждой машины, или некоторое последнее n-нное количество записей по каждой машине, или просто отделить записи по каждой машине друг от друга?
|
|
| AVC |
| Отправлено: 06.06.2005, 09:44 |
|
Ветеран
Группа: Модератор
Сообщений: 1583
|
Вариант, проверен на Oracle, старался соблюдать стандарт синтаксиса SQL
(просто в Oracle такое делается много проще и эффективнее)
| CODE |
Select *
From avc.Int_S
Where ID in (
Select Max (i1.id)
From
avc.Int_S i1
,(Select machine_id, max(last_query) as max_last_query
From avc.Int_S
Group by machine_id
) t1
Where 1 = 1
and t1.machine_id = i1.machine_id
and t1.max_last_query = i1.last_query
Group by i1.machine_id, i1.last_query
)
|
Отбирает ровно по одной строке для каждой machine_id с максимальным last_query и ID (id это для "ровно одной строки")
Если вы гарантируете что для одного machine_id нет записей с одинаковым last_query то от верхнего запроса можно отказаться
Отредактировано AVC — 06/06/2005, 08:52 |
|
| Tertium |
| Отправлено: 06.06.2005, 13:35 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
2 avtoritet:
сама прога тяжелее метра и намного, ибо БД это только её малая часть. а структура БД — простенькая:
| SQL | create database Gambling
go
use Gambling
go
CREATE TABLE Automats (
machine_id int primary key,
creation datetime NULL ,
change datetime NULL ,
last_query datetime NULL ,
club_creator_id int NULL ,
game_id int NULL ,
box_name varchar (255) COLLATE Cyrillic_General_CI_AS NULL ,
prog_ver varchar (50) COLLATE Cyrillic_General_CI_AS NULL ,
inventory varchar (50) COLLATE Cyrillic_General_CI_AS NULL ,
type tinyint NULL ,
absolute_val bit NULL ,
comments varchar (255) COLLATE Cyrillic_General_CI_AS NULL ,
initIn bigint NULL ,
initOut bigint NULL
)
GO
CREATE TABLE Games (
game_id int IDENTITY (1, 1) primary key,
name char (255) COLLATE Cyrillic_General_CI_AS NOT NULL ,
description char (255) COLLATE Cyrillic_General_CI_AS NULL
)
GO
CREATE TABLE Stats (
id int IDENTITY (1, 1) primary key,
machine_id bigint NULL ,
currentIn bigint NULL ,
currentOut bigint NULL ,
last_query datetime NULL
)
GO
|
2AVS:
двойное спасибо. будем разбираться
|
|
| ** avtoritet |
| Отправлено: 06.06.2005, 13:49 |
|
Не зарегистрирован
|
Как раз и нужно эту самую маленкую часть — не программу а БД. Так меньше возни будет! |
|
| Tertium |
| Отправлено: 06.06.2005, 16:02 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
2 AVS:
работает, классный запрос. Правда, пришлось ещё поддерживать mySQL, а уж там-то и запросов вложенных нет... так что удовольствие получилось чисто теоретическое.
Но запрос!.. Я уж успел и забыть про вложенные селекты... Спасибо.
Отредактировано Tertium — 06/06/2005, 16:02
|
|
| Gedeon |
| Отправлено: 06.06.2005, 16:55 |
|
Ветеран
Группа: Модератор
Сообщений: 1742
|
2 Tertium
Вы чего безобразничаете и AVC AVSом обзываете?
|
|
| AVC |
| Отправлено: 06.06.2005, 17:04 |
|
Ветеран
Группа: Модератор
Сообщений: 1583
|
| QUOTE |
пришлось ещё поддерживать mySQL, а уж там-то и запросов вложенных нет
|
Действительно? А у меня получилось и именно в таком синтаксисе.
А то еще можно написать так
| CODE |
Select
i21.*
From
avc.Int_S i21
,(Select Max (i1.id) as max_id
From
avc.Int_S i1
,(Select machine_id, max(last_query) as max_last_query
From avc.Int_S
Group by machine_id
) t1
Where 1 = 1
and t1.machine_id = i1.machine_id
and t1.max_last_query = i1.last_query
Group by i1.machine_id, i1.last_query
)t22
Where i21.ID = t22.max_id
|
Удачи. И как заметил Gedeon мой ник AVC. |
|
| Tertium |
| Отправлено: 08.06.2005, 03:33 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
2AVC
Пардон за обзывательства
И нет — в mySQL ниже 4.1 то, что у вас написано — преступление:
| SQL | select ... from table1, (select ... from ...) as t1
|
2all
После некоторой туевой хучи времени, убитой на вникание в запрос, написание тестового софта и скачивание mySQL 4.1 с поддержкой вложенных запросов (38 M!!!), привожу результаты опытов и конечный запрос.
Предложенный выше запрос всем хорош, кроме скорости, а именно — он выполняется чуть дольше заполнения базы (по крайней мере на 1000 позициях).
Сгарантировал уникальность пар machine_id-last_query, как опять же было предложено. Потом выяснилось, что надо не только последний вход выход, но и данные об устройстве, лежащие в отдельной таблице с machine_id в кач-ве неинкрементного ключа. И вот что получилось:
| SQL | Select a1.*, i1.currentIn, i1.currentOut from stats as i1,
Automats as a1,
(Select machine_id, max(last_query) as max_last_query From stats Group by machine_id) as t1
where (i1.machine_id=t1.machine_id) and i1.last_query=t1.max_last_query) and (a1.machine_id=t1.machine_id)
|
Эта красота выполняется (на 1000 записях при 100 допускаемых уникальных machine_id) около 30-40 мс причём из одной или из двух таблиц (stats и automats) почти неважно. Способ, который я использовал ранее (двухэтапный) — 344-360 мс. Полный же запрос с учётом неуникальности пар machine_id-last_query (исходный предложенный AVC) — 18540 -18766 !!
Завтра будет генеральный кипеть за задержку, но зато я доволен. Копать нужно до конца Даже когда это не необходимо
Ещё раз хоца скать агромное спасибо AVC. У меня в рез-те даже угол зрения на SQL другой стал Не так часто приходится с ним сталкиваться.
Это было как открытие для себя регулярных выражений лет в 18
Отредактировано Tertium — 08/06/2005, 03:36
|
|
| Tertium |
| Отправлено: 08.06.2005, 20:39 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
И знаете, что самое обидное? Эволюция проекта пришла к тому, что данные In и OUT постоянно пишутся в таблицу с данными машин. Где каждый machine_id — уникальный. Где всё тихо и просто, и нет десятитысячных позиций.
Где хватает запроса:
| SQL | select * from Automats order by machine_id !!!!!!!
|
Абыдно, да...
Ваше здоровье!..
Отредактировано Tertium — 08/06/2005, 20:40
|
|
|
