Събиране на елементите на две таблици в PostgreSQL

Здравейте. Наложи ми се да реша следната задача:

Имам две таблици в база данни PostreSQL с колони name (text) и value (int). Идентифицирането на редовете става по полето "име". Всяка таблица може да съдържа само един ред с определено "име". Всяка таблица може да съдържа ред, с име, еднакво на ред от дргата таблица. Задачата е, двете таблици да се съберат така, че събирайки първата с втората (условно приемам едната за първа, а другата за втора), във втората таблица да получим всички редове с неповтарящо се име, а редовете с повтарящо се име да бъдат събрани (да се събере полето value).

Пример:

Първа таблица:

name: value:
Pesho 3
Kiko 4
Ivan 8

Втора таблица:

name: value:
Stefan 1
Ivan 2
Kiko 3

След събирането във втората таблица трябва да се получи:

name: value:
Pesho 3
Kiko 7
Ivan 10
Stefan 1

Задачата е малко странна. Едната таблица периодично се създава от специфичен софтуер (не мога да променя нищо в структурата и), а другата таблица служи за да "събира" първата. Въпросното "събиране" се прави периодично.

Аз написах функция на PHP която изпълнява задачата, но огромен проблем е скоростта. Тъй като нямам опит нито в писането на PHP, нито в бази данни, аз написах функция със следния алгоритъм, предаден накратко: запитваме за всички елементи от първата таблица; цикъл по всички редове на първата таблица: запитваме за ред със същото име във втората таблица , ако има - събираме редовете (полетата value) и ги ъпдейтваме във втората таблица, ако няма само добавяме реда към втората таблица.

Това работи, но бавно - за всеки ред от първата таблица (може да е доста голяма) се правят по две запитвания към базата данни, което, стува ми се, е причината за бавната работа.

Ще съм много благодарен, ако някой с опит в PostgreSQL ми даде напътствия за оптимизиране и още по-благодарен, ако ми предложи решение.

Благодаря предварително.

SELECT name, SUM(value) FROM t1 GROUP BY name;

това трябва да замине във втарата таблица така ли?

salle.online.bg/sig

Ами не знам... ти ще кажеш. Виж примера - там съм показал какво трябва да се получи. Резултатът трябва да се запише във втората таблица, да. Целта е втората таблица да остава една и съща за продължително време, а първата таблица да се подменя периодично (след това подменяне се прави "сумирането", като резултатът остава във втората таблица). По този начин във втората таблица се натрупват суми на редовете появили се в първата таблица.

Благодаря за отделеното време.

В това, което си написал не виждам да се споменава за съществуването на втора таблица. Какво ще сумира то?

Бинго!

Та за какво ти е всъщност втората таблица?

salle.online.bg/sig

Защото софтуерът, който създава първата таблица, не добавя редове, а създава таблицата наново. Изтрива всички редове и създава нови, без оглед на това, дали имената се повтарят, или не. Ако това, което си написал ще събере value на редовете с еднакво name в една таблица, тогава би ли могло предварително да направя:

CREATE TEMP TABLE teble3 AS SELECT * FROM table1, table2;

?

Виж какво е предложил phpGuru от PHP форума:

[линк]

Кое е по-добрият вариант?

Добре. Това беше полезно уточнение.


Само това не използвай:
CREATE TEMP TABLE teble3 AS SELECT * FROM table1, table2;

Това е т.нар. Декартово Произведение т.е. Всеки ред от t1 комбиниран със всеки от t2 ....

Значи имам следното предложение. Не знам какъв е точния синтаксис в PostgreSQL - ще ти кажа как бих го направил в MySQL пък някой ще ти помогне с "превода"


CREATE TEMPORARY TABLE temp SELECT * FROM t1 UNION ALL SELECT * FROM t2;

DELETE FROM t2;

INSERT INTO t2 SELECT name, SUM(value) FROM TEMP GROUP BY name;

DROP TABLE TEMP;

т.е. Правиш Нова Временна таблица в която вмъкваш всички редове от t1 и всички от t2 (последователно - UNION ALL прави точно това) след това сумираш по име (GROUP BY name) и пращаш в t2

Ако в t2 имаш PRIMARY KEY(name) и ако това беше MySQL можеш да спестиш малко писане със:


CREATE TEMPORARY TABLE temp SELECT * FROM t1 UNION ALL SELECT * FROM t2;

REPLACE INTO t2 SELECT name, SUM(value) FROM TEMP GROUP BY name;

DROP TABLE TEMP;


salle.online.bg/sig

... просто прави:

INSERT INTO t2 SELECT * FROM t1; # предполагайки, че в t2 не е дефиниран Primary key

И когато ти затрябва сумата ...

SELECT name, SUM(value) FROM t2 GROUP BY name;

salle.online.bg/sig

Ще пробвам с различните вариант и което работи най-добре то ще остане. Пак ти благодаря.

тези варианти са горе-долу това, което и аз ти предложих, но все пак с FULL JOIN най-вероятно ще е по-бързо отколкото с UNION ALL защото по-малко данни се трансферират и после няма да имаш GROUP BY, което за да работи ок, ти трябват индекси на временната таблица (може да си ги създадеш обаче, ако решиш така да го правиш)

за да ползваш REPLACE обаче, ще трябва да си създадеш тригер BEFORE INSERT в, който да изтриваш евентуланият дублиращ запис (искам да кажа в pgsql-а няма REPLACE), което ще е може би по-добре от DELETE FROM t2 и INSERT от нула, както аз го бях написал, ако данните в t2 са адски много

но, кое ще е най бързо - пробвай!

Сбъркал съм. Готовият софтуер не трие таблицата, а само добавя нови редове. Това, че досега триеше изцяло таблицата се дължеше на грешка в конфигурирането. Друго, което съм сбъркал - таблицата има още една колона.

Понеже вероятно за вас стана малко объркано, ето какво е истинското положение:

единствената задължителна таблица е първата - тя има колони name (text), value(int) и time(bigint). Primary key са name, time. Когато готовият софтуер бъде стартиран, той добавя нови редове към тази таблица. Тези нови редове обикновено са с "name", което вече го имаме, и ново value.
Ето така:

name: value: time:
pesho 5 123456789
ivan 6 123456789

след повторното пускане на програмата:

name: vakue: time:
pesho 5 123456789
ivan 6 123456789
dragan 6 234567890
pesho 3 234567890

Моята задача е да показвам в PHP сумата на полетата value на редовете с еднакви имена.

Мисля, че има два генерални варианта, които бяха споменати. Първият вариант е, таблицата да си остане една, а данните да се събират чак тогава, когато потрябват (чак когато поискаме от PHP да ги покаже). Вторият вариант е да създам втора таблица, където тези суми просто да се натрупват, а първата таблица да я трия, както беше досега. Как мислите, кой вариант е по-добър?

Засега съм направил така:
едната таблица (където готовия софтуер добавя редове) е t1;
втората таблица (където се събират сумите) е t2;
след като готовия софтуер запише данните в базата се прави следното:

CREATE TABLE tt AS SELECT COALESCE(t1.name, t2.name), COALESCE(t1.value, 0) + COALESCE(t2.value, 0) FROM t1 FULL JOIN t2 ON t1.name = t2.name;
DELETE FROM t2;
DELETE FROM t1;
INSERT INTO t2 SELECT * FROM tt;
DROP TABLE tt;

Как мислите, така ли да остане?

Извинявам се, че първоначално ви подведох и пак ви благодаря за помощта.

ами трябва да си изясниш дали искаш да пазиш все пак архив на данните (по-детайлно да се помни всеки запис)

иначе така ще стане, но е хубаво да го правиш в транзакция!!
begin;
....
commit;
или във функция
това може да се сложи и в тригер after insert, но лошото е че postgre-то за сега подържа само row тригери, а не statement тригери и ще е доста бавано


има и друг вариант, да направиш RULE, което при INSERT INTO t1 да прави още нещо - а именно да прави сметките и директно да ги слага в t2 (така може да постигнеш и да имаш архив, ако рула е без INSTEAD или без архив - ако му сложиш INSTEAD) и в двата случая ще трябва да извикваш функция, която да намира реда в другата таблица и да му увеличава стойността, а ако го няма да го добавя - сега нямам време да напиша таква функциика обаче )

Ако оригиналната ти таблица не се трие а само се добавя тогава с пълна сила важи въпросът ми от [линк]

salle.online.bg/sig

незнам дали чак толкова ще се натоварва, обаче

SELECT * FROM t2 ще е значително по-бързо отколкото SELECT name, sum(value) FROM t2 GROUP BY name

а като го прави така в t2 ще му е изнесена директно статистиката

ето примерно решени за директно генериране на t2

CREATE OR REPLACE FUNCTION fake_insert(text, integer) RETURNS bool
AS '
declare
rows integer;
t1_name alias for $1;
t1_value alias for $2;
begin
UPDATE t2 SET value = value + t1_value WHERE name = t1_name;
GET DIAGNOSTICS rows = ROW_COUNT;
if rows = 0 then
INSERT INTO t2 (name, value) VALUES (t1_name, t1_value);
end if;
return true;
end;
'
LANGUAGE 'plpgsql' STRICT;

и сега два варианта

1. данните си остават и в t1

CREATE RULE insert_on_t1 AS ON INSERT TO t1 DO SELECT fake_insert(NEW.name, NEW.value);

2. данните не остават в t1 (всъщност, даже и не влизат там - таблицата става фиктивна)

CREATE RULE insert_on_t1 AS ON INSERT TO t1 DO INSTEAD SELECT fake_insert(NEW.name, NEW.value);


има още едно две неща по функцията могат да се оптимизират, ама ... (мързи ме да търся как стават, а и ти да погледнеж нещо ;-))
(за да я пуснеш таз функция - трябва в базата да си пуснал plpgsql езика, за което виж ТУК)

Много ти благодаря за всичко. Ефекта вече е повече от впечатляващ. От тук нататък ще се оправям сам. Благодаря също и на salle.

Засега ще родължа да си пускам статистиката през PHP, пък като прочета за функциите ще се опитам да го донаправя.

Последно остава така, нали (през PHP):

$query='
BEGIN;
CREATE TABLE tt AS SELECT COALESCE(t1.name, t2.name), COALESCE(t1.value, 0) + COALESCE(t2.value, 0) FROM t1 FULL JOIN t2 ON t1.name = t2.name;
DELETE FROM t2;
DELETE FROM t1;
INSERT INTO t2 SELECT * FROM tt;
DROP TABLE tt;
COMMIT;';

pg_exec($query);

Значи ето ти даваш един възможен отговор

Номера е да си зададеш въпроса първо и после да потърсиш възможните отговори, после да прецениш кой от отговорите сасви предимства и недостатъци има и чак тогава да си харесаш някой от тях

phpGuru знам, че ти си наясно със всичко това. Ама аз не питах теб а Petrov нали?

salle.online.bg/sig

ако ползваш по-нова версия на php, което е препоръчително - ползвай pg_query, а не pg_exec (това ще изчезне скоро от php-то)

верно е, че немам много обучателен подход, ама съмнявам се, че ще и придобия , предполагам и затова не съм станал асистент и доцент и т.н. (въпреки, че и те не всички имат такъв подход) - така, че нека да избълвам квото мога - белким некой го разбере

а и варианта с рула и функцията ми се струва все по-елегантен май ;-))
особено ако сложиш UNIQUE индекс по полето name на таблицата t2