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

Откуда:
Сообщений: 101
добрый день.
сломал голову, но пока не смог придумать сам.
есть набор данных
select p, d, s, n from 
(
select 1 n, 1 d, 100 s, 'a' p from dual
union all
select 2 n, 1 d, 100 s, 'a' p from dual
union all
select 3 n, 1 d, 100 s, 'a' p from dual
union all
select 4 n, 2 d, 250 s, 'b' p from dual
union all
select 5 n, 2 d, 250 s, 'b' p from dual
union all
select 6 n, 3 d, 99 s, 'b' p from dual
union all
select 7 n, 4 d, 15 s, 'a' p from dual
union all
select 8 n, 1 d, 100 s, 'a' p from dual
union all
select 9 n, 7 d, 100 s, 'a' p from dual
) q 
то есть набор строк, где
1) в рамках D все S одинаковые.
2) в рамках D встречается только в одном P.
3) N - уникальный ID записи

мне надо получить для каждого P сумму S, по одной на каждый из D
то есть на выходе должно быть такое
P сумма
a215
b 349

для P="a" это 100(от D=1) + 100(от D=7) + 15(от D=4)
для P="b" это 250(от D=2) + 99(от D=3)

самое главное условие, которое мне всё портит:
запрос должен быть выполнен в один "надзапрос".
то есть должно быть типа
select ... from () q
а не так
select ... from (select ... from () q) 
я уже перепробовал все аналитические функции и их комбинации, которые смог придумать, но пока не выходит...
28 сен 15, 16:06    [18205095]     Ответить | Цитировать Сообщить модератору
 Re: сумма distinct значений поля в рамках другого поля с группировкой по третьему полю  [new]
Darkripple
Member

Откуда:
Сообщений: 101
поправка:
"2) D встречается только в одном P."
28 сен 15, 16:08    [18205122]     Ответить | Цитировать Сообщить модератору
 Re: сумма distinct значений поля в рамках другого поля с группировкой по третьему полю  [new]
Elic
Member

Откуда:
Сообщений: 29979
Почему бы вместо борьбы со следствием не устранить причину - научиться писать запросы так, чтобы данные не размножались как кролики.
28 сен 15, 16:38    [18205426]     Ответить | Цитировать Сообщить модератору
 Re: сумма distinct значений поля в рамках другого поля с группировкой по третьему полю  [new]
-2-
Member

Откуда:
Сообщений: 15330
Darkripple
запрос должен быть выполнен в один "надзапрос"
когда озвучивается подобное ограничение, обычно, по мере предложения вариантов всплывает и другое... огласите весь список!
28 сен 15, 16:39    [18205437]     Ответить | Цитировать Сообщить модератору
 Re: сумма distinct значений поля в рамках другого поля с группировкой по третьему полю  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8131
SQL> with q as (
  2  select 1 n, 1 d, 100 s, 'a' p from dual
  3  union all
  4  select 2 n, 1 d, 100 s, 'a' p from dual
  5  union all
  6  select 3 n, 1 d, 100 s, 'a' p from dual
  7  union all
  8  select 4 n, 2 d, 250 s, 'b' p from dual
  9  union all
 10  select 5 n, 2 d, 250 s, 'b' p from dual
 11  union all
 12  select 6 n, 3 d, 99 s, 'b' p from dual
 13  union all
 14  select 7 n, 4 d, 15 s, 'a' p from dual
 15  union all
 16  select 8 n, 1 d, 100 s, 'a' p from dual
 17  union all
 18  select 9 n, 7 d, 100 s, 'a' p from dual
 19  )
 20  --
 21  SELECT p, SUM(s)
 22  FROM (select distinct p, d, s from q)
 23  GROUP BY p
 24  ORDER BY p;

P     SUM(S)
- ----------
a        215
b        349
28 сен 15, 16:40    [18205446]     Ответить | Цитировать Сообщить модератору
 Re: сумма distinct значений поля в рамках другого поля с группировкой по третьему полю  [new]
stax..
Guest
Darkripple,

  1  with t as (
  2  select p, d, s, n from
  3  (
  4  select 1 n, 1 d, 100 s, 'a' p from dual
  5  union all
  6  select 2 n, 1 d, 100 s, 'a' p from dual
  7  union all
  8  select 3 n, 1 d, 100 s, 'a' p from dual
  9  union all
 10  select 4 n, 2 d, 250 s, 'b' p from dual
 11  union all
 12  select 5 n, 2 d, 250 s, 'b' p from dual
 13  union all
 14  select 6 n, 3 d, 99 s, 'b' p from dual
 15  union all
 16  select 7 n, 4 d, 15 s, 'a' p from dual
 17  union all
 18  select 8 n, 1 d, 100 s, 'a' p from dual
 19  union all
 20  select 9 n, 7 d, 100 s, 'a' p from dual
 21  ) )
 22  select p,sum(s) ss from
 23  (select t.*,row_number() over(partition by p,d,s order by null) rn from t)
 24  where rn=1
 25* group by p
SQL> /

P         SS
- ----------
a        215
b        349


....
stax
28 сен 15, 16:43    [18205473]     Ответить | Цитировать Сообщить модератору
 Re: сумма distinct значений поля в рамках другого поля с группировкой по третьему полю  [new]
roadster
Member [заблокирован]

Откуда: "Церковь тяжеловооружённого Христа" ©
Сообщений: 52495
SQL*Plus,

ТС`а фром селект не устраивает.
28 сен 15, 16:44    [18205479]     Ответить | Цитировать Сообщить модератору
 Re: сумма distinct значений поля в рамках другого поля с группировкой по третьему полю  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8131
roadster
SQL*Plus,

ТС`а фром селект не устраивает.
Пижон?!
28 сен 15, 17:00    [18205630]     Ответить | Цитировать Сообщить модератору
 Re: сумма distinct значений поля в рамках другого поля с группировкой по третьему полю  [new]
-2-
Member

Откуда:
Сообщений: 15330
stax..
...
 23  (select t.*,row_number() over(partition by p,d,s order by null) rn from t)
...
с аналитикой можно и без позапросов.
28 сен 15, 17:19    [18205796]     Ответить | Цитировать Сообщить модератору
 Re: сумма distinct значений поля в рамках другого поля с группировкой по третьему полю  [new]
JDS
Member

Откуда:
Сообщений: 690
-2-
stax..
...
 23  (select t.*,row_number() over(partition by p,d,s order by null) rn from t)
...
с аналитикой можно и без подзапросов.

Можно и без аналитики без подзапросов )

WITH t AS (SELECT 1 n, 1 d, 100 s, 'a' p from dual
           union all
           select 2 n, 1 d, 100 s, 'a' p from dual
           union all
           select 3 n, 1 d, 100 s, 'a' p from dual
           union all
           select 4 n, 2 d, 250 s, 'b' p from dual
           union all
           select 5 n, 2 d, 250 s, 'b' p from dual
           union all
           select 6 n, 3 d, 99 s, 'b' p from dual
           union all
           select 7 n, 4 d, 15 s, 'a' p from dual
           union all
           select 8 n, 1 d, 100 s, 'a' p from dual
           union all
           select 9 n, 7 d, 100 s, 'a' p from dual)
  SELECT p, SUM(DISTINCT s + d) - SUM(DISTINCT d) sum_s
    FROM t
   GROUP BY p
28 сен 15, 17:24    [18205830]     Ответить | Цитировать Сообщить модератору
 Re: сумма distinct значений поля в рамках другого поля с группировкой по третьему полю  [new]
-2-
Member

Откуда:
Сообщений: 15330
JDS
Можно и без аналитики без подзапросов
для такого решения нужно знать магию данных.
28 сен 15, 17:27    [18205857]     Ответить | Цитировать Сообщить модератору
 Re: сумма distinct значений поля в рамках другого поля с группировкой по третьему полю  [new]
JDS
Member

Откуда:
Сообщений: 690
-2-
для такого решения нужно знать магию данных.

Спасибо, конечно )), но гордиться особо нечем, так эту магию еще первоклашки проходят
28 сен 15, 17:40    [18205966]     Ответить | Цитировать Сообщить модератору
 Re: сумма distinct значений поля в рамках другого поля с группировкой по третьему полю  [new]
stax..
Guest
Darkripple,

s и d целые?

ps
sum(distinct d*100000000+s)

.....
stax
28 сен 15, 17:43    [18205989]     Ответить | Цитировать Сообщить модератору
 Re: сумма distinct значений поля в рамках другого поля с группировкой по третьему полю  [new]
Darkripple
Member

Откуда:
Сообщений: 101
Elic, можно и так. сначала всё сгруппировать, потом отфильтровать, потом соединить с D, потом сгруппировать по P... в результате будет несколько вложенных подзапросов, и расстановка хинтов для производительности, которую я возможно не осилю.
можно придумать еще десяток способов, включая например временные таблицы... какое-либо решение я найти смогу.
вопрос был не в этом. у меня есть довольно сложное построение данных, в результате которого я получаю то, что описал (в упрощенном виде). и ограниченное количество времени на реализацию. поэтому надеялся найти удобный вариант с использованием аналитических функций. но пока не получилось.
28 сен 15, 21:22    [18207127]     Ответить | Цитировать Сообщить модератору
 Re: сумма distinct значений поля в рамках другого поля с группировкой по третьему полю  [new]
Darkripple
Member

Откуда:
Сообщений: 101
-2-
огласите весь список!

есть еще одно, которое вспомнил и осознал позже. прошу прощения. (-:
не хочу использовать group by в конце.
28 сен 15, 21:40    [18207189]     Ответить | Цитировать Сообщить модератору
 Re: сумма distinct значений поля в рамках другого поля с группировкой по третьему полю  [new]
Darkripple
Member

Откуда:
Сообщений: 101
stax..
s и d целые?

D - целое,
S - не очень.
stax..
sum(distinct d*100000000+s)

JDS
SELECT p, SUM(DISTINCT s + d) - SUM(DISTINCT d) sum_s

правильно ли я вас понял, что имелось в виду что-то вроде этого?
select distinct sum(distinct d*100000000+s) over (partition by p) - sum(distinct d*100000000) over (partition by p), p from t

ну или вот так
select distinct mod(sum(distinct d*100000000+s) over (partition by p), 100000000), p from t


оба варианта возвращают то, что мне нужно.
Спасибо!
28 сен 15, 21:55    [18207231]     Ответить | Цитировать Сообщить модератору
 Re: сумма distinct значений поля в рамках другого поля с группировкой по третьему полю  [new]
Darkripple
Member

Откуда:
Сообщений: 101
сейчас покурю алгоритм, осознаю как и почему это работает, и попробую впихнуть в исходный запрос.
всем большое спасибо!
28 сен 15, 21:58    [18207237]     Ответить | Цитировать Сообщить модератору
 Re: сумма distinct значений поля в рамках другого поля с группировкой по третьему полю  [new]
roadster
Member [заблокирован]

Откуда: "Церковь тяжеловооружённого Христа" ©
Сообщений: 52495
Darkripple
не хочу использовать group by в конце.
почему?
28 сен 15, 22:03    [18207254]     Ответить | Цитировать Сообщить модератору
 Re: сумма distinct значений поля в рамках другого поля с группировкой по третьему полю  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10043
JDS
Спасибо, конечно )), но гордиться особо нечем, так эту магию еще первоклашки проходят


Для особо одаренных математически:

WITH t AS (SELECT 1 n, 1 d, 100 s, 'a' p from dual
           union all
           select 2 n, 2 d, 99 s, 'a' p from dual
           union all
           select 3 n, 3 d, 98 s, 'a' p from dual
           union all
           select 4 n, 2 d, 250 s, 'b' p from dual
           union all
           select 5 n, 2 d, 250 s, 'b' p from dual
           union all
           select 6 n, 3 d, 99 s, 'b' p from dual
           union all
           select 7 n, 4 d, 15 s, 'a' p from dual
           union all
           select 8 n, 1 d, 100 s, 'a' p from dual
           union all
           select 9 n, 7 d, 100 s, 'a' p from dual)
  SELECT p, SUM(DISTINCT s + d) - SUM(DISTINCT d) sum_s
    FROM t
   GROUP BY p
/

P      SUM_S
- ----------
a        210
b        349

SQL>


SY.
28 сен 15, 22:39    [18207348]     Ответить | Цитировать Сообщить модератору
 Re: сумма distinct значений поля в рамках другого поля с группировкой по третьему полю  [new]
-2-
Member

Откуда:
Сообщений: 15330
Darkripple
для производительности
для производительности нужно меньше извращений про нельзя подзапрос и отвращение к group by.
Но скорее всего, выеживания обусловлены тем, что ты не осилил правильно написать исходный запрос q. Осиль, хотя бы, первый данный тебе запрос, потом будешь рассуждать о хинтах.
29 сен 15, 00:16    [18207553]     Ответить | Цитировать Сообщить модератору
 Re: сумма distinct значений поля в рамках другого поля с группировкой по третьему полю  [new]
mlc
Member

Откуда: Asus Winter Cup
Сообщений: 487
Darkripple,

with t as  (
  select 1 n, 1 d, 100 s, 'a' p from dual
  union all
  select 2 n, 1 d, 100 s, 'a' p from dual
  union all
  select 3 n, 1 d, 100 s, 'a' p from dual
  union all
  select 4 n, 2 d, 250 s, 'b' p from dual
  union all
  select 5 n, 2 d, 250 s, 'b' p from dual
  union all
  select 6 n, 3 d, 99 s, 'b' p from dual
  union all
  select 7 n, 4 d, 15 s, 'a' p from dual
  union all
  select 8 n, 1 d, 100 s, 'a' p from dual
  union all
  select 9 n, 7 d, 100 s, 'a' p from dual
)
select p, r from t
model
return updated rows
partition by (p)
dimension by(n, row_number()over(partition by p order by n) rn)
measures(0 r, nullif(s, lag(s)over(partition by p,d order by n)) lg)
rules (r[n, 1] = sum(lg)[any,any])


P          R
- ----------
a        215 
b        349 
29 сен 15, 09:30    [18208004]     Ответить | Цитировать Сообщить модератору
 Re: сумма distinct значений поля в рамках другого поля с группировкой по третьему полю  [new]
JDS
Member

Откуда:
Сообщений: 690
SY
Для особо одаренных математически:

Такэта панятна, там же stax и сам ТС уже подпилили немного )
WITH t AS (SELECT 1 n, 1 d, 100 s, 'a' p from dual
           union all
           select 2 n, 2 d, 99 s, 'a' p from dual
           union all
           select 3 n, 3 d, 98 s, 'a' p from dual
           union all
           select 4 n, 2 d, 250 s, 'b' p from dual
           union all
           select 5 n, 2 d, 250 s, 'b' p from dual
           union all
           select 6 n, 3 d, 99 s, 'b' p from dual
           union all
           select 7 n, 4 d, 15 s, 'a' p from dual
           union all
           select 8 n, 1 d, 100 s, 'a' p from dual
           union all
           select 9 n, 7 d, 100 s, 'a' p from dual)
  SELECT p, SUM(DISTINCT s + d*10000000) - SUM(DISTINCT d*10000000) sum_s
    FROM t
   GROUP BY p

Но понятно, что и это будет работать криво:
SQL> WITH t AS (select 9 n, 1 d, 10000001 s, 'a' p from dual
  2             union all
  3             select 10 n, 2 d, 1 s, 'a' p from dual)
  4    SELECT p, SUM(DISTINCT d*10000000 + s) - SUM(DISTINCT d*10000000) sum_s
  5      FROM t
  6     GROUP BY p
  7  /
 
P      SUM_S
- ----------
a   -9999999

Поэтому допилим еще:
SQL> WITH t AS (select 9 n, 1 d, 10000001 s, 'a' p from dual
  2             union all
  3             select 10 n, 2 d, 1 s, 'a' p from dual)
  4    SELECT p, (SUM(DISTINCT d*10000000 + s*3000000) - SUM(DISTINCT d*10000000))/3000000 sum_s
  5      FROM t
  6     GROUP BY p
  7  /
 
P      SUM_S
- ----------
a   10000002

И скорее всего, и здесь можно поискать вариант данных, когда будет негуд, но лень )
29 сен 15, 09:49    [18208067]     Ответить | Цитировать Сообщить модератору
 Re: сумма distinct значений поля в рамках другого поля с группировкой по третьему полю  [new]
mlc
Member

Откуда: Asus Winter Cup
Сообщений: 487
Darkripple,

чуть проще
select p, r from t
model
return updated rows
partition by (p)
dimension by(n)
measures(0 r, nullif(s, lag(s)over(partition by p,d order by n)) lg)
rules upsert(r[0] = sum(lg)[any]);


P          R
- ----------
a        215 
b        349
29 сен 15, 09:57    [18208101]     Ответить | Цитировать Сообщить модератору
 Re: сумма distinct значений поля в рамках другого поля с группировкой по третьему полю  [new]
JDS
Member

Откуда:
Сообщений: 690
mlc, колдун аднака )
Пойду в очередной раз покурю model, все никак не приживется в голове )
29 сен 15, 10:02    [18208121]     Ответить | Цитировать Сообщить модератору
 Re: сумма distinct значений поля в рамках другого поля с группировкой по третьему полю  [new]
mlc
Member

Откуда: Asus Winter Cup
Сообщений: 487
Darkripple,

ну и совсем простое решение
with t as  (
  select 1 n, 1 d, 100 s, 'a' p from dual
  union all
  select 2 n, 1 d, 100 s, 'a' p from dual
  union all
  select 3 n, 1 d, 100 s, 'a' p from dual
  union all
  select 4 n, 2 d, 250 s, 'b' p from dual
  union all
  select 5 n, 2 d, 250 s, 'b' p from dual
  union all
  select 6 n, 3 d, 99 s, 'b' p from dual
  union all
  select 7 n, 4 d, 15 s, 'a' p from dual
  union all
  select 8 n, 1 d, 100 s, 'a' p from dual
  union all
  select 9 n, 7 d, 100 s, 'a' p from dual
)
select p,lg from t
model
return updated rows
partition by (p)
dimension by(n)
measures(lag(0,1,s)over(partition by p,d order by n) lg)
rules (lg[0] = sum(lg)[any]);


P         LG
- ----------
a        215 
b        349 
29 сен 15, 11:07    [18208423]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить