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

Откуда: Москва
Сообщений: 763
Задача сводиться к поиску максимального и предыдущего ему значения...
Нижеприведенный вариант работает за исключения ситуации когда предыдущего значения нет.
Т.е. результат (для примера) должен быть:
id	max-1	max
1	2	3
2	4	5
3	null	1

привожу упрощенный вариант для теста:

create table test(id int, value int)

insert into test select 1, 1
insert into test select 1, 2
insert into test select 1, 3
insert into test select 2, 1
insert into test select 2, 2
insert into test select 2, 3
insert into test select 2, 4
insert into test select 2, 5
insert into test select 3, 1

select t.id, maxvalue = max(t.value), maxvalue1 = max(t1.value)
from test t
	left outer join (select id, value = max(value)
			from test 
			group by id
			) t1 on t1.id = t.id 
where
	t.value <> t1.value
group by t.id
4 май 09, 09:46    [7140934]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значения предыдущего максимальному  [new]
Петр
Member

Откуда: Москва
Сообщений: 763
да забыл сказать, что все это нужно во вьюшке... т.е. без временных таблиц и процедур...
4 май 09, 09:47    [7140940]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значения предыдущего максимальному  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
;WITH CTE AS (SELECT ROW_NUMBER()OVER(PARTITION BY id ORDER BY value DESC) N, * FROM test)
SELECT
 id,
 (SELECT T.value FROM CTE T WHERE T.id=CTE.id AND T.N=2)[max-1],
 (SELECT T.value FROM CTE T WHERE T.id=CTE.id AND T.N=1)[max]
FROM CTE
WHERE N=1
ORDER BY id;
4 май 09, 09:57    [7140991]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значения предыдущего максимальному  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
select id
      ,(select max(value)
          from test as t2
         where t2.id = t1.id
           and t2.value < t1.mv
       ) as [max-1]
      ,t1.mv as [max]
  from (select id, max(value) as mv
          from test
         group by id) as t1

id          max-1       max
----------- ----------- -----------
1           2           3
2           4           5
3           NULL        1

(3 row(s) affected)
4 май 09, 10:03    [7141010]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значения предыдущего максимальному  [new]
Петр
Member

Откуда: Москва
Сообщений: 763
спасибо
2 iap - красивое решение но сиквел 2000

2 Паганель - то что нужно - главное теперь как быстро это будет работать :)
4 май 09, 10:13    [7141066]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значения предыдущего максимальному  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Петр
главное теперь как быстро это будет работать :)
На Вашей сотне записей работать будет быстро
4 май 09, 10:15    [7141078]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значения предыдущего максимальному  [new]
Петр
Member

Откуда: Москва
Сообщений: 763
к сожалению из гораздо больше... но это уже в теме не относиться.

спасибо за подсказку
4 май 09, 10:18    [7141099]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значения предыдущего максимальному  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Паганель,

а как будет в случае, если равных друг другу максимальных значений несколько?
Кстати, из постановки задачи вообще непонятно, что делать в таком случае,
и что означает фраза "предыдущее ему значение".
4 май 09, 10:30    [7141158]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значения предыдущего максимальному  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
iap
как будет в случае, если равных друг другу максимальных значений несколько?
В этом случае в таблице будет две неотличимых друг от друга записи
Другими словами, это будет означать, что БД спроектирована, мягко говоря, некачественно
4 май 09, 10:34    [7141191]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значения предыдущего максимальному  [new]
Петр
Member

Откуда: Москва
Сообщений: 763
если таких записей несколько то для данной постановки - это неважно, т.к. в качестве искомых максимальных значений выступает Дата и важна только она. Не важно сколько будет одинаковых дат у конкретного ID - важно ее значение.
4 май 09, 11:16    [7141462]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значения предыдущего максимальному  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
Я ничего не понял из того, что Вы только что сказали,
но если Дата будет с временем, да еще с точностью до долей секунды,
а данные вносятся юзером вручную по одной записи, то повторяющиеся данные практически невозможны
4 май 09, 11:22    [7141503]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значения предыдущего максимальному  [new]
Петр
Member

Откуда: Москва
Сообщений: 763
дата - это дата проверки предприятия. существуют различные расчетные показатели для которых требуется именно последняя дата проверки и предпоследняя.

в общем - расчет показателей довольно сложен и навряд ли кому интересен...
4 май 09, 11:40    [7141656]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значения предыдущего максимальному  [new]
aleks2
Guest
Вот еще один пример как помогает жить наличие primary key

declare  @test table(id int, value int, rowid int identity primary key clustered)

insert into @test select 1, 1
insert into @test select 1, 2
insert into @test select 1, 3
insert into @test select 2, 1
insert into @test select 2, 2
insert into @test select 2, 3
insert into @test select 2, 4
insert into @test select 2, 5
insert into @test select 3, 1

select t.* 
FROM 
@test t
WHERE
t.rowid in (select top 2 x.rowid FROM @test X WHERE X.id=t.id ORDER BY value DESC)
4 май 09, 12:31    [7141947]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значения предыдущего максимальному  [new]
dima.dp.ua
Member

Откуда:
Сообщений: 42
select t1.id, max(t2.value) [max-1], max(t1.value) [max]
from test t1
left join test t2 on t2.id = t1.id and t2.value<t1.value
group by t1.id
6 май 09, 13:17    [7152613]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значения предыдущего максимальному  [new]
SQLkowka
Member

Откуда:
Сообщений: 63
Добрый день!
Как решить эту же задачу, только если нужно вывести не одно предыдущее значение от максимального,а 2 значения
(т.е. 3 максимальных значений), т.е. чтоб они не складывались а именно выводились отдельно, как в последнем варианте решения!

Спасибо!

p.s.
Может как-то добавить в Havinge count(*)=3
т.е. посчитать кол-во строк
15 сен 09, 09:46    [7658831]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значения предыдущего максимальному  [new]
DENIS_CHEL
Member

Откуда:
Сообщений: 23097
SQLkowka
Добрый день!
Как решить эту же задачу, только если нужно вывести не одно предыдущее значение от максимального,а 2 значения
(т.е. 3 максимальных значений), т.е. чтоб они не складывались а именно выводились отдельно, как в последнем варианте решения!

Спасибо!

p.s.
Может как-то добавить в Havinge count(*)=3
т.е. посчитать кол-во строк


select t1.id, max(t3.value) [max-2] , max(t2.value) [max-1], max(t1.value) [max]
from test t1
left join test t2 on t2.id = t1.id and t2.value<t1.value
left join test t3 on t3.id = t2.id and t3.value<t2.value
group by t1.id

Эм... как бы так и вывести... Или я что не понял?
15 сен 09, 09:58    [7658888]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значения предыдущего максимальному  [new]
iljy
Member

Откуда:
Сообщений: 8711
SQLkowka,

версия сервера какая? если 2005 и выше - можно так, причем сколько угодно значений

select Value from
(select top 3 row_number() over(order by Value desc) rn, value from Table order by 1)
where rn > 1

3 заменяете на любое нужное число
15 сен 09, 11:09    [7659440]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значения предыдущего максимальному  [new]
iljy
Member

Откуда:
Сообщений: 8711
SQLkowka,
а если надо в одну строку - тогда так:
select max(case rn then 2 then Value end) Val2, max(case rn then 3 then Value end) Val3 from
(select top 3 row_number() over(order by Value desc) rn, value from Table order by 1)
15 сен 09, 11:23    [7659557]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значения предыдущего максимальному  [new]
SQLkowka
Member

Откуда:
Сообщений: 63
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)


Да, все вроде похоже ,просто у меня ключей больше(3 ключа, но по одному я не привязываюсь, т.к.
он мне в итоге не нужен, т.е. там дата, а мне нужно найти максимум по 2- м ключам, за 2 месяца(не разбивая), т.е. 3 пиковых значения по двум ключам, за 2 месяца)
, да и записей прилично пробую делать как ....

select t1.id, max(t3.value) [max-2] , max(t2.value) [max-1], max(t1.value) [max]
from test t1
left join test t2 on t2.id = t1.id and t2.value<t1.value
left join test t3 on t3.id = t2.id and t3.value<t2.value
group by t1.id

Но почему то висит вроде че-то считает, но ОЧЕНЬ ДОЛГО.....
15 сен 09, 13:01    [7660510]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значения предыдущего максимальному  [new]
DENIS_CHEL
Member

Откуда:
Сообщений: 23097
TOP 100 отрабатывает быстро?
15 сен 09, 13:04    [7660537]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значения предыдущего максимальному  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
SQLkowka
т.е. 3 пиковых значения по двум ключам, за 2 месяца)
Что-то мне кажется, "предыдущее значение от максимального"
в Вашем понимании - это совсем не то, что обсуждается в этой теме...
Похоже, Вы про локальные максимумы говорите?
Пример привести можете?
15 сен 09, 13:13    [7660618]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значения предыдущего максимальному  [new]
SQLkowka
Member

Откуда:
Сообщений: 63
SELECT    dbo.Таблица.CorpCode, MAX(Таблица_2.Кол) AS [max-2], MAX(Таблица_1.Кол) AS [max-1], 
                      MAX(dbo.Таблица.Кол) AS [max], dbo.Таблица.CODE
into 
--drop table 
E_3Znach
FROM         dbo.Таблица 
LEFT OUTER JOIN
                      dbo.Таблица Таблица_1 ON 
--dbo.Таблица_1.ДатаОпер = Таблица.ДатаОпер AND 
Таблица_1.Dkey = Таблица.Dkey AND 
 Таблица_1.CODE = Таблица.CODE AND 
Таблица_1.Кол < dbo.Таблица.Кол
LEFT OUTER JOIN
                      dbo.Таблица Таблица_2 ON 
--dbo.Таблица_2.ДатаОпер = Таблица_1.ДатаОпер AND 
Таблица_2.CODE = Таблица_1.CODE AND 
Таблица_2.Dkey = Таблица_1.Dkey AND 
Таблица_2.Кол < Таблица_1.Кол 
WHERE     (MONTH(dbo.Таблица.ДатаОпер) BETWEEN 7 AND 8) AND (YEAR(dbo.Таблица.ДатаОпер) = 2009) AND 
                      (dbo.Таблица.CorpCode = '123567' OR
                      dbo.Таблица.CorpCode = '789567' OR
                      dbo.Таблица.CorpCode = '589568')
GROUP BY  dbo.Таблица.CorpCode, dbo.Таблица.CODE

Т.е.
Поля в табличке dbo.Таблица.CorpCode, dbo.Таблица.CODE, Дата

И мне необходимо найти за 2 месяца максимальных 3 значения по каждому dbo.Таблица.CorpCode и dbo.Таблица.CODE

Т.е. все записи например: CorpCode1,Code1, 5
(это например самое максимальное значение)
CorpCode1,Code1, 4.9
(это второе существующее значение от максимального существующего)
CorpCode1,Code1, 4.5
(это третье существующее значение от максимального существующего)

А остальные не нужны: CorpCode1,Code1, 4,0
CorpCode1,Code1, 4.3
CorpCode1,Code1, 3.0
и т.д.

И так для каждого существующего

CorpCode1,Code2, 5
CorpCode1,Code2, 4.0
CorpCode1,Code2, 3.2
......
и т.д.
CorpCode2,Code1, 5
CorpCode2,Code1, 3.7
CorpCode2,Code1, 3.2
и т.д.


CorpCode5,Code5, 5
CorpCode5,Code5, 3.9
CorpCode5,Code5, 3.0
и т.д.
.......

Не знаю понятно объяснила или нет....

Спасибо за помощь.
15 сен 09, 13:36    [7660838]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значения предыдущего максимальному  [new]
iljy
Member

Откуда:
Сообщений: 8711
SQLkowka,

WHERE     (MONTH(dbo.Таблица.ДатаОпер) BETWEEN 7 AND 8) AND (YEAR(dbo.Таблица.ДатаОпер) = 2009) AND 

такое условие гарантированно даст вам сканирование таблицы, даже если есть индекс по ДатаОпер. Переделайте так
ДатаОпер >= '20090701' and ДатаОпер < '20090901'

также неплохо бы понять, что за поле DKey, почему вы его используете при join, но не при группировке. И непонятно, должна ли учитываться дата для Таблицы1 и Таблицы2. Ну и для полного счастья - DDL таблиц и план запроса.
15 сен 09, 14:12    [7661214]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значения предыдущего максимальному  [new]
SQLkowka
Member

Откуда:
Сообщений: 63
Да индекс на дату есть, да спасибо заменила.

Но это я думаю не суть.

Про дикей, это однотипные ключи(т.е. я конечно должна была написать для понимания запроса один
или объяснить вам оба)
Т.е. запрос может выглядеть так:
SELECT    dbo.Таблица.CorpCode, MAX(Таблица_2.Кол) AS [max-2], MAX(Таблица_1.Кол) AS [max-1], 
                      MAX(dbo.Таблица.Кол) AS [max], dbo.Таблица.CODE
into 
--drop table 
E_3Znach
FROM         dbo.Таблица 
LEFT OUTER JOIN
                      dbo.Таблица Таблица_1 ON 
--dbo.Таблица_1.ДатаОпер = Таблица.ДатаОпер AND 
Таблица_1.CorpCode = Таблица.CorpCode AND 
 Таблица_1.CODE = Таблица.CODE AND 
Таблица_1.Кол < dbo.Таблица.Кол
LEFT OUTER JOIN
                      dbo.Таблица Таблица_2 ON 
--dbo.Таблица_2.ДатаОпер = Таблица_1.ДатаОпер AND 
Таблица_2.CODE = Таблица_1.CODE AND 
Таблица_2.CorpCode = Таблица_1.CorpCode AND 
Таблица_2.Кол < Таблица_1.Кол 
WHERE     (MONTH(dbo.Таблица.ДатаОпер) BETWEEN 7 AND 8) AND (YEAR(dbo.Таблица.ДатаОпер) = 2009) AND 
                      (dbo.Таблица.CorpCode = '123567' OR
                      dbo.Таблица.CorpCode = '789567' OR
                      dbo.Таблица.CorpCode = '589568')
GROUP BY  dbo.Таблица.CorpCode, dbo.Таблица.CODE

спасибо.

Про дату мне самой не понятно, мне она при выводе не нужна т.е. я должна как бы просмотреть данные за все 2 месяца(за каждый день, и вытащить только те 3 максимальных значения, которые были когда-то в какой-то день)

Т.е. в итоге для каждого CorpCode и Code должны быть 3 максимальных значения.

спасибо.
15 сен 09, 14:40    [7661580]     Ответить | Цитировать Сообщить модератору
 Re: Поиск значения предыдущего максимальному  [new]
SQLkowka
Member

Откуда:
Сообщений: 63
У меня уже минут 10 висит даже top 1

не то что top 100
15 сен 09, 15:25    [7662023]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить