Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Аналитическая задачка / 7 сообщений из 7, страница 1 из 1
07.08.2021, 15:24
    #40089350
sysn1k
sysn1k
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Аналитическая задачка
Сама задача в файле.

Код: 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.
43.
44.
45.
46.
47.
48.
with event as (
	select 1 as user_id, 1 as channel_id, '2020-10-10 13:00:00' as minute
    UNION ALL select 1 as user_id, 1 as channel_id, '2020-10-10 13:01:00' as minute
    UNION ALL select 1 as user_id, 1 as channel_id, '2020-10-10 13:02:00' as minute
    UNION ALL select 1 as user_id, 1 as channel_id, '2020-10-10 13:03:00' as minute
    UNION ALL select 1 as user_id, 4 as channel_id, '2020-10-10 13:04:00' as minute
    UNION ALL select 1 as user_id, 4 as channel_id, '2020-10-10 13:05:00' as minute
    UNION ALL select 1 as user_id, 4 as channel_id, '2020-10-10 13:15:00' as minute
    UNION ALL select 1 as user_id, 7 as channel_id, '2020-10-10 13:16:00' as minute
    UNION ALL select 1 as user_id, 7 as channel_id, '2020-10-10 13:17:00' as minute
    UNION ALL select 12 as user_id, 1 as channel_id, '2020-10-10 13:03:00' as minute
    UNION ALL select 12 as user_id, 1 as channel_id, '2020-10-10 13:05:00' as minute
    UNION ALL select 12 as user_id, 1 as channel_id, '2020-10-10 13:06:00' as minute
    UNION ALL select 12 as user_id, 2 as channel_id, '2020-10-10 13:07:00' as minute
    UNION ALL select 12 as user_id, 2 as channel_id, '2020-10-10 13:10:00' as minute
    UNION ALL select 12 as user_id, 2 as channel_id, '2020-10-10 13:11:00' as minute
   UNION ALL select 12 as user_id, 4 as channel_id, '2020-10-10 13:12:00' as minute
    UNION ALL select 12 as user_id, 4 as channel_id, '2020-10-10 13:13:00' as minute
    UNION ALL select 12 as user_id, 6 as channel_id, '2020-10-10 13:16:00' as minute
    UNION ALL select 12 as user_id, 9 as channel_id, '2020-10-10 13:20:00' as minute
),
a as(
select *
  		,lag(minute) over (partition by user_id, channel_id   order by minute) as 'past_minute'
		,lead(minute) over (partition by user_id, channel_id   order by minute) as 'next_minute'
from event
),
b as (select *
		, case
        		when datediff(second,minute,next_minute) = 60 then 1 else 2
          end as [check]
      	, case
        		when (past_minute is null  and next_minute is null)
      			then [minute] else [next_minute]
          end as [check2]
		from a
),
c as(select user_id, channel_id, minute, check2, [check]
from b
where check2 is not null
),
d as(select * 
		,first_value(minute) over (partition by  user_id, channel_id  order by [minute]) first
      ,last_value(check2) over (partition by  user_id, channel_id  order by [check]) last
	from C
)
select user_id, channel_id, first as [date_from], last as [date_to] from d
group by user_id, channel_id, first, last



Не получается корректно отобразить, когда идет прерывание между просмотрами. Кажется, что ошибка в CTE b. Не могу корректное условие подобрать для поля check2.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.08.2021, 16:12
    #40089352
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Аналитическая задачка
Опять интервалы непрерывности?
Это уже скушно.

Вариант: удаляем (не выбираем) все минуты за которой следует минута с тем же каналом.
Вот и все.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.08.2021, 20:53
    #40089372
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Аналитическая задачка
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
with t as
(
 select
  user_id, channel_id, [minute],
  datediff(mi, min([minute]) over (partition by user_id, channel_id order by [minute]), [minute]) - row_number() over (partition by user_id, channel_id order by [minute]) as g
 from
  events
)
select
 user_id, channel_id, min([minute]), max([minute])
from
 t
group by
 user_id, channel_id, g
order by
 user_id, channel_id;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.08.2021, 21:09
    #40089377
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Аналитическая задачка
Без последствий копипасты
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
with t as
(
 select
  user_id, channel_id, [minute],
  datediff(mi, min([minute]) over (partition by user_id, channel_id), [minute]) - row_number() over (partition by user_id, channel_id order by [minute]) as g
 from
  events
)
select
 user_id, channel_id, min([minute]), max([minute])
from
 t
group by
 user_id, channel_id, g
order by
 user_id, channel_id;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.08.2021, 22:54
    #40089384
sysn1k
sysn1k
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Аналитическая задачка
invm,

спасибо.
Нашел ваше решение и объяснение на https://professorweb.ru/my/sql-server/window-functions/level3/3_12.php.
Все делается легче, чем пытался сотворить сам.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.08.2021, 22:59
    #40089385
sysn1k
sysn1k
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Аналитическая задачка
aleks222,
а вас спасибо, что дали название моей проблемы)
Так бы не нашел нужную статью)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.08.2021, 09:07
    #40089400
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Аналитическая задачка
Выкидываем лишнее
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
t as
(
 select
  user_id, channel_id, [minute],
  datediff(mi, '1900', [minute]) - row_number() over (partition by user_id, channel_id order by [minute]) as g
 from
  event
)
select
 user_id, channel_id, min([minute]), max([minute])
from
 t
group by
 user_id, channel_id, g
order by
 user_id, channel_id;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Аналитическая задачка / 7 сообщений из 7, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 173ms
Закрыть
start [/forum/topic.php?fid=46&mobile=1&tid=1684428]:
 0ms
get settings:
 10ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 60ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 52ms
get tp. blocked users:
 2ms
others: 13ms
total:  173ms
x
x
Закрыть


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