Помогите, пожалуйста, составить SQL-запрос

← →
rom900 (2003-01-14 07:27) [0]

Добрый день! Помогите, пожалуйста, составить SQL-запрос.

Существует запрос:
select Tab.device, Sum(R1), Sum(R2),Round(((Sum(R1)+Sum(R2))/2),2), A_por.porog FROM A_POR,
(select a_data_general.device ,Round((Count(*)/mar),2)R1, "0" R2 from a_data_general, a_por where
(a_data_general.ats="21") and (a_data_general.ats=a_por.ats)and (a_data_general.device=a_por.device)
and (dt>="1.13.2003 9:00") and (dt<="1.13.2003 13:00") group by a_data_general.device,mar
UNION ALL select a_data_general.device , "0" R1,Round((Count(*)/mar),2)R2
from a_data_general, a_por where (a_data_general.ats="21") and (a_data_general.ats=a_por.ats)
and (a_data_general.device=a_por.device) and (dt>="1.14.2003 9:00") and (dt<="1.14.2003 13:00")
group by a_data_general.device,mar) Tab where (Tab.device=a_por.device) and (A_por.ats="21")
group by Tab.device, A_por.porog,A_por.ats order by Tab.device

В нем за определенный промежуток времени(в данном случае за два дня хотя может быть за любой
промежуток, который выбирается пользователем в программе) выводятся типы оборудования для
данной станции, число записей по данным типам оборудования за каждый день интервала, среднее
значение, порог.
Результат запроса(название колонок таблицы изменено, чтобы было понятнее):
Тип прибора 13.1.2003 14.1.2003 Среднее значение Порог
1-ГИ 8,8 0,6 4,7 6
3-ГИМ 0,5 0 0,25 1
АК-АВ вх 0,63 0,09 0,36 2
.................................................................

Можно ли составить данный запрос, таким образом чтобы дни в которых число записей по типам
оборудования был равен нулю не учитывались(например в данном случае, если за 14.1.2003 число
записей по всем типам приборов было равно нулю, то данный столбец не выводился бы)?
Как это сделать? Если можно на данном конкретном примере.

Структура таблицы a_data_general :
ats varchar(50)
dt datetime
device varchar(10)
uak_num smallint(2)
memo varchar(1)
R1 char(1)
R2 char(1)
....
R15 char(1)
R16 char(1)
d_stativ char(3)
d_in char(3)
status char(2)

Пример заполнения:
21
9.17.2002 18:00
3-ГИМ
1
а
2
2
..
3
4
01
31
Ок

Структура таблицы a_por:
ats char(10)
device char(10)
porog numeric(9)
mar numeric(9)
type char(10)

Пример заполнения:
21
АК-АВ вх
2
75
1
..................

← →
ЮЮ (2003-01-14 07:56) [1]

А не проще на клиенте проанализировать DataSet и сделать невидимым или столбец или колонку грида.

← →
rom900 (2003-01-14 08:18) [2]

А как проанализировать DataSet и сделать невидимым или столбец или колонку грида?

← →
sniknik (2003-01-14 08:29) [3]

На гриде сделай двойной клик мышой, откроется редактор полей, то что добавиш будет видимо. Можно и програмно, немного сложней.

← →
ЮЮ (2003-01-14 08:32) [4]

"Пробежаться по нему". Строк-то он возвращает надеюсь не 1000 :-)
DataSet.Fields[i].Visible := false;
DBGrid.Columns[i].Visible := false;

← →
ЮЮ (2003-01-14 08:37) [5]

Кстати нули ( а лучше "пробелы") нагляднее говорят об отсутствии записей на этот день, чем отсутствие столбца целиком, которое можно принять за неправильную работу

← →
rom900 (2003-01-14 08:40) [6]

Спасибо ЮЮ я попробую, так как вы показали.

← →
rom900 (2003-01-14 08:56) [7]

Я еще подумал, в этом случае в колонке среднее значение будет учитываться и второй день(в данном случае), т.е. в запросе будет
сумма записей всех этих дней делиться на число всех дней вне зависимости от того есть ли в данный день записи по типам оборудования. Что я хотел сказать, если 13.1.2003 число записей
скажем по оборудованию 1-ГИ=20 и 14.1.2003 число записей
скажем по оборудованию 1-ГИ=30, то среднее значение будет (20+30)/2=25 -считается правильно и если скрыть столбец то это нормально.Если же 13.1.2003 число записей
скажем по оборудованию 1-ГИ=20 и 14.1.2003 число записей
скажем по оборудованию 1-ГИ=0, то среднее значение будет (20+0)/2=10.А мне в этом случае нужно чтобы среднее значение
подсчитывалось без учета того дня, в котором число записей по типам оборудования было равно нулю.Т.е. необходимо, чтобы в данном случае среднее знечение считалось как 20/1=20.

← →
ЮЮ (2003-01-14 09:00) [8]

А нужно ли среднее по одному числу? Тоже спрятать :-)

← →
rom900 (2003-01-14 09:09) [9]

Нет среднее значение нужно, так как оно сравнивается с порогом и если это значение выше порога окрашивается в определенный цвет(по заданию).Среднее значение берется не по одному дню, а по числу дней в заданном интервале, который выбирает в программе пользователь, исключая те дни в которых число записей по типам оборудования равно нулю. Т.е. например пользователь выбрал интервал 6 дней, в два дня этого интервала число записей 0, то среднее значение подсчитывается как сумма записей в эти дни делить на 4.

← →
ЮЮ (2003-01-14 09:12) [10]

>Т.е. например пользователь выбрал интервал 6 дней
Что-то я не вижу в запросе возможность вывода шести столбцов

← →
ЮЮ (2003-01-14 09:23) [11]

Кстати, какой MS SQL?

← →
rom900 (2003-01-14 09:26) [12]

Запрос создается динамически для любого количества дней, таким образом (в данном случае для пяти дней):

select Tab.device, Sum(R1), Sum(R2), Sum(R3), Sum(R4), Sum(R5),Round(((Sum(R1)+Sum(R2)+Sum(R3)+Sum(R4)+Sum(R5))/5),2), A_por.porog FROM A_POR, (select a_data_general.device ,Round((Count(*)/mar),2)R1, "0" R2, "0" R3, "0" R4, "0" R5 from a_data_general, a_por where (a_data_general.ats="21") and (a_data_general.ats=a_por.ats)and (a_data_general.device=a_por.device) and (dt>="1.6.2003 9:00") and (dt<="1.6.2003 13:00") group by a_data_general.device,mar UNION ALL select a_data_general.device , "0" R1,Round((Count(*)/mar),2)R2, "0" R3, "0" R4, "0" R5 from a_data_general, a_por where (a_data_general.ats="21") and (a_data_general.ats=a_por.ats)and (a_data_general.device=a_por.device) and (dt>="1.7.2003 9:00") and (dt<="1.7.2003 13:00") group by a_data_general.device,mar UNION ALL select a_data_general.device , "0" R1, "0" R2,Round((Count(*)/mar),2)R3, "0" R4, "0" R5 from a_data_general, a_por where (a_data_general.ats="21") and (a_data_general.ats=a_por.ats)and (a_data_general.device=a_por.device) and (dt>="1.8.2003 9:00") and (dt<="1.8.2003 13:00") group by a_data_general.device,mar UNION ALL select a_data_general.device , "0" R1, "0" R2, "0" R3,Round((Count(*)/mar),2)R4, "0" R5 from a_data_general, a_por where (a_data_general.ats="21") and (a_data_general.ats=a_por.ats)and (a_data_general.device=a_por.device) and (dt>="1.9.2003 9:00") and (dt<="1.9.2003 13:00") group by a_data_general.device,mar UNION ALL select a_data_general.device , "0" R1, "0" R2, "0" R3, "0" R4,Round((Count(*)/mar),2)R5 from a_data_general, a_por where (a_data_general.ats="21") and (a_data_general.ats=a_por.ats)and (a_data_general.device=a_por.device) and (dt>="1.10.2003 9:00") and (dt<="1.10.2003 13:00") group by a_data_general.device,mar) Tab where (Tab.device=a_por.device) and (A_por.ats="21") group by Tab.device, A_por.porog,A_por.ats order by Tab.device

← →
rom900 (2003-01-14 09:28) [13]

Используется сервер MS SQL 7.0

← →
ЮЮ (2003-01-14 09:37) [14]

=:0Ь
А не проще на клиенте двумя "легкими" запросом определить необходимое количество строк и столбцов, все посчитать и отобразить в StringGride?

← →
rom900 (2003-01-14 09:57) [15]

Непонятно зачем определять в данном случае количество строк и как двумя "легкими" запросом определить необходимое количество строк и столбцов?
Количество столбцов, которых необходимо выводить в данном запросе можно было бы подсчитать создавая отдельный запрос для каждого дня данного интервала и с учетом результата динамически создавать данный запрос. Но так как в интервале может большое число дней(30), то получится 30 запросов нужно выполнить чтобы сформировать данный запрос.Это очень сильно загружает сервер, который занят и другими задачами.

← →
rom900 (2003-01-14 13:06) [16]

Может кто еще что-нибудь толковое посоветует?

← →
ЮЮ (2003-01-15 06:08) [17]

>Но так как в интервале может большое число дней(30), то получится 30 запросов нужно выполнить чтобы сформировать данный запрос.Это очень сильно загружает сервер, который занят и другими задачами

Запрос с 30 Union, а в каждом используется Group, тоже не подарок для сервера :-) А я как раз говорю о действительно лёгких запросах.

Запрос
select distinct a_data_general
from a_data_general
where
(a_data_general.ats="21") and
(dt>=:d1) and (dt<=:d2) and <доп усл. для временного интервала>
вернет кол-во реально присутствующих дней в интервале, т.е. кол-во столбцов

Запрос
select a_por.* from
(select distinct ats,device
from a_data_general
where
(a_data_general.ats="21") and
(dt>=:d1) and (dt<=:d2) and <доп усл. для временного интервала>) Lines
left join a_por
on (Lines.ats=a_por.ats)and (Lines.device=a_por.device)
вернет кол-во строк c необходимыми порогами

Запрос
select Count(*)
from a_data_general
where
(a_data_general.ats="21") and
(dt>=:d1) and (dt<=:d2) and <доп усл. для временного интервала>
group by device
вернёт необходимые цифры, которые надо будет распихать в Грид.
Вот здесь то и можно будет применить искусство программирования, которое не всегда состоит в том, чтобы получить запрос, рез-ты которого можно отразить в обычном Гриде :-)

← →
rom900 (2003-01-15 08:48) [18]

ЮЮ
Запрос
select distinct a_data_general
from a_data_general
where
(a_data_general.ats="21") and
(dt>=:d1) and (dt<=:d2) and <доп усл. для временного интервала>
-неверен.
А потом что это за <доп усл. для временного интервала>?
Можно на примере?

Запрос
select Count(*)
from a_data_general
where
(a_data_general.ats="21") and
(dt>=:d1) and (dt<=:d2) and <доп усл. для временного интервала>
group by device
вернёт необходимые цифры, которые надо будет распихать в Грид.

А для какого дня? Или для всех вместе? Но это неправильно,т.к.
для каждого дня они могут быть разными?

Может быть проще тогда оставить мой запрос? Только как вы предложили скрыть столбец и исправить данные в наборе данных в столбце для среднего значения?

← →
ЮЮ (2003-01-15 09:26) [19]

>rom900 © (15.01.03 08:48)
>А потом что это за <доп усл. для временного интервала>?
Тебя же интересует только то, что происходит с 9:00 до 13:00, чтобы отсечь записи от 13:00 до 9:00 следующего дня

>А для какого дня?
Сорри, это, думаю и так понятно, что пропустил
group by device, Convert( DateTime, Convert(int,a_data_general))

В первом запросе тоже следует отсечь время:
select distinct Convert( DateTime, Convert(int,a_data_general))DateOnly
from a_data_general
where
(a_data_general.ats="21") and
(dt>=:d1) and (dt<=:d2) and <доп усл. для временного интервала>
order by DateOnly

Если же использовать твой вариант, то вместо /N пиши
/(select Count(*) ... )
Монстроидальней запрос уже не станет - дальше некуда :-)

← →
ЮЮ (2003-01-15 09:59) [20]

Дабы избежать ошибок при округлении лучше
CONVERT(DateTime, FLOOR(CONVERT(float, ORD_DATE))) AS DateOnly

Помогите, пожалуйста, составить SQL-запрос Найти похожие ветки