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

Откуда: Yekaterinburg
Сообщений: 660
Дано: скада система, СУБД внутренняя нестандартная, есть доступ по ODBC, в ней таблицы.
CDBPOINT - описание параметров
CDBHISTORIC - ретроспектива

CDBPOINT.id -id параметра
CDBPOINT.Fullname - имя параметра

CDBHISTORIC.id -id параметра, где CDBPOINT.id=CDBHISTORIC.id
CDBHISTORIC.time - время параметра
CDBHISTORIC.value - значение параметра

Задача: составить запрос, где из таблицы CDBHISTORIC брать параметры со всеми возможными id за определенный период, но каждый параметр должен быть представлен один раз с наиболее "свежей" датой из периода.
SELECT TOP (1500) CDBHISTORIC.value,CDBPOINT.Fullname,CDBHISTORIC.RecordTime AS '~Time' 
       FROM CDBHISTORIC,CDBPOINT 
       WHERE CDBHISTORIC.id=CDBPOINT.id 
                          AND ('~Time'>TIMESTAMP '2018-12-03 01:00:00') AND  ('~Time'<TIMESTAMP '2018-12-03 01:00:30')


Здесь она мне все выводит за 30 сек, даже если по одному параметру было скажем 10 точек, а мне надо только одну по каждому параметру, самую близкую к концу периода. Знаю, что надо использовать MAX и GROUP BY, но почему-то ошибки получаются.
4 дек 18, 14:04    [21752905]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос  [new]
alex55555
Member

Откуда:
Сообщений: 1323
Mari.P
Знаю, что надо использовать MAX и GROUP BY, но почему-то ошибки получаются.

Знаю, что не умею, но почему мне денег никто не даёт? Ну почему?

Девушка, вы зачем изложили много текста, и при этом не сообщили, какой запрос даёт ошибки? И какие ошибки? Или вы опять где-то слышали, что здесь живут телепаты?

А что бы вам готовый запрос кто-то дал, нужно знать, что там у вас за база, ибо синтаксис у неё нестандартный.
4 дек 18, 14:35    [21752962]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос  [new]
Mari.P
Member

Откуда: Yekaterinburg
Сообщений: 660
SCADA система ClearSCADA 2015 R2, СУБД у нее внутренняя, сильно похожа на обычные бинарные файлы, но драйвер ODBC есть, и c SQL запросам работать можно. Насчет денег - это вы зря, я линейный инженер и мне надо сделать выборку для оценки режима сети, если не составлю запрос - придется руками тащить по одному параметру.
4 дек 18, 14:41    [21752974]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 18308
Mari.P
каждый параметр должен быть представлен один раз с наиболее "свежей" датой из периода.
А что делать, если имеется две записи с одинаковым "самым свежим" штампом времени? и не надо лепетать, что "такого не может быть", а в ууникальный индекс я не верю.

Mari.P
в ней таблицы
Показывайте DDL таблиц, а не "сочинение по мотивам".

Mari.P
Знаю, что надо использовать MAX и GROUP BY
Ещё забыли слово "подзапрос". А заодно сказать, а поддерживает ли эта самая нестандартная СУБД подзапросы... а заодно, чтобы два раза не бегать, и CTE.
4 дек 18, 14:43    [21752983]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос  [new]
Dima T
Member

Откуда:
Сообщений: 13032
Mari.P
за 30 сек

Для ускорения надо индексы по ID для CDBPOINT и по (ID,RecordTime) для CDBHISTORIC
4 дек 18, 14:56    [21753017]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос  [new]
Mari.P
Member

Откуда: Yekaterinburg
Сообщений: 660
Akina
Показывайте DDL таблиц, а не "сочинение по мотивам".

Прошу прощения, а что такое DDL и как его показать?
4 дек 18, 15:18    [21753071]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос  [new]
Dima T
Member

Откуда:
Сообщений: 13032
Если подзапросы понимает, то так
SELECT TOP (1500) CDBHISTORIC.value,CDBPOINT.Fullname,CDBHISTORIC.RecordTime AS '~Time' 
       FROM CDBPOINT, CDBHISTORIC, (select CDBHISTORIC.id, max(CDBHISTORIC.RecordTime) as RecordTime 
                                       from CDBHISTORIC 
                                       where (CDBHISTORIC.RecordTime>TIMESTAMP '2018-12-03 01:00:00') 
                                            AND  (CDBHISTORIC.RecordTime<TIMESTAMP '2018-12-03 01:00:30')
                                       group by CDBHISTORIC.id
                                   ) CHMAX
       WHERE CDBHISTORIC.id=CDBPOINT.id 
                   AND CDBHISTORIC.id=CHMAX.id and CDBHISTORIC.RecordTime = CHMAX.RecordTime


Как уже выше подмечено - может быть задвоение, если в CDBHISTORIC для одного и того же ID несколько записей с одинаковым максимальным временем в RecordTime.
4 дек 18, 15:56    [21753185]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос  [new]
exp98
Member

Откуда:
Сообщений: 1492
Mari.P, я тоже не знаток скады, просто сомнение:
SELECT TOP (1500) .... без ORDER BY оно точно возьмёт в нужном порядке?
4 дек 18, 18:15    [21753520]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос  [new]
alex55555
Member

Откуда:
Сообщений: 1323
Mari.P
Насчет денег - это вы зря, я линейный инженер и мне надо сделать выборку для оценки режима сети, если не составлю запрос - придется руками тащить по одному параметру.

Это была попытка показать по аналогии, что "почему-то не получается" есть как минимум очень малоинформативное пояснение.

Вы знаете, что такое вложенные запросы? Вы сумеете понять суть примера, приведённого выше участником Dima T? Эти вопросы возникают в следствии ваших "простых" пояснений, потому что очень похоже, что вам помимо примера запроса ещё много чего нужно разжевать.
5 дек 18, 15:29    [21754521]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос  [new]
Mari.P
Member

Откуда: Yekaterinburg
Сообщений: 660
Спасибо вам огромное!
По синтаксису только так прошло, но выдает QueryTimeOut, зациклился может быть где-то?
SELECT TOP (10000) CDBHISTORIC.ValueAsReal,CDBPOINT.FullName,CDBHISTORIC.RecordTime
  FROM CDBHISTORIC,CDBPOINT,(SELECT CDBHISTORIC.ID,MAX(CDBHISTORIC.RecordTime)  AS "~RecTime" 
FROM CDBHISTORIC,CDBPOINT WHERE CDBHISTORIC.ID=CDBPOINT.ID 
AND CDBHISTORIC.RecordTime>TIMESTAMP '2018-12-03 01:00:00'  
AND CDBHISTORIC.RecordTime<TIMESTAMP '2018-12-03 01:00:10'  GROUP BY CDBHISTORIC.ID)CHMAX 
WHERE CDBHISTORIC.ID=CDBPOINT.ID AND  CDBHISTORIC.ID=CHMAX.ID AND CDBHISTORIC.RecordTime="~RecTime"
6 дек 18, 10:09    [21755406]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос  [new]
Mari.P
Member

Откуда: Yekaterinburg
Сообщений: 660
Почему-то вот такой запрос синтаксически признается корректным
SELECT TOP(100) CDBHISTORIC.ID,MAX(CDBHISTORIC.RecordTime)  AS "~RecTime" 
FROM CDBHISTORIC,CDBPOINT WHERE 
CDBHISTORIC.ID=CDBPOINT.ID AND CDBHISTORIC.RecordTime>TIMESTAMP '2018-12-03 01:00:00'  
AND CDBHISTORIC.RecordTime<TIMESTAMP '2018-12-03 01:00:30'  GROUP BY CDBHISTORIC.ID


А вот такой нет
SELECT TOP(100) CDBHISTORIC.ID,MAX(CDBHISTORIC.RecordTime)  AS "~RecTime" 
FROM CDBHISTORIC WHERE CDBHISTORIC.RecordTime>TIMESTAMP '2018-12-03 01:00:00'  
AND CDBHISTORIC.RecordTime<TIMESTAMP '2018-12-03 01:00:30'  GROUP BY CDBHISTORIC.ID


выдает ошибку. Хотя почему? Вообще понять не могу.

К сообщению приложен файл. Размер - 21Kb
6 дек 18, 11:11    [21755523]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос  [new]
alex55555
Member

Откуда:
Сообщений: 1323
Mari.P
выдает ошибку. Хотя почему? Вообще понять не могу.

Потому что SQL вы не понимаете. А надо понимать. Ну да опытным путём уже выяснили, что ваша шнайдер.бд понимает вложенные запросы, а значит тот первый пример от Dima T может помочь, но что за ошибку вам выдаёт этот пример - вы не говорите. То есть вы даже не понимаете, а что вам вообще нужно рассказать для получения подсказок от других.

Вам дали запрос, вот по нему вы и должны отчитаться - как работает и какие ошибки. И без сочинений вкруг да около, а строго полный текст ошибки и указание на конкретный запрос. Без этого ваши отстранённые рассказы про эксперименты с другим запросом никому не интересны.

Хотя про конкретную ошибку всё понятно - вы делаете группировку, которая содержит внутри строго по одной строке и абсолютно ничем не ограничена, то есть предлагает вывести все данные, которых может быть гигабайты, на что вас система и посылает.
6 дек 18, 14:48    [21755867]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос  [new]
alex55555
Member

Откуда:
Сообщений: 1323
Хотя ещё такой момент - когда дают примеры запросов, то участники их форматируют. Это нужно для лучшего понимания. Так вот вы, Мария, форматирование удаляете, чем делаете запрос неузнаваемым (то есть нужно долго разбираться и сличать, что бы понять, он это или не он). Если бы вы сохраняли исходное форматирование - тогда было бы много проще. А ещё лучше сказать - я взяла вот этот запрос (указать какой) и сделала вот это, получила вот это. Не сумбурно "ой, я что-то сделала, а оно не сработало!", а конкретно, подробно, внимательно.
6 дек 18, 14:52    [21755872]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос  [new]
Mari.P
Member

Откуда: Yekaterinburg
Сообщений: 660
alex55555
пример от Dima T может помочь

Пример от DimaT выдает ту же самую ошибку. Если вы посмотрите, то дело как раз во вложенном запросе, который у DimaT
Я его просто отдельным запросом записала и пытаюсь разобраться почему без
CDBHISTORIC.ID=CDBPOINT.ID

GROUP BY работать не хочет.
6 дек 18, 15:02    [21755893]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос  [new]
Dima T
Member

Откуда:
Сообщений: 13032
Mari.P
Спасибо вам огромное!
По синтаксису только так прошло, но выдает QueryTimeOut, зациклился может быть где-то?

Нет. Не зациклился. Просто очень долго выполняется.
Наверно alex55555 прав, надо мат.часть поизучать. Я чуть выше 21753017 про индексы упоминал. Слово "индекс" о чем-то говорит?
План выполнения запроса знаешь как получить? Если знаешь, то показывай.
6 дек 18, 19:45    [21756312]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос  [new]
KreatorXXI
Member

Откуда: Москва
Сообщений: 578
Mari.P,

а Вы из Экселя в базу стучитесь? А есть у них "студия" какая-нибудь для работы с БД?
7 дек 18, 12:12    [21756847]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос  [new]
alex55555
Member

Откуда:
Сообщений: 1323
Mari.P
alex55555
пример от Dima T может помочь

Пример от DimaT выдает ту же самую ошибку. Если вы посмотрите, то дело как раз во вложенном запросе, который у DimaT
Я его просто отдельным запросом записала и пытаюсь разобраться почему без
CDBHISTORIC.ID=CDBPOINT.ID

GROUP BY работать не хочет.

Вы не только указанную часть удалили, но и таблицу, для этой части нужную.

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

Мария, научитесь сначала делать простой запрос с ограничением по времени, например - одна секунда. Когда доложите, что запрос вам выдаёт данные именно за одну указанную вами секунду, тогда можно продолжить пытаться дистанционно понять ваши проблемы.

А вообще, даже не смотря на то, что слово инженер ныне опущено ниже плинтуса, даже такой инженер должен быть способен скачать детскую книжку по SQL и хоть немного её почитать. Тем более, если ему это по работе важно.
7 дек 18, 13:05    [21756946]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос  [new]
Mari.P
Member

Откуда: Yekaterinburg
Сообщений: 660
Пришел ответ от техподдержки Schneider Electric такого содержания:
Это сделано специально. Предложение WHERE в SQL запросах к CDBHistoric должно быть ограничено временем и идентификатором объекта (Object ID) чтобы предотвратить слишком объемное количество возвращаемых данных, что, в свою очередь может привести к перезагрузке системы, снижению ее производительности или даже сбою системы

Теперь стало понятно почему такой запрос не работает.
SELECT TOP(100) CDBHISTORIC.ID,MAX(CDBHISTORIC.RecordTime)  AS "~RecTime" 
FROM CDBHISTORIC WHERE CDBHISTORIC.RecordTime>TIMESTAMP '2018-12-03 01:00:00'  
AND CDBHISTORIC.RecordTime<TIMESTAMP '2018-12-03 01:00:30'  GROUP BY CDBHISTORIC.ID


И соответственно решение, которое прислал TDima (спасибо!) тоже не сработает:
SELECT TOP (1500) CDBHISTORIC.value,CDBPOINT.Fullname,CDBHISTORIC.RecordTime AS '~Time' 
       FROM CDBPOINT, CDBHISTORIC, (select CDBHISTORIC.id, max(CDBHISTORIC.RecordTime) as RecordTime 
                                       from CDBHISTORIC 
                                       where (CDBHISTORIC.RecordTime>TIMESTAMP '2018-12-03 01:00:00') 
                                            AND  (CDBHISTORIC.RecordTime<TIMESTAMP '2018-12-03 01:00:30')
                                       group by CDBHISTORIC.id
                                   ) CHMAX
       WHERE CDBHISTORIC.id=CDBPOINT.id 
                   AND CDBHISTORIC.id=CHMAX.id and CDBHISTORIC.RecordTime = CHMAX.RecordTime


А если добавлять в подзапрос условие CDBHISTORIC.ID=CDBPOINT.ID, то несмотря на то, что в настройках сервера SCADA в части SQL-запросов выставляешь максимум 10млн строк, все равно драйвер ODBC выдает ошибку QueryTimeOut, рубит слишком емкие запросы.
7 дек 18, 17:57    [21757438]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос  [new]
Dima T
Member

Откуда:
Сообщений: 13032
Mari.P, с одной стороны слишком специфичная СУБД, с другой - слишком тяжелый запрос. В общем случае задача нерешаема.
Нужен тюнинг, т.е. спец по этой СУБД.
Мы тут наощупь можем найти какое-нибудь решение, которое возможно даже заработает, но никакой гарантии нет что завтра-послезавтра оно не перестанет работать. Лично я не хочу в этом участвовать.
Нанимайте спеца.
7 дек 18, 19:48    [21757568]     Ответить | Цитировать Сообщить модератору
 Re: Помогите составить запрос  [new]
alex55555
Member

Откуда:
Сообщений: 1323
Mari.P
Теперь стало понятно почему такой запрос не работает.

И почему же?
Mari.P
И соответственно решение, которое прислал TDima (спасибо!) тоже не сработает

А вот теперь, когда вы сохранили форматирование, запрос Димы легко узнаётся. Поэтому вы действительно теперь можете указывать на его ошибки.

Но тем не менее, мне до сих пор не ясно, поняли ли вы, что такое вложенный запрос и как его писать? Особенно после просвещения со стороны конторы Шнайдер электрик. Если поняли, то вообще-то уже могли бы и написать. А если не поняли, то пока что вы опять рассказываете про что-то отвлечённое, а не про то конкретное, что у вас не получается.

Можно, конечно, взять и написать вам запрос, но неспортивно это, хочется помучать ученицу :)

На самом деле было бы хорошо, если бы вы сами поняли, как писать запросы. И в этом плане помочь вам здесь реально могут.
8 дек 18, 12:57    [21757819]     Ответить | Цитировать Сообщить модератору
Все форумы / Программирование Ответить