Гость
Войти | Профиль | Очистить
Map
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Ранжирование или распределение или ? / 16 сообщений из 16, страница 1 из 1
10.02.2022, 09:13
    #40133016
Dual_Bios
Dual_Bios
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ранжирование или распределение или ?
Добрый день !
Имеется следующая задача
Есть список зарплат сотрудников в таблице.

Надо в результате запроса получить еще одну колонку, в которой будет признак,
в какую группу входит зарплата сотрудника относительно oбщего уровня

Групп ранжирования - 3 , принадлежность определяется относительно всего диапазона

1 - если он входит в 25% от _общего_ массива сотрудников - у которых оплата меньше относительного общего уровня

3 - если он входит в 25% от _общего_ массива сотрудников - у которых оплата больше относительного общего уровня

2 - остальные 50%

Подскажите пожалуйста, куда смотреть, наверняка же можно прикрутить специализированные функции какие-то

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
сreate table  test
    ( n number,
     last_name varchar2(100),
     salary number);

insert into TEST (N, LAST_NAME, SALARY)
values (1, 'Иванов', 100);

insert into TEST (N, LAST_NAME, SALARY)
values (2, 'Петров', 120);

insert into TEST (N, LAST_NAME, SALARY)
values (3, 'Сидоров', 110);

insert into TEST (N, LAST_NAME, SALARY)
values (4, 'Семенов', 160);

insert into TEST (N, LAST_NAME, SALARY)
values (5, 'Никитин', 170);

insert into TEST (N, LAST_NAME, SALARY)
values (6, 'Козлов', 200);
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2022, 09:56
    #40133026
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ранжирование или распределение или ?
Dual_Bios,

Код: 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.
  1  with TEST (N, LAST_NAME, SALARY) as (
  2  select 1, 'Иванов', 400 from dual union all
  3  select 2, 'Петров', 120 from dual union all
  4  select 3, 'Сидоров', 110 from dual union all
  5  select 4, 'Семенов', 160 from dual union all
  6  select 5, 'Никитин', 170 from dual union all
  7  select 6, 'Козлов', 200 from dual)
  8  , tt as (select   t.* ,RATIO_TO_REPORT(salary) over() * 100 r from test t)
  9  select N, LAST_NAME, SALARY,r
 10   ,case
 11    when r<=25 then 1
 12    when r>=75 then 3
 13    else 2 end gr
 14* from tt
SQL> /

         N LAST_NA     SALARY          R         GR
---------- ------- ---------- ---------- ----------
         1 Иванов         400 34.4827586          2
         2 Петров         120 10.3448276          1
         3 Сидоров        110 9.48275862          1
         4 Семенов        160 13.7931034          1
         5 Никитин        170 14.6551724          1
         6 Козлов         200 17.2413793          1

6 rows selected.



.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2022, 10:10
    #40133031
Asmodeus
Asmodeus
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ранжирование или распределение или ?
Dual_Bios,

Персентили ?
Код: 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.
with
    t (n, ln, sal)
    as
        (select 1, 'Иванов', 400 from DUAL
         union all
         select 2, 'Петров', 120 from DUAL
         union all
         select 3, 'Сидоров', 110 from DUAL
         union all
         select 4, 'Семенов', 160 from DUAL
         union all
         select 5, 'Никитин', 170 from DUAL
         union all
         select 6, 'Козлов', 200 from DUAL
         union all
         select 7, 'Васнецов', 145 from DUAL
         union all
         select 8, 'Репин', 100 from DUAL)
select n,
       ln,
       sal,
       low25bound,
       top25bound,
       case
           when sal >= top25bound then 3
           when sal <= low25bound then 1
           else 2
       end    quantile
  from (select n,
               ln,
               sal,
               percentile_disc (0.25) within group (order by sal) over ()
                   low25bound,
               percentile_disc (0.75) within group (order by sal) over ()
                   top25bound
          from t);

        N LN                      SAL LOW25BOUND TOP25BOUND   QUANTILE
---------- ---------------- ---------- ---------- ---------- ----------
         8 Репин                   100        110        170          1
         3 Сидоров                 110        110        170          1
         2 Петров                  120        110        170          2
         7 Васнецов                145        110        170          2
         4 Семенов                 160        110        170          2
         5 Никитин                 170        110        170          3
         6 Козлов                  200        110        170          3
         1 Иванов                  400        110        170          3
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2022, 10:48
    #40133046
Dual_Bios
Dual_Bios
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ранжирование или распределение или ?
Stax, спасибо, но Вы же видите, третья группа пустая. А она не должна быть пустой...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2022, 10:50
    #40133048
Dual_Bios
Dual_Bios
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ранжирование или распределение или ?
Asmodeus, спасибо, очень похоже на правду !
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2022, 11:38
    #40133075
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ранжирование или распределение или ?
Dual_Bios
Stax, спасибо, но Вы же видите, третья группа пустая. А она не должна быть пустой...


не совсем понимаю что надо
мож Вам достатньо простого cume_dist
Код: 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.
SQL> ed
Wrote file afiedt.buf

  1  with TEST (N, LAST_NAME, SALARY) as (
  2   select 1, 'Иванов', 400 from dual union all
  3    select 2, 'Петров', 120 from dual union all
  4    select 3, 'Сидоров', 110 from dual union all
  5    select 4, 'Семенов', 160 from dual union all
  6    select 5, 'Никитин', 170 from dual union all
  7    select 6, 'Козлов', 200 from dual)
  8  ,tt as (select   t.*
  9      ,RATIO_TO_REPORT(salary) over() * 100 r
 10      ,cume_dist() over (order by salary)*100 cu
 11    from test t)
 12  select tt.*
 13     ,case
 14      when cu<=25 then 1
 15      when cu>=75 then 3
 16      else 2 end gr
 17* from tt
SQL> /

         N LAST_NA     SALARY          R         CU         GR
---------- ------- ---------- ---------- ---------- ----------
         3 Сидоров        110 9.48275862 16.6666667          1
         2 Петров         120 10.3448276 33.3333333          2
         4 Семенов        160 13.7931034         50          2
         5 Никитин        170 14.6551724 66.6666667          2
         6 Козлов         200 17.2413793 83.3333333          3
         1 Иванов         400 34.4827586        100          3

6 rows selected.

SQL>


зы
тож одна группа
Код: 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.
48.
49.
50.
51.
52.
SQL> with
  2      t (n, ln, sal)
  3      as
  4          (select 1, 'Иванов', 400 from DUAL
  5           union all
  6           select 2, 'Петров', 1 from DUAL
  7           union all
  8           select 3, 'Сидоров', 1 from DUAL
  9           union all
 10           select 4, 'Семенов', 1 from DUAL
 11           union all
 12           select 5, 'Никитин', 1 from DUAL
 13           union all
 14           select 6, 'Козлов', 2 from DUAL
 15           union all
 16           select 7, 'Васнецов', 1 from DUAL
 17           union all
 18           select 8, 'Репин', 1 from DUAL)
 19  select n,
 20         ln,
 21         sal,
 22         low25bound,
 23         top25bound,
 24         case
 25             when sal >= top25bound then 3
 26             when sal <= low25bound then 1
 27             else 2
 28         end    quantile
 29    from (select n,
 30                 ln,
 31                 sal,
 32                 percentile_disc (0.25) within group (order by sal) over ()
 33                     low25bound,
 34                 percentile_disc (0.75) within group (order by sal) over ()
 35                     top25bound
 36            from t)
 37  /

         N LN              SAL LOW25BOUND TOP25BOUND   QUANTILE
---------- -------- ---------- ---------- ---------- ----------
         4 Семенов           1          1          1          3
         7 Васнецов          1          1          1          3
         2 Петров            1          1          1          3
         3 Сидоров           1          1          1          3
         8 Репин             1          1          1          3
         5 Никитин           1          1          1          3
         6 Козлов            2          1          1          3
         1 Иванов          400          1          1          3

8 rows selected.

SQL>



pss
кто такой "относительного общего уровня "? средняя?

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2022, 12:26
    #40133102
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ранжирование или распределение или ?
Stax

pss
кто такой "относительного общего уровня "? средняя?

.....
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.
31.
32.
33.
34.
SQL> ed
Wrote file afiedt.buf

  1  with TEST (N, LAST_NAME, SALARY) as (
  2   select 1, 'Иванов', 400 from dual union all
  3    select 2, 'Петров', 1 from dual union all
  4    select 3, 'Сидоров', 1 from dual union all
  5    select 4, 'Семенов', 1 from dual union all
  6    select 5, 'Никитин', 1 from dual union all
  7    select 7, 'Stax', 10 from dual union all
  8    select 8, 'Dual_Bios', 20 from dual union all
  9    select 6, 'Козлов', 200 from dual)
 10  ,tt as (select   t.*
 11     ,sign(salary- avg(salary) over ()) m -- массивы (<,=,>) относительного общего уровня ...
 12   from test t)
 13  select tt.*
 14    ,cume_dist() over (partition by m order by salary,last_name,rownum)*100 pr --% от _общего_ массива сотрудников
 15* from tt
SQL> /

         N LAST_NAME     SALARY          M         PR
---------- --------- ---------- ---------- ----------
         5 Никитин            1         -1 16.6666667
         2 Петров             1         -1 33.3333333
         4 Семенов            1         -1         50
         3 Сидоров            1         -1 66.6666667
         7 Stax              10         -1 83.3333333
         8 Dual_Bios         20         -1        100
         6 Козлов           200          1         50
         1 Иванов           400          1        100

8 rows selected.

SQL>



.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2022, 12:31
    #40133103
Dual_Bios
Dual_Bios
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ранжирование или распределение или ?
Stax, хмм
как я понимаю условие задачи, важно чтобы 25% с мин и 25% с макс выдерживали такую пропорцию, т е чтобы остальные составляли +- 50%
те более-усредненные от полной выборкипопадали бы в эти 50%, края в меньше-больше

те которые минимальные в группу 1, основная масса группа 2, маскимальные в 3

Кроме группы на выходе ничего не интересует
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2022, 13:19
    #40133120
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ранжирование или распределение или ?
Dual_Bios,

я очень долго работал с такими постановками

главное понять что нужно бухгалтерам

если подходить формально , что по жизни не продуктивно, то

Код: plsql
1.
1 - если он входит в 25% от _общего_ массива сотрудников - у которых оплата меньше относительного общего уровня



1) что такое "относительного общего уровня"? средняя? принимаем что avg

2) которых оплата меньше/больше avg
получаем три массива сотрудников
1- < avg, 2- = avg, 3- > avg,

3)
>>>1 - если он входит в 25% от _общего_ массива сотрудников - у которых оплата меньше,
ето четверть 1-го массива
напр в первый массив (зп < средней) попало 6-человек
нам надо из них (6-ти) выделить 25% работников, ето полтора человека,
тут неважный нюас (как округлять), допустим берем целых людей
итого, в первую группу войдет один человек Никитин (кто? явно не указано) берем с минимальной ЗП

>>>3 - если он входит в 25% от _общего_ массива сотрудников - у которых оплата больше относительного общего уровня
ето часть третьего массива
у нас два человека (на одного уже приходится 50%), итого в 25% никто не войдет

остальные во вторую группу
Код: 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.
 1  with TEST (N, LAST_NAME, SALARY) as (
  2   select 1, 'Иванов', 400 from dual union all
  3    select 2, 'Петров', 1 from dual union all
  4    select 3, 'Сидоров', 1 from dual union all
  5    select 4, 'Семенов', 1 from dual union all
  6    select 5, 'Никитин', 1 from dual union all
  7    select 7, 'Stax', 10 from dual union all
  8    select 8, 'Dual_Bios', 20 from dual union all
  9    select 6, 'Козлов', 200 from dual)
 10  ,tt as (select   t.*
 11     ,sign(salary- avg(salary) over ()) m -- массивы относительного общего уровня ...
 12   from test t)
 13  ,ttt as (
 14   select tt.*
 15    ,cume_dist() over (partition by m order by salary,last_name,rownum)*100 pr --% от _общего_ массива сотрудников
 16   from tt)
 17  select ttt.*
 18   , case
 19      when m=-1 and pr<=25 then 1
 20      when m=1 and pr>=75 then 3
 21      else 3 end gr
 22* from ttt
SQL> /

         N LAST_NAME     SALARY          M         PR         GR
---------- --------- ---------- ---------- ---------- ----------
         5 Никитин            1         -1 16.6666667          1
         2 Петров             1         -1 33.3333333          3
         4 Семенов            1         -1         50          3
         3 Сидоров            1         -1 66.6666667          3
         7 Stax              10         -1 83.3333333          3
         8 Dual_Bios         20         -1        100          3
         6 Козлов           200          1         50          3
         1 Иванов           400          1        100          3

8 rows selected.

SQL>


зы
прихожу к менеджеру, говорю мож вам надо
1-группа -работники у которих ЗП на 25% меньше средней
3-группа -работники у которих ЗП на 25% больше средней
2- остальные

-да, да, а так можно ...

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2022, 14:32
    #40133165
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ранжирование или распределение или ?
Dual_Bios

те которые минимальные в группу 1, основная масса группа 2, маскимальные в 3


еще вариант
Код: 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.
SQL> ed
Wrote file afiedt.buf

  1  with TEST (N, LAST_NAME, SALARY) as (
  2     select 1, 'Иванов', 400 from dual union all
  3      select 2, 'Петров', 120 from dual union all
  4      select 3, 'Сидоров', 110 from dual union all
  5      select 4, 'Семенов', 160 from dual union all
  6      select 5, 'Никитин', 170 from dual union all
  7      select 6, 'Козлов', 200 from dual)
  8    select t.*
  9     ,decode(ntile(4) over (order by salary),1,1,4,3,2) gr
 10* from test t
SQL> /

         N LAST_NA     SALARY         GR
---------- ------- ---------- ----------
         3 Сидоров        110          1
         2 Петров         120          1
         4 Семенов        160          2
         5 Никитин        170          2
         6 Козлов         200          2
         1 Иванов         400          3

6 rows selected.



.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2022, 14:52
    #40133188
Dual_Bios
Dual_Bios
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ранжирование или распределение или ?
Stax, огромное спасибо за совет, еще раз уточнил, все же, что они хотят... Слабонервную публику просим удалиться. Задача оказалась не та, как они пытались озвучить до того

Итак.
Допустим у нас 200 сотрудников

0. Получаем запрос со списком с сортировкой по возрастанию з/п, при этом считаем сколько человек составляет 25% от общего числа
(в нашем случае - 50)
1. Получение первой группы - идем от начала списка вниз до тех пор, пока количество отобранных не станет равным этой определенной порции (50). Все они получат группу 1
2. Получение третьей группы - аналогично, только от последнего сотрудника в выборке - вверх (по факту от 200 до 150го) им ставим 3
3. Остальные красавцы получают группу 2

Т е вопреки ранее озвученному никакое среднее в расчете не принимает участие
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2022, 15:10
    #40133202
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ранжирование или распределение или ?
если не кратно 4, как вычисляется 25% (как округлять)?

напр для 6, для 13?

ps
вариант меньше 4-х работников отбрасываем

зыы
мож Вам достатньо простого cume_dist

....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2022, 15:21
    #40133207
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ранжирование или распределение или ?
Dual_Bios

Допустим у нас 200 сотрудников


прогоните для разного к-ва
Код: 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.
SQL> l
  1  with TEST (N, SALARY) as (
  2     select rownum, rownum+100 from dual connect by level<=13 --кво работников
  3  )
  4    select t.*
  5     ,decode(ntile(4) over (order by salary),1,1,4,3,2) gr
  6    ,ntile(4) over (order by salary) nt
  7* from test t
SQL> /

         N     SALARY         GR         NT
---------- ---------- ---------- ----------
         1        101          1          1
         2        102          1          1
         3        103          1          1
         4        104          1          1
         5        105          2          2
         6        106          2          2
         7        107          2          2
         8        108          2          3
         9        109          2          3
        10        110          2          3
        11        111          3          4
        12        112          3          4
        13        113          3          4

13 rows selected.



.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2022, 15:40
    #40133222
Dual_Bios
Dual_Bios
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ранжирование или распределение или ?
Stax,

спасибо огромное, буду смотреть, если этот вариант не пройдет, буду с cume_dist делать !
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2022, 15:58
    #40133232
Asmodeus
Asmodeus
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ранжирование или распределение или ?
Dual_Bios
Stax,

спасибо огромное, буду смотреть, если тот вариант не пройдет, буду с cume_dist делать !
Тщательно проговорите пограничные случаи (например, 50 и 51 имею одинаковые ЗП; общее число сотрудников не делится нацело на 4 и т.п.)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.02.2022, 16:41
    #40133255
Dual_Bios
Dual_Bios
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ранжирование или распределение или ?
Asmodeus, да-да, по примерам уже понял, спасибо!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Ранжирование или распределение или ? / 16 сообщений из 16, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 3032ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1879533]:
 0ms
get settings:
 25ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 51ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 315ms
get tp. blocked users:
 1ms
others: 2602ms
total:  3032ms
x
x
Закрыть


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