Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Данные с выборкой дат / 16 сообщений из 16, страница 1 из 1
27.05.2020, 17:09
    #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:35
    #39962962
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Данные с выборкой дат
007alex3,

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

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.05.2020, 21:02
    #39963069
007alex3
007alex3
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Данные с выборкой дат
Stax,а можете, пожалуйста, показать, как с её помощью выбрать нужные даты? Просто никогда ей не пользовался
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
28.05.2020, 09:38
    #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
| Ответить | Цитировать | Написать  
29.05.2020, 09:34
    #39963871
007alex3
007alex3
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Данные с выборкой дат
Уважаемые форумчане, помогите пожалуйста
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
29.05.2020, 10:04
    #39963882
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Данные с выборкой дат
007alex3
Уважаемые форумчане, помогите пожалуйста


22141025

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

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

FIRST_VALUE/LAST VALUE ignore nulls

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

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

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

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


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

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


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


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