Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Помогите написать запрос / 10 сообщений из 10, страница 1 из 1
30.10.2018, 19:58
    #39725238
cglcz
cglcz
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
Вычислить для каждой строки, сколько дней подряд к этому моменту пользователь заходил в приложение.

Существует таблица Пользователи, в которой хранятся 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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
30.10.2018, 23:25
    #39725356
PizzaPizza
PizzaPizza
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
cglcz,

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

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


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
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



зы. по граничным условиям не проверял. Было бы не плохо побольше тестовых данных иметь
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
31.10.2018, 04:43
    #39725430
Щукина Анна
Щукина Анна
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
PizzaPizza,

вариант на разности row_number-ов будет и проще, и на большем числе версий SQL Server-а "взлетит" ;)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
31.10.2018, 04:46
    #39725431
Щукина Анна
Щукина Анна
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
PizzaPizzacglcz,

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

Код: sql
1.
sum(gr_start) over (order by userid,da) as gr




Но вот аналитическая накопительная сумма по любому будет требовать наличие MS SQL Server 2012 и выше.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
31.10.2018, 06:17
    #39725437
PizzaPizza
PizzaPizza
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
Щукина Анна,

Спасибо. Попробую завтра. А то чувствую, что, тк недавно делал тяжёлый запрос на lagах, я застрял на них - не могу переключиться.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
31.10.2018, 09:12
    #39725487
cglcz
cglcz
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
Версия сервера SSMS 2017
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
31.10.2018, 10:44
    #39725590
Щукина Анна
Щукина Анна
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
PizzaPizza,

на ваших же тестовых данных:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
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) по ним;)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
31.10.2018, 11:34
    #39725643
Щукина Анна
Щукина Анна
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
PizzaPizza,

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
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


(внесенные мною изменения в коде - выделены цветом ;))
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
31.10.2018, 21:02
    #39726154
PizzaPizza
PizzaPizza
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
Щукина Анна,

Красиво.

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

Отдельное спасибо за вариант с ROW_NUMBER - мне, как для не математика решение с помощью разницы возрастающих функций (наверное) не очевидно, но очень полезно как алгоритм.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
31.10.2018, 22:15
    #39726176
cglcz
cglcz
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите написать запрос
Спасибо большое!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Помогите написать запрос / 10 сообщений из 10, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]