Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

Форум поддержки пользователей. LibreOffice, Apache OpenOffice, OpenOffice.org

11 Август 2022, 21:35 *
Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
Вам не пришло письмо с кодом активации?

Войти
Новости: Доступно и просто о работе в офисных пакетах
 
   Начало   Помощь Поиск Войти Регистрация    задать вопрос  
Страниц: 1   Вниз
  Печать  
Автор Тема: запрос на сумму полей и записей с переменным количеством полей  (Прочитано 644 раз)
0 Пользователей и 1 Гость смотрят эту тему.
at0mix
Участник
**
Offline Offline

Сообщений: 26


« Стартовое сообщение: 21 Июль 2022, 10:53 »

с ходу на ум ничего не приходит.....
нужно сделать запросы:
- на сумму переменного количества полей в одной записи
- на сумму переменного количества полей по всем записям

суть проблемы - есть таблица - график выполнения работ по неделям - типа такого:

цех----неделя1-неделя2-неделя3......
цех1-----4----------3--------5
цех2-----3----------6--------7

и есть таблица неделя с одним полем "неделя" и одной записью - в которой записан номер недели
если номер-3 то нужно в запросе1 получить сумму полей "неделя1+неделя2+неделя3",если неделя=5 - то сумму полей с именем неделя* от 1 до 5 - по записи выбранного цеха - цех1 или цух2 или далее любой.

запрос2 - то же самое, только на выходе сумма по всем нужным полям по всем имеющимся записям (цехам)

т.е. запрос1 по записи цех1 и неделя=2 ответ будет 7, а по записи цех2 - 9.
если следующий запрос будет при неделя=3 - то ответы будут 12 и 16 соответственно.
Записан
economist
Форумчанин
***
Offline Offline

Сообщений: 1 719


« Ответ #1: 21 Июль 2022, 12:26 »

если номер-3 то нужно в запросе1 получить сумму полей "неделя1+неделя2+неделя3"

Налицо неоптимальная структура хранения информации о временных рядах. Хорошо для "посмотреть", плохо для SQL.
Это называется "переуплотнение таблицы".

Использование SQL-запросов вида на таких данных с оператором CASE
Код:
SELECT SUM(CASE WHEN ... THEN... END) FROM...
который в некоторых движках умеет динамически выводить разное число колонок в качестве аргументов в функцию SUM() - окажется в итоге сложнее, чем перебрать всю таблицу макросом.

Если же упираться с SQL... Я бы "разрядил" таблицу, превратив столбцы неделя1-неделя2... в один столбец. Да, Таблица станет длинее по высоте и "худой". Но зато по ней станет возможно писать простые условия для суммирования с WHERE + HAVING.

Для "разрядки" и решейпинга в больших СУБД, в либах дата-сатанистов Pandas - есть спецальные команды PIVOT, STACK/UNSTACK. которые легко превратят таблицу в простую. Возможно в вашей БД они тоже есть.

PS Если ваши данные и правда целочисленные с 0 до 9 - можно просто представить вашу таблицу в виде всего 2-х столбцов со строками:
"цех1" "435"
"цех2" "367"

И тогда SQL справится влет:
запрос1 с параметрами цех1 и неделя=2 найдет нужную строку с WHERE и возьмет первые 2 символа из 435 и сложит 4+3=7  
запрос3 с параметром цех2 и неделя=3 найдет нужную строку с WHERE и возьмет первые 3 символа из 367 и сложит 3+6+7=16
« Последнее редактирование: 21 Июль 2022, 12:28 от economist » Записан

Руб. за сто, что Питоньяк
Любит водку и коньяк!
Потому что мне, без оных, -
Не понять его никак...
eeigor
Опытный пользователь
***
Offline Offline

Пол: Мужской
Сообщений: 1 150


« Ответ #2: 21 Июль 2022, 14:48 »

с ходу на ум ничего не приходит.....
нужно сделать запросы
Как заметил @economist, SQL не обрабатывает массивы значений, что означает наличие повторяющихся групп данных (неделя 1, неделя 2…) – это нарушает самую первую из пяти форму нормализации данных. Ваши данные не подходят для реляционной БД. Их проще обработать в Calc. А в Base надо выполнить рекомендации по нормализации данных. И всё придёт на ум само собой. Информация по нормализации данных – книга К.Дейта.
« Последнее редактирование: 21 Июль 2022, 14:55 от eeigor » Записан

Ubuntu 18.04 LTS • LibreOffice 7.3.5.2 Community
at0mix
Участник
**
Offline Offline

Сообщений: 26


« Ответ #3: 21 Июль 2022, 16:46 »

Ваши данные не подходят для реляционной БД. Их проще обработать в Calc. А в Base надо выполнить рекомендации по нормализации данных.
Спасибо за ответ но попробую отнять еще немного веремени Подмигивающий
Дело в том что я не занимаюсь профессионально написанием программ - это просто небольшая автоматизация рабочего процесса, ну минимальный практикум на будущее.
Книга конечно весьма полезная, но освоить ее в обозримом будущем мне нереально - просто нет времени %(
Поэтому вопрос - я правильно понял что если таблицу развернуть то реализовать задачу будет проще?
т.е. сделать такую таблицу:

недели: цех1--цех2-цех3
1----------2------3-----4
2----------0------5-----1
3----------5------0-----2
но мне нужно определенное количество записей для каждого цеха которые в этом суммировании не участвуют. наверно их внести вверху таблицы, а потом список недель с данными?
Записан
eeigor
Опытный пользователь
***
Offline Offline

Пол: Мужской
Сообщений: 1 150


« Ответ #4: 21 Июль 2022, 17:35 »

Неделя Цех Значение
1 Цех1 2
1 Цех2 3
1 Цех3 4
2 Цех1 0
2 Цех2 5
2 Цех3 1
3 Цех1 5
3 Цех2 0
3 Цех3 2

Здесь все комбинации. Такой набор SQL примет. Налицо избыточность в данных. И это правильно. Ваш вариант удобен для восприятия/печати, но это одно представление данных, а мой – для хранения и обработки, и из него можно сделать любое представление данных.
« Последнее редактирование: 21 Июль 2022, 17:48 от eeigor » Записан

Ubuntu 18.04 LTS • LibreOffice 7.3.5.2 Community
eeigor
Опытный пользователь
***
Offline Offline

Пол: Мужской
Сообщений: 1 150


« Ответ #5: 23 Июль 2022, 18:27 »

А для того, чтобы собрать ответ под запрос автора, понадобится что-то вроде SQL инструкции TRANSFORM (перекрёстный запрос: развернуть вертикальный вектор по горизонтали и получить ту самую "повторяющуюся группу" или массив значений, который автор загнал в определение таблицы изначально, чем нарушил 1НФ).

Кстати, она (эта инструкция) есть здесь в LO Base (я не знаток LO Base, хотя все концепции мне знакомы)?
« Последнее редактирование: 23 Июль 2022, 18:34 от eeigor » Записан

Ubuntu 18.04 LTS • LibreOffice 7.3.5.2 Community
sokol92
Мастер
*****
Offline Offline

Пол: Мужской
Сообщений: 980


WWW
« Ответ #6: 23 Июль 2022, 19:11 »

Кстати, она (эта инструкция) есть здесь в LO Base
Насколько я знаю, инструкция TRANSFORM относится к Microsoft Access и не поддерживается основными SQL-базами.

Для создания "сводных" таблиц в SQL-базах можно использовать конструкции, указанные @economist в ответе #1:
Код:
SELECT SUM(CASE WHEN ... THEN... END)
(для каждого столбца результата одно выражение). В Oracle, например, этот метод широко применяется (вместо case...end чаще используется decode).

Если число столбцов заранее не известно, то приходится генерировать запрос динамически.

Если мы хотит отобразить данные в Calc, то можно воспользоваться механизмом сводных таблиц Calc (создавая сводные таблицы интерактивно или программным путем), что гораздо удобнее.
« Последнее редактирование: 23 Июль 2022, 19:13 от sokol92 » Записан

Владимир.
eeigor
Опытный пользователь
***
Offline Offline

Пол: Мужской
Сообщений: 1 150


« Ответ #7: 23 Июль 2022, 21:48 »

@sokol92, понятно (хотя это было бы очень удобно). Access, к тому же, позволяет сводную таблицу разместить прямо на форме, а различные представления сводной таблицы сохранять макросом в формате XML и загружать по выбору.
Но здесь тогда данные LO Base можно загрузить в сводную таблицу LO Calc. Вот только гонять данные туда-обратно как-то не рационально... А писать SQL запросы автору может показаться сложным. Вот и остается Ваш макрос "в лоб" под задачу. Но мне по нраву более или мене (на 95%) типовые реализации с широким применением в будущем.
« Последнее редактирование: 23 Июль 2022, 21:58 от eeigor » Записан

Ubuntu 18.04 LTS • LibreOffice 7.3.5.2 Community
Страниц: 1   Вверх
  Печать  
 
Перейти в:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.21 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!