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

Откуда:
Сообщений: 5
необходимо вывести список сотрудников, сумма зарплат которых составляет 50 % от общей суммы зарплат.

Образно, это выглядит так-
имеется таблица A с полями:
сотрудник
зарплата

надо одним запросом вывести список сотрудников, сумма зарплат которых составляет 50 % от общей суммы зарплат всех сотрудников.

Упростила задачу до следущего уровня:
доля зарплаты каждого сотрудника уже посчитана, т.е. имеем таблицу В с полями:
сотрудник
доля

Остаётся последнее-
вывести список сотрудников (сколько будет строк заранее соответственно не знаем),
для которых при суммировании по полю доля получится значение 50 (имеется ввиду %) или близкое к нему (например от 40 до 50, понятно, что точно может и не получиться).

Самое обидное, что знаю как упорядочить по убыванию, как вывести первые N записей с самой большой долей, а вот как сделать последнее не знаю :(

Может кто сталкивался с подобной проблемой?
или есть стандарные аналитические функции для решения подобной задачи?

Кто знает, напишите, пожалуйста, буду очень признательна.
20 фев 08, 22:13    [5319156]     Ответить | Цитировать Сообщить модератору
 Re: вывести список сотрудников, сумма зарплат которых составляет 50 % от общей суммы зарпл  [new]
[:|||:]
Guest
не совсем понятно, вывести надо первые N сотрудников,
сумма долей которых будет >= 50, начиная с самой толстой доли?

просто можно, к примеру, пойти снизу, от меньших долей и тогда
список этот будет несколько иным :)
20 фев 08, 22:25    [5319187]     Ответить | Цитировать Сообщить модератору
 Re: вывести список сотрудников, сумма зарплат которых составляет 50 % от общей суммы зарплат  [new]
Andrei Fomichev
Member

Откуда: Москва
Сообщений: 453
отношение нарастающего итога к общей сумме - вот так

select * from (
  select сотрудник, sum(зарплата) over(order by сотрудник)/сум(зарплата) over() x from a 
) where x <= 0.50
20 фев 08, 22:26    [5319193]     Ответить | Цитировать Сообщить модератору
 Re: вывести список сотрудников, сумма зарплат которых составляет 50 % от общей суммы зарплат  [new]
Denis Popov
Member

Откуда: Санкт-Петербург
Сообщений: 7862

SQL> select empno, ename, sal, round(cume_dist, 2) cume_dist
   2  from (select empno, ename, sal
   3             , cume_dist() over (order by sal) as cume_dist
   4        from scott.EMP)
   5  where cume_dist <=.5;

      EMPNO ENAME             SAL  CUME_DIST
---------- ---------- ---------- ----------
       7369 SMITH             800        .07
       7900 JAMES             950        .14
       7876 ADAMS            1100        .21
       7521 WARD             1250        .36
       7654 MARTIN           1250        .36
       7934 MILLER           1300        .43
       7844 TURNER           1500         .5

7 rows selected.

Posted via ActualForum NNTP Server 1.4

20 фев 08, 22:35    [5319214]     Ответить | Цитировать Сообщить модератору
 Re: вывести список сотрудников, сумма зарплат которых составляет 50 % от общей суммы зарпл  [new]
[:|||:]-2
Guest
{:|||:}
не совсем понятно, вывести надо первые N сотрудников,
сумма долей которых будет >= 50, начиная с самой толстой доли?

просто можно, к примеру, пойти снизу, от меньших долей и тогда
список этот будет несколько иным :)

Поддерживаю [:|||:]. Задача сформулирована неточно.
20 фев 08, 22:47    [5319253]     Ответить | Цитировать Сообщить модератору
 Re: вывести список сотрудников, сумма зарплат которых составляет 50 % от общей суммы зарплат  [new]
йцукен-1
Guest
Denis Popov

SQL> select empno, ename, sal, round(cume_dist, 2) cume_dist
   2  from (select empno, ename, sal
   3             , cume_dist() over (order by sal) as cume_dist
   4        from scott.EMP)
   5  where cume_dist <=.5;

      EMPNO ENAME             SAL  CUME_DIST
---------- ---------- ---------- ----------
       7369 SMITH             800        .07
       7900 JAMES             950        .14
       7876 ADAMS            1100        .21
       7521 WARD             1250        .36
       7654 MARTIN           1250        .36
       7934 MILLER           1300        .43
       7844 TURNER           1500         .5

7 rows selected.
Posted via ActualForum NNTP Server 1.4

Все что я вижу, это что у половины сотрудников зарплата меньше чем у TURNER. Но спрашивали то вроде о другом?
20 фев 08, 23:12    [5319317]     Ответить | Цитировать Сообщить модератору
 Re: вывести список сотрудников, сумма зарплат которых составляет 50 % от общей суммы зарпл  [new]
Denis Popov
Member

Откуда: Санкт-Петербург
Сообщений: 7862
йцукен-1
Все что я вижу, это что у половины сотрудников зарплата меньше чем у TURNER. Но спрашивали то вроде о другом?

Да, ошибся, в результате пришел к варианту, аналогичному от Andrei Fomichev..
20 фев 08, 23:52    [5319372]     Ответить | Цитировать Сообщить модератору
 Re: вывести список сотрудников, сумма зарплат которых составляет 50 % от общей суммы зарплат  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10039
It is not a trivial task. It is classic knapsack problem Задача о рюкзаке.

SY.

Сообщение было отредактировано: 21 фев 08, 00:01
21 фев 08, 00:00    [5319383]     Ответить | Цитировать Сообщить модератору
 Re: вывести список сотрудников, сумма зарплат которых составляет 50 % от общей суммы зарплат  [new]
serge_b77
Member

Откуда:
Сообщений: 67
SY
It is not a trivial task. It is classic knapsack problem Задача о рюкзаке.

SY.

Вы ошибаетесь.Задача в данном случае тривиальная.И определена полностью.Интересует только верхний слой высокоплачиваемых сотрудников.Решение уже приведено(с небольшими помарками).
21 фев 08, 09:06    [5319748]     Ответить | Цитировать Сообщить модератору
 Re: вывести список сотрудников, сумма зарплат которых составляет 50 % от общей суммы зарпл  [new]
StarWoofy
Member

Откуда: Moscow
Сообщений: 1005
Оригинальное название темы!
:)
21 фев 08, 09:18    [5319780]     Ответить | Цитировать Сообщить модератору
 Re: вывести список сотрудников, сумма зарплат которых составляет 50 % от общей суммы зарпл  [new]
serge_b77
Member

Откуда:
Сообщений: 67
StarWoofy
Оригинальное название темы!
:)

Название - это задача о рюкзаке).А в посте все детерминировано до тривиальности.
21 фев 08, 09:28    [5319815]     Ответить | Цитировать Сообщить модератору
 вывести список сотрудников, сумма зарплат которых составляет 50 % от общей суммы зарплат  [new]
RA\/EN
Member

Откуда:
Сообщений: 3658
Я думаю так (если без упрощения "ну можно и 40%"):
WITH q AS (
  SELECT 'Иванов' n,200 z FROM dual UNION ALL
  SELECT 'Медведев' n,300 z FROM dual UNION ALL
  SELECT 'Путин' n,400 z FROM dual)
SELECT n,z,d
  FROM (SELECT n,z,d,lag(d,1,0) over (ORDER BY d) pd
          FROM (SELECT n,z,
                       SUM(z) over (ORDER BY z DESC,n ASC)/SUM(z) over () d
                  FROM q))
 WHERE pd<.5
21 фев 08, 09:46    [5319894]     Ответить | Цитировать Сообщить модератору
 Re: вывести список сотрудников, сумма зарплат которых составляет 50 % от общей суммы зарпл  [new]
marina_06
Member

Откуда:
Сообщений: 5
[quot [:|||:]]не совсем понятно, вывести надо первые N сотрудников,
сумма долей которых будет >= 50, начиная с самой толстой доли?

просто можно, к примеру, пойти снизу, от меньших долей и тогда
список этот будет несколько иным :)[/quot]

согласна, не совсем понятно сформулирована задача
надо вывести первые N сотрудников, начиная с самой толстой доли
21 фев 08, 10:46    [5320368]     Ответить | Цитировать Сообщить модератору
 Re: вывести список сотрудников, сумма зарплат которых составляет 50 % от общей суммы зарплат  [new]
marina_06
Member

Откуда:
Сообщений: 5
serge_b77
SY
It is not a trivial task. It is classic knapsack problem Задача о рюкзаке.

SY.

Вы ошибаетесь.Задача в данном случае тривиальная.И определена полностью.Интересует только верхний слой высокоплачиваемых сотрудников.Решение уже приведено(с небольшими помарками).



Прочитала задачу о рюкзаке, похожа на мою конечно, но там представлено всё сложнее, я в таких случаях (как с рюкзаком) пишу функции с циклами по условию.
А в моём случае думаю, что задача проста и решима одним запросом.
21 фев 08, 11:03    [5320536]     Ответить | Цитировать Сообщить модератору
 Re: вывести список сотрудников, сумма зарплат которых составляет 50 % от общей суммы зарплат  [new]
marina_06
Member

Откуда:
Сообщений: 5
Сейчас попробую представленные решения для своего случая и скажу подошли или нет.
Спасибо за помощь :)
21 фев 08, 11:03    [5320544]     Ответить | Цитировать Сообщить модератору
 Re: вывести список сотрудников, сумма зарплат которых составляет 50 % от общей суммы зарпл  [new]
Andrei Fomichev
Member

Откуда: Москва
Сообщений: 453
marina_06
:|||:
не совсем понятно, вывести надо первые N сотрудников,
сумма долей которых будет >= 50, начиная с самой толстой доли?

просто можно, к примеру, пойти снизу, от меньших долей и тогда
список этот будет несколько иным :)


согласна, не совсем понятно сформулирована задача
надо вывести первые N сотрудников, начиная с самой толстой доли


select * from (
  select сотрудник, sum(зарплата) over(order by зарплата desc)/sum(зарплата) over() x from a 
) where x <= 0.50
21 фев 08, 13:30    [5322051]     Ответить | Цитировать Сообщить модератору
 Re: вывести список сотрудников, сумма зарплат которых составляет 50 % от общей суммы зарплат  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
еще баянчик
with t as (select ename ,sal from scott.emp)
   ,t2 as (select t.*
                 ,width_bucket(sum(sal) over(order by sal) ,0 
                              ,sum(sal) over () ,2) ba
             from t
           ) select * from t2 where ba = 2 order by 1 desc
21 фев 08, 13:50    [5322213]     Ответить | Цитировать Сообщить модератору
 Re: вывести список сотрудников, сумма зарплат которых составляет 50 % от общей суммы зарплат  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10039
serge_b77
Вы ошибаетесь.Задача в данном случае тривиальная.И определена полностью.Интересует только верхний слой высокоплачиваемых сотрудников.


I don't think so. First of all where did you find высокоплачиваемых? I don't see it in problem definition. The only difference between this task and knapsack problem is knapsack problem would look for a set of employees сумма зарплат которых is closest to 50 % от общей суммы зарплат versus this task req of для которых при суммировании по полю доля получится значение 50 (имеется ввиду %) или близкое к нему (например от 40 до 50, понятно, что точно может и не получиться). But even with this req, we might need to go through all combinations of employees to find a combination with доля зарплат between 40 and 50. So it is a variation of knapsack problem.

serge_b77
Решение уже приведено(с небольшими помарками).


Are you talking about Raven's solution. If so, change z as follows:

Ivanov200
Zubkov200
Medvedev300
Putin400


Now доля for each of them will be:

SQL> WITH q AS (
  2    SELECT 'Ivanov' n,200 z FROM dual UNION ALL
  3    SELECT 'Zubkov' n,200 z FROM dual UNION ALL
  4    SELECT 'Medvedev' n,300 z FROM dual UNION ALL
  5    SELECT 'Putin' n,400 z FROM dual)
  6  SELECT n,z,z/sum(z) over() d
  7  from q
  8  order by d desc
  9  /

N                 Z          D
-------- ---------- ----------
Putin           400 .363636364
Medvedev        300 .272727273
Ivanov          200 .181818182
Zubkov          200 .181818182

SQL> 

Raven's solution as is and adjusted to "близкое к нему (например от 40 до 50, понятно, что точно может и не получиться). ":

SQL> WITH q AS (
  2    SELECT 'Ivanov' n,200 z FROM dual UNION ALL
  3    SELECT 'Zubkov' n,200 z FROM dual UNION ALL
  4    SELECT 'Medvedev' n,300 z FROM dual UNION ALL
  5    SELECT 'Putin' n,400 z FROM dual)
  6  SELECT n,z,d
  7    FROM (SELECT n,z,d,lag(d,1,0) over (ORDER BY d) pd
  8            FROM (SELECT n,z,
  9                         SUM(z) over (ORDER BY z DESC,n ASC)/SUM(z) over () d
 10                    FROM q))
 11   WHERE pd<.5
 12  /

N                 Z          D
-------- ---------- ----------
Putin           400 .363636364
Medvedev        300 .636363636

SQL> WITH q AS (
  2    SELECT 'Ivanov' n,200 z FROM dual UNION ALL
  3    SELECT 'Zubkov' n,200 z FROM dual UNION ALL
  4    SELECT 'Medvedev' n,300 z FROM dual UNION ALL
  5    SELECT 'Putin' n,400 z FROM dual)
  6  SELECT n,z,d
  7    FROM (SELECT n,z,d,lag(d,1,0) over (ORDER BY d) pd
  8            FROM (SELECT n,z,
  9                         SUM(z) over (ORDER BY z DESC,n ASC)/SUM(z) over () d
 10                    FROM q))
 11   WHERE pd between .4 and .5
 12  /

no rows selected

SQL> 

As you can see, we got incorrect results since Medvedev + Ivanov and/or Medvedev + Zubkov produce "от 40 до 50". Again, IMHO, it is knapsack problem where we are looking for a first combination between 40 and 50 versus a combination closest to 50.

SY.
21 фев 08, 16:32    [5323980]     Ответить | Цитировать Сообщить модератору
 Re: вывести список сотрудников, сумма зарплат которых составляет 50 % от общей суммы зарплат  [new]
marina_06
Member

Откуда:
Сообщений: 5
всё-таки придётся согласиться, что задача нетривиальная.

Выбирала между двумя решениями:
Andrei Fomichev


select * from (
select сотрудник, sum(зарплата) over(order by зарплата desc)/sum(зарплата) over() x from a
) where x <= 0.50


and
RA\/EN

Я думаю так (если без упрощения "ну можно и 40%"):
WITH q AS (
SELECT 'Иванов' n,200 z FROM dual UNION ALL
SELECT 'Медведев' n,300 z FROM dual UNION ALL
SELECT 'Путин' n,400 z FROM dual)
SELECT n,z,d
FROM (SELECT n,z,d,lag(d,1,0) over (ORDER BY d) pd
FROM (SELECT n,z,
SUM(z) over (ORDER BY z DESC,n ASC)/SUM(z) over () d
FROM q))
WHERE pd<.5


аналогичный пример в своих рассуждениях привёл SY

SY


SQL> WITH q AS (
2 SELECT 'Ivanov' n,200 z FROM dual UNION ALL
3 SELECT 'Zubkov' n,200 z FROM dual UNION ALL
4 SELECT 'Medvedev' n,300 z FROM dual UNION ALL
5 SELECT 'Putin' n,400 z FROM dual)
6 SELECT n,z,d
7 FROM (SELECT n,z,d,lag(d,1,0) over (ORDER BY d) pd
8 FROM (SELECT n,z,
9 SUM(z) over (ORDER BY z DESC,n ASC)/SUM(z) over () d
10 FROM q))
11 WHERE pd<.5
12 /

N Z D
-------- ---------- ----------
Putin 400 .363636364
Medvedev 300 .636363636

SQL>




решение предложенное RA\/EN (тоже самое есть у SY) мне больше подходит по специфики моей задачи, хотя строго и не соответствует описанному мной заданию.
Но сделать в точности то, что требуется по моему заданию я знаю, что невозможно, а вариант получить список сотрудников, сумма зарплат которых составляет менее 50 % (причём, возможно намного менее 50%), как в примере Andrei Fomichev меня устраивает намного меньше (хотя сам пример хороший).

Большое Вам спасибо :)
21 фев 08, 23:19    [5325744]     Ответить | Цитировать Сообщить модератору
 Re: вывести список сотрудников, сумма зарплат которых составляет 50 % от общей суммы зарплат  [new]
Anna Yu.
Member

Откуда:
Сообщений: 25
в предложении Andrei Fomichev сортировку для окна надо обязательно дополнить (ид записи, или фамилия, но так, чтоб каждая строка была уникальна), иначе проблема с одинаковыми зарплатами

WITH q AS (
    SELECT 'Ivanov' n,200 z, 1 id FROM dual UNION ALL
    SELECT 'Zubkov' n,200 z, 2 FROM dual UNION ALL
    SELECT 'Petrov' n,300 z, 3 FROM dual UNION ALL
    SELECT 'Putin' n,400 z, 4 FROM dual)
  SELECT n,z
  ,sum(q.z) over (order by  q.z desc) a1 -- сумма нарастающим итогом
  ,trunc((sum(q.z) over (order by  q.z desc)/sum(q.z) over ()), 2) a2 -- доля нарастающим итогом
  ,sum(q.z) over (order by  q.z desc, id) a3 -- сумма нарастающим итогом
  ,trunc((sum(q.z) over (order by  q.z desc, id)/sum(q.z) over ()), 2) a4 -- доля нарастающим итогом
  from q
  order by  q.z desc
и результат:
Putin	400	400	0,36	400	0,36
Petrov	300	700	0,63	700	0,63
Ivanov	200	1100	1	900	0,81
Zubkov	200	1100	1	1100	1
22 фев 08, 08:20    [5326120]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить