|
Вложенный запрос или агрегаты?
#39649225
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Имеется справочник привязки электронных кошельков к клиентам
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.
CREATE TABLE cl_moneybags (
CRId bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Идентификатор кошелька',
ClientId bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Код клиента', -- FK из справочника клиентов
MoneybagId tinyint(3) UNSIGNED NOT NULL COMMENT 'Код кошелька', -- FK из справочника кошельков
Checked tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Используется?',
TotalMoneybagCountRemains decimal(10, 2) DEFAULT 0.00 COMMENT 'Остаток кол-во месячных лимитов',
LocalMoneybagCountRemains decimal(10, 2) DEFAULT 0.00 COMMENT 'Остаток кол-во дневных лимитов',
TotalMoneybagSumRemains decimal(10, 2) DEFAULT 0.00 COMMENT 'Остаток месячного лимита - сумма',
LocalMoneybagSumRemains decimal(10, 2) DEFAULT 0.00 COMMENT 'Остаток дневного лимита - сумма',
TotalMoneybagCountSpend decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT 'Потрачено в месяц кол-ва',
LocalMoneybagCountSpend decimal(10, 2) DEFAULT 0.00 COMMENT 'Потрачено в день кол-ва',
TotalMoneybagSumSpend decimal(10, 2) NOT NULL DEFAULT 0.00 COMMENT 'Потрачено в месяц суммы',
LocalMoneybagSumSpend decimal(10, 2) DEFAULT 0.00 COMMENT 'Потрачено в день суммы',
PRIMARY KEY (CRId),
INDEX ClientId (ClientId),
UNIQUE INDEX UK_ClientId_MoneybagId (ClientId, MoneybagId),
CONSTRAINT FK_cl_moneybags_ref_Moneybags FOREIGN KEY (MoneybagId)
REFERENCES ref_moneybags (MoneybagId) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT fk_client_moneybags_clients FOREIGN KEY (ClientId)
REFERENCES cl_clients (ClientId) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE = INNODB
CHARACTER SET cp1251
COLLATE cp1251_general_ci
COMMENT = 'Справочник кошельков клиентов';
(Кошельки могут быть как суммовые, так и количественные, поэтому все поля в двух экземплярах)
и таблица оплат
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
CREATE TABLE ch_item_moneybags (
CIRId bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Идентификатор',
ChekId bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Код чека',
NumPos int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '№ позиции',
MoneybagId tinyint(3) UNSIGNED DEFAULT NULL COMMENT 'Код кошелька', -- FK из справочника кошельков
IPSumCalc decimal(10, 2) DEFAULT NULL COMMENT 'Сумма оплаты',
IPCount decimal(14, 6) DEFAULT NULL COMMENT 'Количество оплаты',
PRIMARY KEY (CIRId),
INDEX FK_item_payments_rations (MoneybagId),
UNIQUE INDEX UK_ch_item_moneybags (ChekId, NumPos, MoneybagId),
CONSTRAINT FK_ch_item_moneybags FOREIGN KEY (ChekId, NumPos)
REFERENCES ch_chek_items (ChekId, NumPos) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE = INNODB
CHARACTER SET cp1251
COLLATE cp1251_general_ci
COMMENT = 'Оплаты чека по позициям';
Каждая позиция чека может быть оплачена несколькими кошельками.
После заполнения таблицы оплат требуется пробежаться по всем ее строкам в пределах выбранного чека, посчитать сумму оплаты каждым кошельком и отредактировать все поля "Остаток" и "Потрачено". Сейчас это сделано так:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
UPDATE cl_moneybags ccr
JOIN (SELECT
ip.MoneybagId,
SUM(ip.IPCount) AS MoneybagCount,
SUM(ip.IPSumCalc) AS MoneybagSum
FROM ch_item_moneybags ip
WHERE ip.ChekId = :ChekId
GROUP BY ip.MoneybagId) AS tmp2
ON tmp2.MoneybagId = ccr.MoneybagId
SET ccr.TotalMoneybagCountRemains = ccr.TotalMoneybagCountRemains - tmp2.MoneybagCount,
ccr.TotalMoneybagSumRemains = ccr.TotalMoneybagSumRemains - tmp2.MoneybagSum,
ccr.LocalMoneybagCountRemains = ccr.LocalMoneybagCountRemains - tmp2.MoneybagCount,
ccr.LocalMoneybagSumRemains = ccr.LocalMoneybagSumRemains - tmp2.MoneybagSum,
ccr.TotalMoneybagCountSpend = ccr.TotalMoneybagCountSpend + tmp2.MoneybagCount,
ccr.TotalMoneybagSumSpend = ccr.TotalMoneybagSumSpend + tmp2.MoneybagSum,
ccr.LocalMoneybagCountSpend = ccr.LocalMoneybagCountSpend + tmp2.MoneybagCount,
ccr.LocalMoneybagSumSpend = ccr.LocalMoneybagSumSpend + tmp2.MoneybagSum
WHERE ccr.ClientId = :ClientId;
Оптимизатору эта конструкция не нравится, судя по строке <derived2>.
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
==========================================================================================================================================================================================================
|id |select_type |table |type |possible_keys |key |key_len |ref |rows |Extra |
==========================================================================================================================================================================================================
|1 |PRIMARY |<derived2> |ALL |null |null |null |null |2 |Using where |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|1 |PRIMARY |ccr |eq_ref |UK_ClientId_MoneybagId,ClientId,FK_cl_moneybags_re|UK_ClientId_MoneybagId |9 |const,tmp2.MoneybagId |1 | |
| | | | |f_Moneybags | | | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|2 |DERIVED |ip |ref |UK_ch_item_moneybags |UK_ch_item_moneybags |8 |const |1 |Using where; Using temporary; Using filesort |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Есть идея реализовать все это через агрегатные функции
1.
2.
3.
4.
5.
6.
7.
UPDATE cl_moneybags cm SET
TotalMoneybagCountRemains = TotalMoneybagCountRemains -
(SELECT SUM(cim.IPCount) FROM ch_item_moneybags cim WHERE cim.ChekId = :ChekId AND cim.MoneybagId = cm.MoneybagId),
TotalMoneybagSumRemains = TotalMoneybagSumRemains -
(SELECT SUM(cim.IPSumCalc) FROM ch_item_moneybags cim WHERE cim.ChekId = :ChekId AND cim.MoneybagId = cm.MoneybagId)
-- аналогично для остальных полей
WHERE cm.ClientId = :ClientId
Но смущает тот факт, что вычисление сумм в этом случае будет выполняться 8 раз. Будет ли этот вариант оптимальнее? |
|
|
