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

Откуда:
Сообщений: 13
Есть запрос, который должен посчитать, на какие месяцы у нас есть запись в таблице и сохранить все это в текстовую строку в другой таблице. Исходя из этого я потом буду брать лояльность клиента.

Запрос должен выводить:
"Атлант" ООО 011111111111
"Варяг" ООО 0
"Хознужды" 0111111111111

Запрос выводит:
"Атлант" ООО NULL
"Атлант" ООО 011111111111
"Варяг" ООО 0


Собственно запрос:
CREATE TABLE #T4 (Client_net varchar(80), loyalty varchar(80))
DECLARE @cntr int, @n int, @n1 int, @n2 int, @n3 int, @client_net varchar(80), @client_net2 varchar(80), @client_net3 varchar(80), @Datestr varchar(80)
DECLARE Date_Cursor SCROLL CURSOR FOR
SELECT #Temp3.art_month, #Temp3.clent_net
FROM #Temp3
Where #Temp3.clent_net LIKE '%Атлант%' OR #Temp3.clent_net LIKE '%Варяг%' OR #Temp3.clent_net LIKE '%Хознужды%'
Group BY #Temp3.art_month, #Temp3.clent_net
ORDER BY  #Temp3.clent_net, #Temp3.art_month DESC
OPEN Date_Cursor
FETCH NEXT FROM Date_Cursor
into @n2, @client_net2 
WHILE @@FETCH_STATUS = 0
BEGIN
IF @client_net2 = @client_net 
BEGIN
SET @n = @n2 - @n1
SET @Datestr=
CASE WHEN @n = 1 THEN
@Datestr+'1'
 WHEN @n = 2 THEN
@Datestr+'01'
 WHEN @n = 3 THEN
@Datestr+'001'
 WHEN @n = 4 THEN
@Datestr+'0001'
 WHEN @n = 5 THEN
@Datestr+'00001'
 WHEN @n = 6 THEN
@Datestr+'000001'
 WHEN @n = 7 THEN
@Datestr+'0000001'
 WHEN @n = 8 THEN
@Datestr+'00000001'
 WHEN @n = 9 THEN
@Datestr+'000000001'
 WHEN @n = 10 THEN
@Datestr+'0000000001'
 WHEN @n = 11 THEN
@Datestr+'00000000001'
 WHEN @n = 12 THEN
@Datestr+'000000000001'
ELSE @Datestr
END
PRINT @n
PRINT @Datestr
end
ELSE
begin
PRINT @Datestr
INSERT INTO #t4 (Client_net, loyalty) VALUES (@client_net2, @Datestr)
Set @Datestr ='0'
end


Если что -- прошу простить, первый раз работаю с FETCH и CURSOR
24 янв 13, 10:29    [13819876]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом FETCH  [new]
aleks2
Guest
Болееш чтоле? Императивной лихорадкой...
24 янв 13, 10:34    [13819904]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом FETCH  [new]
aleks2
Guest
Помощь голодающим поволжья...
SET @Datestr = @Datestr + right(''000000000001', @n)

вместо психоделического case.
24 янв 13, 10:39    [13819928]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом FETCH  [new]
Георгий Ушаков
Member

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

Точно ведь... Спасибо огромное. Я вообще не SQL-щик ни разу, учетчик обычный, который когда-то что-то учил, просто потребовалось вот... :)
24 янв 13, 10:53    [13819993]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом FETCH  [new]
Glory
Member

Откуда:
Сообщений: 104751
Как этот цикл вообще может закончится, если в его теле нет команды передвижения по курсору?
24 янв 13, 10:56    [13820010]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом FETCH  [new]
Георгий Ушаков
Member

Откуда:
Сообщений: 13
Glory,
И правда чтоли, не все отправил.

CREATE TABLE #T4 (Client_net varchar(80), loyalty varchar(80))
DECLARE @cntr int, @n int, @n1 int, @n2 int, @n3 int, @client_net varchar(80), @client_net2 varchar(80), @client_net3 varchar(80), @Datestr varchar(80)
DECLARE Date_Cursor SCROLL CURSOR FOR
SELECT #Temp3.art_month, #Temp3.clent_net
FROM #Temp3
Where #Temp3.clent_net LIKE '%Атлант%' OR #Temp3.clent_net LIKE '%Варяг%' OR #Temp3.clent_net LIKE '%Хознужды%'
Group BY #Temp3.art_month, #Temp3.clent_net
ORDER BY  #Temp3.clent_net, #Temp3.art_month DESC
OPEN Date_Cursor
FETCH NEXT FROM Date_Cursor
into @n2, @client_net2 
WHILE @@FETCH_STATUS = 0
BEGIN
IF @client_net2 = @client_net 
BEGIN
SET @n = @n2 - @n1
SET @Datestr=  @Datestr + right('000000000001', @n)

PRINT @n
PRINT @Datestr
end
ELSE
begin
PRINT @Datestr
INSERT INTO #t4 (Client_net, loyalty) VALUES (@client_net2, @Datestr)
Set @Datestr ='0'
end

FETCH NEXT FROM Date_Cursor
FETCH PRIOR FROM Date_Cursor
into @n2, @client_net2 
PRINT @client_net2
IF @@Fetch_Status = 0
Begin
FETCH NEXT FROM Date_Cursor
into @n1, @client_net 



End
END

CLOSE Date_Cursor
DEALLOCATE Date_Cursor 


Так точнее будет.
24 янв 13, 10:59    [13820045]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом FETCH  [new]
aleks2
Guest
declare @t table(art_month int, clent_net varchar(16))

insert @t values( 1, 'Атлант')
insert @t values( 3, 'Атлант')
insert @t values( 7, 'Атлант')
insert @t values( 3, 'Варяг')
insert @t values( 7, 'Варяг')
insert @t values( 11,'Варяг')
insert @t values( 11,'Варяг')
insert @t values( 7, 'Хознужды')
insert @t values( 3, 'Хознужды')
insert @t values( 7, 'Хознужды')
insert @t values( 11,'Хознужды')
insert @t values( 12,'Хознужды')

;with
myselect as ( SELECT art_month, clent_net
                      FROM @t
                      Where clent_net LIKE '%Атлант%' OR clent_net LIKE '%Варяг%' OR clent_net LIKE '%Хознужды%'
)
,
clients as ( select DISTINCT clent_net  FROM myselect )
,
months as (                               select 1 month
                                   union all select 2
                                   union all select 3
                                   union all select 4
                                   union all select 5
                                   union all select 6
                                   union all select 7
                                   union all select 8
                                   union all select 9
                                   union all select 10
                                   union all select 11
                                   union all select 12
                  )
,
-- вам чо, для каждого клиента clent_net поставить в строчке из 12 нулей '000000000000'
-- единички на место месяцев, которые есть в выборке myselect для клиента?
[01] as (select c.clent_net, m.month
                  , case 
                       when exists(select * from myselect s where s.clent_net = c.clent_net and s.art_month = m.month ) then '1'
                       else '0'
                    end [01]
              from clients c cross join months m
          )

select c.clent_net
       , replace( (select [01] as [data()] from [01] where c.clent_net = [01].clent_net order by [01].month for xml path('')), ' ','')
  from clients c
24 янв 13, 12:37    [13820784]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом FETCH  [new]
Георгий Ушаков
Member

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

Благодарю. Мне не совсем это надо сделать, просто лучше способов что-то мне не придумалось. Мне нужно посчитать лояльность каждого клиента. Лояльность -- это количество непрерывных месяцев с продажами у каждого клиента, по текущий и с периодом в год. У меня есть номера месяцев с продажами, потом можно будет отрезать от начала строки месяцы этого года и переносить их в конец. И считать число единиц в строке до первого нуля.
25 янв 13, 01:14    [13824897]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом FETCH  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Георгий Ушаков
aleks2,

Благодарю. Мне не совсем это надо сделать, просто лучше способов что-то мне не придумалось. Мне нужно посчитать лояльность каждого клиента. Лояльность -- это количество непрерывных месяцев с продажами у каждого клиента, по текущий и с периодом в год. У меня есть номера месяцев с продажами, потом можно будет отрезать от начала строки месяцы этого года и переносить их в конец. И считать число единиц в строке до первого нуля.
+ запрос
;with 
client as (select * from (values(1,'Атлант'),(2,'Варяг'),(3,'Хознужды'))tmp(id,name)),
sales as (select * from (values
	(1,1),(2,1),(7,1),
	(3,2),(7,2),(11,2),(12,2),
	(7,3),(8,3),(9,3),(11,3),(12,3),
	(2,1),(7,2),(2,1),(9,3),(7,3)
	)tmp(month,client))
-- рассчитать лояльность клиента в виде:
-- клиент, месяц первой продажи, число месяцев с ежемесячными продажами
-- т.е. получить: (1,1,2),(1,7,1),(2,3,1),(2,7,1),(2,11,2),(3,7,3),(3,11,2)
----------select c.name, s.month
----------	from sales s 
----------	join client c on c.id=s.client
----------	order by c.name, s.month
,recurs as (
	select distinct s.client,s.month, s.month last, 1 loyal
		from sales s
		left join sales p on p.client=s.client and p.month=s.month-1
		where p.month is null
	union all
	select r.client, r.month, s.month, r.loyal+1
		from recurs r
		join sales s on s.client=r.client and s.month=r.last+1
	)
,loyal as (
	select distinct *, max(loyal) OVER (PARTITION BY client,month) loyality
		from recurs r
	) 
select l.client, c.name, l.month, l.loyality
	from loyal l
	join client c on c.id=l.client
	where l.loyal=l.loyality
	order by l.loyality desc, l.month desc, l.client
результат
client      name     month       loyality
----------- -------- ----------- -----------
3 Хознужды 7 3
2 Варяг 11 2
3 Хознужды 11 2
1 Атлант 1 2
1 Атлант 7 1
2 Варяг 7 1
2 Варяг 3 1
25 янв 13, 03:22    [13824986]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом FETCH  [new]
Георгий Ушаков
Member

Откуда:
Сообщений: 13
Cygapb-007,

Спасибо, курю ваш код. Сразу он не запустился, буду разбирать его по полочкам.
25 янв 13, 03:49    [13824993]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом FETCH  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
Георгий Ушаков
Cygapb-007,

Спасибо, курю ваш код. Сразу он не запустился, буду разбирать его по полочкам.
Ээээ... а что пишет в ошибках?
+ поправочка кода для больших объемов данных
;with 
client as (select * from (values(1,'Атлант'),(2,'Варяг'),(3,'Хознужды'))tmp(id,name)),
sales as (select * from (values
	(1,1),(2,1),(7,1),
	(3,2),(7,2),(11,2),(12,2),
	(7,3),(8,3),(9,3),(11,3),(12,3),
	(2,1),(7,2),(2,1),(9,3),(7,3)
	)tmp(month,client))
-- рассчитать лояльность клиента в виде:
-- клиент, месяц первой продажи, количество месяцев непрерывных ежемесячных продаж
,recurs as (
	select distinct s.client,s.month, s.month last, 1 loyal
		from sales s
		left join sales p on p.client=s.client and p.month=s.month-1
		where p.month is null
	union all
	select r.client, r.month, s.last, r.loyal+1
		from recurs r
		--join sales s on s.client=r.client and s.month=r.last+1
		-- уйти от повторений непосредственно в рекурсии
		cross apply(
			select s.month last, ROW_NUMBER() OVER (ORDER BY s.month) rn
				from sales s
				where s.client=r.client and s.month=r.last+1
			) s
		where rn=1
	)
,loyal as (
	select /*distinct*/ *, max(loyal) OVER (PARTITION BY client,month) loyality
		from recurs r
	) 
select l.client, c.name, l.month, l.loyality
	from loyal l
	join client c on c.id=l.client
	where l.loyal=l.loyality
	order by l.loyality desc, l.month desc, l.client
25 янв 13, 03:58    [13824995]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом FETCH  [new]
Георгий Ушаков
Member

Откуда:
Сообщений: 13
Cygapb-007,
автор
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'WITH'.

Ну то есть я понимаю, что там еще какой-то синтаксис нужен, вот и разбираюсь, что такое WITH и как он работает.
25 янв 13, 04:02    [13824999]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом FETCH  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
он вроде с 2008к2 работает, не ниже (
25 янв 13, 04:06    [13825001]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом FETCH  [new]
Георгий Ушаков
Member

Откуда:
Сообщений: 13
Cygapb-007,

У меня SQL SERVER 8.0 ))) Чур какашками не кидаться, софтина, которая с ним работает другие версии сервака не воспринимает совсем. Проверяли.
25 янв 13, 04:09    [13825002]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом FETCH  [new]
aleks2
Guest
Георгий Ушаков
Благодарю. Мне не совсем это надо сделать, просто лучше способов что-то мне не придумалось. Мне нужно посчитать лояльность каждого клиента. Лояльность -- это количество непрерывных месяцев с продажами у каждого клиента, по текущий и с периодом в год.

Блин, когда эти наивные чукотские въюноши перестанут играть в партизанов?

Георгий Ушаков
У меня есть номера месяцев с продажами, потом можно будет отрезать от начала строки месяцы этого года и переносить их в конец. И считать число единиц в строке до первого нуля.

Охренел, чтоле?
set nocount on
declare @t table(month int, client varchar(16))

insert @t values( 1, 'Атлант')
insert @t values( 3, 'Атлант')
insert @t values( 4, 'Атлант')
insert @t values( 5, 'Атлант')
insert @t values( 7, 'Атлант')
insert @t values( 3, 'Варяг')
insert @t values( 7, 'Варяг')
insert @t values( 10,'Варяг')
insert @t values( 11,'Варяг')
insert @t values( 11,'Варяг')
insert @t values( 7, 'Хознужды')
insert @t values( 3, 'Хознужды')
insert @t values( 7, 'Хознужды')
insert @t values( 11,'Хознужды')
insert @t values( 12,'Хознужды')

declare @ibeg table (n int identity, client varchar(16), month int, primary key clustered(client, month))
declare @iend table (n int identity, client varchar(16), month int, primary key clustered(client, month))

-- начала интервалов непрерывности
insert @ibeg (client, month )
select DISTINCT client, month
from @t T where not exists(select * from @t X where X.client = T.client and X.month=T.month-1)
order by client, month

select * from @ibeg

-- Концы интервалов непрерывности
insert @iend (client, month )
select DISTINCT client, month
from @t T where not exists(select * from @t X where X.client = T.client and X.month=T.month+1)
order by client, month

select * from @iend

-- интервалы
declare @i table (i int identity, client varchar(16), monthB int, monthE int, length int, primary key clustered(client, monthB))
insert @i(client, monthB, monthE, length )
select b.client, b.month, e.month, e.month-b.month+1
from @ibeg b inner join @iend e on b.client=e.client and b.n=e.n
order by b.client, b.month

select * from @i

-- максимальные интервалы
select *
from @i i 
where i.length = (select max(j.length) from @i j where j.client = i.client)
25 янв 13, 06:46    [13825065]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом FETCH  [new]
Георгий Ушаков
Member

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

Спасибо большое, но как я понял -- ваш вариант считает месяцы ТОЛЬКО с 1 по 12-й
А у меня же задача, цитирую
автор
количество непрерывных месяцев с продажами у каждого клиента, по текущий и с периодом в год

Ну то есть если сейчас, например, январь, то месяца должны идти в таком порядке: 2,3,4,5,6,7,8,9,10,11,12,1

А ваш пример, если мы его берем, будет неправильно тут считать интервалы между 1 и 2. 2 тут -- февраль предыдущего года.
Но можно переопределить, да... Чем, видимо, и займусь.
25 янв 13, 08:07    [13825136]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом FETCH  [new]
=]8)
Guest
Георгий Ушаков
aleks2,

Спасибо большое, но как я понял -- ваш вариант считает месяцы ТОЛЬКО с 1 по 12-й
А у меня же задача, цитирую
автор
количество непрерывных месяцев с продажами у каждого клиента, по текущий и с периодом в год

Ну то есть если сейчас, например, январь, то месяца должны идти в таком порядке: 2,3,4,5,6,7,8,9,10,11,12,1

А ваш пример, если мы его берем, будет неправильно тут считать интервалы между 1 и 2. 2 тут -- февраль предыдущего года.
Но можно переопределить, да... Чем, видимо, и займусь.
Достаточно пересчитать месяц по формуле
year(@date)*100+month(@date)
25 янв 13, 10:26    [13825877]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом FETCH  [new]
aleks2
Guest
Георгий Ушаков
aleks2,

А у меня же задача, цитирую
Ну то есть если сейчас, например, январь, то месяца должны идти в таком порядке: 2,3,4,5,6,7,8,9,10,11,12,1



Точно больной. Ты чо не в курсе, как ПРАВИЛЬНО задают месяцы?
12.2012, 01.2013...

ЗЫ. Но рвать зубы через жопу запретить низзя.
25 янв 13, 10:27    [13825882]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом FETCH  [new]
=]8}
Guest
aleks2
Георгий Ушаков
aleks2,

А у меня же задача, цитирую
Ну то есть если сейчас, например, январь, то месяца должны идти в таком порядке: 2,3,4,5,6,7,8,9,10,11,12,1



Точно больной. Ты чо не в курсе, как ПРАВИЛЬНО задают месяцы?
12.2012, 01.2013...

ЗЫ. Но рвать зубы через жопу запретить низзя.
гыыы
А что, воспользоваться алгоритмом по месяцам yyyymm а потом в финальном селекте выдать
select right(@month,2)+'.'+left(@month,4)
религия не позволяет?
25 янв 13, 10:37    [13825944]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом FETCH  [new]
Георгий Ушаков
Member

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

Эта табличка уже ЕСТЬ, вот в чем проблема, ее писал не я)

Ладно, формирую другую, заново.

declare @ibeg table (n int identity, clent_net varchar(80), art_month int primary key clustered(clent_net, art_month))
declare @iend table (n int identity, clent_net varchar(80), art_month int primary key clustered(clent_net, art_month))

-- начала интервалов непрерывности
insert @ibeg (clent_net, art_month)
select DISTINCT clent_net, art_month
from #Temp3 T where not exists(select * from #Temp3 X where X.clent_net = T.clent_net and X.art_month=T.art_month-1)
order by clent_net, art_month

select * from @ibeg <---- Вот в этом месте выскакивает ошибка Violation of PRIMARY KEY constraint 'PK__@ibeg__3CE3C0EF'. Cannot insert duplicate key in object '#3BEF9CB6'.
The statement has been terminated.


При том, что дубликатов там точно нет...
25 янв 13, 11:04    [13826090]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом FETCH  [new]
aleks2
Guest
Георгий Ушаков
aleks2,

Эта табличка уже ЕСТЬ, вот в чем проблема, ее писал не я)

Ладно, формирую другую, заново.

declare @ibeg table (n int identity, clent_net varchar(80), art_month int primary key clustered(clent_net, art_month))
declare @iend table (n int identity, clent_net varchar(80), art_month int primary key clustered(clent_net, art_month))

-- начала интервалов непрерывности
insert @ibeg (clent_net, art_month)
select DISTINCT clent_net, art_month
from #Temp3 T where not exists(select * from #Temp3 X where X.clent_net = T.clent_net and X.art_month=T.art_month-1)
order by clent_net, art_month

select * from @ibeg <---- Вот в этом месте выскакивает ошибка Violation of PRIMARY KEY constraint 'PK__@ibeg__3CE3C0EF'. Cannot insert duplicate key in object '#3BEF9CB6'.
The statement has been terminated.

1. select * from @ibeg тут нипричем. Нарушение уникальности возможно только при вставке.
insert @ibeg (clent_net, art_month)
select DISTINCT clent_net, art_month
...

2. Протерев свой хрустальный шар, я увидел, что varchar(80) - маловато длины будет и у вас есть имена клиентов, различающиеся тока с хвоста...

3. Но ваще то
select DISTINCT clent_net, art_month
from #Temp3 T where not exists(select * from #Temp3 X where X.clent_net = T.clent_net and X.art_month=T.art_month-1)
order by clent_net, art_month


можно и глазками посмотреть?
25 янв 13, 11:11    [13826148]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом FETCH  [new]
aleks2
Guest
Ну и запятую, на фсякий случай, поставьте

declare @ibeg table (n int identity, clent_net varchar(512), art_month int
, primary key clustered(clent_net, art_month))
25 янв 13, 11:25    [13826244]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом FETCH  [new]
Cygapb-007
Member

Откуда:
Сообщений: 1677
=
8)]
Георгий Ушаков
aleks2,

Спасибо большое, но как я понял -- ваш вариант считает месяцы ТОЛЬКО с 1 по 12-й
А у меня же задача, цитирую
пропущено...

Ну то есть если сейчас, например, январь, то месяца должны идти в таком порядке: 2,3,4,5,6,7,8,9,10,11,12,1

А ваш пример, если мы его берем, будет неправильно тут считать интервалы между 1 и 2. 2 тут -- февраль предыдущего года.
Но можно переопределить, да... Чем, видимо, и займусь.
Достаточно пересчитать месяц по формуле
year(@date)*100+month(@date)
уточнение: yy*100+mm даст разрывы между годами, надо использовать yy*12+mm-1, а в финале отображать
right(101+yymm%12,2)+'.'+cast(yymm%12 as varchar(5))
25 янв 13, 11:44    [13826435]     Ответить | Цитировать Сообщить модератору
 Re: Проблема с запросом FETCH  [new]
Георгий Ушаков
Member

Откуда:
Сообщений: 13
aleks2,
Спасибо.

Cygapb-007
Все, в общем, уже написано и работает. Спасибо за совет. Тему можно закрывать.
28 янв 13, 05:11    [13837113]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить