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

Откуда:
Сообщений: 292
Добрый день , есть таблица изменения статуса собтрудника , тут входит либо перемещение на новую должность либо повышения зарплаты , вот отрывок :

id	дат_назн	оклад	ид_сотруд	код_департ	код_должн
70943	04.09.2006	1500,00	1051	        01000502	925
71475	06.02.2007	2500,00	1051	        01000502	946
72125	01.06.2007	3500,00	1051	        01000502	946
74735	01.09.2007	4400,00	1051	        01000502	946
77652	01.03.2008	6500,00	1051	        01000502	946
78162	02.06.2008	7000,00	1051	        01000502	925
79181	02.01.2009	7700,00	1051	        01000502	925
80600	03.06.2010	9000,00	1051	        01000502	925

 


Мне нужно выбрать последнюю дату перемещения сотрудника , в данном случае это 02.06.2008 . как можно в данном случае брать все строки от max(дат_назн) и до min(дат_назн) где поле код_должн меняет свое значение .... Уже всю голову сломал .

Спасибо большое .
8 апр 19, 10:33    [21855538]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на последние N строк  [new]
Anton_Demin
Member

Откуда: Ставрополь
Сообщений: 261
maverick2104,

WITH empl AS (
SELECT 1 empl_id,to_date('01.02.2015','DD.MM.YYYY') dt, '123' dept, 1000 summ from dual union
SELECT 1 empl_id,to_date('01.03.2015','DD.MM.YYYY') dt, '123' dept, 1100 summ from dual union
SELECT 1 empl_id,to_date('01.04.2017','DD.MM.YYYY') dt, '321' dept, 1110 summ from dual union
SELECT 2 empl_id,to_date('01.02.2015','DD.MM.YYYY') dt, '000' dept, 1010 summ from dual union
SELECT 2 empl_id,to_date('02.02.2015','DD.MM.YYYY') dt, '000' dept, 1011 summ from dual union
SELECT 2 empl_id,to_date('04.02.2015','DD.MM.YYYY') dt, '000' dept, 1111 summ from dual 
)
SELECT * FROM empl e WHERE 1=1  AND e.dt = (SELECT MAX(e2.dt) FROM empl e2 WHERE e.empl_id = e2.empl_id)

EMPL_ID	           DT	       DEPT	   SUMM
1	        01.04.2017	321	   1110
2	        04.02.2015	000	   1111
8 апр 19, 10:55    [21855588]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на последние N строк  [new]
maverick2104
Member

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

Смотрим внимательно , что код_департ тот же самый , по нему никак не отфильтровать .
8 апр 19, 11:01    [21855603]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на последние N строк  [new]
j2k
Member

Откуда: Новосибирск
Сообщений: 540
maverick2104, если бы вы тестовые данные в with обернули, вам бы уже помогли
8 апр 19, 11:07    [21855614]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на последние N строк  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром продолжается. КЯЗ
Сообщений: 28823
maverick2104
перемещения
x <> lag(x)
8 апр 19, 11:10    [21855623]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на последние N строк  [new]
Dshedoo
Member

Откуда:
Сообщений: 280
with q (id,  data_naz, oklad, id_sotr, kod_dep, kod_dol) as (
select 70943,  to_date('04.09.2006','DD.MM.YYYY'),  1500, 1051, '01000502',  925 from dual union all
select 71475,  to_date('06.02.2007','DD.MM.YYYY'),  2500, 1051, '01000502',  946 from dual union all
select 72125,  to_date('01.06.2007','DD.MM.YYYY'),  3500, 1052, '01000502',  946 from dual union all
select 74735,  to_date('01.09.2007','DD.MM.YYYY'),  4400, 1052, '01000502',  946 from dual union all
select 77652,  to_date('01.03.2008','DD.MM.YYYY'),  6500, 1051, '01000502',  946 from dual union all
select 78162,  to_date('02.06.2008','DD.MM.YYYY'),  7000, 1051, '01000502',  925 from dual union all
select 79181,  to_date('02.01.2009','DD.MM.YYYY'),  7700, 1052, '01000502',  945 from dual union all
select 80600,  to_date('03.06.2010','DD.MM.YYYY'),  9000, 1051, '01000502',	925 from dual )

, w as (select q.*
, row_number() OVER (partition by id_sotr order by data_naz) 
- rank() OVER (partition by kod_dol, id_sotr order by data_naz) as rn --ранжируем записи, получим блоки по id_sotr и kod_dol
from q) 

, e as (
select w.*, row_number() OVER (partition by id_sotr order by data_naz) as rn2 --ранжируем записи, которые останутся после not exists
from w
where not exists (select 1 from w w1 where w1.rn > w.rn and w1.id_sotr = w.id_sotr)) --убираем все, кроме последних рангов

select * from e where rn2 = 1--выводим 1ую запись из ранга 
8 апр 19, 11:40    [21855672]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на последние N строк  [new]
AmKad
Member

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

with q (id,  data_naz, oklad, id_sotr, kod_dep, kod_dol) as (
select 70943,  to_date('04.09.2006','DD.MM.YYYY'),  1500, 1051, '01000502',  925 from dual union all
select 71475,  to_date('06.02.2007','DD.MM.YYYY'),  2500, 1051, '01000502',  946 from dual union all
select 72125,  to_date('01.06.2007','DD.MM.YYYY'),  3500, 1052, '01000502',  946 from dual union all
select 74735,  to_date('01.09.2007','DD.MM.YYYY'),  4400, 1052, '01000502',  946 from dual union all
select 77652,  to_date('01.03.2008','DD.MM.YYYY'),  6500, 1051, '01000502',  946 from dual union all
select 78162,  to_date('02.06.2008','DD.MM.YYYY'),  7000, 1051, '01000502',  925 from dual union all
select 79181,  to_date('02.01.2009','DD.MM.YYYY'),  7700, 1052, '01000502',  945 from dual union all
select 80600,  to_date('03.06.2010','DD.MM.YYYY'),  9000, 1051, '01000502',	925 from dual )
select id, data_naz, oklad, id_sotr, kod_dep, kod_dol
from
   (select q.*,
    case when kod_dol != lag(kod_dol) over (partition by id_sotr order by data_naz) then 1 end lg
    from q
   )
order by row_number() over (partition by id_sotr order by lg, data_naz desc)
fetch first 1 row with ties;

        ID DATA_NAZ                 OKLAD    ID_SOTR KOD_DEP     KOD_DOL
---------- ------------------- ---------- ---------- -------- ----------
     78162 2008-06-02 00:00:00       7000       1051 01000502        925
     79181 2009-01-02 00:00:00       7700       1052 01000502        945
8 апр 19, 12:21    [21855740]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на последние N строк  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1711
AmKad,

автор
Мне нужно выбрать последнюю дату перемещения сотрудника


если выбрать токо дату, то достаточно group by max

....
stax
8 апр 19, 12:43    [21855769]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на последние N строк  [new]
maverick2104
Member

Откуда:
Сообщений: 292
AmKad,
with q (id,  data_naz, oklad, id_sotr, kod_dep, kod_dol) as (
select 70943,  to_date('04.09.2006','DD.MM.YYYY'),  1500, 1051, '01000502',  925 from dual union all
select 71475,  to_date('06.02.2007','DD.MM.YYYY'),  2500, 1051, '01000502',  925 from dual union all
select 72125,  to_date('01.06.2007','DD.MM.YYYY'),  3500, 1051, '01000502',  925 from dual union all
select 74735,  to_date('01.09.2007','DD.MM.YYYY'),  4400, 1051, '01000502',  925 from dual union all
select 77652,  to_date('01.03.2008','DD.MM.YYYY'),  6500, 1051, '01000502',  925 from dual union all
select 78162,  to_date('02.06.2008','DD.MM.YYYY'),  7000, 1051, '01000502',  925 from dual union all
select 79181,  to_date('02.01.2009','DD.MM.YYYY'),  7700, 1051, '01000502',  925 from dual union all
select 80600,  to_date('03.06.2010','DD.MM.YYYY'),  9000, 1051, '01000502',  925 from dual )
select id, data_naz, oklad, id_sotr, kod_dep, kod_dol
from
   (select q.*,
    case when kod_dol != lag(kod_dol) over (partition by id_sotr order by data_naz) then 1 end lg
    from q
   )
order by row_number() over (partition by id_sotr order by lg, data_naz desc)
fetch first 1 row with ties;

   	ID	DATA_NAZ	OKLAD	ID_SOTR	KOD_DEP	        KOD_DOL
1	80600	03.06.2010	9000	1051	01000502	925



А правильный ответ 04.06.2006 .
8 апр 19, 12:56    [21855792]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на последние N строк  [new]
maverick2104
Member

Откуда:
Сообщений: 292
Stax
AmKad,

автор
Мне нужно выбрать последнюю дату перемещения сотрудника


если выбрать токо дату, то достаточно group by max

....
stax


Моя вина , не до конца все обьяснил .

Под перемещением имеется ввиду изменение код_должн либо код_департ.
8 апр 19, 12:58    [21855795]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на последние N строк  [new]
123йй
Member

Откуда:
Сообщений: 1501
maverick2104
А правильный ответ 04.06.2006 .

Прочитай про параметры lag
8 апр 19, 13:05    [21855801]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на последние N строк  [new]
maverick2104
Member

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

Возможное изменения код_департ не учитывается .


AmKad,
maverick2104

А правильный ответ 04.09.2006 .
8 апр 19, 13:06    [21855805]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на последние N строк  [new]
AmKad
Member

Откуда:
Сообщений: 4953
Мавериск, либо уже сам доделывай, либо, не будь партизаном и сделай полную постановку своей задачи.
8 апр 19, 13:10    [21855810]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на последние N строк  [new]
Dshedoo
Member

Откуда:
Сообщений: 280
maverick2104
Dshedoo,

Возможное изменения код_департ не учитывается .



rank() OVER (partition by kod_dol, id_sotr order by data_naz)


partition by kod_dol, id_sotr -- вот изменение каких полей учитывается.
Нужен ещё и департамент - добавляешь его в партишен:

rank() OVER (partition by kod_dol, id_sotr, kod_dep order by data_naz)


Хоть оклад туда засовывай.
8 апр 19, 13:22    [21855826]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на последние N строк  [new]
maverick2104
Member

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


Супер ,спасибо ,всунул в отчет , протестировал = ОК .
Теперь только разберусь как все работает , да и про lag() нужно почитать , интересная вещица .
8 апр 19, 13:36    [21855863]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на последние N строк  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1711
maverick2104
Dshedoo,


Супер ,спасибо ,всунул в отчет , протестировал = ОК .
Теперь только разберусь как все работает , да и про lag() нужно почитать , интересная вещица .


Уверены? покажите селект

что должно получится для
with q (id,  data_naz, oklad, id_sotr, kod_dep, kod_dol) as (
select 70943,  to_date('01.09.2006','DD.MM.YYYY'),  1500, 1051, '01000502',  920 from dual union all
select 71475,  to_date('02.02.2007','DD.MM.YYYY'),  2500, 1051, '01000502',  925 from dual union all
select 72125,  to_date('01.06.2007','DD.MM.YYYY'),  3500, 1051, '01000500',  925 from dual union all
select 74735,  to_date('01.09.2007','DD.MM.YYYY'),  4400, 1051, '01000503',  925 from dual union all
select 77652,  to_date('01.03.2008','DD.MM.YYYY'),  6500, 1051, '01000500',  925 from dual union all
select 78162,  to_date('02.06.2008','DD.MM.YYYY'),  7000, 1052, '01000501',  925 from dual union all
select 79181,  to_date('03.06.2008','DD.MM.YYYY'),  7700, 1052, '01000502',  925 from dual union all
select 80600,  to_date('04.06.2008','DD.MM.YYYY'),  9000, 1052, '01000502',  924 from dual )
...


....
stax
8 апр 19, 14:13    [21855936]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на последние N строк  [new]
merch
Member

Откуда:
Сообщений: 135
maverick2104
всунул в отчет


maverick2104
Теперь только разберусь как все работает


Картинка с другого сайта.
8 апр 19, 15:10    [21856040]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на последние N строк  [new]
maverick2104
Member

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

Тестовый , не волнуйтесь .


Stax
maverick2104
Dshedoo,


Супер ,спасибо ,всунул в отчет , протестировал = ОК .
Теперь только разберусь как все работает , да и про lag() нужно почитать , интересная вещица .


Уверены? покажите селект

....
stax


Да вы правы , поторопился.
Селект слишком громоздный , там правило что он берет все ид_сотрудников из таблицы Persons .

Тоесть если следовать правилам по получается :

+
 (select x.dat_nazn16 from (select a.* from
select a.* from
(with q as (select  dat_nazn16 , oklad16 , person_tabn3 , podr_code23,doljnost_code9 from paths,persons p where person_tabn3=p.tabn3), 
        w as (select q.*, row_number() OVER (partition by person_tabn3 order by dat_nazn16) - rank() OVER (partition by doljnost_code9 
                order by dat_nazn16) as rn from q),
         e as (select w.*, row_number() OVER (partition by person_tabn3 order by dat_nazn16) as rn2 
from w
where not exists (select 1 from w w1 where w1.rn > w.rn and w1.person_tabn3 = w.person_tabn3))
select * from e where rn2 = 1 ) a , persons p where p.tabn3=a.person_tabn3 


Вот только в этом ввиде он мне почему-то выдает неправильную дату ( 06.02.2007 )...

Если же вставлять номер в with q вот так :
+
select a.* from
(with q as (select  dat_nazn16 , oklad16 , person_tabn3 , podr_code23,doljnost_code9 from paths,persons p where person_tabn3='1731' ), 
     w as (select q.*
, row_number() OVER (partition by person_tabn3 order by dat_nazn16) - rank() OVER (partition by doljnost_code9 order by dat_nazn16) as rn 
from q),
     e as (select w.*, row_number() OVER (partition by person_tabn3 order by dat_nazn16) as rn2 
from w
where not exists (select 1 from w w1 where w1.rn > w.rn and w1.person_tabn3 = w.person_tabn3))
select * from e where rn2 = 1 ) a , persons p where '1731'=a.person_tabn3


То все ок ( 03.06.2010 ) .

Вот данные на 1731....
with q (id,  dat_nazn16, oklad16, person_tabn3, podr_code23, doljnosts_code9) as (
select 70943,  to_date('04.09.2006','DD.MM.YYYY'),  1500, 1731, '010003104',  2053 from dual union all
select 71475,  to_date('06.02.2007','DD.MM.YYYY'),  2500, 1731, '010003108',  3149 from dual union all
select 72125,  to_date('01.06.2007','DD.MM.YYYY'),  3500, 1731, '010003108',  3149 from dual union all
select 74735,  to_date('01.09.2007','DD.MM.YYYY'),  4400, 1731, '010003108',  3149 from dual union all
select 77652,  to_date('01.03.2008','DD.MM.YYYY'),  6500, 1731, '010003108',  3149 from dual union all
select 78162,  to_date('02.06.2008','DD.MM.YYYY'),  7000, 1731, '010003108',  3149 from dual union all
select 79181,  to_date('02.01.2009','DD.MM.YYYY'),  7700, 1731, '010003112',  3950 from dual union all
select 80600,  to_date('03.06.2010','DD.MM.YYYY'),  9000, 1731, '010003112',  3950 from dual )
8 апр 19, 16:19    [21856152]     Ответить | Цитировать Сообщить модератору
 Re: Запрос на последние N строк  [new]
Stax
Member

Откуда: Ukraine,Lviv
Сообщений: 1711
maverick2104,
автор
Под перемещением имеется ввиду изменение код_должн либо код_департ.


так влоб добавте проверку через or

  1  with q (id,  dat_nazn16, oklad16, person_tabn3, podr_code23, doljnosts_code9) as (
  2  select 70943,  to_date('04.09.2006','DD.MM.YYYY'),  1500, 1731, '010003104',  2053 from dual union all
  3  select 71475,  to_date('06.02.2007','DD.MM.YYYY'),  2500, 1731, '010003108',  3149 from dual union all
  4  select 72125,  to_date('01.06.2007','DD.MM.YYYY'),  3500, 1731, '010003108',  3149 from dual union all
  5  select 74735,  to_date('01.09.2007','DD.MM.YYYY'),  4400, 1731, '010003108',  3149 from dual union all
  6  select 77652,  to_date('01.03.2008','DD.MM.YYYY'),  6500, 1731, '010003108',  3149 from dual union all
  7  select 78162,  to_date('02.06.2008','DD.MM.YYYY'),  7000, 1731, '010003108',  3149 from dual union all
  8  select 79181,  to_date('02.01.2009','DD.MM.YYYY'),  7700, 1731, '010003112',  3950 from dual union all
  9  select 80600,  to_date('03.06.2010','DD.MM.YYYY'),  9000, 1731, '010003112',  3950 from dual union all
 10  --
 11  select 80601,  to_date('03.06.2010','DD.MM.YYYY'),  9000, 1732, '010003112',  3950 from dual union all
 12  --
 13  select 70943,  to_date('01.09.2006','DD.MM.YYYY'),  1500, 1051, '01000502',  920 from dual union all
 14  select 71475,  to_date('02.02.2007','DD.MM.YYYY'),  2500, 1051, '01000502',  925 from dual union all
 15  select 72125,  to_date('01.06.2007','DD.MM.YYYY'),  3500, 1051, '01000500',  925 from dual union all
 16  select 74735,  to_date('01.09.2007','DD.MM.YYYY'),  4400, 1051, '01000503',  925 from dual union all
 17  select 77652,  to_date('01.03.2008','DD.MM.YYYY'),  6500, 1051, '01000500',  925 from dual union all
 18  select 78162,  to_date('02.06.2008','DD.MM.YYYY'),  7000, 1052, '01000501',  925 from dual union all
 19  select 79181,  to_date('03.06.2008','DD.MM.YYYY'),  7700, 1052, '01000502',  925 from dual union all
 20  select 80600,  to_date('04.06.2008','DD.MM.YYYY'),  9000, 1052, '01000502',  924 from dual
 21  )
 22  --select * from
 23  select person_tabn3,max(dat_nazn16) max_dat from
 24  (
 25    select q.*
 26       ,lead(podr_code23,1,podr_code23||'*')      over (partition by person_tabn3 order by dat_nazn16 desc) le_podr
 27       ,lead(doljnosts_code9,1,doljnosts_code9-1) over (partition by person_tabn3 order by dat_nazn16 desc) le_dolj
 28    from q
 29  ) where doljnosts_code9<>le_dolj or podr_code23<>le_podr
 30* group by person_tabn3
SQL> /

PERSON_TABN3 MAX_DAT
------------ --------
        1051 01.03.08
        1052 04.06.08
        1731 02.01.09
        1732 03.06.10


ps
мне было интересно Ваше решение через rank

і еще
что делать если одна запись для тн

.....
stax
8 апр 19, 17:04    [21856229]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить