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

Откуда:
Сообщений: 18
Всем привет! Дело такое: допустим есть таблица посещений клиента Users с полями Fam, Im, Otch, DatePos в таком виде:
Иванов Иван Иванович 2016-02-01
Иванов Иван Иванович 2016-02-02
Иванов Иван Иванович 2016-02-05
Иванов Иван Иванович 2016-02-08

Мне требуется сделать такую выборку, что бы выдались строки с последовательными промежутками между посещениями пользователя такого вида:
Иванов Иван Иванович 2016-02-01 2016-02-02
Иванов Иван Иванович 2016-02-02 2016-02-05
Иванов Иван Иванович 2016-02-05 2016-02-08

Я написал такой запрос:
SELECT
    u.FAM,
    u.IM,
    u.OTCH,
    u.DatePos,
    t.DatePos
FROM Users u
JOIN    (
            SELECT
            u1.FAM,
            u1.IM,
            u1.OTCH,
            u1.DatePos
        FROM Users u1
    )
     t ON t.FAM = u.FAM AND t.DatePos > u.DatePos
ORDER BY u.DatePos


Получается такая картина:
Иванов Иван Иванович 2016-02-01 2016-02-02
Иванов Иван Иванович 2016-02-01 2016-02-05
Иванов Иван Иванович 2016-02-01 2016-02-08
Иванов Иван Иванович 2016-02-02 2016-02-05
Иванов Иван Иванович 2016-02-02 2016-02-08
Иванов Иван Иванович 2016-02-05 2016-02-08

То есть присутствуют лишние строки:
Иванов Иван Иванович 2016-02-01 2016-02-05
Иванов Иван Иванович 2016-02-01 2016-02-08
Иванов Иван Иванович 2016-02-02 2016-02-08

Чувствую, что где-то прям чуть-чуть не догоняю.. То ли понедельник, то ли вечер, то ли всё вместе ))) Может завтра сутра озарит мою голову решение, но пока туплю...
Кому не сложно - помогите дельным советом )))
4 апр 16, 17:00    [19015563]     Ответить | Цитировать Сообщить модератору
 Re: Выборка отрезков времени между сеансами посещения  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Почитайте про оконные функции:

DECLARE @t TABLE (Name NVARCHAR(50), [Date] DATE)

INSERT INTO @t
VALUES
    (N'Иванов', '2016-02-01'),
    (N'Иванов', '2016-02-02'),
    (N'Петров', '2016-02-05'),
    (N'Иванов', '2016-02-05'),
    (N'Петров', '2016-02-12'),
    (N'Иванов', '2016-02-08')

SELECT *
FROM (
    SELECT *, [To] = LEAD([Date]) OVER (PARTITION BY Name ORDER BY [Date])
    FROM @t
) t
WHERE t.[To] IS NOT NULL
4 апр 16, 17:09    [19015633]     Ответить | Цитировать Сообщить модератору
 Re: Выборка отрезков времени между сеансами посещения  [new]
iljy
Member

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

t.DatePos > u.DatePos

все честно, это условие задает ВСЕ строки, где одна дата больше второй, а не только соседние. Для решения (в зависимости от версии сервера) можно использовать ROW_NUMBER + JOIN либо LEAD.
4 апр 16, 17:11    [19015641]     Ответить | Цитировать Сообщить модератору
 Re: Выборка отрезков времени между сеансами посещения  [new]
koly4ii86
Member

Откуда:
Сообщений: 18
AlanDenton, спасибо за совет, только у меяня 2008 server и LEAD не работает.
4 апр 16, 17:20    [19015686]     Ответить | Цитировать Сообщить модератору
 Re: Выборка отрезков времени между сеансами посещения  [new]
koly4ii86
Member

Откуда:
Сообщений: 18
iljy, согласен, что всё честно ))) За ROW_NUMBER - спасибо, сейчас погуглю ))
4 апр 16, 17:21    [19015692]     Ответить | Цитировать Сообщить модератору
 Re: Выборка отрезков времени между сеансами посещения  [new]
Добрый Э - Эх
Guest
koly4ii86,

Outer apply c top 1 подзапросом
4 апр 16, 17:38    [19015802]     Ответить | Цитировать Сообщить модератору
 Re: Выборка отрезков времени между сеансами посещения  [new]
koly4ii86
Member

Откуда:
Сообщений: 18
Добрый Э - Эх, попробовал со своим пониманием где это использовать - результат получился не тот. Можно по конкретнее где и как использовать?
4 апр 16, 18:06    [19015905]     Ответить | Цитировать Сообщить модератору
 Re: Выборка отрезков времени между сеансами посещения  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
SELECT *
FROM @t t
CROSS APPLY (
    SELECT TOP(1) t2.[Date]
    FROM @t t2
    WHERE t2.Name = t.Name
        AND t2.[Date] > t.[Date]
    ORDER BY t2.[Date]
) t2

Покажите как Вы пробовали?
4 апр 16, 18:16    [19015934]     Ответить | Цитировать Сообщить модератору
 Re: Выборка отрезков времени между сеансами посещения  [new]
koly4ii86
Member

Откуда:
Сообщений: 18
AlanDenton, спасибо! С работы уже ушёл, завтра попробую обязательно!
4 апр 16, 18:21    [19015950]     Ответить | Цитировать Сообщить модератору
 Re: Выборка отрезков времени между сеансами посещения  [new]
koly4ii86
Member

Откуда:
Сообщений: 18
AlanDenton, запрос не сохранил как я вчера пробовал, вспомнить уже не смогу, но явно по-другому, ибо Ваш вариант отработал правильно! Если честно не полностью ещё разобрался как работает CROSS Aply, но за помощь - огромное спасибо! Буду сидеть разбираться как же это сработало )))
5 апр 16, 08:29    [19017071]     Ответить | Цитировать Сообщить модератору
 Re: Выборка отрезков времени между сеансами посещения  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Пожалуйста. Почитайте Ичица Бен-Гана у него про CROSS/OUTER APPLY написано очень хорошо. Да и Ваш пример в книге где-то был.
5 апр 16, 08:33    [19017081]     Ответить | Цитировать Сообщить модератору
 Re: Выборка отрезков времени между сеансами посещения  [new]
mishanya3624
Member

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

+1
189 страница:)
5 апр 16, 10:30    [19017547]     Ответить | Цитировать Сообщить модератору
 Re: Выборка отрезков времени между сеансами посещения  [new]
koly4ii86
Member

Откуда:
Сообщений: 18
AlanDenton, обязательно ознакомлюсь! Сделал ещё через ROW_COUNT, то же получилось, но остановился на вашем варианте, он более прозрачный как по мне. Ещё раз спасибо за помощь!
5 апр 16, 11:31    [19017911]     Ответить | Цитировать Сообщить модератору
 Re: Выборка отрезков времени между сеансами посещения  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
koly4ii86
AlanDenton, обязательно ознакомлюсь! Сделал ещё через ROW_COUNT, то же получилось, но остановился на вашем варианте, он более прозрачный как по мне. Ещё раз спасибо за помощь!
Что такое ROW_COUNT?
5 апр 16, 11:33    [19017928]     Ответить | Цитировать Сообщить модератору
 Re: Выборка отрезков времени между сеансами посещения  [new]
iljy
Member

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

APPLY может и более прозрачный, но ROW_NUMBER + JOIN гораздо лучше оптимизируется, на реальных задачах это может оказаться существенно. Да и с прозрачностью тоже ИМХО там все нормально, явно пишем условия соединения соседних строк, чего там непрозрачного?
5 апр 16, 11:34    [19017939]     Ответить | Цитировать Сообщить модератору
 Re: Выборка отрезков времени между сеансами посещения  [new]
koly4ii86
Member

Откуда:
Сообщений: 18
iap, очепятка, конечно ROW_NUMBER
5 апр 16, 11:54    [19018085]     Ответить | Цитировать Сообщить модератору
 Re: Выборка отрезков времени между сеансами посещения  [new]
koly4ii86
Member

Откуда:
Сообщений: 18
iljy, ну я руководствовался вот этим кодом:
WITH balance_details
AS
(
SELECT * FROM (
VALUES
('Tom','20140101',100),
('Tom','20140102',120),
('Tom','20140103',150),
('Tom','20140104',140),
('Tom','20140105',160),
('Tom','20140106',180),
('Jerry','20140101',210),
('Jerry','20140102',240),
('Jerry','20140103',230),
('Jerry','20140104',270),
('Jerry','20140105',190),
('Jerry','20140106',200),
('David','20140101',170),
('David','20140102',230),
('David','20140103',240),
('David','20140104',210),
('David','20140105',160),
('David','20140106',200)
) AS t (customer, balancedate,balance)
),
balance_cte AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY customer ORDER BY balancedate) rn
,(ROW_NUMBER() OVER (PARTITION BY customer ORDER BY balancedate))/2 rndiv2
,(ROW_NUMBER() OVER (PARTITION BY customer ORDER BY balancedate) + 1)/2 rnplus1div2
/*
,COUNT(*) OVER (PARTITION BY customer) partitioncount
*/
,customer
,balancedate
,balance
FROM balance_details
)
SELECT
rn
,rndiv2
,rnplus1div2
,customer,balancedate,balance
,CASE WHEN rn%2=1 THEN MAX(CASE WHEN rn%2=0 THEN balance END) OVER (PARTITION BY customer,rndiv2) ELSE MAX(CASE WHEN rn%2=1 THEN balance END) OVER (PARTITION BY customer,rnplus1div2)
END AS balance_lag
,CASE WHEN rn%2=1 THEN MAX(CASE WHEN rn%2=0 THEN balance END) OVER (PARTITION BY customer,rnplus1div2) ELSE MAX(CASE WHEN rn%2=1 THEN balance END) OVER (PARTITION BY customer,rndiv2)
END AS balance_lead
/*
,MAX(CASE WHEN rn=1 THEN balance END) OVER (PARTITION BY customer) AS first_value
,MAX(CASE WHEN rn=partitioncount THEN balance END) OVER (PARTITION BY customer) AS last_value
,MAX(CASE WHEN rn=4 THEN balance END) OVER (PARTITION BY customer) AS fourth_value
*/
FROM balance_cte
ORDER BY customer,balancedate



И как-то мне тяжелее далось понимание этих операций.
5 апр 16, 12:06    [19018154]     Ответить | Цитировать Сообщить модератору
 Re: Выборка отрезков времени между сеансами посещения  [new]
koly4ii86
Member

Откуда:
Сообщений: 18
Кстати в итоге сделал в обще через Union, по скольку у меня было ограниченное число возможных комбинаций дат ))) Но так конечно это не вариант, зато работает быстро в моём случае )))
26 апр 16, 10:45    [19106603]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить