Помогите разобратя с запросом....

yurchello

Доброго всем времени суток. Недавно перешёл на MSSQL 2000. и вопрос : есть таблица
rates(provider_id,prefix_pattern),
вьюшка
Create VIEW unique_prov_prefixes
 AS
 SELECT rtrim(ltrim(convert(char,prefix_pattern))) as u_prefix_pattern
 FROM rates
 GROUP BY prefix_pattern
вьюшка даёт весь перечень уникальных prefix_pattern из таблицы- это фактически перечень всех телефонных префиксов предоставляемых разными провайдерами (provider_id)Вот запрос SELECT u.u_prefix_pattern, r.prefix_pattern, len(r.prefix_pattern) as rpef_len FROM unique_prov_prefixes as u JOIN rates as r on PATINDEX(RTRIM(LTRIM(CONVERT(char,r.prefix_pattern)))+ '%', ltrim(rtrim(u.u_prefix_pattern)))= 1иными словами берётся префикс например 38050, и в rates находятся: provider_id prefix_pattern 1 380 1 38050 2 3 2 380 2 3805Так вот задача - нужно чтобы запрос выдавал только тот prefix_pattern по провайдеру у которого максимальное кол-во знаков. Тоесть для 1 провайдера нужен 1 38050для 2: 2 3805Как это сделать без повторного запроса и временной таблицы в одном запросе?
24 ответа

yurchello

SELECT u.u_prefix_pattern,
max(r.prefix_pattern),
len(r.prefix_pattern) as rpef_len
FROM unique_prov_prefixes as u
JOIN rates as r 
on PATINDEX(RTRIM(LTRIM(CONVERT(char,r.prefix_pattern)))+ '%', ltrim(rtrim(u.u_prefix_pattern)))= <b>1</b>
group by u.u_prefix_pattern, len(r.prefix_pattern)
попробуй так


yurchello

select rates.*
from rates inner join
(select provider_id, max(len(prefix_pattern)) as ml
from rates
where '38050' like prefix_pattern + '%'
group by provider_id) as xxx
on rates.provider_id = xxx.provider_id and len(rates.prefix_pattern) = xxx.ml
where '38050' like rates.prefix_pattern + '%'
?


yurchello

SELECT max(len(u.u_prefix_pattern)),
 r.provider_id,
 FROM unique_prov_prefixes as u
 JOIN rates as r 
 on PATINDEX(RTRIM(LTRIM(CONVERT(char,r.prefix_pattern)))+ '%', ltrim(rtrim(u.u_prefix_pattern)))= <b>1</b>
group by r.provider_id


yurchello

SELECT r.provider_id,
 max(r.prefix_pattern)
 FROM rates r
 GROUP BY r.provider_id
По-моему все остальное лишнее


yurchello

Александр был немного не прав в
SELECT u.u_prefix_pattern,max(r.prefix_pattern), len(r.prefix_pattern) as rpef_len FROM unique_prov_prefixes as u JOIN rates as r on PATINDEX(RTRIM(LTRIM(CONVERT(char,r.prefix_pattern)))+ '%', ltrim(rtrim(u.u_prefix_pattern)))= 1 group by u.u_prefix_pattern, len(r.prefix_pattern)
так как выбирается максимальное численное значение - а это не правильно. нужно максимальное кол-во цирф. тоесть если будет вариант выбора 380 и 381 для префикса 38 - то в вашем случае выберится 381, а правильно будет оба!
SELECT max(len(r.prefix_pattern)), r.provider_id, FROM unique_prov_prefixes as u JOIN rates as r on PATINDEX(RTRIM(LTRIM(CONVERT(char,r.prefix_pattern)))+ '%', ltrim(rtrim(u.u_prefix_pattern)))= 1group by r.provider_id
Это верно, с некоторыми изменениями так как поиск проходит по второй таблице - r, а префиксы берутся из u. мы ищем максимальное кол-во совпавших знаков из u в r. Но как я узнаю какой префикс выбран? как вывести r.prefix_pattern а не его максимальную длину?


yurchello

Посмотри второй запрос :-)


yurchello

SELECT r.provider_id, max(r.prefix_pattern) FROM rates r GROUP BY r.provider_idПо-моему все остальное лишнее
Да ну как же лишнее! мы же не просто ищем максимальный префикс! есть 5 провайдеров телефонии. первой вьюшкой я из них выбираю список всех направлений которые они предоставляют (без повторов) запросом я бегу по этой вьюшке и беру например 380. мне нужно выбрать с каждого провайдера префиксы, схожие по нарастающей - тоесть 3 подойдёт, 38 тоже.. и 380. (380050 уже нет) тоесть кто может принять звонок на 380. и найти из них максимальный. Тоесть если есть 38 и 380 - то нужно выбрать 380, тоже и для 3 и 38 - нужно выбрать более детализированное направление.


yurchello

SELECT
 FROM rates r
 inner join
 (SELECT provider_id,
 max(len(convert(char, prefix_pattern)))
 FROM rates
 WHERE '38050' like convert(char, prefix_pattern) + '%'
 GROUP BY provider_id) g
 on g.provider_id = r.provider_id and
 max(len(convert(char, g.prefix_pattern))) = max(len(convert(char, r.prefix_pattern)))


yurchello

Виноват, не доглядел
SELECT
 FROM rates r
 inner join
 (SELECT provider_id,
 preffix = max(len(convert(char, prefix_pattern)))
 FROM rates
 WHERE '38050' like convert(char, prefix_pattern) + '%'
 GROUP BY provider_id) g
 on g.provider_id = r.provider_id and
 g.preffix = max(len(convert(char, r.prefix_pattern)))


yurchello

И еще одна поправочка
SELECT distinct r.*
 FROM rates r
 inner join
 (SELECT provider_id,
 preffix = max(len(convert(char, prefix_pattern)))
 FROM rates
 WHERE '38050' like convert(char, prefix_pattern) + '%' and
 len('38050') >= len(convert(char, prefix_pattern))
 GROUP BY provider_id) g
 on g.provider_id = r.provider_id and
 g.preffix = max(len(convert(char, r.prefix_pattern)))


yurchello

?
DECLARE @rates table (provider_id int ,prefix_pattern varchar(<b>255</b>))
INSERT INTO @rates
SELECT <b>1</b>, '380' UNION ALL
SELECT <b>1</b>, '38050' UNION ALL
SELECT <b>2</b>, '3' UNION ALL
SELECT <b>2</b>, '380' UNION ALL
SELECT <b>2</b>, '3805' 

SELECT * FROM @rates

DECLARE @prefix varchar(<b>255</b>)
SELECT @prefix='38050'


SELECT r.provider_id,
max(r.prefix_pattern)
FROM @rates r
WHERE @prefix LIKE r.prefix_pattern + '%'
GROUP BY r.provider_id


yurchello

SELECT distinct r.* FROM rates r inner join (SELECT provider_id, preffix = max(len(convert(char, prefix_pattern))) FROM rates WHERE '38050' like convert(char, prefix_pattern) + '%' and len('38050') >= len(convert(char, prefix_pattern)) GROUP BY provider_id) g on g.provider_id = r.provider_id and g.preffix = max(len(convert(char, r.prefix_pattern)))
немного не пойму добавкуНеправильно - во первых это уже два вложеных запроса и тормоза начинаются.во вторых - проблема что если '38050' нету, но есть 38 ? 38050 при отсуцтвие точного указания на 38050 может проходить через 38 (просто тарифы разные на эти префиксы)


yurchello

BugsBunny - неправильно по той же причине !!! WHERE @prefix LIKE r.prefix_pattern + '%' неподходит!так как при 380 в этом условии не выберится 38 (при отсуцтвии 380)я написал же PATINDEX(RTRIM(LTRIM(CONVERT(char,r.prefix_pattern)))+ '%', ltrim(rtrim(u.u_prefix_pattern)))= 1


yurchello

так как при 380 в этом условии не выберится 38 (при отсуцтвии 380)
What is 38? Did you see test data? Please provide test data like these
DECLARE @rates table (provider_id int ,prefix_pattern varchar(<b>255</b>))
INSERT INTO @rates
SELECT <b>1</b>, '380' UNION ALL
SELECT <b>1</b>, '38050' UNION ALL
SELECT <b>2</b>, '3' UNION ALL
SELECT <b>2</b>, '380' UNION ALL
SELECT <b>2</b>, '3805' 
SELECT * FROM @rates
and provide what should be in resultset, like
DECLARE @prefix varchar(<b>255</b>)
SELECT @prefix='38050'

SELECT...


<b>1</b>	<b>38050</b>
<b>2</b>	<b>3805</b>


yurchello

я так решил, но знаю что раком - запрос при 1500 префиксах и 2 провайдерах работает почти минуту
SELECT u.u_prefix_pattern,
 r.prefix_pattern,
 
FROM unique_prov_prefixes as u
JOIN rates as r on 
 PATINDEX(RTRIM(LTRIM(CONVERT(char,r.prefix_pattern)))+ '%', ltrim(rtrim(u.u_prefix_pattern)))= <b>1</b>
 and len(r.prefix_pattern)=(select max(len(prefix_pattern)) from rates as r2 where 
 PATINDEX(RTRIM(LTRIM(CONVERT(char,r2.prefix_pattern)))+ '%', ltrim(rtrim(u.u_prefix_pattern)))= <b>1</b> and r2.provider_id=r.provider_id)


yurchello

What is 38? Did you see test data? Please provide test data like theseand provide what should be in resultset, like
Да - совершенно верно. Приношу свои извинения, так работает. Но поле MAX() оказывается делает тоже подзапрос и поэтому мой способ и ваш занимает почти одинаковые промежутки времени - около минуты, но всё же ваш способ на в среднем 10 секунд быстрее. Без сомнения версия с группировкой более приемема так как проще и понятнее. СПАСИБО!!!


yurchello

BugsBunny - неправильно по той же причине !!! WHERE @prefix LIKE r.prefix_pattern + '%' неподходит!так как при 380 в этом условии не выберится 38 (при отсуцтвии 380)я написал же PATINDEX(RTRIM(LTRIM(CONVERT(char,r.prefix_pattern)))+ '%', ltrim(rtrim(u.u_prefix_pattern)))= 1
тут я был не прав так как проверял WHERE r.prefix_pattern + '%' LIKE @prefix результат зависит от перемены местами операндов.


yurchello

Now you can try to open another topic : "Help to optimize query" :)
DECLARE @rates table (provider_id int ,prefix_pattern varchar(<b>255</b>))
INSERT INTO @rates
SELECT <b>1</b>, '380' UNION ALL
SELECT <b>1</b>, '38050' UNION ALL
SELECT <b>2</b>, '3' UNION ALL
SELECT <b>2</b>, '380' UNION ALL
SELECT <b>2</b>, '3805' 

SELECT * FROM @rates

SELECT DISTINCT u.prefix_pattern, r.provider_id,
max(r.prefix_pattern)
FROM @rates r,@rates u
WHERE u.prefix_pattern LIKE r.prefix_pattern + '%'
GROUP BY u.prefix_pattern,r.provider_id 
ORDER BY u.prefix_pattern


yurchello

Как-то не очень задача сформулирована, лучше таки это делать по правилам.
if object_id('rates','U') is not null drop table rates
create table rates (ii int, pp varchar(<b>8</b>))
go
insert into rates values (<b>11</b>, '3')
insert into rates values (<b>11</b>, '38')
insert into rates values (<b>11</b>, '380')
insert into rates values (<b>11</b>, '389')
insert into rates values (<b>21</b>, '5')
insert into rates values (<b>21</b>, '53')
insert into rates values (<b>21</b>, '537')
insert into rates values (<b>21</b>, '5370')
insert into rates values (<b>21</b>, '5373')

select r.ii, r.pp from rates r inner join 
 (select ii, max(ii*<b>1000000000</b>.<b>0</b>+len(pp)) as ff from rates group by ii) as q
 on r.ii=q.ii and r.ii*<b>1000000000</b>.<b>0</b>+len(r.pp) = q.ff


yurchello

Как-то не очень задача сформулирована, лучше таки это делать по правилам.
Может оно и правильно - но это уже привязка к количеству, к объемам - не думаю что это верные правила. Это таже самая группировка по провайдеру и нахождение max(length). Но задача не ограничивается нахождением всех префиксов у которых равны колво цифр.
Now you can try to open another topic : "Help to optimize query" :)DECLARE @rates table (provider_id int ,prefix_pattern varchar(255))INSERT INTO @ratesSELECT 1, '380' UNION ALLSELECT 1, '38050' UNION ALLSELECT 2, '3' UNION ALLSELECT 2, '380' UNION ALLSELECT 2, '3805' SELECT * FROM @ratesSELECT DISTINCT u.prefix_pattern, r.provider_id,max(r.prefix_pattern)FROM @rates r,@rates uWHERE u.prefix_pattern LIKE r.prefix_pattern + '%'GROUP BY u.prefix_pattern,r.provider_id ORDER BY u.prefix_pattern
Да.. дивно. И так оказывается можно !... Но у меня следующая трудность с группировкой. Дело в том что в таблице есть ещё стоимость минуты разговора для каждого префикса - unit_charge.Мне нужно выщитать долю стоимости минуты по выбранному префиксу выбранного провайдера от максимальной стоимости минуты по этому префиксу от всех провайдеров.Запрос типа SELECT DISTINCT u.prefix_pattern, r.provider_id, max(r.prefix_pattern) as cur_pref, ( select max(rr.unit_charge) from rates as rr where rr.prefix_pattern=cur_pref)as max_charg FROM @rates r,@rates uWHERE u.prefix_pattern LIKE r.prefix_pattern + '%'GROUP BY u.prefix_pattern,r.provider_id ORDER BY u.prefix_patternнеканает, Хотя в SyBase он бы работал.


yurchello

Мне нужно выщитать долю стоимости минуты по выбранному префиксу выбранного провайдера от максимальной стоимости минуты по этому префиксу от всех провайдеров
is not hte same as
( select max(rr.unit_charge) from rates as rr where rr.prefix_pattern=cur_pref)as max_charg
For just MAX it's easy:
DECLARE @rates table (provider_id int ,prefix_pattern varchar(<b>255</b>),unit_charge money)
INSERT INTO @rates
SELECT <b>1</b>, '380', <b>1</b>.<b>38</b> UNION ALL
SELECT <b>1</b>, '38050', <b>1</b>.<b>3805</b> UNION ALL
SELECT <b>2</b>, '3', <b>2</b>.<b>3</b> UNION ALL
SELECT <b>2</b>, '380', <b>2</b>.<b>38</b> UNION ALL
SELECT <b>2</b>, '3805', <b>2</b>.<b>3805</b> 

SELECT * FROM @rates

SELECT DISTINCT u.prefix_pattern, r.provider_id,
max(r.prefix_pattern)
FROM @rates r,@rates u
WHERE u.prefix_pattern LIKE r.prefix_pattern + '%'
GROUP BY u.prefix_pattern,r.provider_id 
ORDER BY u.prefix_pattern

SELECT DISTINCT u.prefix_pattern, r.provider_id,
max(r.prefix_pattern) as closest_prefix_pattern,
u.max_unit_charge
FROM @rates r,(SELECT prefix_pattern,MAX(unit_charge) AS max_unit_charge FROM @rates GROUP BY prefix_pattern) u
WHERE u.prefix_pattern LIKE r.prefix_pattern + '%'
GROUP BY u.prefix_pattern,r.provider_id,u.max_unit_charge
ORDER BY u.prefix_pattern


yurchello

DISTINCT is not needed in last query. I just forgot to remove it.


yurchello

SELECT DISTINCT u.prefix_pattern, r.provider_id,max(r.prefix_pattern) as closest_prefix_pattern,u.max_unit_chargeFROM @rates r,(SELECT prefix_pattern,MAX(unit_charge) AS max_unit_charge FROM @rates GROUP BY prefix_pattern) uWHERE u.prefix_pattern LIKE r.prefix_pattern + '%'GROUP BY u.prefix_pattern,r.provider_id,u.max_unit_chargeORDER BY u.prefix_pattern
это не совсем то - не просто максимальну по префиксу от всех провайдеров. а брать цену того префикса который быбран max(r.prefix_pattern)closest_prefix_pattern .. как это сделать ? подселектом типа select unit_charge from rates where prefix_pattern=closest_prefix_pattern and provider_id=r.provider_id??? и потом находит процентную долю этой стоимости от максимальной - 100-unit_charge*100/max(r.prefix_pattern) (при max(r.prefix_pattern) <>0)эну нужно для того что к этой доле буду добавлятся други достоинства провайдера


yurchello

это не совсем то - не просто максимальну по префиксу от всех провайдеров
***. I know that and I mentioned that
is not the same asFor just MAX it's easy...
So, if you want to get final numbers, you have to add another select (meaning 3 in total).See youself:fist select: to get max_unit_charge by prefixsecond: to get closest_prefix by providerthird: to link results of two together
DECLARE @rates table (provider_id int ,prefix_pattern varchar(<b>255</b>),unit_charge money)
INSERT INTO @rates
SELECT <b>1</b>, '380', <b>1</b>.<b>38</b> UNION ALL
SELECT <b>1</b>, '38050', <b>1</b>.<b>3805</b> UNION ALL
SELECT <b>2</b>, '3', <b>2</b>.<b>3</b> UNION ALL
SELECT <b>2</b>, '380', <b>2</b>.<b>38</b> UNION ALL
SELECT <b>2</b>, '3805', <b>2</b>.<b>3805</b> 

SELECT * FROM @rates

<i>--to link results of two together
</i>SELECT r2.*,r1.max_unit_charge,CASE WHEN r1.max_unit_charge<><b>0</b> THEN r2.unit_charge/r1.max_unit_charge END AS [достоинства провайдера]
FROM
	@rates r2
	INNER JOIN
	(<i>--to get closest_prefix by provider
</i>	SELECT u.prefix_pattern, r.provider_id,
	max(r.prefix_pattern) as closest_prefix_pattern,
	u.max_unit_charge
	FROM 
		@rates r,
		(<i>--fist select: to get max_unit_charge by prefix
</i>			SELECT prefix_pattern,MAX(unit_charge) AS max_unit_charge 
			FROM @rates 
			GROUP BY prefix_pattern
		) u
	WHERE u.prefix_pattern LIKE r.prefix_pattern + '%'
	GROUP BY u.prefix_pattern,r.provider_id,u.max_unit_charge
	) r1 
		ON r2.provider_id=r1.provider_id AND r2.prefix_pattern=r1.closest_prefix_pattern
ORDER BY r1.prefix_pattern