Ранжирование или распределение или ? / Oracle

ReSQL.ru

Мобильная версия Контакт Правила FAQ Помощь

Гость

Войти | Регистрация | Профиль | Очистить

Новые сообщения | Избранное

Форумы | Пользователи | Статистика | Мод. лог | Поиск

Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Ранжирование или распределение или ?

16 сообщений из 16, страница 1 из 1

Ранжирование или распределение или ?

#40133016

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:13

| Ответить | Цитировать | Написать

Ранжирование или распределение или ?

#40133026

Stax

Участник

Откуда: Ukraine,Lviv

Сообщения: 4 370

Рейтинг: 0 / 0

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, 09:56

| Ответить | Цитировать | Написать

Ранжирование или распределение или ?

#40133031

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:10

| Ответить | Цитировать | Написать

Ранжирование или распределение или ?

#40133046

Dual_Bios

Гость

Stax, спасибо, но Вы же видите, третья группа пустая. А она не должна быть пустой...

...

Рейтинг:

0 / 0

10.02.2022, 10:48

| Ответить | Цитировать | Написать

Ранжирование или распределение или ?

#40133048

Dual_Bios

Гость

Asmodeus, спасибо, очень похоже на правду !

...

Рейтинг:

0 / 0

10.02.2022, 10:50

| Ответить | Цитировать | Написать

Ранжирование или распределение или ?

#40133075

Stax

Участник

Откуда: Ukraine,Lviv

Сообщения: 4 370

Рейтинг: 0 / 0

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, 11:38

| Ответить | Цитировать | Написать

Ранжирование или распределение или ?

#40133102

Stax

Участник

Откуда: Ukraine,Lviv

Сообщения: 4 370

Рейтинг: 0 / 0

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:26

| Ответить | Цитировать | Написать

Ранжирование или распределение или ?

#40133103

Dual_Bios

Гость

Stax, хмм
как я понимаю условие задачи, важно чтобы 25% с мин и 25% с макс выдерживали такую пропорцию, т е чтобы остальные составляли +- 50%
те более-усредненные от полной выборкипопадали бы в эти 50%, края в меньше-больше

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

Кроме группы на выходе ничего не интересует

...

Рейтинг:

0 / 0

10.02.2022, 12:31

| Ответить | Цитировать | Написать

Ранжирование или распределение или ?

#40133120

Stax

Участник

Откуда: Ukraine,Lviv

Сообщения: 4 370

Рейтинг: 0 / 0

Dual_Bios,

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

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

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

Код: plsql

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, 13:19

| Ответить | Цитировать | Написать

Ранжирование или распределение или ?

#40133165

Stax

Участник

Откуда: Ukraine,Lviv

Сообщения: 4 370

Рейтинг: 0 / 0

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:32

| Ответить | Цитировать | Написать

Ранжирование или распределение или ?

#40133188

Dual_Bios

Гость

Stax, огромное спасибо за совет, еще раз уточнил, все же, что они хотят... Слабонервную публику просим удалиться. Задача оказалась не та, как они пытались озвучить до того

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

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

Т е вопреки ранее озвученному никакое среднее в расчете не принимает участие

...

Рейтинг:

0 / 0

10.02.2022, 14:52

| Ответить | Цитировать | Написать

Ранжирование или распределение или ?

#40133202

Stax

Участник

Откуда: Ukraine,Lviv

Сообщения: 4 370

Рейтинг: 0 / 0

если не кратно 4, как вычисляется 25% (как округлять)?

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

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

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

....
stax

...

Рейтинг:

0 / 0

10.02.2022, 15:10

| Ответить | Цитировать | Написать

Ранжирование или распределение или ?

#40133207

Stax

Участник

Откуда: Ukraine,Lviv

Сообщения: 4 370

Рейтинг: 0 / 0

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:21

| Ответить | Цитировать | Написать

Ранжирование или распределение или ?

#40133222

Dual_Bios

Гость

Stax,

спасибо огромное, буду смотреть, если этот вариант не пройдет, буду с cume_dist делать !

...

Рейтинг:

0 / 0

10.02.2022, 15:40

| Ответить | Цитировать | Написать

Ранжирование или распределение или ?

#40133232

Asmodeus

Гость

Dual_Bios

Stax,

спасибо огромное, буду смотреть, если тот вариант не пройдет, буду с cume_dist делать !

Тщательно проговорите пограничные случаи (например, 50 и 51 имею одинаковые ЗП; общее число сотрудников не делится нацело на 4 и т.п.)

...

Рейтинг:

0 / 0

10.02.2022, 15:58

| Ответить | Цитировать | Написать

Ранжирование или распределение или ?

#40133255

Dual_Bios

Гость

Asmodeus, да-да, по примерам уже понял, спасибо!

...

Рейтинг:

0 / 0

10.02.2022, 16:41

| Ответить | Цитировать | Написать

16 сообщений из 16, страница 1 из 1

Форумы / Oracle [игнор отключен] [закрыт для гостей] / Ранжирование или распределение или ?

Цитировать

Написать

Автор*:

Ввести пароль для входа

Тема*:

Сообщение

Данное сообщение тематическое

Сообщение содержит картинки или видео 18+

Автор:

ВНИМАНИЕ! На данном подфоруме действуют строгие правила. Удостоверьтесь, что ваше сообщение соответствует им!

Форум или тема закрыты для гостей. Необходима авторизация!

Загрузить последнюю сохраненную версию

Вложение:

Вставить как галерею

Максимальный размер вложений: 4,0 МБ, аудио/видео: 8,0 МБ. Картинки большего размера ужимаются, если возможно.

Введите код, изображенный на картинке. Если код нечитаемый, кликните картинку, чтобы загрузить другой вариант.

Отправляя сообщение, я выражаю свое согласие с правилами форума и принимаю пользовательское соглашение.

Читали тему (0):

Читали форум (0):

Пользователи онлайн (0):

start [/forum/topic.php?fid=52&fpage=3&tid=1879533]:	0ms
get settings:	24ms
get forum list:	15ms
check forum access:	4ms
check topic access:	4ms
track hit:	61ms
get topic data:	13ms
get forum data:	3ms
get page messages:	309ms
get tp. blocked users:	2ms
others:	2464ms

total:	2899ms