ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Аналитическая задачка
7 сообщений из 7, страница 1 из 1
Аналитическая задачка
    #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, 15:24
| Ответить | Цитировать | Написать  
Аналитическая задачка
    #40089352
aleks222
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
aleks222
Участник
Опять интервалы непрерывности?
Это уже скушно.

Вариант: удаляем (не выбираем) все минуты за которой следует минута с тем же каналом.
Вот и все.
...
Рейтинг: 0 / 0
07.08.2021, 16:12
| Ответить | Цитировать | Написать  
Аналитическая задачка
    #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, 20:53
| Ответить | Цитировать | Написать  
Аналитическая задачка
    #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, 21:09
| Ответить | Цитировать | Написать  
Аналитическая задачка
    #40089384
sysn1k
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sysn1k
Гость
invm,

спасибо.
Нашел ваше решение и объяснение на https://professorweb.ru/my/sql-server/window-functions/level3/3_12.php.
Все делается легче, чем пытался сотворить сам.
...
Рейтинг: 0 / 0
07.08.2021, 22:54
| Ответить | Цитировать | Написать  
Аналитическая задачка
    #40089385
sysn1k
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sysn1k
Гость
aleks222,
а вас спасибо, что дали название моей проблемы)
Так бы не нашел нужную статью)
...
Рейтинг: 0 / 0
07.08.2021, 22:59
| Ответить | Цитировать | Написать  
Аналитическая задачка
    #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
08.08.2021, 09:07
| Ответить | Цитировать | Написать  
7 сообщений из 7, страница 1 из 1
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Аналитическая задачка
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/search_topic.php?author=KoViVl&author_mode=last_posts&do_search=1]:
 0ms
get settings:
 9ms
get forum list:
 14ms
get settings:
 10ms
get forum list:
 13ms
get settings:
 11ms
get forum list:
 12ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 133ms
get topic data:
 10ms
get forum data:
 3ms
get page messages:
 43ms
get tp. blocked users:
 1ms
others: 1053ms
total:  1316ms
созд. / загр.: 1316ms
x
x
Закрыть


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