ReSQL.ru
     
powered by simpleCommunicator - 2.0.49     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Sybase ASA, ASE, IQ [игнор отключен] [закрыт для гостей] / SQL запрос со сложной агрегацией
21 сообщений из 21, страница 1 из 1
SQL запрос со сложной агрегацией
    #39259122
SQL_AVG_FROM_FIRST
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL_AVG_FROM_FIRST
Гость
Задача: есть 2 таблицы: заголовки (header) и позиции (item)
В таблице header есть показатель fact1.
Нужно написать запрос:
Код: sql
1.
2.
3.
4.
5.
6.
7.
select
агрегация?(fact1)
from
item
left join header on header.id = item.header_id
where
item.dim1 in (1, 2, 3)


который будет работать возвращать 35,33 для следующего набора данных:
header_iditem_idfact111112123152415251526153790389039903109031190
То есть взять среднее значение для fact1 каждого заголовка.
IQ 16.
...
Рейтинг: 0 / 0
20.06.2016, 18:14
| Ответить | Цитировать | Написать  
SQL запрос со сложной агрегацией
    #39259130
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
White Owl
Участник
http://www.sql.ru/forum/844750/kak-zadavat-vopros
...
Рейтинг: 0 / 0
20.06.2016, 18:26
| Ответить | Цитировать | Написать  
SQL запрос со сложной агрегацией
    #39259139
SQL_AVG_FROM_FIRST
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL_AVG_FROM_FIRST
Гость
White Owl http://www.sql.ru/forum/844750/kak-zadavat-vopros какую функцию смотреть?
...
Рейтинг: 0 / 0
20.06.2016, 18:36
| Ответить | Цитировать | Написать  
SQL запрос со сложной агрегацией
    #39259281
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
SQL_AVG_FROM_FIRST,

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

но в целом, создается впечатление, что двигаться нужно начинать от таблицы header, а данные таблицы item использовать в качестве фильтра в секции where.

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
select avg(header.fact1)
  from header
 where header.id in 
        (
          select item.header_id
            from item
           where item.dim1 in (1,2,3) 
        )


ну или в форме джойна:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
select avg(header.fact1)
  from header
  join (
          select distinct item.header_id
            from item
           where item.dim1 in (1,2,3) 
        ) v
    on header.id = v.header_id
...
Рейтинг: 0 / 0
21.06.2016, 05:59
| Ответить | Цитировать | Написать  
SQL запрос со сложной агрегацией
    #39259438
SQL_AVG_FROM_FIRST
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL_AVG_FROM_FIRST
Гость
Таблица header:
header_idfact111215390
таблица item:
header_iditem_iddim111112123124125126137138139131013111
запрос:
Код: sql
1.
select * from header where header_id in (select header_id from item where dim1 in (...))

не подходит, поскольку dim1 нужен в результирующем наборе данных.
то есть нужно написать запрос который вернет:
dim1avg(fact1)135.33
...
Рейтинг: 0 / 0
21.06.2016, 11:15
| Ответить | Цитировать | Написать  
SQL запрос со сложной агрегацией
    #39259442
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
SQL_AVG_FROM_FIRST,

не скупись на тестовых данных. Приведи набор строк таблицы ITEM с разными DIM и покажи, как в этом случае должен выглядеть результат запроса.
...
Рейтинг: 0 / 0
21.06.2016, 11:17
| Ответить | Цитировать | Написать  
SQL запрос со сложной агрегацией
    #39259452
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
SQL_AVG_FROM_FIRST,

пока (по уточненным данным) напрашивается такое:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
select v.dim1, avg(header.fact1)
  from header
  join (
          select distinct item.header_id, dim1
            from item
           where item.dim1 in (1,2,3) 
        ) v
    on header.header_id = v.header_id
 group by v.dim1
...
Рейтинг: 0 / 0
21.06.2016, 11:27
| Ответить | Цитировать | Написать  
SQL запрос со сложной агрегацией
    #39259613
SQL_AVG_FROM_FIRST
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL_AVG_FROM_FIRST
Гость
Добрый Э - Эх,

это не решает проблему, поля из item нужны в блоке select, но при этом агрегация avg по header.fact1 должна работать без учета строк из item.

может так моя задача будет понятнее:
Код: sql
1.
2.
3.
4.
5.
6.
select
avg(header.fact1)
from
item
left join
header on header.id = item.header_id

посчитает среднее как 46,5
если на примере функции row_number():
Код: sql
1.
2.
3.
4.
5.
6.
7.
select
header.id, item.id, header.fact1,
row_number() over (partition by header.id order by item.id) as row_num
from
item
left join
header on header.id = item.header_id

мне нужно среднее по строкам с row_num = 1
...
Рейтинг: 0 / 0
21.06.2016, 13:53
| Ответить | Цитировать | Написать  
SQL запрос со сложной агрегацией
    #39259630
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
SQL_AVG_FROM_FIRST,

То есть, в итоге ты хочешь получить ВСЮ выборку, но расчет делать только по первым строкам групп?
Тогда ещё раз - приведи репрезентативный юзабильный набор тестовых данных для обеих таблиц и желаемый результат на этих тестовых данных. Ибо по твоим отрывочным пояснениям не совсем понятно, какие именно значения полей тебе нужны и как всё это будет по итогу выглядеть в результирующей выборке
...
Рейтинг: 0 / 0
21.06.2016, 14:06
| Ответить | Цитировать | Написать  
SQL запрос со сложной агрегацией
    #39259688
SQL_AVG_FROM_FIRST
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL_AVG_FROM_FIRST
Гость
Добрый Э - Эх,

Таблица header:idfact111215390
таблица item:header_idid111223242526373839310311
Запрос:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
select
header.id as header_id,
--item.id as item_id,
avg(header.fact1) as avg_fact1,
требуемая_агрегация(header.id, header.fact1) as требуемая_агрегация
from
item
left join
header on header.id = item.header_id

результат:header_idavg_fact1требуемая_агрегация1112151539090
Запрос:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
select
--header.id as header_id,
--item.id as item_id,
avg(header.fact1) as avg_fact1,
требуемая_агрегация(header.id, header.fact1) as требуемая_агрегация
from
item
left join
header on header.id = item.header_id

результат:avg_fact1требуемая_агрегация46.5454545535.33333333
...
Рейтинг: 0 / 0
21.06.2016, 14:55
| Ответить | Цитировать | Написать  
SQL запрос со сложной агрегацией
    #39259841
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
White Owl
Участник
SQL_AVG_FROM_FIRSTWhite Owl http://www.sql.ru/forum/844750/kak-zadavat-vopros какую функцию смотреть?Прочитай второй пункт. Внимательно прочитай.
...
Рейтинг: 0 / 0
21.06.2016, 18:05
| Ответить | Цитировать | Написать  
SQL запрос со сложной агрегацией
    #39259867
SQL_AVG_FROM_FIRST
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL_AVG_FROM_FIRST
Гость
White Owl,

скрипт на создание тестовых таблиц:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
--drop table header
--drop table item

create table header (id integer, fact1 double)
go
insert into header values ( 1, 1 )
insert into header values ( 2, 15 )
insert into header values ( 3, 90 )
go

create table item (id integer, header_id integer)
go
insert into item values (1, 1)
insert into item values (2, 1)
insert into item values (3, 2)
insert into item values (4, 2)
insert into item values (5, 2)
insert into item values (6, 2)
insert into item values (7, 3)
insert into item values (8, 3)
insert into item values (9, 3)
insert into item values (10, 3)
insert into item values (11, 3)
go

стандартное среднее работает так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
select
--header.id as header_id, item.id as item_id,
avg( header.fact1 )
from
item
left join
header on header.id = item.header_id

avg( header.fact1 )46.54545454545455нужно, чтобы среднее считалось только по header (без учета того, сколько строк в item). То есть нужный результат:результат35.333333333333336
...
Рейтинг: 0 / 0
21.06.2016, 18:49
| Ответить | Цитировать | Написать  
SQL запрос со сложной агрегацией
    #39259872
SQL_AVG_FROM_FIRST
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL_AVG_FROM_FIRST
Гость
SQL_AVG_FROM_FIRSTWhite Owl,

скрипт на создание тестовых таблиц:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
--drop table header
--drop table item

create table header (id integer, fact1 double)
go
insert into header values ( 1, 1 )
insert into header values ( 2, 15 )
insert into header values ( 3, 90 )
go

create table item (id integer, header_id integer)
go
insert into item values (1, 1)
insert into item values (2, 1)
insert into item values (3, 2)
insert into item values (4, 2)
insert into item values (5, 2)
insert into item values (6, 2)
insert into item values (7, 3)
insert into item values (8, 3)
insert into item values (9, 3)
insert into item values (10, 3)
insert into item values (11, 3)
go

стандартное среднее работает так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
select
--header.id as header_id, item.id as item_id,
avg( header.fact1 )
from
item
left join
header on header.id = item.header_id

avg( header.fact1 )46.54545454545455нужно, чтобы среднее считалось только по header (без учета того, сколько строк в item). То есть нужный результат:результат35.333333333333336на человеческом языке мне нужно вот что: есть счета фактуры, они хранятся в двух таблицах - данные заголовка и данные позиций.
на уровне заголовка хранится некий показатель, этот показатель нужно анализировать в отчете с возможностью фильтра/развертки по продуктам/группам продуктов.
Но при этом агрегация должна быть AVG по заголовкам.
Другими словами, если в одной фактуре 10 продуктов группы 1, а в другой 1 продукт группы 1, и показатель по первой фактуре 100, а по второй 50, то агрегация должна рассчитаться равной 75, а не 95,45454545454545
надеюсь кто-то уже сталкивался с подобной задачей.
...
Рейтинг: 0 / 0
21.06.2016, 19:00
| Ответить | Цитировать | Написать  
SQL запрос со сложной агрегацией
    #39260014
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
White Owl
Участник
SQL_AVG_FROM_FIRSTскрипт на создание тестовых таблицНу наконец-то. Это было так сложно?


SQL_AVG_FROM_FIRSTнужно, чтобы среднее считалось только по header (без учета того, сколько строк в item). То есть нужный результат:результат35.333333333333336Ну и делаем без учета item
Код: sql
1.
2.
3.
select
avg( header.fact1 )
from header
...
Рейтинг: 0 / 0
22.06.2016, 00:16
| Ответить | Цитировать | Написать  
SQL запрос со сложной агрегацией
    #39260194
SQL_AVG_FROM_FIRST
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL_AVG_FROM_FIRST
Гость
White Owl,

Нужно с учетом item. В этом и есть основная сложность.
...
Рейтинг: 0 / 0
22.06.2016, 11:13
| Ответить | Цитировать | Написать  
SQL запрос со сложной агрегацией
    #39260323
SQL_AVG_FROM_FIRST
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL_AVG_FROM_FIRST
Гость
В общем если я правильно понял, то функциональность "окон" не распространяется на AVG...

То есть такую задачу решить на уровне БД нельзя. печаль.
...
Рейтинг: 0 / 0
22.06.2016, 13:14
| Ответить | Цитировать | Написать  
SQL запрос со сложной агрегацией
    #39260602
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
White Owl
Участник
SQL_AVG_FROM_FIRSTWhite Owl,

Нужно с учетом item. В этом и есть основная сложность.Сначала ты говоришь что item не нужно учитывать. Теперь говоришь что нужно. Ты сам знаешь что ты хочешь????

SQL_AVG_FROM_FIRSTВ общем если я правильно понял, то функциональность "окон" не распространяется на AVG...Что за глупости? Мы OLAP еще не трогали, мы даже о группировке не вспоминали.
Реши наконец что ты хочешь, сделай четкий пример данных и ясное описание желаемого результата. Тогда и будем смотреть что можно сделать в база данных, а для чего достаточно букварь прочитать: http://www.sql.ru/docs/sql/u_sql/
...
Рейтинг: 0 / 0
22.06.2016, 17:19
| Ответить | Цитировать | Написать  
SQL запрос со сложной агрегацией
    #39260679
SQL_AVG_FROM_FIRST
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL_AVG_FROM_FIRST
Гость
White Owl,

Таблицы для теста:
Код: sql
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.
--drop table header
--drop table item
--drop table product
--go

create table header (id integer, fact1 double)
go
insert into header values ( 1, 0 )
insert into header values ( 2, 50 )
insert into header values ( 3, 100 )
go

create table item (id integer, header_id integer, product_id integer)
go
insert into item values (1, 1, 1)
insert into item values (2, 2, 1)
insert into item values (3, 2, 2)
insert into item values (4, 3, 3)
insert into item values (11, 2, 4)
insert into item values (5, 3, 1)
insert into item values (6, 3, 2)
insert into item values (7, 3, 3)
insert into item values (8, 3, 4)
insert into item values (9, 3, 5)
insert into item values (10, 3, 6)
go

create table product (id integer, group_id integer)
insert into product values (1, 1)
insert into product values (1, 1)
insert into product values (2, 1)
insert into product values (3, 2)
insert into product values (4, 3)
insert into product values (5, 3)
insert into product values (6, 1)



Запрос:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
select
prd.group_id,
count(distinct hdr.id) as header_count,
list(distinct hdr.id, ';' order by hdr.id) as header_list,
list(distinct hdr.fact1, ';') as fact1_list,
avg(hdr.fact1) as fact1
from
item itm
left join
header hdr on hdr.id = itm.header_id
left join
product prd on prd.id = itm.product_id
group by
prd.group_id


Результат:group_idheader_countheader_listfact1_listfact1322;350;10083.33333333333333213100100131;2;30;50;10061.111111111111114
Нужно:group_idheader_countheader_listfact1_listfact1322;350;100(50+100)/2=75213100100131;2;30;50;100(0+50+100)/3=50
...
Рейтинг: 0 / 0
22.06.2016, 18:35
| Ответить | Цитировать | Написать  
SQL запрос со сложной агрегацией
    #39260713
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
White Owl
Участник
Ох... теперь у тебя уже три исходные таблицы...

Ну во первых, у тебя ошибка в задании product. Две строки с id=1, ну спишем это на опечатку...

А во вторых, вот:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
select distinct
    group_id,
    ( select avg(fact1)
      from header
      where id in  
          ( select distinct hdr.id
            from header hdr
            where hdr.id in
                 ( select distinct itm.header_id
                   from item itm 
                   where product_id in
                         ( select distinct prd_in.id
                           from product prd_in
                           where prd_in.group_id=product.group_id)
                )
         )
   )
from product
...
Рейтинг: 0 / 0
22.06.2016, 19:23
| Ответить | Цитировать | Написать  
SQL запрос со сложной агрегацией
    #39260827
SQL_AVG_FROM_FIRST
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SQL_AVG_FROM_FIRST
Гость
White OwlОх... теперь у тебя уже три исходные таблицы...Их больше. У продукта 7 атрибутов. у остальных аналитик тоже есть атрибуты аналогично продукту...

в общем подумаю...
...
Рейтинг: 0 / 0
22.06.2016, 22:49
| Ответить | Цитировать | Написать  
SQL запрос со сложной агрегацией
    #39260851
White Owl
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
White Owl
Участник
SQL_AVG_FROM_FIRSTWhite OwlОх... теперь у тебя уже три исходные таблицы...Их больше. У продукта 7 атрибутов. у остальных аналитик тоже есть атрибуты аналогично продукту...

в общем подумаю...Ну тогда забудь про одиночные SQL. Делай datamart'ы с полноценными кубами. Жить будет намного проще.
Впрочем у тебя же IQ? Так что что-то в этом духе должно подразумеваться. Иначе, на аггрегациях через много таблиц просядешь по производительности до полного ой.
...
Рейтинг: 0 / 0
23.06.2016, 00:05
| Ответить | Цитировать | Написать  
21 сообщений из 21, страница 1 из 1
Форумы / Sybase ASA, ASE, IQ [игнор отключен] [закрыт для гостей] / SQL запрос со сложной агрегацией
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=55&msg=39259872&tid=2009707]:
 0ms
get settings:
 7ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 182ms
get topic data:
 9ms
get forum data:
 3ms
get page messages:
 62ms
get tp. blocked users:
 2ms
others: 12ms
total:  298ms
созд. / загр.: 298ms
x
x
Закрыть


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