Добрый день. Не могу справиться с такой задачкой:
Имеется таблица:
id(uuid) Time(int) Instrument(string) AvgLatency(int) Count(int)
По заданному id необходимо для всех значений Time выдать сумму от Count сгруппированного по инструментам (что легко), а также некоторое значение, которое получается как среднее от AvgLatency* по инструментам. Где AvgLatency* получается как AvgLatency для каждого инструмента из которого вычтено первое по времени ненулевое значение для этого инструмента.
Очень надеюсь на помощь RSDN'овцев.
Здравствуйте, __MasteR__, Вы писали:
__M>Добрый день. Не могу справиться с такой задачкой:
__M>Имеется таблица:
__M>id(uuid) Time(int) Instrument(string) AvgLatency(int) Count(int)
__M>По заданному id необходимо для всех значений Time выдать сумму от Count сгруппированного по инструментам (что легко), а также некоторое значение, которое получается как среднее от AvgLatency* по инструментам. Где AvgLatency* получается как AvgLatency для каждого инструмента из которого вычтено первое по времени ненулевое значение для этого инструмента.
__M>Очень надеюсь на помощь RSDN'овцев.
Добрый день!
Не очень прозрачно сформулирован вопрос... Если я правильно понял, то если бы была колонка AvgLatency* в таблице, то запрос бы выглядел так:
select Instrument, avg(AvgLatency*), sum(Count)
......
group by Instrument
Если да (и я правильно понял задачу) то представляется логичным использовать курсор и ручное вычисление агрегатных функций. Открываете курсор с выборкой отсортированной по Instrument, Time. Для каждого Instrument на первой строке с ненулевым Time имеем вычитаемое значение для AvgLatency*. Если хочется иметь именно запрос, то можно пронумеровать строки выборки, вычислить номера строк с вычитаемыми значениями и построить требуемое
create table test(
xTime int,
xInstrument varchar(12),
xAvgLatency int,
xCount int
);
x-ы добавил чтобы не совпадало с ключевыми словами.
select
test.xInstrument,
AVG(test.xAvgLatency - t.xAvgLatency) Avg,
SUM(test.xCount)
from test join
(
select
numbered_test.xInstrument,
numbered_test.xAvgLatency
from
(select ROW_NUMBER() over (order by xInstrument, xTime) n, * from test where xTime > 0) numbered_test
join
(select xInstrument, MIN(n) min_row from(
select ROW_NUMBER() over (order by xInstrument, xTime) n, xInstrument from test where xTime > 0) t
group by xInstrument ) min_rows on min_rows.min_row = numbered_test.n
) t on t.xInstrument = test.xInstrument
group by test.xInstrument
Я написал какую-то чушь выше, а удалить или поправить нельзя (( не бейте больно...
вот вариант:
select xInstrument, AVG(xAvgLatency_star), SUM(xCount) from (
select
*,
(test.xAvgLatency - (
select top 1 t1.xAvgLatency from test t1
where t1.xInstrument = test.xInstrument and t1.xTime = (
select MIN(t2.xTime) from test t2
where t2.xInstrument = test.xInstrument and t2.xTime > 0))
) xAvgLatency_star
from test ) t
group by t.xInstrument
И еще можно оптимизировать, вычисляя "*" не для каждой записи. а для каждого инструмента.
Здравствуйте, Anton Burtsev, Вы писали:
AB>Я написал какую-то чушь выше, а удалить или поправить нельзя (( не бейте больно...
AB>вот вариант:
AB>AB>select xInstrument, AVG(xAvgLatency_star), SUM(xCount) from (
AB>select
AB> *,
AB> (test.xAvgLatency - (
AB> select top 1 t1.xAvgLatency from test t1
AB> where t1.xInstrument = test.xInstrument and t1.xTime = (
AB> select MIN(t2.xTime) from test t2
AB> where t2.xInstrument = test.xInstrument and t2.xTime > 0))
AB> ) xAvgLatency_star
AB>from test ) t
AB>group by t.xInstrument
AB>
AB>И еще можно оптимизировать, вычисляя "*" не для каждой записи. а для каждого инструмента.
Спасибо за ответы. На самом деле здесь, в соседней теме встретил ссылку на опросник по SQL, с краткой вменяемой справкой и у меня получился следующий запрос:
SELECT RunId, Time, MAX(MaxLatency) MaxLatency, Avg(AvgLatency) vgLatency, SUM(Count) FROM
(
SELECT
S1.RunId RunId,
S1.Time Time,
S.Instrument Instrument,
S1.AvgLatency - S.AvgLatency AvgLatency,
S1.Count Count FROM
(
SELECT RunId, MIN(Time) as MinTime, Instrument FROM Stats S1
where RunId = '...' and AvgLatency != 0
group by RunId, Instrument
) as T
JOIN Stats S ON T.MinTime = S.Time and T.Instrument = S.Instrument and S.RunId = T.RunId
LEFT JOIN Stats S1 ON S1.RunId = T.RunId and S1.Instrument = T.Instrument
)
GROUP BY RunId, Time
ORDER BY Time
Т. е. сначала выбираем таблицу с MinTime — минимальным временем с ненулевым Latency, затем джойним к этому значение Latency при этом минимальном времени, а затем джойним все остальные данные и вычисляем то, что нам надо.
