Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / выборка данных из 2 связанных таблиц с несколькими sum функциями / 25 сообщений из 44, страница 1 из 2
  1  все
07.09.2014, 20:04
    #38739911
Davidkoko
Davidkoko
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
выборка данных из 2 связанных таблиц с несколькими sum функциями
Здравствуйте!
`В базе имеется таблицы:incomes,sales,Products. Задача следуюшая- в query вывести:
1. sum(incomes)
2. sum(sales)
3. количество товаров сейчас: a-b

таблица 'incomes' имеет столбцы:Incomeid,ProductId,quant(количество прыходов)...
таблица 'sales' имеет столбцы: SalesId, IncomeId, ProductId, quant(количество продаж товара) ...

пишу SQL:

select i.productId, sum(i.quant) as iq, sum(s.quant) as sq
from income i,sales s*
where (i.incomeId=s.incomeId)
group by i.productId

или

select i.productId, sum(i.quant) as iq, sum(s.quant) as sq
from income
left outer join s on (i.IncomeId=s.IncomeId)
group by i.productId

но вместо того чтобы вывести:

Код: plaintext
1.
2.
3.
4.
5.
 productIdId   sum(income)   sum(sales)    now

       1          200           50         150    
       2          150           20         130    
       3          120           100        20
выводит неправильную сумму количеств прыходов-sum(income):
Код: plaintext
1.
2.
3.
4.
5.
 productIdId    sum(income)   sum(sales)    now       

       1          20000        50         19950     
       2          27500        20         27480   
       3          125000      100         12400
database server- Interbase 2009.

спасибо заранее!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.09.2014, 20:21
    #38739914
Dimitry Sibiryakov
Dimitry Sibiryakov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
выборка данных из 2 связанных таблиц с несколькими sum функциями
Davidkokodatabase server- Interbase 2009.
В котором нет ни derived tables, ни CTE. Если уберёшь третий пункт и разницу будешь
считать непосредственно при выводе - можешь обойтись подзапросами. Иначе - хранимая
процедура или монстровитый union all с последующим group by.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.09.2014, 20:30
    #38739922
Davidkoko
Davidkoko
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
выборка данных из 2 связанных таблиц с несколькими sum функциями
Dimitry SibiryakovDavidkokodatabase server- Interbase 2009.
В котором нет ни derived tables, ни CTE. Если уберёшь третий пункт и разницу будешь
считать непосредственно при выводе - можешь обойтись подзапросами. Иначе - хранимая
процедура или монстровитый union all с последующим group by.


без третьего пункта тоже не смог решить.
А если перевести базу в Firebird 2.5? поможет (derived tables и CTE)?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.09.2014, 20:34
    #38739926
Dimitry Sibiryakov
Dimitry Sibiryakov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
выборка данных из 2 связанных таблиц с несколькими sum функциями
DavidkokoА если перевести базу в Firebird 2.5?
Тогда у тебя они будут в распоряжении и запрос станет простым и элегантным.

Но если ты с такой лёгкостью можешь манипулировать базой, то проще будет объединить эти
две таблицы в одну. Тогда запрос вообще станет тривиальным.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.09.2014, 21:54
    #38739978
Davidkoko
Davidkoko
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
выборка данных из 2 связанных таблиц с несколькими sum функциями
Dimitry SibiryakovDavidkokoА если перевести базу в Firebird 2.5?
Тогда у тебя они будут в распоряжении и запрос станет простым и элегантным.

Но если ты с такой лёгкостью можешь манипулировать базой, то проще будет объединить эти
две таблицы в одну. Тогда запрос вообще станет тривиальным.


Dimitry Sibiryakov, огромнейшее спасибо! проверил в Firebird 2.5 и все правильно работает, и разницу sum(income)-sum(sales) считивает.

если можно еще одинь вопрос:что вы имели ввиду когда писали: "проще будет объединить эти две таблицы в одну"? я всегда думал что в целях нормализации БД sales и incomes обьязательно должни быть в отдельных таблицах!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.09.2014, 22:02
    #38739987
Dimitry Sibiryakov
Dimitry Sibiryakov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
выборка данных из 2 связанных таблиц с несколькими sum функциями
Davidkokoчто вы имели ввиду когда писали: "проще будет объединить эти две таблицы в
одну"? я всегда думал что в целях нормализации БД sales и incomes обьязательно должни быть
в отдельных таблицах!
И какую же нормальную форму ты пытаешься воплотить в жизнь разделяя одну сущность
"движение средств" на две таблицы?..
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.09.2014, 22:17
    #38739999
Davidkoko
Davidkoko
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
выборка данных из 2 связанных таблиц с несколькими sum функциями
Dimitry SibiryakovDavidkokoчто вы имели ввиду когда писали: "проще будет объединить эти две таблицы в
одну"? я всегда думал что в целях нормализации БД sales и incomes обьязательно должни быть
в отдельных таблицах!
И какую же нормальную форму ты пытаешься воплотить в жизнь разделяя одну сущность
"движение средств" на две таблицы?..


еще раз спасибо, я много раз думал об этом, но никак не мог рисковать. Вы мне очен помогли!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.10.2014, 11:45
    #38768840
des1roer
des1roer
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
выборка данных из 2 связанных таблиц с несколькими sum функциями
народ подскажите как сумму выбрать? фб 1.5
Код: sql
1.
2.
3.
4.
select SUM(BRUTTO)
from PROTOKOL
where (D +  T >  '07.10.2014 8:00:00' and D +  T <  '07.10.2014 20:00:00' )
order by PROTOKOL.D + PROTOKOL.T desc;


Can't format message 13:896 -- message system code -4.
Dynamic SQL Error.
SQL error code = -104.
Invalid expression in the ORDER BY clause (not contained in either an aggregate function or the GROUP BY clause).
(3,058 с)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.10.2014, 11:52
    #38768855
Симонов Денис
Симонов Денис
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
выборка данных из 2 связанных таблиц с несколькими sum функциями
des1roer,

накой тебе ORDER BY при подсчёте одной единственной суммы?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.10.2014, 11:53
    #38768856
des1roer
des1roer
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
выборка данных из 2 связанных таблиц с несколькими sum функциями
order был лишним
Код: sql
1.
2.
3.
select SUM(BRUTTO)
from PROTOKOL
where (D +  T >  '07.10.2014 8:00:00' and D +  T <  '07.10.2014 20:00:00' )
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.10.2014, 11:54
    #38768859
des1roer
des1roer
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
выборка данных из 2 связанных таблиц с несколькими sum функциями
Симонов Денис, спс. Сам увидел. просто из более сложного запроса осталось))))
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.10.2014, 12:46
    #38768960
des1roer
des1roer
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
выборка данных из 2 связанных таблиц с несколькими sum функциями
млин объясните нубу.
вот этот запрос намертво ложит емс
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
   SELECT
            FIRST 1 D + T            
            FROM PROTOKOL
             left outer join CARGO CARGO1 on (PROTOKOL.FK_CARGO = CARGO1.ID_CARGO)
            where 
             (    D +  T >  
           (
            SELECT
            FIRST 1 D + T  
            FROM PROTOKOL
            left outer join CARGO CARGO1 on (PROTOKOL.FK_CARGO = CARGO1.ID_CARGO)
            where 
          (D +  T >  '07.10.2014 8:00:00' and D +  T <  '07.10.2014 20:00:00' ) 
           and NAME_CARGO = 'Магнетитовая'
            order by D + T desc
             ) 
            and  D +  T <  '07.10.2014 23:00:00'  )       
            and NAME_CARGO = 'Магнет. порожняк'


вложенный запрос отрабатывает хоть и медленно (3 с), но корректно
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
  SELECT
            FIRST 1 D + T  
            FROM PROTOKOL
            left outer join CARGO CARGO1 on (PROTOKOL.FK_CARGO = CARGO1.ID_CARGO)
            where 
          (D +  T >  '07.10.2014 8:00:00' and D +  T <  '07.10.2014 20:00:00' ) 
           and NAME_CARGO = 'Магнетитовая'
            order by D + T desc
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.10.2014, 12:55
    #38768980
Dimitry Sibiryakov
Dimitry Sibiryakov
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
выборка данных из 2 связанных таблиц с несколькими sum функциями
des1roerобъясните нубу.
Исчерпывающее описание
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.10.2014, 12:56
    #38768984
kdv
kdv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
выборка данных из 2 связанных таблиц с несколькими sum функциями
des1roer,

по мелочи
D + T
сделай уже вычисляемый столбец DT как D+T, чтобы не писать постоянно D+T

FROM PROTOKOL left outer join CARGO CARGO1
либо не пиши алиасы совсем, либо пиши везде.
from protocol p left join cargo c
тут точно нужен left join?

des1roerвложенный запрос отрабатывает хоть и медленно (3 с), но корректно
вложенный запрос сначала выполняется целиком, потому что
order by D + T desc
приводит к PLAN SORT, т.е. сначала запрос выполнится, и отсортируется результат, а потом только будет выбрана его первая запись (first).

И "намертво ложит емс" - емс тут ни при чем, он просто ждет ответа от сервера, а запрос выполняется очень долго.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.10.2014, 12:56
    #38768985
Симонов Денис
Симонов Денис
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
выборка данных из 2 связанных таблиц с несколькими sum функциями
des1roer,

сам виноват. Этот запрос

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
  SELECT
            FIRST 1 D + T  
            FROM PROTOKOL
            left outer join CARGO CARGO1 on (PROTOKOL.FK_CARGO = CARGO1.ID_CARGO)
            where 
          (D +  T >  '07.10.2014 8:00:00' and D +  T <  '07.10.2014 20:00:00' ) 
           and NAME_CARGO = 'Магнетитовая'
            order by D + T desc


наверняка не использует индекс. Попробуй создать вычисляемый индекс по D + T.

По поводу запроса в целом. Такие подзапросы не материализуются, а следовательно будут перевыполняться на каждой итерации. Поэтому твои 3 сек будут умножены на количество записей основного запроса. Хранимая процедура или EB в котором эти запросы разделены на 2 поможет.

Для понимания поможет вот эта статейка http://www.ibase.ru/devinfo/dataaccesspaths.htm
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.10.2014, 13:01
    #38768993
des1roer
des1roer
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
выборка данных из 2 связанных таблиц с несколькими sum функциями
что за вычисляемый индекс д+т?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.10.2014, 13:08
    #38769002
kdv
kdv
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
выборка данных из 2 связанных таблиц с несколькими sum функциями
des1roer,

http://www.firebirdsql.org/refdocs/langrefupd20-create-index.html
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.10.2014, 11:47
    #38770147
des1roer
des1roer
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
выборка данных из 2 связанных таблиц с несколькими sum функциями
Код: sql
1.
2.
3.
4.
5.
6.
7.
CREATE [UNIQUE] [ASC[ENDING] | [DESC[ENDING]] INDEX indexname
   ON tablename
   { (<col> [, <col> ...]) | COMPUTED BY (expression) }

CREATE  INDEX indexname
   ON tablename
    COMPUTED BY (d+t)


так что ли
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.10.2014, 11:51
    #38770159
Симонов Денис
Симонов Денис
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
выборка данных из 2 связанных таблиц с несколькими sum функциями
des1roer,

вроде того. А ещё лучше если добавить в таблицу обычный столбец типа TIMESTAMP и вычислять его в триггерах before insert, before update. Тогда можно будет обычный индекс вешать, да и запрос упростится.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.10.2014, 11:57
    #38770169
des1roer
des1roer
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
выборка данных из 2 связанных таблиц с несколькими sum функциями
таблица не моя - единственное что могу - это дергать селектом. может ну его в болото, ложить в постгрес и уж оттуда выгребать нужные данные... млин а индекс не положит базу?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.10.2014, 11:59
    #38770171
Симонов Денис
Симонов Денис
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
выборка данных из 2 связанных таблиц с несколькими sum функциями
des1roer,

индекс не может положить базу. Разве что вставка записей немного замедлится.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.10.2014, 12:07
    #38770182
Симонов Денис
Симонов Денис
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
выборка данных из 2 связанных таблиц с несколькими sum функциями
des1roer,

вот этот блок поможет

Код: 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.
EXECUTE BLOCK
RETURNS (
  BYDATE TIMESTAMP
)
AS
  DECLARE TEMP_DATE TIMESTAMP;
BEGIN
  SELECT
    MAX(P.D + P.T)
  FROM
    PROTOKOL P
    LEFT OUTER JOIN CARGO CARGO1 ON (P.FK_CARGO = CARGO1.ID_CARGO)
  WHERE P.D + P.T > '07.10.2014 8:00:00'
    AND P.D + P.T < '07.10.2014 20:00:00'
    AND CARGO1.NAME_CARGO = 'Магнетитовая'
  INTO :TEMP_DATE;

  SELECT
    MAX(P.D + P.T)
  FROM
    PROTOKOL P
    LEFT OUTER JOIN CARGO CARGO1 ON (P.FK_CARGO = CARGO1.ID_CARGO)
  WHERE P.D + P.T > :TEMP_DATE
    AND P.D + P.T < '07.10.2014 23:00:00'
    AND CARGO1.NAME_CARGO = 'Магнет. порожняк'
  INTO :BYDATE;
  SUSPEND;
END
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.10.2014, 12:23
    #38770210
des1roer
des1roer
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
выборка данных из 2 связанных таблиц с несколькими sum функциями
Симонов Денис,
прошу заметить у меня какашка firebird 1.5.
Ругается
Can't format message 13:896 -- message system code -4.
Dynamic SQL Error.
SQL error code = -104.
Token unknown - line 1, column 9.
BLOCK.
(3,417 с)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.10.2014, 12:28
    #38770219
Симонов Денис
Симонов Денис
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
выборка данных из 2 связанных таблиц с несколькими sum функциями
тогда делай процедуру.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.10.2014, 12:45
    #38770276
des1roer
des1roer
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
выборка данных из 2 связанных таблиц с несколькими sum функциями
ну его на кол этот таймстамп
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
 SELECT
            FIRST 1 D + T            
            FROM PROTOKOL
             left outer join CARGO CARGO1 on (PROTOKOL.FK_CARGO = CARGO1.ID_CARGO)
            where 
             (    D +  T >  
           (
            SELECT
            FIRST 1 D + T  
            FROM PROTOKOL
            left outer join CARGO CARGO1 on (PROTOKOL.FK_CARGO = CARGO1.ID_CARGO)
            where 
          (D = '07.10.2014' and  T >  '8:00:00' and T <  '20:00:00' ) 
           and NAME_CARGO = 'Магнетитовая'
            order by D + T desc
             ) 
            and  D = '07.10.2014' and  T <  '23:00:00'  )       
            and NAME_CARGO = 'Магнет. порожняк'


------ QUERY PERFORMANCE ------
Prepare : 16 мс
Execute : 31 мс
Avg fetch time: 23 мс
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / выборка данных из 2 связанных таблиц с несколькими sum функциями / 25 сообщений из 44, страница 1 из 2
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 450ms
Закрыть
start [/forum/topic.php?fid=40&mobile=1&tid=1563295]:
 0ms
get settings:
 5ms
get forum list:
 14ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 161ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 44ms
get tp. blocked users:
 1ms
others: 210ms
total:  450ms
x
x
Закрыть


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