ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Запрос с частичным суммированием по строкам и столбцам
16 сообщений из 16, страница 1 из 1
Запрос с частичным суммированием по строкам и столбцам
    #36152743
Koryuu
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Koryuu
Участник
Здравствуйте!
Прошу помощи в оптимизации подсчетов и запросов в таблице.

Имеется таблица
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
"tmptbl" (
  "spec"      VARCHAR( 15 ),
  "c3_1"      BIGINT,
  "c3_2"      BIGINT,
  "c4_1"      BIGINT,
  "c4_2_1"    BIGINT,
  "c4_2_2"    BIGINT,
  "c5"        BIGINT,
  "c7_1_1"    BIGINT,
  "c7_1_2_1"  BIGINT,
  "c7_1_2_2"  BIGINT,
  "c7_1_2_3"  BIGINT,
  "c7_2_1"    BIGINT,
  "c7_2_2"    BIGINT,
  "c8_2"      BIGINT,
  "c8_3"      BIGINT,
  "c8_4"      BIGINT,
  "c8_5"      BIGINT,
  "maincode"  VARCHAR( 15 ),
  "naimen"    VARCHAR( 25 )
Поле spec выполняет роль ID, т.е. уникально для каждой записи. Поле maincode - идентификатор, имеющий значения - "+" - строка имеет подчиненные строки (строка-заголовок), пусто("") - подчиненных строк нет, одно из значений spec - строка является подчиненной. Строки-заголовки должны быть заполнены суммами соответствующих полей из подчиненных строк.
В итоге мне нужно получить таблицу (или вьюшку, пока не важно. Наверное, лучше вьюшку), которая будет построена по след запросу:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT 
  "naimen", 
  (coalesce("c4_1","c4_1", 0 )+coalesce("c5",  "c5",   0 )) as "sumpol",
  (coalesce("c7_1_1","c7_1_1", 0 )+coalesce("c7_2_1","c7_2_1", 0 )) as "sumdom",
  (coalesce("c4_1","c4_1", 0 )+coalesce("c5","c5", 0 )+
  coalesce("c7_1_1","c7_1_1", 0 )+coalesce("c7_2_1","c7_2_1", 0 )-
  coalesce("c8_2","c8_2", 0 )-coalesce("c8_3","c8_3", 0 )-
  coalesce("c8_4","c8_4", 0 )-coalesce("c8_5","c8_5", 0 )) as "sumvpay",
  "spec", "maincode"
FROM *** where "maincode"='' or "maincode"='+'
order by "spec";
т.е. туда надо будет выбрать суммы/разницы из строк, которые не имеют подчиненных строк либо являются строками-заголовками (в которые суммируются подчиненные им суммы)
Пока что было вырождено два полуработающих способа решения вопроса:
1. В tmptbl вставляются как строки-заголовки, так и подчиненные. После каждой правки проводится проверка поля maincode. Если оно не пустое и не равно "+" (т.е. строка является подчиненной), то строка-заголовок удаляется, а вместо нее вставляется строка типа:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
 insert into "tmptbl"
select :InSpec,
sum(coalesce("c3_1","c3_1", 0 )),
sum(coalesce("c3_2","c3_2", 0 )) ,
sum(coalesce("c4_1","c4_1", 0 )) ,
sum(coalesce("c4_2_1","c4_2_1", 0 )) ,
sum(coalesce("c4_2_2","c4_2_2", 0 )) ,
sum(coalesce("c5","c5", 0 )) ,
sum(coalesce("c7_1_1","c7_1_1", 0 )),
sum(coalesce("c7_1_2_1","c7_1_2_1", 0 )),
sum(coalesce("c7_1_2_2","c7_1_2_2", 0 )),
sum(coalesce("c7_1_2_3","c7_1_2_3", 0 )),
sum(coalesce("c7_2_1","c7_2_1", 0 )),
sum(coalesce("c7_2_2","c7_2_2", 0 )),
sum(coalesce("c8_2","c8_2", 0 )),
sum(coalesce("c8_3","c8_3", 0 )),
sum(coalesce("c8_4","c8_4", 0 )),
sum(coalesce("c8_5","c8_5", 0 )),
'+', (select "naimen" from "spec" where "txtcode"=:InSpec)
from "tmptbl" where "maincode"=:InSpec;

InSpec - значение поля Spec, по которому суммируем.
Приведенный кусок - попытка выродить сие в триггер (пока не работает)

2. В таблице tmptbl строк-заголовков нет. Только подчиненные строки и независимые.
На основе этой таблицы строится вьюшка:
Код: plaintext
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.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
CREATE VIEW "Sumview"
(
  "spec",
  "c3_1",
  "c3_2",
  "c4_1",
  "c4_2_1",
  "c4_2_2",
  "c5",
  "c7_1_1",
  "c7_1_2_1",
  "c7_1_2_2",
  "c7_1_2_3",
  "c7_2_1",
  "c7_2_2",
  "c8_2",
  "c8_3",
  "c8_4",
  "c8_5",
  "maincode",
  "naimen"
)
AS
select "spec",
"c3_1",
  "c3_2",
  "c4_1",
  "c4_2_1",
  "c4_2_2",
  "c5",
  "c7_1_1",
  "c7_1_2_1",
  "c7_1_2_2",
  "c7_1_2_3",
  "c7_2_1",
  "c7_2_2",
  "c8_2",
  "c8_3",
  "c8_4",
  "c8_5",
  "maincode",
  "naimen" from "tmptbl" where "maincode"<>'+'
union 
select "maincode",
sum(coalesce("c3_1","c3_1", 0 )),
sum(coalesce("c3_2","c3_2", 0 )) ,
sum(coalesce("c4_1","c4_1", 0 )) ,
sum(coalesce("c4_2_1","c4_2_1", 0 )) ,
sum(coalesce("c4_2_2","c4_2_2", 0 )) ,
sum(coalesce("c5","c5", 0 )) ,
sum(coalesce("c7_1_1","c7_1_1", 0 )),
sum(coalesce("c7_1_2_1","c7_1_2_1", 0 )),
sum(coalesce("c7_1_2_2","c7_1_2_2", 0 )),
sum(coalesce("c7_1_2_3","c7_1_2_3", 0 )),
sum(coalesce("c7_2_1","c7_2_1", 0 )),
sum(coalesce("c7_2_2","c7_2_2", 0 )),
sum(coalesce("c8_2","c8_2", 0 )),
sum(coalesce("c8_3","c8_3", 0 )),
sum(coalesce("c8_4","c8_4", 0 )),
sum(coalesce("c8_5","c8_5", 0 )),
'+', (select "naimen" from "spec" where "txtcode"="tmptbl"."maincode")
from "tmptbl" where "maincode"<>'' and "maincode"<>'+'
 group by "maincode";
Но тут впираемся в проблему. Если попробовать на основе этой вьюшки сделать выборку из второго приведенного мной куска (то, что мне надо в итоге), то получаю ошибку Undefined name .... Полазила на форуме. Пока что нашла только, что с вьюшками как с таблицами работать нельзя.

Вооооть.... Пока что хотелось бы послушать (почитать) людей, которые работали с такими запросами. Или решали похожую проблему.
ПС Читала про grant . Но то ли пишу неправильно, хотя ошибку не выдает, либо у меня просто не работает.
...
Рейтинг: 0 / 0
19.08.2009, 16:25
| Ответить | Цитировать | Написать  
Запрос с частичным суммированием по строкам и столбцам
    #36152885
Гость совсем Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гость совсем Гость
Гость
Koryuu,

Читал,

1. в этой таблице хранятся и строки заголовки и подчиненные им строки ?
(иначе чего вопрос то задавать)

2. как мы узнаем какой строке заголовка "принадлежит" та или иная строка

3. и на кой такая куча кавычек

4. если в поле "maincode" могут быть только два значения + и пусто
то зачем в запросе стоит ограничение "maincode"='' or "maincode"='+'

5. Это что за чудо coalesce("c4_1","c4_1",0)
типа если "c4_1" null, если точно "c4_1" null то пущай будет 0

6. И это тоже sum(coalesce("c3_1","c3_1",0))
кому оно coalesce там в SUM нужно

но сей поток сознания не осилил :(
...
Рейтинг: 0 / 0
19.08.2009, 17:03
| Ответить | Цитировать | Написать  
Запрос с частичным суммированием по строкам и столбцам
    #36152906
Мимопроходящий
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Мимопроходящий
Участник
Hello, Гость!
You wrote on Wed, 19 Aug 09 14:03:42 GMT:

Гостьно сей поток сознания не осилил :(+1
просмотрел по диагонали.
ф топку!

--
With best regards, Мимопроходящий.

Posted via ActualForum NNTP Server 1.4
...
Рейтинг: 0 / 0
19.08.2009, 17:10
| Ответить | Цитировать | Написать  
Запрос с частичным суммированием по строкам и столбцам
    #36153224
Фотография kdv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kdv
Участник
меня еще на двойных кавычках затрясло. это ж надо себе такой гембель добровольно устроить.
вторая песня - идентификаторы в варчарах, а числа в bigint. впрочем, согласен, 2.5 миллиарда - не деньги.
ну и третье - чего-нибудь почитать про деревья, и про cte (если FB 2.1).
...
Рейтинг: 0 / 0
19.08.2009, 19:30
| Ответить | Цитировать | Написать  
Запрос с частичным суммированием по строкам и столбцам
    #36153230
Esperito
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Esperito
Участник
Нипонятнааа...
Koryuu
В итоге мне нужно получить таблицу (или вьюшку, пока не важно. Наверное, лучше вьюшку), которая будет построена по след запросу:
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
CREATE VIEW TMP_VIEW(
    "naimen",
    "sumpol",
    "sumdom",
    "sumvpay",
    "spec",
    "maincode")
AS
SELECT
    "naimen",
    (COALESCE("c4_1",  0 ) + COALESCE("c5",  0 )) AS "sumpol",
    (COALESCE("c7_1_1",  0 ) + COALESCE("c7_2_1",  0 )) AS "sumdom",
    (COALESCE("c4_1",  0 ) + COALESCE("c5",  0 ) + COALESCE("c7_1_1",  0 ) + COALESCE("c7_2_1",  0 ) - COALESCE("c8_2",  0 ) - COALESCE("c8_3",  0 ) - COALESCE("c8_4",  0 ) - COALESCE("c8_5",  0 )) AS "sumvpay",
    "spec",
    "maincode"
FROM "tmptbl"
WHERE "maincode" = '' OR "maincode" = '+'
ORDER BY "spec";
И расскажи подробнее, что там с grant не получилось.
...
Рейтинг: 0 / 0
19.08.2009, 19:41
| Ответить | Цитировать | Написать  
Запрос с частичным суммированием по строкам и столбцам
    #36153694
Koryuu
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Koryuu
Участник
Я плохо объясняю, да. Меня даже юзеры только с десятого раза понимают. Только объясняю я им по пять раз максимум =(
2 Гость
автородно из значений spec - строка является подчиненной
Т.е., предположим у нас таблица:
Spec c3_1 c3_2 ...... maincode naimen03502 ........+ Строка-заголовок03_11 2 .........03 Строка 1 подчиненная 0303_249 null ..........03 Строка 2 подчиненная 0304 nullnull ......... Независимая строка...........160 1 ........+ Еще одна строка-заголовок16_1 0 1 ..........16Строка подчиненная 1617 null 7.......... Независимая строка
В итоге мне надо (к примеру):
Naimen Sumpol sumdom sumvpay spec maincodeСтрока-заголовок 0151403+Независимая строка00004 Еще одна строка заголовок5121716+Независимая строка10112117

2 kdv
Без двойных кавычек не работает. Фиг знает почему. Из-за этого неудобно к значениям полей обращаться.
coalesce был найден на этом же форуме. Без него арифметические операции null возвращают, т.к. изначально таблица нуллами и забита.
С bigint погорячилась, признаю. Исправлю на int
А чем varchar хуже char?
2 Esperito
Если делать выборку на основе tmptbl, то придется включить в саму таблицу строки-заголовки. А поскольку они являются суммами подчиненных строк, то при каждом изменении в подчиненной строке строку-заголовок придется пересчитывать. Повторюсь, пока что самый быстрый и эээ.... оптимальный в плане кода способ - удалять строку-заголовок, затем добавлять новую строку на основе выборки, суммирующей подчиненные строки. Но кромсать таблицу, на мой взгляд, не самое лучшее решение. Пользователь будет раз по 20 вносить изменения в одну и ту же запись, а подчиненных записей там почти половина. Как-то это.... негуманно =D
Код: plaintext
GRANT SELECT ON "SumView" TO view "SvodView";
SvodView - это вьюшка, которая получается по тому же запросу, что вы привели, только делается запрос не к tmptbl, а к вьюшке SumView, которая строится на основе tmptbl (последний кусок кода в первом посте)

После пятого объяснения юзеры предпочитают сделать вид, что поняли. Пока снова чего-нибудь не сломают :-[
...
Рейтинг: 0 / 0
20.08.2009, 08:59
| Ответить | Цитировать | Написать  
Запрос с частичным суммированием по строкам и столбцам
    #36153819
Фотография kdv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kdv
Участник
авторБез двойных кавычек не работает. Фиг знает почему. Из-за этого неудобно к значениям полей обращаться.
т.е. пункт FAQ, который я привел, проигнорирован? Фиг знает, но читать не буду?

авторС bigint погорячилась, признаю. Исправлю на int
А чем varchar хуже char?
фиг с ним, с bigint. просто идентификаторы в varchar - экзотика. деревья обычно не так делают. опять же, www.ibase.ru/develop.htm, соответствующий раздел. хотя можешь не читать, и продолжать дальше.
...
Рейтинг: 0 / 0
20.08.2009, 10:01
| Ответить | Цитировать | Написать  
Запрос с частичным суммированием по строкам и столбцам
    #36154205
Koryuu
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Koryuu
Участник
When i do right, nobody remember, when i do wrong, no one forget >_<

Прочитала, но.... насколько я поняла, там говорилось про настройку в IBExpert. У меня другая прога для доступа к БД, и где там эта настройка (и есть ли она вообще) я не знаю (не нашла. Может, хорошо запрятана?). Не вижу вообще проблем с использованием двойных кавычек, если все работает нормально.

С деревьями работаю редко, поэтому как-то... боязно. А вдруг совсем сломаю? )))

ПС А при чем тут 2,5 миллиарда денюх? =^_^=
...
Рейтинг: 0 / 0
20.08.2009, 11:44
| Ответить | Цитировать | Написать  
Запрос с частичным суммированием по строкам и столбцам
    #36154444
Фотография kdv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kdv
Участник
авторПС А при чем тут 2,5 миллиарда денюх?
обычно, когда используют какие-то типы данных, предварительно смотрят, что эти типы данных могут хранить. int это знаковое целое до 2.4 миллиарда. Оно не подойдет например для умножения и деления int, содержащего эти самые миллиарды - будет переполнение.

авторнасколько я поняла, там говорилось про настройку в IBExpert.
да, да, настройку, в IBExpert и в другом ПО, в любом, которое может создавать таблицы мышекликаньем.

авторНе вижу вообще проблем с использованием двойных кавычек, если все работает нормально.
т.е. запросы вручную не пишете. ок. если пишете, то разве не напрягает, что все идентификаторы приходится обрамлять этими самыми двойными кавычками?
Или вы все-таки не поняли, что написано в том пункте FAQ? Там написано, что без двойных кавычек проще и легче.
...
Рейтинг: 0 / 0
20.08.2009, 12:58
| Ответить | Цитировать | Написать  
Запрос с частичным суммированием по строкам и столбцам
    #36154664
Esperito
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Esperito
Участник
Лучше бы разбить эту таблицу на две, чтобы в одной подчинённые строки, а в другой - суммы.
И повесить триггер на первую таблицу для автоматического пересчёта строки суммы в другой таблице.
Это всё при условии, что есть только строки и их суммы, а не иерархия.
...
Рейтинг: 0 / 0
20.08.2009, 14:01
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
Запрос с частичным суммированием по строкам и столбцам
    #39084293
chyber
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
chyber
Гость
Koryuu,
Как правильно советует, Esperito, я бы разбил одну таблицу две: на главную и подчиненную, по типу записей, но это тебе решать
Если я правильно тебя понял, тебе нужно суммировать значения в подчиненных строках только, но добавляя заголовок для группы.
Чтобы избавиться от NULL смени тип поля на NUMERIC или выполни
Код: sql
1.
update table1 set c31=0 where c31 is null


Следующий запрос генерирует результат ниже в таблице
Код: sql
1.
2.
select sum(iif((s1.maincode='+'),0,s1.c31)),max(iif(s1.maincode<>'+','',s1.naimen)),
min(s1.spec),min(s1.maincode) from tmp007 s1 group by left(s1.spec,2)



Строка-заголовок+4603Независимая строка004Еще одна строка-заголовок+016Независимая строка017
исходная таблица:
Specc31c32maincodenaimen03502+Строка-заголовок03_11203Строка 1 подчиненная 0303_245003Строка 2 подчиненная 030400Независимая строка1601+Еще одна строка-заголовок16_10116Строка подчиненная 161707Независимая строка
Не бойся экспериментировать с этим запросами, последняя документация всегда доступна на
www.ibexpert.net/ibe/index.php
...
Рейтинг: 0 / 0
23.10.2015, 09:14
| Ответить | Цитировать | Написать  
Запрос с частичным суммированием по строкам и столбцам
    #39084295
WildSery
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
WildSery
Участник
chyber,

Рассчитываешь, что за прошедшие 6 лет Koryuu до сих пор мучается с этой задачей?
...
Рейтинг: 0 / 0
23.10.2015, 09:18
| Ответить | Цитировать | Написать  
Запрос с частичным суммированием по строкам и столбцам
    #39084653
Фотография kdv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kdv
Участник
WildSery,

похоже, это спамер от ибэксперта.
...
Рейтинг: 0 / 0
23.10.2015, 13:13
| Ответить | Цитировать | Написать  
Запрос с частичным суммированием по строкам и столбцам
    #39084901
Фотография Gallemar
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Gallemar
Участник
kdvWildSery,

похоже, это спамер от ибэксперта.
От кого?
...
Рейтинг: 0 / 0
23.10.2015, 15:00
| Ответить | Цитировать | Написать  
Запрос с частичным суммированием по строкам и столбцам
    #39085023
IBExpert
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
IBExpert
Участник
kdvпохоже, это спамер от ибэксперта.

Чего???
...
Рейтинг: 0 / 0
23.10.2015, 16:08
| Ответить | Цитировать | Написать  
Запрос с частичным суммированием по строкам и столбцам
    #39085052
Фотография kdv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kdv
Участник
IBExpert,

chyberНе бойся экспериментировать с этим запросами, последняя документация всегда доступна на
спрашивается, какого фига чел с двумя сообщениями на скруле вдруг вторым сообщением указывает, какую документацию нам надо читать? :-)
В то время как документация по SQL (а не по ибэксперту) всегда тут
http://www.ibase.ru/firebird/Firebird_2_5_Language_Reference_RUS.pdf
...
Рейтинг: 0 / 0
23.10.2015, 16:19
| Ответить | Цитировать | Написать  
16 сообщений из 16, страница 1 из 1
Форумы / Firebird, InterBase [игнор отключен] [закрыт для гостей] / Запрос с частичным суммированием по строкам и столбцам
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=40&fpage=69&tid=1562562]:
 0ms
get settings:
 12ms
get forum list:
 12ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 53ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 56ms
get tp. blocked users:
 2ms
others: 255ms
total:  414ms
созд. / загр.: 414ms
x
x
Закрыть


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