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

Откуда:
Сообщений: 15
Всем доброе утро!
Прошу вас о помощи в решении задачи. Суть задачи - необходимо определить актуальный статус клиентов (оплата/просрочка) , а также минимальную дату вхождения в этот статус. Например, клиент Иванов на протяжении 6 месяцев находился в таких статусах: 15.01.2012 - оплата , 15.02.2012 - просрочка, 15.03.2012 - оплата, 15.04.2012 - оплата, 15.05.2012 - просрочка, 15.06.2012 - просрочка.
На выходе результат по столбцам будет следующим: Иванов/Просрочка/15.05.2012.
28 янв 13, 12:04    [13838208]     Ответить | Цитировать Сообщить модератору
 Re: Определение минимальной даты нахождения клиента в сегменте  [new]
Alchenok
Member

Откуда:
Сообщений: 15
Соориентируйте в каком направлении двигаться..
28 янв 13, 12:26    [13838388]     Ответить | Цитировать Сообщить модератору
 Re: Определение минимальной даты нахождения клиента в сегменте  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
В сторону выполнения пункта 4 и 6 из https://www.sql.ru/forum/actualthread.aspx?tid=127456
28 янв 13, 12:28    [13838402]     Ответить | Цитировать Сообщить модератору
 Re: Определение минимальной даты нахождения клиента в сегменте  [new]
iap
Member

Откуда: Москва
Сообщений: 47000
Alchenok,

действительно, Вам словами всё описать? Может, даже, в стихах?
И версию сервера не назвали. А от этого зависит вариант решения,
которых очень много (поиск по форуму это подтвердит).
28 янв 13, 12:33    [13838427]     Ответить | Цитировать Сообщить модератору
 Re: Определение минимальной даты нахождения клиента в сегменте  [new]
_djХомяГ
Guest
для >=2005
set dateformat dmy
declare @t table 
(
 date        smalldatetime,
 status      int
 )
 
 insert into @t 
 select '15.02.2012',0  ----- 0 просрочка 
 union all
 select '15.03.2012',1  ------1 оплата 
 union all
 select '15.04.2012',1
 union all
 select '15.05.2012',0
 union all
 select '15.06.2012',0
 
 ;with cte 
 as
 (
    row_number() over (order by date) - row_number() over (partition by status order by date) as GR
 ,* from @t
 )
 select top 1 MIDATE,STATUS
 from 
 (
 select min(date) MIDATE ,max(date) MADATE,GR,status
 from cte 
 group by gr,status
 ) t order by MIDATE desc
 
28 янв 13, 12:39    [13838460]     Ответить | Цитировать Сообщить модератору
 Re: Определение минимальной даты нахождения клиента в сегменте  [new]
aleks2
Guest
declare @t table(clientId int, date datetime, state char(1), primary key clustered(clientId, date))

insert @t
select 1, '20120115', 'о'
union all
select 1, '20120215', 'п'
union all
select 1, '20120315', 'о'
union all
select 1, '20120415', 'о'
union all
select 1, '20120515', 'п'
union all
select 1, '20120615', 'п'
union all
select 2, '20120115', 'п'
union all
select 2, '20120215', 'щ'
union all
select 2, '20120315', 'п'
union all
select 2, '20120415', 'о'
union all
select 2, '20120515', 'о'
union all
select 2, '20120615', 'о'


select *
from
	(
             select  *, row_number() over(partition by clientId order by date asc) n
	from
		(select *
		, row_number() over(partition by clientId, state order by date desc) rn 
		, row_number() over(partition by clientId  order by date desc) nr
		from @t 
		) X
	where rn = nr
	) Y
where n = 1
28 янв 13, 12:50    [13838545]     Ответить | Цитировать Сообщить модератору
 Re: Определение минимальной даты нахождения клиента в сегменте  [new]
Добрый Э - Эх
Guest
+ < Как вариант под MS SQl Server 2012:
declare @t table(clientId int, date datetime, state char(1), primary key clustered(clientId, date))

insert @t
select 1, '20120115', 'o'
union all
select 1, '20120215', 'p'
union all
select 1, '20120315', 'o'
union all
select 1, '20120415', 'o'
union all
select 1, '20120515', 'p'
union all
select 1, '20120615', 'p'
union all
select 2, '20120115', 'p'
union all
select 2, '20120215', 'm'
union all
select 2, '20120315', 'p'
union all
select 2, '20120415', 'o'
union all
select 2, '20120515', 'o'
union all
select 2, '20120615', 'o'

select clientid, max(state) as last_state, min(date) as start_date 
  from (
         select *, sum(start_of_group) over(partition by clientid order by date desc) as grp_id 
           from (
                  select *, case when state = lag(state) over(partition by clientid order by date desc) 
                              then 0 else 1 
                            end as start_of_group
                    from @t
                ) v
       ) v
 where grp_id = 1
 group by clientid
online проверка на sqlfiddle.com
28 янв 13, 20:33    [13841592]     Ответить | Цитировать Сообщить модератору
 Re: Определение минимальной даты нахождения клиента в сегменте  [new]
Reinmar
Member

Откуда:
Сообщений: 1
aleks2, Спасибо!
29 янв 13, 03:44    [13842360]     Ответить | Цитировать Сообщить модератору
 Re: Определение минимальной даты нахождения клиента в сегменте  [new]
Добрый Э - Эх
Guest
aleks2, top 1 with ties решил не прикручивать? Так бы сэкономил один уровень вложенности:
+ < с TOP ... WITH TIES:
declare @t table(clientId int, date datetime, state char(1), primary key clustered(clientId, date))

insert @t
select 1, '20120115', 'o'
union all
select 1, '20120215', 'p'
union all
select 1, '20120315', 'o'
union all
select 1, '20120415', 'o'
union all
select 1, '20120515', 'p'
union all
select 1, '20120615', 'p'
union all
select 2, '20120115', 'p'
union all
select 2, '20120215', 'm'
union all
select 2, '20120315', 'p'
union all
select 2, '20120415', 'o'
union all
select 2, '20120515', 'o'
union all
select 2, '20120615', 'o'

select top 1 with ties *
  from (
         select *
              , row_number() over(partition by clientId, state order by date desc) rn 
              , row_number() over(partition by clientId  order by date desc) nr
           from @t 
       ) X
 where rn = nr
 order by row_number() over(partition by clientId order by date asc)
29 янв 13, 06:32    [13842396]     Ответить | Цитировать Сообщить модератору
 Re: Определение минимальной даты нахождения клиента в сегменте  [new]
aleks2
Guest
Добрый Э - Эх
aleks2, top 1 with ties решил не прикручивать? Так бы сэкономил один уровень вложенности:

Мине уровней не жалко.
29 янв 13, 06:47    [13842401]     Ответить | Цитировать Сообщить модератору
 Re: Определение минимальной даты нахождения клиента в сегменте  [new]
Добрый Э - Эх
Guest
Добрый Э - Эх
+ < Как вариант под MS SQl Server 2012:
declare @t table(clientId int, date datetime, state char(1), primary key clustered(clientId, date))

insert @t
select 1, '20120115', 'o'
union all
select 1, '20120215', 'p'
union all
select 1, '20120315', 'o'
union all
select 1, '20120415', 'o'
union all
select 1, '20120515', 'p'
union all
select 1, '20120615', 'p'
union all
select 2, '20120115', 'p'
union all
select 2, '20120215', 'm'
union all
select 2, '20120315', 'p'
union all
select 2, '20120415', 'o'
union all
select 2, '20120515', 'o'
union all
select 2, '20120615', 'o'

select clientid, max(state) as last_state, min(date) as start_date 
  from (
         select *, sum(start_of_group) over(partition by clientid order by date desc) as grp_id 
           from (
                  select *, case when state = lag(state) over(partition by clientid order by date desc) 
                              then 0 else 1 
                            end as start_of_group
                    from @t
                ) v
       ) v
 where grp_id = 1
 group by clientid
online проверка на sqlfiddle.com


Чего-то подумал, что и у себя неплохо было бы избавиться от лишнего...
+ < ... lead + top N ties, и ничего лишнего :)
declare @t table(clientId int, date datetime, state char(1), primary key clustered(clientId, date))

insert @t
select 1, '20120115', 'o'
union all
select 1, '20120215', 'p'
union all
select 1, '20120315', 'o'
union all
select 1, '20120415', 'o'
union all
select 1, '20120515', 'p'
union all
select 1, '20120615', 'p'
union all
select 2, '20120115', 'p'
union all
select 2, '20120215', 'm'
union all
select 2, '20120315', 'p'
union all
select 2, '20120415', 'o'
union all
select 2, '20120515', 'o'
union all
select 2, '20120615', 'o'
--
--
select top 1 with ties *
  from (
         select *, case when state = lead(state) over(partition by clientid order by date desc) 
                     then 0 else 1 
                   end as end_of_group
           from @t
       ) v
 where end_of_group = 1
 order by row_number() over(partition by clientid order by date desc)
29 янв 13, 09:33    [13842660]     Ответить | Цитировать Сообщить модератору
 Re: Определение минимальной даты нахождения клиента в сегменте  [new]
Alchenok
Member

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

Спасибо вам огромное за помощь! Получилось! Ура-ура-ура!!!
29 янв 13, 18:36    [13846622]     Ответить | Цитировать Сообщить модератору
 Re: Определение минимальной даты нахождения клиента в сегменте  [new]
Alchenok
Member

Откуда:
Сообщений: 15
Извините за некорректно изложенную суть задачи. MS SQL Server - 2005.
29 янв 13, 18:39    [13846644]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить