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

Имам две таблици в база данни 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;

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

Съдържаниет е скрито
Влезте за да го видите

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

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

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

Бинго!

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

Съдържаниет е скрито
Влезте за да го видите

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

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

?

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

http://clubs.dir.bg/showflat.php?Cat=8&Board=php&Number=1939001212&page=0&view=collapsed&sb=5&vc=1

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

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


Само това не използвай:
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 на 19.02.03 00:07.

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

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

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

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

Съдържаниет е скрито
Влезте за да го видите

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

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

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

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