Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 задачка для начинающего  [new]
CloseToSuccess
Member

Откуда:
Сообщений: 23
Задача: В таблицу записана информация об удачных и неудачных попытках подключения к БД (Пользователь, Удачно\Неудачно, Время). Требуется получить список пользователей, которые совершили подряд три (и более) неудачных попыток подключения.

USERNAME LOG_RESULT LOG_TIME
------------------------------ ---------- -------------------------
Smith Удачно 15.12.10
Hacker Неудачно 14.12.10
Hacker Неудачно 13.12.10
Hacker Неудачно 12.12.10
Hacker1 Удачно 11.12.10
Hacker1 Неудачно 10.12.10
Hacker1 Удачно 09.12.10
Hacker1 Неудачно 08.12.10
Hacker1 Удачно 07.12.10
Hacker1 Неудачно 06.12.10
Hacker2 Неудачно 05.12.10

Говорят, хитрость в том что нужно использовать приемы реляционной алгебры, т.е. join-ы но к сожалению моих способностей разобраться в этом пока не достаточно, поэтому прошу помощи у многоуважаемой общественности.
16 дек 11, 15:13    [11778292]     Ответить | Цитировать Сообщить модератору
 Re: задачка для начинающего  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
CloseToSuccess,

stff start_of_group

PS. Джойны не нужны.
16 дек 11, 15:15    [11778317]     Ответить | Цитировать Сообщить модератору
 Re: задачка для начинающего  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
CloseToSuccess,

тут еще проще можно -
1) вычислить в каждой строке lag(LOG_RESULT,1) и lag(LOG_RESULT,2)
2) отобрать уникальных пиплов, у которых.. (уверен, догадаетесь)
16 дек 11, 15:55    [11778683]     Ответить | Цитировать Сообщить модератору
 Re: задачка для начинающего  [new]
publexus
Member

Откуда: Москва
Сообщений: 955
CloseToSuccess,

select distinct USERNAME
from (
	select t.*, lag(LOG_RESULT) over(partition by username order by LOG_TIME) LOG_RESULT_1
		, lag(LOG_RESULT,2) over(partition by username order by LOG_TIME) LOG_RESULT_2
	from t)
where LOG_RESULT='Неудачно' and LOG_RESULT=LOG_RESULT_1 and LOG_RESULT=LOG_RESULT_2
16 дек 11, 16:47    [11779183]     Ответить | Цитировать Сообщить модератору
 Re: задачка для начинающего  [new]
CloseToSuccess
Member

Откуда:
Сообщений: 23
О, огромное спасибо! Стал разбираться с LAG-ами, а тут уже решение!!!))
16 дек 11, 17:01    [11779310]     Ответить | Цитировать Сообщить модератору
 Re: задачка для начинающего  [new]
CloseToSuccess
Member

Откуда:
Сообщений: 23
А возможно ли решить эту задачу без использования аналитических функций?
17 дек 11, 19:24    [11782968]     Ответить | Цитировать Сообщить модератору
 Re: задачка для начинающего  [new]
AmKad
Member

Откуда:
Сообщений: 5222
CloseToSuccess
А возможно ли решить эту задачу без использования аналитических функций?
Лень разбираться с lag-ами?
17 дек 11, 20:03    [11783072]     Ответить | Цитировать Сообщить модератору
 Re: задачка для начинающего  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
CloseToSuccess
А возможно ли решить эту задачу без использования аналитических функций?


Something like:

with sample_table as (
                      select 'Smith' username,'SUCCESS' log_result,to_date('15.12.10','dd.mm.yy') log_time from dual union all
                      select 'Hacker','FAILURE',to_date('14.12.10','dd.mm.yy') from dual union all
                      select 'Hacker','FAILURE',to_date('13.12.10','dd.mm.yy') from dual union all
                      select 'Hacker','FAILURE',to_date('12.12.10','dd.mm.yy') from dual union all
                      select 'Hacker1','SUCCESS',to_date('11.12.10','dd.mm.yy') from dual union all
                      select 'Hacker1','FAILURE',to_date('10.12.10','dd.mm.yy') from dual union all
                      select 'Hacker1','SUCCESS',to_date('09.12.10','dd.mm.yy') from dual union all
                      select 'Hacker1','SUCCESS',to_date('07.12.10','dd.mm.yy') from dual union all
                      select 'Hacker1','FAILURE',to_date('06.12.10','dd.mm.yy') from dual union all
                      select 'Hacker2','FAILURE',to_date('05.12.10','dd.mm.yy') from dual
                     ),
               t1 as (
                      select  *
                        from  sample_table
                        order by username,
                                 log_time desc
                     ),
               t2 as (
                      select  t1.*,
                              rownum rn
                        from  t1
                     ),
               t3 as (
                      select  *
                        from  sample_table
                        order by log_time
                     ),
               t4 as (
                      select  t3.*,
                              rownum rn
                        from  t3
                     )
select  distinct t2.username
  from  t2,
        t4
  where t4.username = t2.username
    and t4.log_time = t2.log_time
    and t4.log_result = t2.log_result
    and t4.log_result = 'FAILURE'
  group by t2.username,
           t2.rn + t4.rn
  having count(*) >= 3
/

USERNAM
-------
Hacker

SQL> 


SY.
17 дек 11, 23:26    [11783590]     Ответить | Цитировать Сообщить модератору
 Re: задачка для начинающего  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
Never mind, my suggestion will not work.

SY.
17 дек 11, 23:35    [11783609]     Ответить | Цитировать Сообщить модератору
 Re: задачка для начинающего  [new]
CloseToSuccess
Member

Откуда:
Сообщений: 23
AmKad
CloseToSuccess
А возможно ли решить эту задачу без использования аналитических функций?
Лень разбираться с lag-ами?


Нет, просто на данном этапе наших занятий предполагается, что они нам неизвестны, и нужно решить задачу без них)
18 дек 11, 00:09    [11783689]     Ответить | Цитировать Сообщить модератору
 Re: задачка для начинающего  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10045
CloseToSuccess
А возможно ли решить эту задачу без использования аналитических функций?


This should work:

with sample_table as (
                      select 'Smith' username,'SUCCESS' log_result,to_date('15.12.10','dd.mm.yy') log_time from dual union all
                      select 'Hacker','FAILURE',to_date('14.12.10','dd.mm.yy') from dual union all
                      select 'Hacker','FAILURE',to_date('13.12.10','dd.mm.yy') from dual union all
                      select 'Hacker','FAILURE',to_date('12.12.10','dd.mm.yy') from dual union all
                      select 'Hacker1','SUCCESS',to_date('11.12.10','dd.mm.yy') from dual union all
                      select 'Hacker1','FAILURE',to_date('10.12.10','dd.mm.yy') from dual union all
                      select 'Hacker1','SUCCESS',to_date('09.12.10','dd.mm.yy') from dual union all
                      select 'Hacker1','SUCCESS',to_date('07.12.10','dd.mm.yy') from dual union all
                      select 'Hacker1','FAILURE',to_date('06.12.10','dd.mm.yy') from dual union all
                      select 'Hacker2','FAILURE',to_date('05.12.10','dd.mm.yy') from dual
                     ),
               t1 as (
                      select  *
                        from  sample_table
                        order by username,
                                 log_time
                     ),
               t2 as (
                      select  t1.*,
                              rownum rn
                        from  t1
                     ),
               t3 as (
                      select  a.*,
                              case (select b.log_result from t2 b where b.username = a.username and b.rn = a.rn - 1)
                                when 'FAILURE' then 0
                                else 1
                              end start_of_group
                        from  t2 a
                        where log_result = 'FAILURE'
                     )
select  distinct username
  from  t3
  where level = 3
  start with start_of_group = 1
  connect by username = prior username
         and rn = prior rn + 1
         and level <= 3
/

USERNAM
-------
Hacker

SQL> 


SY.
18 дек 11, 00:39    [11783770]     Ответить | Цитировать Сообщить модератору
 Re: задачка для начинающего  [new]
shamrock
Guest
Сессия близко...)

Как вариант,

with src as (
  select 'Smith' username,'SUCCESS' log_result,to_date('15.12.10','dd.mm.yy') log_time from dual union all
  select 'Hacker','FAILURE',to_date('14.12.10','dd.mm.yy') from dual union all
  select 'Hacker','FAILURE',to_date('13.12.10','dd.mm.yy') from dual union all
  select 'Hacker','FAILURE',to_date('12.12.10','dd.mm.yy') from dual union all
  select 'Hacker1','SUCCESS',to_date('11.12.10','dd.mm.yy') from dual union all
  select 'Hacker1','FAILURE',to_date('10.12.10','dd.mm.yy') from dual union all
  select 'Hacker1','SUCCESS',to_date('09.12.10','dd.mm.yy') from dual union all
  select 'Hacker1','SUCCESS',to_date('07.12.10','dd.mm.yy') from dual union all
  select 'Hacker1','FAILURE',to_date('06.12.10','dd.mm.yy') from dual union all
  select 'Hacker2','FAILURE',to_date('05.12.10','dd.mm.yy') from dual)
select 
  username
 from 
  src att1 inner join src att2
  on att1.username=att2.username
  and att1.log_result=att2.log_result
  and att1.log_time<att2.log_time
  inner join src att3
  on att1.username=att3.username
  and att1.log_result=att3.log_result
  and att3.log_time<att1.log_time
 where
  att1.log_result = 'FAILURE'
  and not exists
  (select 
     * 
    from
     src tmp
    where 
     tmp.username=att1.username and
     tmp.log_result<>att1.log_result and
     (tmp.log_time between att1.log_time and att2.log_time or
      tmp.log_time between att3.log_time and att1.log_time))                     
 group by 
  username;
18 дек 11, 01:18    [11783890]     Ответить | Цитировать Сообщить модератору
 Re: задачка для начинающего  [new]
CloseToSuccess
Member

Откуда:
Сообщений: 23
shamrock, спс, так оно) Но, предполагается что БД будет пополняться.
18 дек 11, 01:35    [11783952]     Ответить | Цитировать Сообщить модератору
 Re: задачка для начинающего  [new]
shamrock
Guest
CloseToSuccess,

какие тогда проблемы возникнут?
18 дек 11, 01:54    [11783999]     Ответить | Цитировать Сообщить модератору
 Re: задачка для начинающего  [new]
CloseToSuccess
Member

Откуда:
Сообщений: 23
shamrock
CloseToSuccess,

какие тогда проблемы возникнут?


Необходимость модификации запроса, написание дополнительных селектов
18 дек 11, 10:43    [11784233]     Ответить | Цитировать Сообщить модератору
 Re: задачка для начинающего  [new]
CloseToSuccess
Member

Откуда:
Сообщений: 23
shamrock
CloseToSuccess,

какие тогда проблемы возникнут?


Необходимость модификации запроса, написание дополнительных селектов

но что-то и так не работает((

Error starting at line 1 in command:
with src as (
select 'Smith'...
...
Error at Command Line:35 Column:10
Error report:
SQL Error: ORA-00918: столбец определен неоднозначно
18 дек 11, 10:58    [11784248]     Ответить | Цитировать Сообщить модератору
 Re: задачка для начинающего  [new]
CloseToSuccess
Member

Откуда:
Сообщений: 23
CloseToSuccess
shamrock
CloseToSuccess,

какие тогда проблемы возникнут?


Необходимость модификации запроса, написание дополнительных селектов


Поспешил, с этим вроде разобрался) сорри за двойной пост, но ошибки так и остаются
18 дек 11, 11:37    [11784280]     Ответить | Цитировать Сообщить модератору
 Re: задачка для начинающего  [new]
shamrock
Guest
CloseToSuccess, cудя по всему версия БД не поддерживает конструкцию with (насколько я помню, она появилась в 10).
18 дек 11, 12:08    [11784325]     Ответить | Цитировать Сообщить модератору
 Re: задачка для начинающего  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54383
shamrock
CloseToSuccess, cудя по всему версия БД не поддерживает конструкцию with (насколько я помню, она появилась в 10).
в 9-ке есть
18 дек 11, 12:09    [11784328]     Ответить | Цитировать Сообщить модератору
 Re: задачка для начинающего  [new]
shamrock
Guest
CloseToSuccess, похоже я не о том... раз столбец определен неоднозначно, значит
username
в select и group by нужно заменить на
att1.username
...
18 дек 11, 12:16    [11784335]     Ответить | Цитировать Сообщить модератору
 Re: задачка для начинающего  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54383
еще примерчик
без аналитики и exists и connect by
WITH sample_table AS
 (
      SELECT 'Smith' username,'1' log_result,TO_DATE('15.12.10','dd.mm.yy') log_time FROM dual UNION ALL
      SELECT 'Hacker' ,'0',TO_DATE('14.12.10','dd.mm.yy') FROM dual UNION ALL
      SELECT 'Hacker' ,'0',TO_DATE('13.12.10','dd.mm.yy') FROM dual UNION ALL
      SELECT 'Hacker' ,'0',TO_DATE('12.12.10','dd.mm.yy') FROM dual UNION ALL
      SELECT 'Hacker1','1',TO_DATE('11.12.10','dd.mm.yy') FROM dual UNION ALL
      SELECT 'Hacker1','0',TO_DATE('10.12.10','dd.mm.yy') FROM dual UNION ALL
      SELECT 'Hacker1','1',TO_DATE('09.12.10','dd.mm.yy') FROM dual UNION ALL
      SELECT 'Hacker1','1',TO_DATE('07.12.10','dd.mm.yy') FROM dual UNION ALL
      SELECT 'Hacker1','0',TO_DATE('06.12.10','dd.mm.yy') FROM dual UNION ALL
      SELECT 'Hacker2','1',TO_DATE('15.12.10','dd.mm.yy') FROM dual UNION ALL
      SELECT 'Hacker2','0',TO_DATE('11.12.10','dd.mm.yy') FROM dual UNION ALL
      SELECT 'Hacker2','0',TO_DATE('10.12.10','dd.mm.yy') FROM dual UNION ALL
      SELECT 'Hacker2','1',TO_DATE('09.12.10','dd.mm.yy') FROM dual UNION ALL
      SELECT 'Hacker2','0',TO_DATE('07.12.10','dd.mm.yy') FROM dual UNION ALL
      SELECT 'Hacker2','0',TO_DATE('06.12.10','dd.mm.yy') FROM dual UNION ALL
      SELECT 'Hacker2','0',TO_DATE('05.12.10','dd.mm.yy') FROM dual
     ),
t1 AS (
      SELECT  *
        FROM  sample_table
       ORDER BY username,
                log_time
     ),
t0 AS (
      SELECT  t1.*,
              ROWNUM rn
        FROM  t1
     ),
t3 AS
(
SELECT t1.username, t1.rn, t1.log_result
  FROM t0 t1, t0 t2
 WHERE t2.rn-1=t1.rn
   AND t2.username=t1.username
   AND t2.log_result = t1.log_result
   AND t1.log_result = 0
 ORDER BY
       t1.username,
       t1.log_time
)

SELECT DISTINCT
       username
  FROM t3
 GROUP BY username, ROWNUM-rn
HAVING COUNT(*) >=2
18 дек 11, 12:33    [11784366]     Ответить | Цитировать Сообщить модератору
 Re: задачка для начинающего  [new]
CloseToSuccess
Member

Откуда:
Сообщений: 23
andreymx
еще примерчик
без аналитики и exists и connect by

Спасибо, то что надо!
18 дек 11, 13:33    [11784497]     Ответить | Цитировать Сообщить модератору
 Re: задачка для начинающего  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54383
CloseToSuccess,

сам объяснить сможешь преподу?
18 дек 11, 13:39    [11784506]     Ответить | Цитировать Сообщить модератору
 Re: задачка для начинающего  [new]
CloseToSuccess
Member

Откуда:
Сообщений: 23
andreymx
CloseToSuccess,

сам объяснить сможешь преподу?


да, уже разобрался, спасибо97
18 дек 11, 15:47    [11784744]     Ответить | Цитировать Сообщить модератору
 Re: задачка для начинающего  [new]
dfgsdfg
Guest
CloseToSuccess,
короткое решение
with sample_table as (
select 'Smith' username,'SUCCESS' log_result,to_date('15.12.10','dd.mm.yy') log_time from dual union
select 'Hacker','FAILURE',to_date('14.12.10','dd.mm.yy') from dual union
select 'Hacker','FAILURE',to_date('13.12.10','dd.mm.yy') from dual union
select 'Hacker','FAILURE',to_date('12.12.10','dd.mm.yy') from dual union
select 'Hacker1','SUCCESS',to_date('11.12.10','dd.mm.yy') from dual union
select 'Hacker1','FAILURE',to_date('10.12.10','dd.mm.yy') from dual union
select 'Hacker1','SUCCESS',to_date('09.12.10','dd.mm.yy') from dual union
select 'Hacker1','SUCCESS',to_date('07.12.10','dd.mm.yy') from dual union
select 'Hacker1','FAILURE',to_date('06.12.10','dd.mm.yy') from dual union
select 'Hacker2','FAILURE',to_date('05.12.10','dd.mm.yy') from dual
)
select distinct a.UserName
from sample_table a
inner join sample_table b on a.UserName=b.UserName and a.Log_Time<b.Log_time and b.LOG_result='FAILURE'
inner join sample_table c on a.UserName=c.UserName and b.Log_Time<c.Log_time and c.LOG_result='FAILURE'
left join sample_table n on a.UserName=n.UserName and n.LOG_result='SUCCESS' and n.Log_Time between a.Log_Time and c.Log_Time
where a.LOG_result='FAILURE' and n.UserName IS NULL
31 янв 12, 16:29    [12005403]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить