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

Откуда:
Сообщений: 46
Добрый день. Помогите написать запрос.
Есть таблица с показаниями (показания с разными типами - типы:1,2,3) в разрезе месяцев в течении года. Нужно вытащить всех абонентов у которых показания с одинаковым типом предположим тип=2 встречается 5 месяце подряд по ноябрь включительно.

Пример таблицы
+

Абонент Месяц Тип Показания
Иван 01.01.2014 1 100
Саня 01.01.2014 1 150
Саня 01.02.2014 1 250
Иван 01.02.2014 1 100
Саня 01.03.2014 1 300
Иван 01.04.2014 2 400
Иван 01.05.2014 1 500
Саня 01.06.2014 1 600
Иван 01.07.2014 2 700
Иван 01.08.2014 2 800
Иван 01.09.2014 2 900
Саня 01.09.2014 1 450
Иван 01.10.2014 2 1000
Иван 01.11.2014 2 1100
Иван 01.12.2014 2 1200
16 дек 14, 11:24    [17003176]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Glory
Member

Откуда:
Сообщений: 104760
И что у вас не получилось ?
16 дек 14, 11:28    [17003212]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
ibrasul
Member

Откуда:
Сообщений: 46
я вытаскиваю количество записей в диапазоне от 01,07,2014 по 01,11,2014 по пользователю, где тип беру равный 2. Но это не совсем верно мне кажется. Хочу еще варианты как это можно сделать
16 дек 14, 11:30    [17003229]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1873
https://www.sql.ru/forum/127456/rekomendacii-po-oformleniu-soobshheniy-v-forume
16 дек 14, 11:57    [17003459]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
aleks2
Guest
declare @b datetime = ' 20140701';
declare @e datetime = ' 20141101';

with
months as (select @b as month1, dateadd(month, 1, @b ) as month31
           union all
           select dateadd(month, 1, month1 ) as month1, dateadd(month, 2, month1 ) as month31
             from months 
             where dateadd(month, 1, month1 )<=@e
           )

select * 
  from [Пример таблицы] t
    where Тип = 2 and Месяц between @b and @e
      and not exists( select * from [Пример таблицы] tt right outer join months on tt.Тип = t.Тип and tt.Абонент = t.Абонент and month1 <= Месяц and Месяц < month31 
                        where tt.Тип is null 
                    );
16 дек 14, 12:18    [17003590]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Wlr-l
Member

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

С использованием оконных функций примерно так:

declare @d1 date = '20140101',
        @d2 date = '20141201',
        @cnt int = 5;

with A as (
  select *
    from (values ('Иван', '01.01.2014', 1, 100),
                 ('Саня', '01.01.2014', 1, 150),
                 ('Саня', '01.02.2014', 1, 250),
                 ('Иван', '01.02.2014', 1, 100),
                 ('Саня', '01.03.2014', 1, 300),
                 ('Иван', '01.04.2014', 2, 400),
                 ('Иван', '01.05.2014', 1, 500),
                 ('Саня', '01.06.2014', 1, 600),
                 ('Иван', '01.07.2014', 2, 700),
                 ('Иван', '01.08.2014', 2, 800),
                 ('Иван', '01.09.2014', 2, 900),
                 ('Саня', '01.09.2014', 1, 450),
                 ('Иван', '01.10.2014', 2, 1000),
                 ('Иван', '01.11.2014', 2, 1100),
                 ('Иван', '01.12.2014', 2, 1200)
         ) as T(Абонент, Месяц, Тип,Показания)
)
, B as (
  select *
        ,count(*) over(partition by Абонент,Тип order by Тип,Месяц) as cnt
   from A
)

select distinct Абонент, Тип
  from B
 where cnt>=@cnt
 order by Абонент;

Результат:
Абонент	Тип
Иван 2
Саня 1
16 дек 14, 14:10    [17004326]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
o-o
Guest
Wlr-l,
нет, не пойдет.
ТС-у надо, чтобы 5 месяцев подряд один и тот же тип.
проверьте свое решение на таких данных:

with A as (
  select *
    from (values ('Иван', '01.01.2014', 1, 100),
                 ('Саня', '01.01.2014', 1, 150),
                 ('Саня', '01.02.2014', 1, 250),
                 ('Иван', '01.02.2014', 1, 100),
                 ('Саня', '01.03.2014', 1, 300),
                 ('Иван', '01.04.2014', 2, 400),
                 ('Иван', '01.05.2014', 1, 500),
                 ('Саня', '01.06.2014', 1, 600),
                 ('Иван', '01.07.2014', 2, 700),
                 ('Иван', '01.08.2014', 2, 800),
                 ('Иван', '01.09.2014', 3, 900),
                 ('Саня', '01.09.2014', 1, 450),
                 ('Иван', '01.10.2014', 2, 1000),
                 ('Иван', '01.11.2014', 2, 1100),
                 ('Иван', '01.12.2014', 2, 1200)
         ) as T(Абонент, Месяц, Тип,Показания)
)
16 дек 14, 14:38    [17004527]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Wlr-l
Member

Откуда:
Сообщений: 522
o-o,
Спасибо, поэтому я написал "примерно так".

Мы уже, если помните, обсуждали интервалы непрерывности (поиск островков). Пересчет будет немного сложнее, да и ТС будет над чем подумать.
16 дек 14, 15:25    [17004963]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
o-o
Guest
Wlr-l,

не, не помню, у меня в любом случае на работе никак нет 2012,
вернее, его можно тайно на ноуте запустить и за это получить (не буду).
я на то, что в глаза бросается, указываю. для вас, не для ТС.
а раз и так знаете, то и ладно :)
16 дек 14, 15:35    [17005078]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Добрый Э - Эх
Guest
ibrasul,

просто как информация к размышлению:
with
  t (Абонент, Месяц, Тип, Показания) as 
    (
      select *
        from (values
                 ('Ivan', '2014-01-01', 1, 100),
                 ('Egor', '2014-01-01', 1, 150),
                 ('Egor', '2014-02-01', 1, 250),
                 ('Ivan', '2014-02-01', 1, 100),
                 ('Egor', '2014-03-01', 1, 300),
                 ('Ivan', '2014-04-01', 2, 400),
                 ('Ivan', '2014-05-01', 1, 500),
                 ('Egor', '2014-06-01', 1, 600),
                 ('Ivan', '2014-07-01', 2, 700),
                 ('Ivan', '2014-08-01', 2, 800),
                 ('Ivan', '2014-09-01', 2, 900),
                 ('Egor', '2014-09-01', 1, 450),
                 ('Ivan', '2014-10-01', 2, 1000),
                 ('Ivan', '2014-11-01', 2, 1100),
                 ('Ivan', '2014-12-01', 2, 1200)
             ) as v(a,b,c,d)
    )
select v.Абонент, v.Месяц, v.Тип, v.Показания,
       count(1) over(partition by Абонент, Тип, grp_id) as [Кол-во показаний одного типа подряд]
  from (
         select *, 
                row_number() over(partition by Абонент, Тип order by Месяц) -
                row_number() over(partition by Абонент order by Месяц) as grp_id
           from t
       ) v
 order by Абонент, Месяц, Тип
16 дек 14, 18:48    [17006601]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
o-o
Guest
Добрый Э - Эх,

у вас код считает число строк, идущих подряд при сортировке по дате, с одинаковым типом.
а ТС ищет не просто строки подряд, а месяцы.
вот это:
('Саня', '01.06.2014', 1, 600),
('Саня', '01.09.2014', 1, 450),
-- то, что у вас считается "подряд",

а вот такое должно быть по условию задачи:
('Саня', '01.06.2014', 1, 600),
('Саня', '01.07.2014', 1, 600),
('Саня', '01.08.2014', 1, 450),
('Саня', '01.09.2014', 1, 450)
---
чем решение-то в лоб от aleks2 не угодило?
острова в другой теме, вот Wlr-l подскажет, где :)
16 дек 14, 19:10    [17006697]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Добрый Э - Эх
Guest
o-o,

в лоб - зачастую далеко не самое лучшее (во всех отношениях) решение.
16 дек 14, 19:30    [17006770]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Добрый Э - Эх
Guest
o-o
Добрый Э - Эх,

у вас код считает число строк, идущих подряд при сортировке по дате, с одинаковым типом.
а ТС ищет не просто строки подряд, а месяцы.
рассказать про диапазонные окна?
16 дек 14, 19:35    [17006794]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Добрый Э - Эх
Guest
Добрый Э - Эх
o-o
Добрый Э - Эх,

у вас код считает число строк, идущих подряд при сортировке по дате, с одинаковым типом.
а ТС ищет не просто строки подряд, а месяцы.
рассказать про диапазонные окна?
хотя нет. получается слишком притянутое за уши решение. к тому оно, скорее всего, не взлетит на версия MS SQL Server-а до 2012 включительно. в виду невозможности указания модификатора DISTINCT внутри функции, определяемой диапазонным окном с сортировкой.

гораздо проще будет заменить разность двух разнооконных row_number-ов на вычитания из даты числа месяцев, определяемых row_number-ом в рамках окна по сочетанию Абонента и Типа, в сортировке по дате.

16 дек 14, 20:04    [17006927]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
o-o
Guest
Добрый Э - Эх
рассказать про диапазонные окна?

спасибо, но у меня и правда есть хорошая книга по этому поводу.
из нее же могу привести адаптированное под эту задачу решение в общем виде для версий >= 2005
в ответе -- дата + число месяцев подряд (одного типа) до нее

просто здесь это вроде как из пушки по воробьям, но к разговору об оконных ф-циях пойдет
declare @t table (name varchar(100), dt date, tp int, val int);

insert into @t(name, dt, tp, val)
values
('Ivan', '2014-01-01', 1, 100),
('Egor', '2014-01-01', 1, 150),
('Egor', '2014-02-01', 1, 250),
('Ivan', '2014-02-01', 1, 100),
('Egor', '2014-03-01', 1, 300),
('Ivan', '2014-04-01', 2, 400),
('Ivan', '2014-05-01', 1, 500),
('Egor', '2014-06-01', 1, 600),
('Ivan', '2014-07-01', 2, 700),
('Ivan', '2014-08-01', 2, 800),
('Ivan', '2014-09-01', 3, 900),
('Egor', '2014-09-01', 1, 450),
('Ivan', '2014-10-01', 2, 1000),
('Ivan', '2014-11-01', 2, 1100),
('Ivan', '2014-12-01', 2, 1200);

with cte as
(
select name, tp, dt, dateadd(month, -1 * dense_rank() over(partition by name, tp order by dt), dt) as grp
from @t
)
select name, tp, max(dt) as end_dt, count(dt) as cnt
from cte
group by grp, name, tp
order by name, max(dt);


name	tp	end_dt	      cnt
Egor 1 2014-03-01 3
Egor 1 2014-06-01 1
Egor 1 2014-09-01 1
Ivan 1 2014-02-01 2
Ivan 2 2014-04-01 1
Ivan 1 2014-05-01 1
Ivan 2 2014-08-01 2
Ivan 3 2014-09-01 1
Ivan 2 2014-12-01 3
16 дек 14, 22:22    [17007461]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Добрый Э - Эх
Guest
o-o
with cte as
(
select name, tp, dt, dateadd(month, -1 * dense_rank() over(partition by name, tp order by dt), dt) as grp
from @t
)
select name, tp, max(dt) as end_dt, count(dt) as cnt
from cte
group by grp, name, tp
order by name, max(dt);

Выделенное - ровно то, о чем я написал словами:
Добрый Э - Эх
гораздо проще будет заменить разность двух разнооконных row_number-ов на вычитания из даты числа месяцев, определяемых row_number-ом в рамках окна по сочетанию Абонента и Типа, в сортировке по дате.
17 дек 14, 03:25    [17008130]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
o-o
Guest
как по мне, так вот такое
Добрый Э - Эх
вычитания из даты числа месяцев, определяемых row_number-ом в рамках окна по сочетанию Абонента и Типа, в сортировке по дате.

лучше 1 раз увидеть, чем 100 раз услышать

P.S. Добрый Э - Эх использует row_number() в надежде на уникальность {name, tp, dt}, a dense_rank() универсален.
но если у ТС и правда гарантирована уникальность, то и огород городить не стОит:
"я вытаскиваю количество записей в диапазоне от 01,07,2014 по 01,11,2014 по пользователю, где тип беру равный 2"
+ даты -- только первые числа месяца -> если в ответе 5, то ответ положительный.

P.P.S. да даже уникальность не нужна, ведь можно и count distinct считать.
нужна лишь гарантия того, что все даты за первое число (а без этого полетит и решение с инвариантом)

короче. много написано, а как ни крути -- решение "в лоб" -- самое универсальное
17 дек 14, 11:46    [17009600]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Wlr-l
Member

Откуда:
Сообщений: 522
o-o,
Что-то слишком пессимистично...

ТС непрофессионально поставил задачу и так же непрофессионально ее решил. Его решение не "самое универсальное", а самое примитивное. Ему нужно каждый раз задать интервалы и тип, подсчитать кол-во полученных записей.

Как я уже написал, нужно найти интервалы непрерывности (островки) и потом, об этом я не написал, думал - очевидно, вычислить агрегаты на этих интервалах (островках). Как и обещал, пересчет получился немного сложнее. "Показания" есть неубывающая функция от "Месяц", поэтому при пропуске какого-то месяца считаем, что значение "Показания" не изменилось по сравнению с предыдущим месяцем.
declare @cnt int = 5;

with A as (
  select *
    from (values ('Иван', '20140101', 1, 100),
                 ('Саня', '20140101', 1, 150),
                 ('Саня', '20140201', 1, 250),
                 ('Иван', '20140201', 1, 100),
                 ('Саня', '20140301', 1, 300),
                 ('Иван', '20140415', 2, 400),
                 ('Иван', '20140505', 1, 500),
                 ('Саня', '20140601', 1, 600),
                 ('Иван', '20140701', 2, 700),
                 ('Иван', '20140801', 2, 800),
                 ('Иван', '20140901', 2, 900),
                 ('Саня', '20140901', 1, 450),
                 ('Иван', '20141001', 2, 1000),
--                 ('Иван', '20141001', 3, 1000),
                 ('Иван', '20141101', 2, 1100),
                 ('Иван', '20141201', 2, 1200),
                 ('Петя', '20131001', 2, 1200),
                 ('Петя', '20131101', 2, 1200),
                 ('Петя', '20131201', 2, 1200),
                 ('Петя', '20140101', 2, 1200),
                 ('Петя', '20140201', 2, 1200)
         ) as T (Абонент,    Месяц, Тип, Показания)
)
, B as (
  select *
        ,row_number() over(                         order by Абонент,Месяц)
        -row_number() over(partition by Абонент,Тип order by Тип,    Месяц) as  dg
-- Может быть, более правильно использовать dense_rank()?
   from A
)

select Абонент, Тип, min(Месяц) as с, max(Месяц) as по, count(*) as cnt
  from B 
 group by dg, Абонент, Тип
 having count(*)>=@cnt
 order by Абонент, с;

Результат:
Абонент	Тип	с		по		cnt
Иван 2 20140701 20141201 6
Петя 2 20131001 20140201 5
Саня 1 20140101 20140901 5

Если раскомментировать строку в таблице исходных данных, то получим:
Абонент	Тип	с		по		cnt
Петя 2 20131001 20140201 5
Саня 1 20140101 20140901 5

Как видим решение дает полную информацию и работает не только в пределах года (Петя).
17 дек 14, 15:45    [17011317]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
o-o
Guest
Wlr-l
o-o,
ТС непрофессионально поставил задачу и так же непрофессионально ее решил.
Его решение не "самое универсальное", а самое примитивное

вы неправильно меня прочли.
самое универсальное относилось к решению aleks2: 17003590
o-o
...
чем решение-то в лоб от aleks2 не угодило?

+
o-o
как ни крути -- решение "в лоб" -- самое универсальное

ибо оно плюет на то, начало ли месяца, не начало ли, уникальны ли строки, нет ли...
17 дек 14, 17:53    [17012343]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
o-o
Guest
Wlr-l,
свое решение протестируйте на таком:
('Иван', '20140101', 1, 100),
('Иван', '20140101', 1, 100),
('Иван', '20140101', 1, 100),
('Иван', '20140101', 1, 100),
('Иван', '20140101', 1, 100)

и вот таком:
('Иван', '20140101', 1, 100),
('Иван', '20140102', 1, 100),
('Иван', '20140103', 1, 100),
('Иван', '20140104', 1, 100),
('Иван', '20140105', 1, 100)


никакими 5-ю месяцами подряд и не пахнет, а ответ у вас в обоих случаях положительный
17 дек 14, 18:04    [17012422]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Wlr-l
Member

Откуда:
Сообщений: 522
o-o,

Наверно, сначала Вы говорите о ТС, а потом делаете заключение об универсальности. В последнем Вашем сообщении где-то пропущено упоминание другого решения.

Я видел решение от aleks2. Насчет универсальности не уверен, так как он явно использует границы интервала "Месяц between @b and @e"

ТС: "Хочу еще варианты как это можно сделать". Поэтому предложил оконные функции. В последнем примере есть даты, у которых день не равен 1, но он все равно работает. Уникальность, повторы, пропуски - это организационная сторона. Неточности в данных нужно устранять, а не маскировать. Например, если ввести столбец df:
select Абонент, Тип, min(Месяц) as с, max(Месяц) as по, count(*) as cnt
      ,datediff(mm,min(Месяц),max(Месяц))+1 as df
  from B 
 group by dg, Абонент, Тип
 having count(*)>=@cnt
 order by Абонент, с;

Результат:
Абонент	Тип	с		по		cnt	df
Иван 2 20140701 20141201 6 6
Петя 2 20131001 20140201 5 5
Саня 1 20140101 20140901 5 9

то из сравнения столбцов cnt и df видно, что у Ивана и Сани есть проблемы с данными.

Как всегда, есть несколько решений, которые можно довести до рабочих, а ТС исчез, да и мне пора.
17 дек 14, 18:27    [17012547]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Wlr-l
Member

Откуда:
Сообщений: 522
o-o
Wlr-l,
свое решение протестируйте на таком:
('Иван', '20140101', 1, 100),
('Иван', '20140101', 1, 100),
('Иван', '20140101', 1, 100),
('Иван', '20140101', 1, 100),
('Иван', '20140101', 1, 100)

и вот таком:
('Иван', '20140101', 1, 100),
('Иван', '20140102', 1, 100),
('Иван', '20140103', 1, 100),
('Иван', '20140104', 1, 100),
('Иван', '20140105', 1, 100)


никакими 5-ю месяцами подряд и не пахнет, а ответ у вас в обоих случаях положительный


Неточности в данных нужно устранять, а не маскировать.
17 дек 14, 18:30    [17012560]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
aleks2
Guest
Wlr-l
Я видел решение от aleks2. Насчет универсальности не уверен, так как он явно использует границы интервала "Месяц between @b and @e"

Не, использование хрустального шара в деле запросов к MS SQL мне недоступно.

ЗЫ. Интересуюся: а как иначе установить желаемые "пять месяцев"?
17 дек 14, 18:41    [17012649]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
o-o
Guest
Wlr-l
Неточности в данных нужно устранять, а не маскировать.

"универсальность" как раз и предполагает отсутствие оговорок "а и то, и это, и пятое, и десятое -- должны соблюдаться"
алексово решение сработет на тех данных, ваше -- нет.
при этом именно ВЫ и именно универсальность ставите под сомнение
17 дек 14, 18:53    [17012730]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Добрый Э - Эх
Guest
вот чем примечателен скуль.ру, так тем, что топикстартер уже давно забыл про свой топик и забил на него, а участники форума всё ещё продолжают сраться между собой и решать задачу исходя из своихъ собственных пониманий треований вопрощающего, причем с "баянами", не обращая внимания на уже предложенные решения.
17 дек 14, 20:36    [17013186]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить