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

Откуда: Заветы Ильича
Сообщений: 152
Есть лог входа пользователей в систему с информацией пользователь-дата входа.
USERAUTH_DATE
1'01-01-2018'
2'01-01-2018'
1'02-01-2018'
3'01-01-2018'
1'03-01-2018'



Как сделать запрос, который покажет кто из пользователей заходил каждый день в течении недели или месяца?
5 дек 18, 12:58    [21754256]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
MaximaXXL
Member

Откуда: Киев
Сообщений: 632
sossisson,

count(distinct ) + group by + having
5 дек 18, 13:02    [21754261]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
sossisson
Member

Откуда: Заветы Ильича
Сообщений: 152
MaximaXXL,
это понятно. Но я хотел выбрать именно, где 7 дней подряд люди есть в логах.
Я так понимаю, что без аналитических функций здесь не обойтись, но вот как?
5 дек 18, 16:10    [21754618]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
982183
Member

Откуда: VL
Сообщений: 2475
Тебе и ответили
count(distinct )=7
count(distinct )=30(31)
5 дек 18, 16:12    [21754623]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
sossisson
Member

Откуда: Заветы Ильича
Сообщений: 152
982183,
разве это будет работать, если пользователь заходил не все дни подряд, а были перерывы по 3-4 дня?
5 дек 18, 16:15    [21754627]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
982183
Member

Откуда: VL
Сообщений: 2475
Задача тогда не та, которую ты написал.
5 дек 18, 16:28    [21754639]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
sossisson
Member

Откуда: Заветы Ильича
Сообщений: 152
982183,
я же написал в первом посте "запрос, который покажет кто из пользователей заходил каждый день в течении недели или месяца"
5 дек 18, 16:33    [21754656]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
Dshedoo
Member

Откуда:
Сообщений: 239
Поищи темы про объединение временных промежутков.
Сформируешь данные по типу:

1 '01-01-2018' '03-01-2018'
2 '01-01-2018' '11-01-2018'
3 '01-01-2018' '02-01-2018'

Ну и выбираешь те записи, у которых разница между датами >= 7.
5 дек 18, 16:45    [21754672]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
MaximaXXL
Member

Откуда: Киев
Сообщений: 632
sossisson,

Напишите Ваш селект, или хотябы тестовые данные в формате with, а то пока не понятно что вообще Вы хотите

select "USER", count(distinct trunc(AUTH_DATE))
from table
where trunc(AUTH_DATE) between :p_Date_From and :p_Date_To
group by "USER"
having count(distinct trunc(AUTH_DATE)) = 1+:p_Date_To - :p_Date_From
5 дек 18, 16:51    [21754687]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1134
sossisson,

надеюсь, AUTH_DATE имеет тип DATE?

но в целом, "курите" в эту торону :
trunc(AUTH_DATE) - dense_rank() over(partition by user order by trunc(AUTH_DATE)) as grp_id

далее - группировка по user , grp_id и фильтрация результата группировки по уже упомянутому count(distinct trunc(AUTH_DATE)) в упомянутом же HAVING-е
5 дек 18, 16:53    [21754689]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
-2-
Member

Откуда:
Сообщений: 14079
sossisson
в течении недели или месяца
а это течение, все-таки, семь дней, семь суток или неделя. И течение месяца, это месяц или сколько-то там суток, в зависимости от ...
5 дек 18, 16:54    [21754693]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1134
MaximaXXL
sossisson,

Напишите Ваш селект, или хотябы тестовые данные в формате with, а то пока не понятно что вообще Вы хотите

select "USER", count(distinct trunc(AUTH_DATE))
from table
where trunc(AUTH_DATE) between :p_Date_From and :p_Date_To
group by "USER"
having count(distinct trunc(AUTH_DATE)) = 1+:p_Date_To - :p_Date_From
пользователь мог три дня ходить по сто раз, затем день перекурить и ещё четыре дня ходить по 10 раз. Будет ли это "семь дней подряд" в постановке автора? Вряд ли. А в решении вашим запросом? Запросто...
5 дек 18, 16:55    [21754695]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
Zhelezyakin
Member

Откуда:
Сообщений: 3
sossisson,

Думаю, вам надо пойти от обратного . Для выбранного интервала ищите даты когда данный пользователь не регистрировался.
Если такой запрос вернет ноль записей , значит данный пользователь регистрировался каждый день.
5 дек 18, 16:57    [21754699]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
982183
Member

Откуда: VL
Сообщений: 2475
sossisson
я же написал в первом посте "запрос, который покажет кто из пользователей заходил каждый день в течении недели или месяца"

А выборку ты делаешь за неделю/месяц или за более длительный срок?
5 дек 18, 16:58    [21754704]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
MaximaXXL
Member

Откуда: Киев
Сообщений: 632
Щукина Анна,

Не совсем понятно при чем тут перекур в 1 день, если Вас смущает 1+ то это из за between который учитывает оба параметра.
итого например между 07.01.2018 и 01.01.2018 разница 6 (а человек думает что 7 ) отсюда и появилось 1+
5 дек 18, 17:02    [21754710]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1413
sossisson
982183,
разве это будет работать, если пользователь заходил не все дни подряд, а были перерывы по 3-4 дня?


не важно сколько раз заходил count(distinct trunc(AUTH_DATE)) даст для одного дня 1

если были перерывы, то count не совпадет с к-вом дней за период

другой вопрос, учитывать только рабочие дни или календарные?

....
stax
5 дек 18, 17:18    [21754725]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1134
MaximaXXL,

Дату начала и дату кончала - это уже вы сами додумали. В исходной постановке задачи у ТС про это нет ни слова. ;). Положим, период - плавающий. Первый пользователь логинился каждый день с первого по седьмое число. Второй - с десятого по шестнадцатое. Формально - оба логинились каждый день в течении недели. Но если ограничить период датами с шестого по двенадцатое, как предлагаете вы, то ни один пользователь не логинился семь дней подряд.
Понимаете, о чем я? ;)
Единственное, в чем с вами не поспоришь, так это то, что задача сформулирована очень размыто и нечетко. И каждый её решает, как понял. А что на самом деле нужно Т -
5 дек 18, 17:48    [21754780]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1134
Щукина Анна
MaximaXXL,

Дату начала и дату кончала - это уже вы сами додумали. В исходной постановке задачи у ТС про это нет ни слова. ;). Положим, период - плавающий. Первый пользователь логинился каждый день с первого по седьмое число. Второй - с десятого по шестнадцатое. Формально - оба логинились каждый день в течении недели. Но если ограничить период датами с шестого по двенадцатое, как предлагаете вы, то ни один пользователь не логинился семь дней подряд.
Понимаете, о чем я? ;)
Единственное, в чем с вами не поспоришь, так это то, что задача сформулирована очень размыто и нечетко. И каждый её решает, как понял. А что на самом деле нужно ТС-у - одному ему известно
5 дек 18, 17:49    [21754783]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1413
Щукина Анна,

мы по разному понимаем
автор
Как сделать запрос, который покажет кто из пользователей заходил каждый день в течении недели или месяца?

я считаю что надо показать пользователей которые логинились каждый день за конкретный период,
напр в октябре 2018 года

.....
stax
5 дек 18, 18:01    [21754800]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
mama.said
Member

Откуда:
Сообщений: 59
sossisson,

хорошо бы еще понимать в каком виде нужен аутпут

with dt as (
  select 1 as usr, to_date('01-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('01-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 2 as usr, to_date('01-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('02-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 3 as usr, to_date('01-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('03-01-2018', 'dd-mm-yyyy') as auth_date from dual
)
select * 
from dt
pivot (
  count(*) for auth_date in (to_date('01-01-2018', 'dd-mm-yyyy') as "01_jan_2018", to_date('02-01-2018', 'dd-mm-yyyy') as "02_jan_2018", to_date('03-01-2018', 'dd-mm-yyyy') as "03_jan_2018") 
)


USR  01_jan_2018  02_jan_2018 03_jan_2018
1	      2	         1	     1
2	      1	         0           0
3	      1	         0           0
5 дек 18, 18:41    [21754832]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
sossisson
Member

Откуда: Заветы Ильича
Сообщений: 152
mama.said,
Выборку делаю за год.
В общем, при помощи двух group by я могу добиться для каждого пользователя отображения того, сколько дней за год он логинился.
Проблема в том, что я хочу найти тех, кто без перерыва на отпуски и больничные работал. Вот для этого и хочу найти того, кто подряд 7-20-100 дней заходил на сервер.
6 дек 18, 07:45    [21755287]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1134
sossisson,

Вам уже все возможные случаи подсказали, как решать. Осталось только выбрать наиболее подходящий.
6 дек 18, 07:57    [21755292]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
-2-
Member

Откуда:
Сообщений: 14079
sossisson
Проблема в том, что я хочу найти тех, кто без перерыва на отпуски и больничные работал. Вот для этого и хочу найти того, кто подряд 7-20-100 дней заходил на сервер.
7 дней подряд без отпуска это произвол!
6 дек 18, 07:58    [21755293]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
123йй
Member

Откуда:
Сообщений: 1445
sossisson
Проблема в том, что я хочу найти тех, кто без перерыва на отпуски и больничные работал.

а также без выходных и праздников, т.е 24/7/365.
так это можно без запроса получить - сервер БД
6 дек 18, 08:54    [21755315]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
CN_1
Member

Откуда:
Сообщений: 3
sossisson,

Проще написать функцию и не париться с запросом.
6 дек 18, 10:03    [21755393]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1413
sossisson,
  1  with dt as (
  2    select 1 as usr, to_date('01-01-2018 10:00', 'dd-mm-yyyy hh24:mi') as auth_date from dual union all
  3    select 1 as usr, to_date('01-01-2018 11:00', 'dd-mm-yyyy hh24:mi') as auth_date from dual union all
  4    select 1 as usr, to_date('02-01-2018 10:00', 'dd-mm-yyyy hh24:mi') as auth_date from dual union all
  5    select 1 as usr, to_date('03-01-2018 10:00', 'dd-mm-yyyy hh24:mi') as auth_date from dual union all
  6    select 1 as usr, to_date('04-01-2018 10:00', 'dd-mm-yyyy hh24:mi') as auth_date from dual union all
  7    select 1 as usr, to_date('06-01-2018 10:00', 'dd-mm-yyyy hh24:mi') as auth_date from dual union all
  8    select 1 as usr, to_date('07-01-2018 10:00', 'dd-mm-yyyy hh24:mi') as auth_date from dual union all
  9    select 2 as usr, to_date('02-01-2018 10:00', 'dd-mm-yyyy hh24:mi') as auth_date from dual union all
 10    select 2 as usr, to_date('03-01-2018 11:00', 'dd-mm-yyyy hh24:mi') as auth_date from dual union all
 11    select 2 as usr, to_date('11-01-2018 10:00', 'dd-mm-yyyy hh24:mi') as auth_date from dual union all
 12    select 2 as usr, to_date('11-01-2018 12:00', 'dd-mm-yyyy hh24:mi') as auth_date from dual
 13  )
 14  ,t as (select distinct usr,trunc(auth_date) dat from dt)
 15  ,tt as (select t.*,dat-row_number() over (partition by usr order by dat) gr from t)
 16  ,ttt as (select usr,max(dat)-min(dat)+1 dn from tt group by usr,gr)
 17* select usr,max(dn) max_dn from ttt group by usr
SQL> /

       USR     MAX_DN
---------- ----------
         1          4
         2          2


.....
stax
6 дек 18, 10:27    [21755441]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
MazoHist
Member

Откуда:
Сообщений: 36
sossisson,

RTFM start_of_group
with dt as (
  select 1 as usr, to_date('01-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('01-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 2 as usr, to_date('01-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('02-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 3 as usr, to_date('01-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('03-01-2018', 'dd-mm-yyyy') as auth_date from dual
)
select usr
     , sog 
  from (select usr
             , lead(auth_date,1,auth_date+1) over (partition by usr order by auth_date )-auth_date sog
          from dt)
  where sog = 1
group by usr, sog 
  having count(*) = 3
6 дек 18, 10:30    [21755443]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
MazoHist
Member

Откуда:
Сообщений: 36
MazoHist
sossisson,

RTFM start_of_group

не RTFM а STFF
6 дек 18, 10:32    [21755448]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
-2-
Member

Откуда:
Сообщений: 14079
CN_1
Проще написать функцию и не париться с запросом.
Знание одного языка не предполагает возможность сравнивать с незнакомым языком. Знание в должной степени двух языков декларативного и императивного для данной задачи должно отдавать предпочтение декларативному.
Остается третий вариант, не отягощающий принятие заключений о "проще" - незнание ни sql, ни plsql.
6 дек 18, 11:00    [21755503]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
MaximaXXL
Member

Откуда: Киев
Сообщений: 632
sossisson
mama.said,
Выборку делаю за год.
В общем, при помощи двух group by я могу добиться для каждого пользователя отображения того, сколько дней за год он логинился.
Проблема в том, что я хочу найти тех, кто без перерыва на отпуски и больничные работал. Вот для этого и хочу найти того, кто подряд 7-20-100 дней заходил на сервер.


Я надеюсь Вы из отдела кадров и хотите отправить человека в отпуск иначе это нарушение конституции

Похоже Вам надо что-то вроде такого, но постановка оставляет желать лучшего

with dt as (
  select 1 as usr, to_date('01-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('01-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 2 as usr, to_date('01-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('02-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 3 as usr, to_date('01-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('05-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('06-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('07-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('08-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('10-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('11-01-2018', 'dd-mm-yyyy') as auth_date from dual
)
select usr, sum(decode(delta,1,1,0))+1 result
from( select usr
           , delta
           , sum(flag) over (partition by usr order by auth_date asc, usr) as group_no 
           , auth_date
       from( select usr 
                  , auth_date - lag(auth_date,1,auth_date) over (partition by usr order by auth_date ) delta 
                  , decode(auth_date - lag(auth_date,1,auth_date) over (partition by usr order by auth_date ),1,null,0,null,1) flag
                  , auth_date
               from dt)
)
group by usr, group_no
--having sum(decode(delta,1,1,0))+1 >= 3
order by usr, min(auth_date)
6 дек 18, 11:38    [21755568]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
Щукина Анна
Member

Откуда:
Сообщений: 1134
MaximaXXL
sossisson
mama.said,
Выборку делаю за год.
В общем, при помощи двух group by я могу добиться для каждого пользователя отображения того, сколько дней за год он логинился.
Проблема в том, что я хочу найти тех, кто без перерыва на отпуски и больничные работал. Вот для этого и хочу найти того, кто подряд 7-20-100 дней заходил на сервер.


Я надеюсь Вы из отдела кадров и хотите отправить человека в отпуск иначе это нарушение конституции

Похоже Вам надо что-то вроде такого, но постановка оставляет желать лучшего

with dt as (
  select 1 as usr, to_date('01-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('01-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 2 as usr, to_date('01-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('02-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 3 as usr, to_date('01-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('05-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('06-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('07-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('08-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('10-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('11-01-2018', 'dd-mm-yyyy') as auth_date from dual
)
select usr, sum(decode(delta,1,1,0))+1 result
from( select usr
           , delta
           , sum(flag) over (partition by usr order by auth_date asc, usr) as group_no 
           , auth_date
       from( select usr 
                  , auth_date - lag(auth_date,1,auth_date) over (partition by usr order by auth_date ) delta 
                  , decode(auth_date - lag(auth_date,1,auth_date) over (partition by usr order by auth_date ),1,null,0,null,1) flag
                  , auth_date
               from dt)
)
group by usr, group_no
--having sum(decode(delta,1,1,0))+1 >= 3
order by usr, min(auth_date)


Столько много лишних телодвижений...

Вот решение из разряда "Первое, что пришло в голову", на ваших тестовых данных):
with dt as (
  select 1 as usr, to_date('01-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('01-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 2 as usr, to_date('01-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('02-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 3 as usr, to_date('01-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('05-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('06-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('07-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('08-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('10-01-2018', 'dd-mm-yyyy') as auth_date from dual union all
  select 1 as usr, to_date('11-01-2018', 'dd-mm-yyyy') as auth_date from dual
)
--
--
select usr, count(distinct trunc(auth_date)) as cnt 
  from (
          select dt.*
               , dense_rank() over(partition by usr order by trunc(auth_date)) as dnr
            from dt
       ) v
 group by usr, trunc(auth_date) - dnr
 order by usr, min(trunc(auth_date))
6 дек 18, 12:13    [21755620]     Ответить | Цитировать Сообщить модератору
 Re: Простая выборка с данными  [new]
merch
Member

Откуда:
Сообщений: 126
-2-
7 дней подряд без отпуска это произвол!


Так соссиссон и ищет таких людей чтобы премировать.
6 дек 18, 12:28    [21755637]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Oracle Ответить