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

Откуда:
Сообщений: 33
Вычислить для каждой строки, сколько дней подряд к этому моменту пользователь заходил в приложение.

Существует таблица Пользователи, в которой хранятся ID пользователей и дата каждого захода в приложение.
Необходимо написать запрос вычисляющий для каждой строки, сколько дней подряд к этому моменту пользователь заходил в приложение (первый вход считаем как 1 день подряд).
Написала вот это:

USE Useractivity
SELECT UserActivity.Date, UserActivity.UserId, COUNT(Date) AS DaysInRow
FROM UserActivity
GROUP BY UserActivity.UserId, UserActivity.Date
ORDER BY COUNT(Date)DESC

а как осуществить сам подсчет не понимаю(((

должно получится как то так:

Date UserId DaysInRow
2014-10-01 2 1
2014-10-01 1 1
2014-10-02 1 2
2014-10-02 3 1
2014-10-03 1 3
2014-10-05 1 1
30 окт 18, 19:58    [21719476]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
PizzaPizza
Member

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

А какая версия у вас сервера? Если можете делать lag/lead то попробуйте так:

партицируем по пользователям и сортируем по датам и там ищем разрывы в датах
каждый непрерывный интервал нумеруем сплошной нумерацией
делим по группам теперь и сортируем по датам и считаем сколько там срок на каждую дату


declare @t table (
da DATE,
userid integer
)

Insert into @t values 

('2014-10-01',2),
('2014-10-05',2),
('2014-10-06',2),
('2014-10-01',1),
('2014-10-02',1),
('2014-10-02',3),
('2014-10-03',1),
('2014-10-05',1),
('2014-10-07',1),
('2014-10-03',3);

with a as (select
    t.*,
    case when lag(da) over (PARTITION BY userid  order by da) <> DATEADD(day, -1, da) 
        OR lag(da) over (PARTITION BY userid  order by da) is null then 1 else 0 end as gr_start
    from
@t as t
),

b as (
select 
    a.*
    ,sum(gr_start) over (order by userid,da) as gr
from a)


SELECT 
    da,
    userid,
    count(gr) over (PARTITION BY gr  order by da) as cont_days
from b
order by da


 


зы. по граничным условиям не проверял. Было бы не плохо побольше тестовых данных иметь
30 окт 18, 23:25    [21719634]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Щукина Анна
Member

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

вариант на разности row_number-ов будет и проще, и на большем числе версий SQL Server-а "взлетит" ;)
31 окт 18, 04:43    [21719729]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1467
PizzaPizza
cglcz,

А какая версия у вас сервера? Если можете делать lag/lead то попробуйте так:

sum(gr_start) over (order by userid,da) as gr



Но вот аналитическая накопительная сумма по любому будет требовать наличие MS SQL Server 2012 и выше.
31 окт 18, 04:46    [21719730]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
PizzaPizza
Member

Откуда:
Сообщений: 352
Щукина Анна,

Спасибо. Попробую завтра. А то чувствую, что, тк недавно делал тяжёлый запрос на lagах, я застрял на них - не могу переключиться.
31 окт 18, 06:17    [21719740]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
cglcz
Member

Откуда:
Сообщений: 33
Версия сервера SSMS 2017
31 окт 18, 09:12    [21719824]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Щукина Анна
Member

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

на ваших же тестовых данных:
declare @t table (
da DATE,
userid integer
)

Insert into @t values 

('2014-10-01',2),
('2014-10-05',2),
('2014-10-06',2),
('2014-10-01',1),
('2014-10-02',1),
('2014-10-02',3),
('2014-10-03',1),
('2014-10-05',1),
('2014-10-07',1),
('2014-10-03',3);

with b as (
select 
    a.*
    , dateadd(day, - row_number() over(partition by userid order by da), da) as gr
from @t a)


SELECT 
    da,
    userid,
    count(gr) over (PARTITION BY userid, gr  order by da) as cont_days
from b
order by userid, da 

Для идентификации группы используется всего один row_number, вместо комбинации двух lag-ов с последующей накопительной sum()over(order by) по ним;)
31 окт 18, 10:44    [21719949]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Щукина Анна
Member

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

но даже в случае с LAG-ом - вполне достаточно и одного...

declare @t table (
da DATE,
userid integer
)

Insert into @t values 

('2014-10-01',2),
('2014-10-05',2),
('2014-10-06',2),
('2014-10-01',1),
('2014-10-02',1),
('2014-10-02',3),
('2014-10-03',1),
('2014-10-05',1),
('2014-10-07',1),
('2014-10-03',3);

with a as (select
    t.*,
    case when lag(da) over (PARTITION BY userid  order by da) = DATEADD(day, -1, da) then 0 else 1 end gr_start
    from
@t as t
),

b as (
select 
    a.*
    ,sum(gr_start) over (order by userid,da) as gr
from a)


SELECT 
    da,
    userid,
    count(gr) over (PARTITION BY userid, gr  order by da) as cont_days
from b
order by userid, da

(внесенные мною изменения в коде - выделены цветом ;))
31 окт 18, 11:34    [21720016]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
PizzaPizza
Member

Откуда:
Сообщений: 352
Щукина Анна,

Красиво.

Причем я же начинал с одного лага в первом сте, но что то мне не понравилось... а потом начал добавлять строки в тестовые данные. Все же важно работать с правильным набором сразу.

Отдельное спасибо за вариант с ROW_NUMBER - мне, как для не математика решение с помощью разницы возрастающих функций (наверное) не очевидно, но очень полезно как алгоритм.
31 окт 18, 21:02    [21720862]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
cglcz
Member

Откуда:
Сообщений: 33
Спасибо большое!
31 окт 18, 22:15    [21720912]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить