ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Помогите написать запрос
10 сообщений из 10, страница 1 из 1
Помогите написать запрос
    #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, 19:58
| Ответить | Цитировать | Написать  
Помогите написать запрос
    #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
30.10.2018, 23:25
| Ответить | Цитировать | Написать  
Помогите написать запрос
    #39725430
Фотография Щукина Анна
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Щукина Анна
Участник
PizzaPizza,

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

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

Красиво.

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

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


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