Блог

Fiddle: Пожалуйста, посмотрите этот SQLFiddle:http://sqlfiddle.com /#!9/af05d8/1/0

Сценарий: Мне нужно обновить остатки на счетах клиентов на основе таблицы, в которой перечислены полученные задания / услуги. С каждой учетной записи клиента должна взиматься определенная цена за каждую работу / услугу, путем добавления новой строки в таблицу Главной книги с новым балансом учетной записи клиента (previous_balance — price).

Проблема: Скрипты / запросы работают нормально, когда у клиента есть только одно задание в таблице, требующее обновления баланса. Однако, когда у клиента более одной строки в таблице заданий, фактический баланс учетной записи не используется для каждого задания — вместо этого для всех заданий используется исходный баланс учетной записи.

отредактируйте, чтобы уточнить: проблема была выявлена spencer5973 в ответе # 1. SELECT выполняется для таблицы, поскольку она находится в начале инструкции. Новые строки, добавленные в книгу с помощью запроса, не учитываются при последующих выборках, что означает, что правильный текущий итог не выбирается для клиентов с более чем одной строкой в таблице заданий.

Подробнее:

tmptblLedger: текущий список остатков на счетах (записей главной книги) для всех клиентов. Каждый раз, когда изменяется баланс учетной записи клиента (оплата, покупки и т.д.), Добавляется новая строка. В каждой строке записывается начальный баланс, который совпадает с предыдущим балансом на закрытие, и новый баланс на закрытие.

отредактируйте, чтобы уточнить: баланс учетной записи клиента может быть уменьшен путем оплаты задания (как показано в скрипке и этом вопросе), или увеличен за счет платежа на счет (не показан), или иным образом изменен транзакцией, не связанной с таблицей заданий. Это не позволяет приведенному ниже ответу spencer5973 работать. Бухгалтерская книга является единственным авторитетным источником всех транзакций / корректировок остатков на счетах клиентов.

tmptblJobs: таблица отслеживания «платной выполненной работы». Каждый раз, когда Клиент выполняет какую-либо работу (задание), добавляется строка. Строка указывает, сколько заданий было выполнено, и цену за задание.

Требование довольно простое — бухгалтерская книга каждого клиента должна быть обновлена, чтобы отразить выполненные задания. В псевдокоде:

 foreach(row in tmptblJobs)
{
    get row.Customer ID;
    get Customer's current account balance (MAX LedgerID in Ledger table)
    new_balance = subtract (row.NumberOfJobs * row.PricePerJob) from current balance
    write new row with new_balance to ledger table
}
  

Запросы работают корректно, когда у клиента есть только одна строка в таблице заданий.

Проблема, по-видимому, заключается в том, что, когда у клиента более 1 строки в таблице заданий, новая строка, записанная в таблицу главной книги (новый баланс), игнорируется для последующих строк в таблице заданий. Вместо этого используется исходный баланс до выполнения запроса — например, результат кэшируется.

Я могу поверить, что мне не хватает какого-то SQL или функции, чтобы заставить это работать, но я не знаю, чего…

Структура таблицы и примеры данных:

 DROP TABLE IF EXISTS tmptblLedger;
DROP TABLE IF EXISTS tmptblJobs;

CREATE TABLE `tmptblLedger` (
  `LedgerID` int(10) UNSIGNED NOT NULL COMMENT 'Unique transaction ID',
  `Timestamp` DATETIME,
  `BalanceOpen` float NOT NULL COMMENT 'Last balance for customer before this record was created',
  `BalanceClose` float NOT NULL  COMMENT 'Balance now that record has been created = BalanceOpen   Adjustment Amount',
  `Customer_CustID` int(10) UNSIGNED NOT NULL
);
ALTER TABLE `tmptblLedger` 
    MODIFY `LedgerID` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1,
    ADD UNIQUE KEY (`LedgerID`);

/* some representative data, this example only uses Customer_CustID == 1). All ledgers start at zero, this example has a second ledger entry for CustID == 1 to put the balance to 100 */
INSERT INTO `tmptblLedger` (`Timestamp`, `BalanceOpen`, `BalanceClose`, `Customer_CustID`)  VALUES (NOW(), 0, 0, 1), (NOW(), 0, 100, 1), (NOW(), 0, 5, 2), (NOW(), 0, 7, 3);

CREATE TABLE `tmptblJobs` (
  `ScheduledTargetID` bigint(20) NOT NULL,
  `Customer_CustID` int(10) UNSIGNED NOT NULL,
  `NumberOfJobsCompleted` int UNSIGNED,
  `PricePerJob` float UNSIGNED
);
ALTER TABLE `tmptblJobs`
    MODIFY `ScheduledTargetID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1,
    ADD UNIQUE KEY(`ScheduledTargetID`);

/* insert 3 jobs for CustID 1, and an example 1 job for CustID 2 to represent real world data */
INSERT INTO `tmptblJobs` (`Customer_CustID`, `NumberOfJobsCompleted`, `PricePerJob`)    VALUES (1, 2, 5), (1, 1, 3), (1, 1, 1), (2, 1, 1);
  

Запрос:

 INSERT INTO `tmptblLedger` (`Timestamp`, `BalanceOpen`, `BalanceClose`, `Customer_CustID`)
    SELECT
        NOW(),
        derivedLedger.LedgerCurrentBalance,
        derivedLedger.LedgerCurrentBalance - ( @TotalPrice:=(tmptblJobs.NumberOfJobsCompleted * tmptblJobs.PricePerJob) ) AS NEWBALANCE,
        tmptblJobs.Customer_CustID
    FROM
        (
            /* Obtain current balance for each CustID, using newest LedgerID */
            SELECT
                derivedNewestLedgerRow.LedgerCustID,
                tmptblLedger.BalanceClose AS LedgerCurrentBalance /* current balance == closing balance of newest ledger entry */
            FROM
                tmptblLedger
            RIGHT JOIN
            (
                /* Obtain newest LedgerID for each CustID */
                SELECT
                    tmptblLedger.Customer_CustID AS LedgerCustID,
                    MAX(LedgerID) AS NewestCustLedgerID
                FROM
                    tmptblLedger
                GROUP BY tmptblLedger.Customer_CustID
            ) as derivedNewestLedgerRow ON LedgerID = NewestCustLedgerID
        ) as derivedLedger
    INNER JOIN tmptblJobs ON tmptblJobs.Customer_CustID = derivedLedger.LedgerCustID
    WHERE 1;

/* LegerID rows 5, 6 and 7 will now all start from a BalanceOpen of 100, instead of BalanceOpen 100, then 90 (for ScheduledTargetID 1), then 87 (ScheduledTargetID 2), then 86 (ID 3)... */ 
SELECT * FROM tmptblLedger;
  

Результаты:

 LedgerID    Timestamp   BalanceOpen BalanceClose    Customer_CustID
1   2019-03-06T16:03:11Z    0   0   1
2   2019-03-06T16:03:11Z    0   100 1
3   2019-03-06T16:03:11Z    0   5   2
4   2019-03-06T16:03:11Z    0   7   3
5   2019-03-06T16:03:11Z    100 90  1
6   2019-03-06T16:03:11Z    100 97  1
7   2019-03-06T16:03:11Z    100 99  1
8   2019-03-06T16:03:11Z    5   4   2
  

Строки 5, 6 и 7 неверны. BalanceClose должен быть:

 Row 5 = 90
Row 6 = 87
Row 7 = 86
  

комментарий 1: «С помощью INSERT INTO tmptblLedger SELECT ... FROM tmptblLedger ... инструкции вставляемые строки не будут прочитаны SELECT . SELECT Выполняется для таблицы, которая находится в начале инструкции. Мы можем запустить только SELECT (без INSERT). Это набор строк, которые будут вставлены.»

комментарий 2: «Одним из подходов к исправлению этого было бы объединение заданий, т.е. SUM(qty*unit_price) в одну строку для каждого клиента. Таким образом, вместо добавления трех строк (5,6,7) для custid = 1 (по одной для каждого задания), мы бы добавили единственную строку с balance_open=100 и balance_close= 86. (100-((2*5) (1*3) (1*1)) )

комментарий 3: «Если нам нужно добавить три строки (5,6,7) для custid = 1, то open_balance — это не просто баланс из последнего идентификатора книги. Нам нужно включить корректировки для предыдущих заданий. Должен быть какой-то способ однозначно упорядочить задания,… Я думаю в терминах коррелированного подзапроса, который получает СУММУ (кол-во * ед. цена) для заданий, которые предшествовали текущей строке. Мы вычитаем это из баланса из последней строки книги, чтобы получить balance_open для текущей строки.»

Невозможно заставить SELECT читать строки, которых не было, когда инструкция начала выполняться, не в контексте одной инструкции SQL. (Мы можем углубиться в обсуждение уровней изоляции транзакций и разрешения «грязных чтений» в контексте транзакции, которая открывает возможность считывания некоторых незафиксированных изменений, но это ненадежно, гарантии нет. Даже при уровне изоляции, допускающем грязные чтения, этого все равно не произойдет при выполнении INSERT ... SELECT инструкции.

Нет способа «исправить» неспособность инструкции SQL считывать строки, которые вставляются по мере его выполнения. Это не та проблема, которую нужно решать. Это ограничение на самом деле является особенностью дизайна реляционных баз данных; оно фактически решает проблемы, которые могли бы возникнуть, если бы это произошло.

В итоге, нет способа заставить этот SELECT читать вставляемые строки.

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

Мы можем сделать это с помощью соответствующего подзапроса.

В настоящее время оператор SELECT имеет вид:

 SELECT NOW()
     , d.ledgercurrentbalance
     , d.ledgercurrentbalance - (j.numberofjobscompleted * j.priceperjob) AS newbalance
     , j.customer_custid
FROM ( SELECT n.ledgercustid
            , t.balanceclose AS ledgercurrentbalance /* current balance == closing balance of newest ledger entry */
         FROM ( /* Obtain newest LedgerID for each CustID */
                SELECT tmptblLedger.Customer_CustID AS LedgerCustID
                     , MAX(LedgerID) AS NewestCustLedgerID
                  FROM tmptblLedger l
                 GROUP BY l.customer_custid
              ) n
         LEFT 
         JOIN tmptblLedger t
           ON t.ledgerid = n.newestcustledgerid
     ) d
JOIN tmptblJobs j 
  ON j.customer_custid = d.ledgercustid
  

Мы могли бы ввести коррелированный подзапрос, чтобы получить SUM(qty*price) для предыдущих заданий для клиента. Учитывая, что scheduledtargetid это уникально в таблице jobs, мы можем использовать это для упорядочивания.

Что-то вроде этого:

 SELECT NOW()                                             AS `Timestamp`
     , b.balanceclose - b.prevjobs_cost                  AS `BalanceOpen`
     , b.balanceclose - b.prevjobs_cost - b.currjob_cost AS `BalanceClose`
     , b.customer_custid                                 AS `Customer_CustID`
  FROM ( SELECT j.scheduledtargetid
              , j.customer_custid
              , d.balanceclose
              , ( j.numberofjobscompleted * j.priceperjob ) AS currjob_cost

              , IFNULL(
                  ( /* correlated subquery to get total cost of previous jobs */
                    SELECT SUM(p.numberofjobscompleted * p.priceperjob)
                      FROM `tmptblJobs` p
                     WHERE p.customer_custid   = j.customer_custid
                       AND p.scheduledtargetid < j.scheduledtargetid
                  )
                ,0) AS prevjobs_cost

           FROM ( SELECT n.ledgercustid
                       , t.balanceclose
                    FROM ( /* latest ledgerid for each custid */
                           SELECT l.customer_custid AS ledgercustid
                                , MAX(l.ledgerid) AS newestcustledgerid
                             FROM `tmptblLedger` l
                            GROUP BY l.customer_custid
                         ) n
                    LEFT
                    JOIN `tmptblLedger` t
                      ON t.ledgerid = n.newestcustledgerid
                ) d
           JOIN `tmptblJobs` j
             ON j.customer_custid = d.ledgercustid
       ) b
 ORDER
    BY b.customer_custid
     , b.scheduledtargetid
  

Некоторые проблемы не устранены:

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