Arrow
Arrow27 июля 2018 г. 9:31

Выборка данных из базы данных

SQL, PostgreSQL

Доброго времени суток!
Использую PostgreSQL.
Попробую объяснить вопрос на примере.
Есть таблица main_table с данными:
id         first_name                          name                          project                          proposal_count
1          Jon                                       Dow                          Qt                                                       1
1          Jeck                                     D                               SQL                                                    1
1          Jon                                       Dow                          Qt                                                       2
1          Jeck                                     D                               SQL                                                    1
1          Jon                                       Dow                          SQL                                                    1


Как можно получить выборку или создать новую таблицу следующего вида:

first_name                          name                         project                          all_proposal_count
Jon                                       Dow                          Qt                                                       3
Jeck                                     D                               SQL                                                    2
Jon                                       Dow                          SQL                                                   1

Рекомендуем хостинг TIMEWEB
Рекомендуем хостинг TIMEWEB
Стабильный хостинг, на котором располагается социальная сеть EVILEG. Для проектов на Django рекомендуем VDS хостинг.

Вам это нравится? Поделитесь в социальных сетях!

19
Arrow
  • 28 июля 2018 г. 4:10
  • (ред.)

Нужное получил через GROUP BY и SUM().

Только второй вопрос возник, как к полученному добавить еще три колонки (пустые), чтобы получить такое:

first_name                          name                         project                          all_proposal_count                  department                date                   state
Jon                                       Dow                          Qt                                                       3
Jeck                                     D                               SQL                                                    2
Jon                                       Dow                          SQL                                                   1


К сожалению информации по этому вопросу нигде не могу найти.
    Evgenii Legotckoi
    • 28 июля 2018 г. 4:25

    Думаю, что вам нужно либо иметь внешний ключ на таблицу с этими данными, либо иметь эти дополнительные колонки в первоначальной таблице.

    Как бы SELECT из воздуха не делает дополнительные колонки, а те, которые получаются, они делаются из существующих колонок.
    Если используете Qt для отображения, то можно иметь в самой модели данных дополнительные колонки, а потом, если требуется сохранения, то создавать необходимым запросом записи в таблице, где всё необходимое уже существует.
    Просто я сколько занимаюсь сайтом, то подобной ситуации, как у вас за полтора года у меня не было. Может быть вам стоит подумать над архитектурой этой части проекта, возможно, что-то не так спроектировали, что вам в SELECT запросе требуется добавлять дополнительные колонки. Либо я не так понял и эти колонки уже существуют у вас в какой-то иной таблице.



      Arrow
      • 28 июля 2018 г. 4:47
      • (ред.)

      Спасибо. Ввел в таблицу дополнительные колонки, получил такое:

      SELECT order_number AS order_num, equipment AS equip_name, part AS part_name, SUM(otk) AS otk_finish, SUM(hours_count) AS time,
      param, written_off, remainder, state
      FROM main_table WHERE otk = quantity GROUP BY order_number, equipment, part, param, written_off, remainder, state

      Только пока не понимаю как сохранить изменения в этих колонках
      param (int), written_off (int), remainder (int), state (bool)
      вернее куда это сохранять. Пользователь будет в
      param
      Вносить число, а остальные колонки будут автоматически вычисляться в зависимости от внесенного числа.
      Программу пишу на Qt.

        Arrow
        • 28 июля 2018 г. 4:51

        Вот как это выглядит в виде таблицы

          Evgenii Legotckoi
          • 28 июля 2018 г. 5:05

          Хреново то, что у вас там в двух колонках суммирование, то есть это по сути не является записью в базе данных... Это агрегированный результат SELECT выборки...

          Может тогда немного иначе переделать структуру базы. Например добавить таблицу с колонками
          order_num    sum_otk    sum_hours_count    param    written_off    remainder    state
          Таким образом по номеру заказа то есть по order_num сможете связать две таблицы. Можно будет делать суммирование otk и hours_count, после чего записывать их во вторую таблицу, во вторую и третью колонку.
          А в таблицу на Qt выводить уже готовый результат второй таблицы. Там будет обычный SELECT без суммирования. А сохранение можно будет делать через UPDATE запрос.
          При этом запретить пользователю в программе на Qt редактировать сумм otk и времени.







            Arrow
            • 28 июля 2018 г. 5:26

            Большое спасибо!
            Я точно не знаю насколько правильно и нужно привязывать данные к order_number, без привязки к

            equipment и part
            Возможно их также нужно будет вносить в новую таблицу.

            И такой вопрос: суммирование otk и hours_count, и запись их во вторую таблицу лучше делать в программе или в самой базе после записи данных в основную таблицу? (Возможно через триггерные функции.)
              Evgenii Legotckoi
              • 28 июля 2018 г. 5:45

              Честно, с тригерными функциями я дела не имел, но в той же документации по Django советуют делать по возможность через базу данных большую часть работы.

              Например там сказано следующее про скорость работы сайта
              1. Самый быстрый вариант обработки данных средства базы данных,
              2. Далее работа языка Python
              3. А потом уже язык шаблонов
              Так что я думаю, что если сможете реализовать на уровне БД, то лучше в ней.
              Хотя триггерные функции могут оказаться несколько более сложным вариантом, да и без документировании информации о том, как работает БД в данном конкретном случае, можно попасть впросак в том плане, что если после вас кто-то будет поддерживать разработку программы, то для него может оказаться сюрпризом перерасчёт данных.
              Также equipment и part можно вынести в другую таблицу, если можно обойтись только номером заказа, это скорее будет зависеть от того, несут ли те колонки важную информацию, по которой можно конкретизировать выборку, или они не влияют всё-таки на результат
                Arrow
                • 28 июля 2018 г. 5:52

                Спасибо! Попробую подумать над реализацией без триггеров. возможно есть другие варианты.

                  Arrow
                  • 29 июля 2018 г. 10:34
                  • (ред.)

                  Подумав решил сделать вторую таблицу account_table (возможно не правильно) с  полями
                  order_numb    equip_name    part_name    otk_finish    time    param    written_off    remainder    state
                  и перед загрузкой данных из этой таблицы в программе буду выполнять такой запрос:

                  INSERT INTO account_table (order_numb, equip_name, part_name, otk_finish, time)
                  SELECT order_number, equipment, part, SUM(otk), SUM(hours_count)
                  FROM main_table GROUP BY order_number, equipment, part;
                  то есть заполнять таблицу выборкой данных из main_table, и давать ее (account_table) для работы пользователю.
                  Только возник вопрос как можно предотвратить повторное копирование данных?
                  Я имею в виду что если в account_table уже есть строка с данными:
                  order_numb      equip_name      part_name       otk_finish            time
                  1                                        1                            1                            1                 4:35:00
                  то ее повторно не копировать.
                    Arrow
                    • 30 июля 2018 г. 3:57
                    Выдумал такое:
                    INSERT INTO account_table (order_numb, equip_name, part_name, otk_finish, time_sum)
                    SELECT order_number, equipment, part, SUM(otk), SUM(hours_count)
                    FROM main_table GROUP BY order_number, equipment, part
                    HAVING NOT EXISTS(SELECT order_numb, equip_name, part FROM account_table WHERE order_numb=order_number AND equip_name=equipment AND part_name=part);
                    Работает. Осталось только решить, что лучше: выполнять это в самой программе перед отображением account_table или после внесения данных в main_table выполнять триггерную функцю.
                    Что будет более правильным и не даст неожиданных "сюрпризов" в будущем?
                      Evgenii Legotckoi
                      • 30 июля 2018 г. 4:10
                      я бы делал создание записи в таблице account_table с появлением первой записи в main_table о новом order_number, equipment, part.
                      а потом бы делал после обновления данных otk и hours_count обновление записи в account_table. Причём здесь будет достаточно просто обновить именно эти колонки, прибавив к ним данные из новой записи. Это было бы менее накладно, чем каждый раз суммировать данные из всей таблицы.
                        Arrow
                        • 30 июля 2018 г. 4:40
                        • (ред.)
                        То есть  для записи (триггерная функция с запросом):
                        CREATE TRIGGER insert_Data
                        AFTER INSERT ON main_table
                        FOR ROW EXECUTE PROCEDURE insertData();
                        
                        Содержимое:
                        
                        INSERT INTO account_table (order_numb, equip_name, part_name, otk_finish, time_sum)
                        SELECT order_number, equipment, part, otk, hours_count
                        FROM main_table
                        WHERE NOT EXISTS(SELECT order_numb, equip_name, part FROM account_table WHERE order_numb=order_number AND equip_name=equipment AND part_name=part);

                        Вроде так (как запихнуть SQL запрос в функцию точно не помню).

                        А обновление:
                        CREATE TRIGGER update_Data
                        AFTER UPDATE ON main_table
                        FOR ROW EXECUTE PROCEDURE updateData();
                        UPDATE account_table
                        SET otk_finish, time_sum VALUE (..., ...)

                        А с запросом туго, пока не совсем понимаю, если можно пример?
                          Evgenii Legotckoi
                          • 30 июля 2018 г. 8:22

                          Для самого хранимые процедуры лес густой, то вот в этой статье удалось добавить рабочую хранимую процедуру , в самом конце, где перемещение элемента. Возможно, это то, что вам нужно.

                          Не совсем понял, насчёт запроса. Какого именно, чтобы вытянуть что-то конкретное? или этот UPDATE?
                            Arrow
                            • 30 июля 2018 г. 8:34
                            • Ответ был помечен как решение.
                            Спасибо огромное за помощь!
                            Подумал, что пользователь может еще захотеть и удалить данные и решил, что одними UPDATE и INSERT не обойтись и добавил реакцию на DELETE. Работает все корректно.
                            Если пригодится, вот рабочий код PostgreSQL:
                            CREATE OR REPLACE FUNCTION process_select_to_account()
                            RETURNS TRIGGER AS
                            $select_to$
                            BEGIN
                            IF (TG_OP = 'DELETE') THEN
                            DELETE FROM account_table WHERE order_numb = OLD.order_number AND part_name = OLD.part;
                            IF NOT FOUND THEN RETURN NULL; END IF;
                            RETURN OLD;
                            ELSEIF (TG_OP = 'INSERT') THEN
                            INSERT INTO account_table (order_numb, equip_name, part_name, otk_finish, time_sum)
                            VALUES (NEW.order_number, NEW.equipment, NEW.part, NEW.otk, NEW.hours_count);
                            RETURN NEW;
                            ELSIF (TG_OP = 'UPDATE') THEN
                            UPDATE account_table SET order_numb = NEW.order_number, equip_name = NEW.equipment, part_name = NEW.part,
                            otk_finish = NEW.otk, time_sum = NEW.hours_count
                            WHERE order_numb = OLD.order_number AND equip_name = OLD.equipment AND part_name = OLD.part AND
                            otk_finish = OLD.otk AND time_sum = OLD.hours_count;
                            RETURN NEW;
                            END IF;
                            RETURN NULL;
                            END;
                            $select_to$ LANGUAGE plpgsql;

                            CREATE TRIGGER select_to_acoount_table
                            BEFORE INSERT OR UPDATE OR DELETE ON main_table
                            FOR ROW EXECUTE PROCEDURE process_select_to_account();
                              Evgenii Legotckoi
                              • 30 июля 2018 г. 8:37

                              Круто, круто... поздравляю

                              Вы не хотели бы поподробнее описать данное решение в виде статьи для раздела о PostgreSQL?
                                Arrow
                                • 30 июля 2018 г. 8:43
                                Крутого, здесь мало, бывает и хуже (к сожалению видел).
                                Статью могу подготовить с полным описанием поставленной задачи и ее решением, но только в соавторстве с Вами и под Вашей редакцией (опыта в написании статей нет).
                                Вопрос только куда текст скидывать, когда будет готов (думаю за завтра справлюсь)?
                                  Arrow
                                  • 30 июля 2018 г. 9:15
                                  • (ред.)

                                  Нашел раздел на сайте, выложу туда.

                                    Evgenii Legotckoi
                                    • 30 июля 2018 г. 9:18
                                    • (ред.)

                                    Как выложите, напишите мне в личку. Я посмотрю, что там, проведу редакцию и опубликую, если не будет дополнительных вопросов или дополнений.

                                    Спасибо

                                      Arrow
                                      • 30 июля 2018 г. 9:29

                                      Хорошо.

                                        Комментарии

                                        Только авторизованные пользователи могут публиковать комментарии.
                                        Пожалуйста, авторизуйтесь или зарегистрируйтесь
                                        AD

                                        C++ - Тест 004. Указатели, Массивы и Циклы

                                        • Результат:50баллов,
                                        • Очки рейтинга-4
                                        m
                                        • molni99
                                        • 26 октября 2024 г. 1:37

                                        C++ - Тест 004. Указатели, Массивы и Циклы

                                        • Результат:80баллов,
                                        • Очки рейтинга4
                                        m
                                        • molni99
                                        • 26 октября 2024 г. 1:29

                                        C++ - Тест 004. Указатели, Массивы и Циклы

                                        • Результат:20баллов,
                                        • Очки рейтинга-10
                                        Последние комментарии
                                        ИМ
                                        Игорь Максимов22 ноября 2024 г. 11:51
                                        Django - Урок 017. Кастомизированная страница авторизации на Django Добрый вечер Евгений! Я сделал себе авторизацию аналогичную вашей, все работает, кроме возврата к предидущей странице. Редеректит всегда на главную, хотя в логах сервера вижу запросы на правильн…
                                        Evgenii Legotckoi
                                        Evgenii Legotckoi31 октября 2024 г. 14:37
                                        Django - Урок 064. Как написать расширение для Python Markdown Добрый день. Да, можно. Либо через такие же плагины, либо с постобработкой через python библиотеку Beautiful Soup
                                        A
                                        ALO1ZE19 октября 2024 г. 8:19
                                        Читалка fb3-файлов на Qt Creator Подскажите как это запустить? Я не шарю в программировании и кодинге. Скачал и установаил Qt, но куча ошибок выдается и не запустить. А очень надо fb3 переконвертировать в html
                                        ИМ
                                        Игорь Максимов5 октября 2024 г. 7:51
                                        Django - Урок 064. Как написать расширение для Python Markdown Приветствую Евгений! У меня вопрос. Можно ли вставлять свои классы в разметку редактора markdown? Допустим имея стандартную разметку: <ul> <li></li> <li></l…
                                        d
                                        dblas55 июля 2024 г. 11:02
                                        QML - Урок 016. База данных SQLite и работа с ней в QML Qt Здравствуйте, возникает такая проблема (я новичок): ApplicationWindow неизвестный элемент. (М300) для TextField и Button аналогично. Могу предположить, что из-за более новой верси…
                                        Сейчас обсуждают на форуме
                                        m
                                        moogo22 ноября 2024 г. 7:17
                                        Mosquito Spray System Effective Mosquito Systems for Backyard | Eco-Friendly Misting Control Device & Repellent Spray - Moogo ; Upgrade your backyard with our mosquito-repellent device! Our misters conce…
                                        Evgenii Legotckoi
                                        Evgenii Legotckoi24 июня 2024 г. 15:11
                                        добавить qlineseries в функции Я тут. Работы оень много. Отправил его в бан.
                                        t
                                        tonypeachey115 ноября 2024 г. 6:04
                                        google domain [url=https://google.com/]domain[/url] domain [http://www.example.com link title]
                                        NSProject
                                        NSProject4 июня 2022 г. 3:49
                                        Всё ещё разбираюсь с кешем. В следствии прочтения данной статьи. Я принял для себя решение сделать кеширование свойств менеджера модели LikeDislike. И так как установка evileg_core для меня не была возможна, ибо он писался…

                                        Следите за нами в социальных сетях