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

Откуда:
Сообщений: 38
Добрый день.
Подскажите, можно ли оптимизировать данный запрос, а то практически полное повторение строк не очень впечатляет, да и может легче его можно сделать.
Исходная таблица
длина (в см)
--------------
3
4
5
6
11
12
13
14

Таблица на выходе:
параметр макс. длина мин. длина всего данных 0-10 11-20
'Частота, экз' 14 3 8 4 4
'Частота, %' 14 3 8 50 50
Т.е. задается интервал отображения данных и считается количество попаданий в каждый интервал.
А запрос я составил такой:


select p.param as 'Параметр', min(minl) as 'Мин_длина', max(maxl) as 'Мах_длина',
sum(p.m0+p.m1) as 'Количество',
case p.param when 'Частота, экз' then sum(p.m0) else round(sum(p.m0)*100.0/sum(p.m0+p.m1),2) end as '0 - 40',
case p.param when 'Частота, экз' then sum(p.m1) else round(sum(p.m1)*100.0/sum(p.m0+p.m1),2) end as ' > 40'
from(
select 'Частота, экз' as param, max(a.length) as maxl, min(a.length) as minl,
count(a.length) as m0, 0 as m1
from vi_MassSurvey a
where (a.length>0 and a.length<=40)
union all
select 'Частота, экз' as param, max(a.length) as maxl, min(a.length) as minl,
0 as m0,count(a.length) as m1
from vi_MassSurvey a
where (a.length>40)
union all
select 'Частота, %' as param, max(a.length) as maxl, min(a.length) as minl,
count(a.length) as m0, 0 as m1
from vi_MassSurvey a
where (a.length>0 and a.length<=40)
union all
select 'Частота, %' as param, max(a.length) as maxl, min(a.length) as minl,
0 as m0,count(a.length) as m1
from vi_MassSurvey a
where (a.length>40)

) p
group by p.param

В принципе запрос работает, но проблема возникает когда количество интервалов превышает 60 (выдается ошибка, что присоединеных таблиц больше, чем 256).
Заранее спасибо
13 май 04, 06:33    [675074]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация статистического запроса  [new]
buser
Member

Откуда: Санкт-Петербург
Сообщений: 4537
брать union
case when a.length >0 and <= 40 then 1 when ... then 2 and so one... else ... end as FGroup
13 май 04, 10:48    [675450]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация статистического запроса  [new]
Paul Chabinsky
Member

Откуда:
Сообщений: 322
Я бы предложил Вам вынести в отдельную табличку интервалы...
--интервалы в которые должны попадать значения

declare @lim table( [mi] [int] NOT NULL , [ma] [int] NOT NULL , primary key clustered( mi,ma ) ) insert into @lim (mi,ma) select 1,10 union select 11,20 --значения
declare @dat table( [val] [int] NOT NULL ) insert into @dat (val) select 3 union select 4 union select 5 union select 6 union select 11 union select 12 union select 13 union select 14 select l.mi [начало интервала], l.ma [конец интервала], count(*) [количество вхождений] from @lim l left join @dat d on d.val between l.mi and l.ma group by l.mi,l.ma --Результат:
начало интервала конец интервала количество вхождений ---------------- --------------- --------------------
1 10 4 11 20 4

Посчитать остальные величины будет несложно...
13 май 04, 10:52    [675469]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация статистического запроса  [new]
BugsBunny
Member

Откуда: GMT+5=EST
Сообщений: 2414
To Paul Chabinsky:
You're absolutelly correct but just allow, please, add some touches to the guy who has to write a way too many reports (see fancy code below)

To y_nikonov:
I believe you could easily write query youself if you realize that any SELECT statement returns ROWS (not columns!). One row, many rows or none. So, you should start thinking this direction (vertically) instead of from left to right. SQL is not very good at that. You can use other apps for pivoting (rotating) your results (for ex. Excel)
So good luck!

USE pubs
GO
IF object_id('some_MassSurvey') IS NOT NULL
	DROP TABLE [some_MassSurvey]
GO
CREATE TABLE some_MassSurvey ([id] int not null identity(1,1) PRIMARY KEY,length decimal NULL)
insert into some_MassSurvey ([length])
select 3
union select 4
union select 5
union select 6
union select 11
union select 12
union select 13
union select 14

IF object_id('sp_AnalyzeThat') IS NOT NULL
	DROP PROCEDURE [sp_AnalyzeThat]
GO
CREATE PROCEDURE sp_AnalyzeThat
	@interval_len int,
	@interval_qty int
AS
SET NOCOUNT ON
--validate parameters

SELECT @interval_len=ABS(ISNULL(@interval_len,0)), @interval_qty=ABS(ISNULL(@interval_qty,0)) declare @lim table( [display_order] int NULL, [name] varchar(255) NULL, [mi] [int] NOT NULL , [ma] [int] NOT NULL ) declare @i int set @i=0 while @i<@interval_qty begin set @i=@i+1 insert into @lim ( [display_order], [name], [mi], [ma] ) values ( @i, CAST((@i-1)*@interval_len as varchar(255)) + '<= x <' + CAST(@i*@interval_len as varchar(255)), (@i-1)*@interval_len, @i*@interval_len) end INSERT INTO @lim ([display_order],[name],[mi],[ma]) SELECT -1,'Below ' + CAST(MIN([mi]) as varchar(255)),-1 as [mi], MIN([mi]) as [ma] FROM @lim INSERT INTO @lim ([display_order],[name],[mi],[ma]) SELECT @i+1,'Over ' + CAST(MAX([ma]) as varchar(255)),MAX([ma]) as [mi],(2^31 - 1) as [ma] FROM @lim SELECT l.[name] AS [интервал], MIN(d.length) AS [мин. длина], MAX(d.length) AS [макс. длина], COUNT(d.length) AS [всего данных], 100*CAST(count(d.length) as decimal)/(SELECT COUNT(*) FROM [some_MassSurvey] WITH(NOLOCK)) AS [% данных] FROM @lim l left join [some_MassSurvey] d WITH(NOLOCK) on d.length >= l.mi AND d.length < l.ma group by l.display_order, l.[name] ORDER BY l.display_order SET NOCOUNT OFF Go EXECUTE sp_AnalyzeThat 10,2 Go IF object_id('some_MassSurvey') IS NOT NULL DROP TABLE [some_MassSurvey] GO IF object_id('sp_AnalyzeThat') IS NOT NULL DROP PROCEDURE [sp_AnalyzeThat] GO
13 май 04, 18:29    [677210]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация статистического запроса  [new]
y_nikonov
Member

Откуда:
Сообщений: 38
Спасибо за ответы.
Очень много они дали информации для размышления. Узнал конструкцию case when a.length >0 and <= 40 then (спасибо buser), понял как узка у меня тропинка мысли (спасибо Paul Chabinsky) и спасибо BugsBunny за вдумчивый ответ. Буду экспериментировать.
Еще раз спасибо всем.
14 май 04, 05:19    [677570]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить