ReSQL.ru
     
powered by simpleCommunicator - 2.0.52     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Данные с выборкой дат
16 сообщений из 16, страница 1 из 1
Данные с выборкой дат
    #39962952
007alex3
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
007alex3
Гость
Всем доброго времени суток.
Помогите, пожалуйста, с проблемой.
Нужно вытащить id, начало сессии и конец сессии с условиями: сессия начинается с homework (до homework игрок может посещать абсолютно любые страницы), затем должна быть страница view.step (разница между homework и view.step не должна быть больше часа) и заканчиваться сессия должна на странице lesson (разница между view.step и lesson не должна быть больше часа).

Пример таблицы:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
with test_1 as (
select 27073 user_id,'lesson' page,to_date('2017-03-01 12:00','yyyy-mm-dd hh24:mi') time_s from dual
union all
select 27073 deal_id,'homework' page, to_date('2017-03-01 12:01','yyyy-mm-dd hh24:mi') time_s from dual
union all
select 27073 deal_id,'view.step' page, to_date('2017-03-01 12:01','yyyy-mm-dd hh24:mi') time_s from dual
union all
select 27073 deal_id,'view.step' page, to_date('2017-03-01 12:02','yyyy-mm-dd hh24:mi') time_s from dual
union all
select 27073 deal_id,'lesson' page, to_date('2017-03-01 12:05','yyyy-mm-dd hh24:mi') time_s from dual
union all
select 27073 deal_id,'homework' page, to_date('2017-03-01 14:00','yyyy-mm-dd hh24:mi') time_s from dual
union all
select 27073 deal_id,'view.step' page, to_date('2017-03-01 14:10','yyyy-mm-dd hh24:mi') time_s from dual
)

select * from test_1



Из данной таблицы должно получиться:

id;дата начала;дата окончания
27073 ;2017-03-01 12:01;2017-03-01 12:05
...
Рейтинг: 0 / 0
27.05.2020, 17:09
| Ответить | Цитировать | Написать  
Данные с выборкой дат
    #39962962
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
007alex3,

если версия позволяет match_recognize

.....
stax
...
Рейтинг: 0 / 0
27.05.2020, 17:35
| Ответить | Цитировать | Написать  
Данные с выборкой дат
    #39963069
007alex3
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
007alex3
Гость
Stax,а можете, пожалуйста, показать, как с её помощью выбрать нужные даты? Просто никогда ей не пользовался
...
Рейтинг: 0 / 0
27.05.2020, 21:02
| Ответить | Цитировать | Написать  
Данные с выборкой дат
    #39963190
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
007alex3
Stax,а можете, пожалуйста, показать, как с её помощью выбрать нужные даты? Просто никогда ей не пользовался

тоже никогда ей не пользовался

лень тестовые данные набивать (мож из-за празника)

поетому просто шаблон (если надо допилите или коллеги форума докритикуют меня)
Код: plsql
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.
with test_1 as (
select 27073 user_id,'lesson' page,to_date('2017-03-01 12:00','yyyy-mm-dd hh24:mi') time_s from dual
union all
select 27073 deal_id,'homework' page, to_date('2017-03-01 12:01','yyyy-mm-dd hh24:mi') time_s from dual
union all
select 27073 deal_id,'view.step' page, to_date('2017-03-01 12:01','yyyy-mm-dd hh24:mi') time_s from dual
union all
select 27073 deal_id,'view.step' page, to_date('2017-03-01 12:02','yyyy-mm-dd hh24:mi') time_s from dual
union all
select 27073 deal_id,'lesson' page, to_date('2017-03-01 12:05','yyyy-mm-dd hh24:mi') time_s from dual
union all
select 27073 deal_id,'homework' page, to_date('2017-03-01 14:00','yyyy-mm-dd hh24:mi') time_s from dual
union all
select 27073 deal_id,'view.step' page, to_date('2017-03-01 14:10','yyyy-mm-dd hh24:mi') time_s from dual
union all
select 27073 deal_id,'lesson' page, to_date('2017-03-02 14:11','yyyy-mm-dd hh24:mi') time_s from dual
)
select * from test_1
 match_recognize(
                  partition by user_id
                  order by time_s
                  measures
                    first(h.time_s) as dt_start,
                    last(l.time_s)  as dt_finish
                  pattern(h v+ l)
                  define 
                     h as page='homework',
                     v as page='view.step'and time_s-last(h.time_s)<=1/24,
                     l as page='lesson' and time_s-last(v.time_s)<=1/24
                 )



USER_IDDT_STARTDT_FINISH2707301.03.2017 12:01:0001.03.2017 12:05:00

....
stax
...
Рейтинг: 0 / 0
28.05.2020, 09:38
| Ответить | Цитировать | Написать  
Данные с выборкой дат
    #39963871
007alex3
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
007alex3
Гость
Уважаемые форумчане, помогите пожалуйста
...
Рейтинг: 0 / 0
29.05.2020, 09:34
| Ответить | Цитировать | Написать  
Данные с выборкой дат
    #39963882
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
007alex3
Уважаемые форумчане, помогите пожалуйста


22141025

что не получаєтся?

....
stax
...
Рейтинг: 0 / 0
29.05.2020, 10:04
| Ответить | Цитировать | Написать  
Данные с выборкой дат
    #39964079
007alex3
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
007alex3
Гость
Stax, к сожалени, версия не позволяет...
...
Рейтинг: 0 / 0
29.05.2020, 14:09
| Ответить | Цитировать | Написать  
Данные с выборкой дат
    #39964153
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
007alex3,

FIRST_VALUE/LAST VALUE ignore nulls

таблица большая?

ps
или влоб подзапросами искать даты для homework - view.step, для view.step - lesson

pss
конец месяца пригрузили меня
.....
stax
...
Рейтинг: 0 / 0
29.05.2020, 16:02
| Ответить | Цитировать | Написать  
Данные с выборкой дат
    #39964327
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
007alex3,
Код: plsql
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.
with test_1 as (
select 27073 user_id,'lesson' page,to_date('2017-03-01 12:00','yyyy-mm-dd hh24:mi') time_s from dual
union all
select 27073 deal_id,'homework' page, to_date('2017-03-01 12:01','yyyy-mm-dd hh24:mi') time_s from dual
union all
select 27073 deal_id,'view.step' page, to_date('2017-03-01 12:01','yyyy-mm-dd hh24:mi') time_s from dual
union all
select 27073 deal_id,'view.step' page, to_date('2017-03-01 12:02','yyyy-mm-dd hh24:mi') time_s from dual
union all
select 27073 deal_id,'lesson' page, to_date('2017-03-01 12:05','yyyy-mm-dd hh24:mi') time_s from dual
union all
select 27073 deal_id,'homework' page, to_date('2017-03-01 14:00','yyyy-mm-dd hh24:mi') time_s from dual
union all
select 27073 deal_id,'view.step' page, to_date('2017-03-01 14:10','yyyy-mm-dd hh24:mi') time_s from dual
union all
select 27073 deal_id,'lesson' page, to_date('2017-03-02 12:05','yyyy-mm-dd hh24:mi') time_s from dual
)
,tt as (
select 
 t.*
,sum(decode(page,'homework',1,0)) over (partition by user_id order by time_s,decode(page,'homework',1,'view.step',2,3)) gh
,sum(decode(page,'lesson',1,0)) over (partition by user_id order by time_s desc,decode(page,'homework',1,'view.step',2,3)) gl
from test_1 t)
select 
  user_id
  ,min(time_s) s
  ,max(time_s) e
from tt
group by user_id,gh,gl
having count(distinct page)=3
   and min(decode(page,'view.step',time_s))-min(time_s)<=1/24
   and max(time_s)-max(decode(page,'view.step',time_s))<=1/24
order by 1,2



Result Set 17
USER_ID S E27073 01.03.2017 12:01:00 01.03.2017 12:05:00
Download CSV

....
stax
...
Рейтинг: 0 / 0
30.05.2020, 11:08
| Ответить | Цитировать | Написать  
Данные с выборкой дат
    #39964898
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Stax,

sum(decode(page,'lesson',1,0)) over (partition by user_id order by time_s desc,decode(page,'homework',1,'view.step',2,3) DESC)

или
decode(page,'homework',3,'view.step',2,1)

.....
stax
...
Рейтинг: 0 / 0
01.06.2020, 09:18
| Ответить | Цитировать | Написать  
Данные с выборкой дат
    #39964981
007alex3
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
007alex3
Гость
Stax, выдает ошибку Aggregate window functions with an ORDER BY clause require a frame clause

Даже с Вашими аналогичными вариантами ниже....
...
Рейтинг: 0 / 0
01.06.2020, 12:41
| Ответить | Цитировать | Написать  
Данные с выборкой дат
    #39964986
dmdmdm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dmdmdm
Участник
Приведите листинг для проверки.

 
Код: plsql
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.
Connected to Oracle Database 18c Express Edition Release 18.0.0.0.0 
Connected as system@//192.168.1.201:1522/XE

SQL> 
SQL> with test_1 as (
  2  select 27073 user_id,'lesson' page,to_date('2017-03-01 12:00','yyyy-mm-dd hh24:mi') time_s from dual
  3  union all
  4  select 27073 deal_id,'homework' page, to_date('2017-03-01 12:01','yyyy-mm-dd hh24:mi') time_s from dual
  5  union all
  6  select 27073 deal_id,'view.step' page, to_date('2017-03-01 12:01','yyyy-mm-dd hh24:mi') time_s from dual
  7  union all
  8  select 27073 deal_id,'view.step' page, to_date('2017-03-01 12:02','yyyy-mm-dd hh24:mi') time_s from dual
  9  union all
 10  select 27073 deal_id,'lesson' page, to_date('2017-03-01 12:05','yyyy-mm-dd hh24:mi') time_s from dual
 11  union all
 12  select 27073 deal_id,'homework' page, to_date('2017-03-01 14:00','yyyy-mm-dd hh24:mi') time_s from dual
 13  union all
 14  select 27073 deal_id,'view.step' page, to_date('2017-03-01 14:10','yyyy-mm-dd hh24:mi') time_s from dual
 15  union all
 16  select 27073 deal_id,'lesson' page, to_date('2017-03-02 12:05','yyyy-mm-dd hh24:mi') time_s from dual
 17  )
 18  ,tt as (
 19  select
 20   t.*
 21  ,sum(decode(page,'homework',1,0)) over (partition by user_id order by time_s,decode(page,'homework',1,'view.step',2,3)) gh
 22  ,sum(decode(page,'lesson',1,0)) over (partition by user_id order by time_s desc,decode(page,'homework',1,'view.step',2,3)) gl
 23  from test_1 t)
 24  select
 25    user_id
 26    ,min(time_s) s
 27    ,max(time_s) e
 28  from tt
 29  group by user_id,gh,gl
 30  having count(distinct page)=3
 31     and min(decode(page,'view.step',time_s))-min(time_s)<=1/24
 32     and max(time_s)-max(decode(page,'view.step',time_s))<=1/24
 33  order by 1,2
 34  ;

   USER_ID S           E
---------- ----------- -----------
     27073 01.03.2017  01.03.2017

SQL>

...
Рейтинг: 0 / 0
01.06.2020, 12:55
| Ответить | Цитировать | Написать  
Данные с выборкой дат
    #39964989
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
007alex3
Stax, выдает ошибку Aggregate window functions with an ORDER BY clause require a frame clause

Даже с Вашими аналогичными вариантами ниже....


яка версия? в какой строке ошибка?
Код: plsql
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.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> with test_1 as (
  2  select 27073 user_id,'lesson' page,to_date('2017-03-01 12:00','yyyy-mm-dd hh24:mi') time_s from dual
  3  union all
  4  select 27073 deal_id,'homework' page, to_date('2017-03-01 12:01','yyyy-mm-dd hh24:mi') time_s from dual
  5  union all
  6  select 27073 deal_id,'view.step' page, to_date('2017-03-01 12:01','yyyy-mm-dd hh24:mi') time_s from dual
  7  union all
  8  select 27073 deal_id,'view.step' page, to_date('2017-03-01 12:02','yyyy-mm-dd hh24:mi') time_s from dual
  9  union all
 10  select 27073 deal_id,'lesson' page, to_date('2017-03-01 12:05','yyyy-mm-dd hh24:mi') time_s from dual
 11  union all
 12  select 27073 deal_id,'homework' page, to_date('2017-03-01 14:00','yyyy-mm-dd hh24:mi') time_s from dual
 13  union all
 14  select 27073 deal_id,'view.step' page, to_date('2017-03-01 14:10','yyyy-mm-dd hh24:mi') time_s from dual
 15  union all
 16  select 27073 deal_id,'view.step' page, to_date('2017-03-02 12:05','yyyy-mm-dd hh24:mi') time_s from dual
 17  union all
 18  select 27073 deal_id,'lesson' page, to_date('2017-03-02 12:05','yyyy-mm-dd hh24:mi') time_s from dual
 19  )
 20  ,tt as (
 21  select
 22   t.*
 23  ,sum(decode(page,'homework',1,0)) over (partition by user_id order by time_s,decode(page,'homework',1,'view.step',2,3)) gh
 24  ,sum(decode(page,'lesson',1,0)) over (partition by user_id order by time_s desc,decode(page,'homework',1,'view.step',2,3) DESC) gl
 25  from test_1 t)
 26  select
 27    user_id
 28    ,min(time_s) s
 29    ,max(time_s) e
 30  from tt
 31  group by user_id,gh,gl
 32  having count(distinct page)=3
 33     and min(decode(page,'view.step',time_s))-min(time_s)<=1/24
 34     and max(time_s)-max(decode(page,'view.step',time_s))<=1/24
 35  order by 1,2
 36  /

   USER_ID S                   E
---------- ------------------- -------------------
     27073 01.03.2017 12:01:00 01.03.2017 12:05:00
     27073 01.03.2017 14:00:00 02.03.2017 12:05:00

SQL>



....
stax
...
Рейтинг: 0 / 0
01.06.2020, 13:02
| Ответить | Цитировать | Написать  
Данные с выборкой дат
    #39964998
007alex3
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
007alex3
Гость
Stax, работаем на redash.io, аналог postgre как я понимаю.
Ошибка в 23 и 24 строках
...
Рейтинг: 0 / 0
01.06.2020, 13:41
| Ответить | Цитировать | Написать  
Данные с выборкой дат
    #39965013
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
007alex3
Stax, работаем на redash.io, аналог postgre как я понимаю.
Ошибка в 23 и 24 строках


не знаком с redash.io

.....
stax
...
Рейтинг: 0 / 0
01.06.2020, 14:06
| Ответить | Цитировать | Написать  
Данные с выборкой дат
    #39965045
dmdmdm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dmdmdm
Участник
007alex3
аналог postgre


Читайте доку по диалекту Postgres.
...
Рейтинг: 0 / 0
01.06.2020, 15:04
| Ответить | Цитировать | Написать  
16 сообщений из 16, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Данные с выборкой дат
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39964898&tid=1881198]:
 0ms
get settings:
 9ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 272ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 70ms
get tp. blocked users:
 2ms
others: 330ms
total:  715ms
созд. / загр.: 715ms
x
x
Закрыть


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