[sq] Aggregation van string icm group by

[Verwijderd]

Hoewel ik eigenlijk tegen stored procedures ben, heb ik nu 1 situatie waar ik wel heel graag eentje wil gebruiken: voor een user-defined aggregate function. Ik zit een beetje te twijfelen, want ik heb er juist moeite voor gedaan om alle queries die ik heb zo clean mogelijk te houden. _{(wat geavanceerdere dingen doe ik dmv een eigen libje voor het dynamisch opbouwen van queries, waar ik dan via plug-ins client-side processing code aanhang, maar de uiteindelijk gegenereerde SQL die naar de DB gaat is zo puur mogelijk)}
Waar het om gaat is een aggregate die waardes in een array stopt. Niks meer en niks minder.

Voor postgres zou het om de volgende functie gaan:

code:

CREATE AGGREGATE array_accum (
sfunc = array_append,
basetype = anyelement,
stype = anyarray,
initcond = '{}'
);

Zie: http://www.issociate.de/b...nction_with_Argument.html

Ik kan daar bv een query mee doen als:

SQL:

select a, array_accum(b) AS b
from t1
group by a

Gegeven de tabel:

code:

t1
a  |  b
----------
1  |  x
1  |  y
1  |  z
2  |  q
2  |  w

Zou het resultaat zijn:

code:

a   |   b
-----------
1   |  x, y, z
2   |  q,w

Waarbij de comma's in kolom b even voor het printen erbij gezet zijn, maar het dus eigenlijk om een SQL Array gaat.

Een andere mogelijkheid die ik zag, maar die veel langzamer is, is de volgende:

code:

select a,ARRAY(SELECT b FROM t1 AS t2 WHERE  t1.a = t2.a ) FROM t1;

Dit is een scalar subquery in combinatie met een array. De uitkomst is goed, de snelheid niet.

Wat vinden jullie van deze aanpak?


_{ps ik poste deze message ook al in de thread "Zeg nee tegen stored procedures", maar bij nader inzien is het toch meer een topic opzich. De post in de andere thread mag dan ook getrashed worden.}

[Verwijderd]

Niemand die hier wat over te zeggen heeft?

[curry684]

Gezien het feit dat custom aggregates voor zover ik zo snel kan zien PostgreSQL-only zijn zal het niet storm lopen nee, de meeste mensen hier zitten vnl. in de MySQL, MSSQL or hier en daar wat Oracle hoeken

[ Voor 2% gewijzigd door curry684 op 11-04-2005 11:37 . Reden: custom erbij, ja het was vroeg ]

[Verwijderd]

Nja, aggregates zijn standaard SQL (sum, count, average, min, max), maar het gaat hier natuurlijk om custom defined aggregates.

Mijn MSSQL en Oracale kennis is vrij beperkt, maar die voor die DBs kun je geen custom aggregate definen?

[curry684]

Verwijderd schreef op maandag 11 april 2005 @ 11:24:
Nja, aggregates zijn standaard SQL (sum, count, average, min, max), maar het gaat hier natuurlijk om custom defined aggregates.

Mijn MSSQL en Oracale kennis is vrij beperkt, maar die voor die DBs kun je geen custom aggregate definen?

In MSSQL not that I know, de CREATE AGGREGATE statement bestaat iig niet en ik zie bij CREATE FUNCTION ook geen mogelijkheden om iets dergelijks aan te maken.

[P_de_B]

SQL Server kan het niet. SQL Server ondersteunt ook geen array's dus dat maakt het al weer wat lastiger. Het enige dat in de buurt komt is een user defined functie die een table teruggeeft, maar die kun je volgens mij weer niet zo aanroepen.

Custom Aggregates zijn dus niet mogelijk, maar misschien is wat jij wilt ook wel anders mogelijk. In SQL Server zou het mogelijk zijn met een een UDF, maar die geeft dan bijv. een csv string terug, niet echt een array datatype.

Zou je het niet beter in de client code kunnen doen?

[4VAlien]

je kan de stored procedure ontwijken door ipv de aggegrate een ORDER BY te doen op kolom a en dan kan je in je code met het query resultaat de aggegrate uitvoeren. Of het echt handig is weet ik niet, maar je vermijdt de stored procedure.

[curry684]

P_de_B schreef op maandag 11 april 2005 @ 11:40:
SQL Server kan het niet. SQL Server ondersteunt ook geen array's dus dat maakt het al weer wat lastiger. Het enige dat in de buurt komt is een user defined functie die een table teruggeeft, maar die kun je volgens mij weer niet zo aanroepen.

Yukon kan het wel:

quote: http://msdn.microsoft.com.../YukonBasics/default.aspx

Developers can now write stored procedures, triggers, and user-defined functions (UDFs) with any Microsoft .NET-compliant language, including Visual Basic® .NET and C#. In addition, three new objects—user-defined types (UDTs), aggregates, and functions—can be created in managed code.

[...]

The ability to create additional aggregate functions beyond those already in the box was not available to developers using previous releases of SQL Server. With Yukon, developers can create custom aggregate functions in managed code and make these functions accessible to T-SQL or other managed code. The user-defined aggregate is also a .NET class that expects to reference an assembly already available in the database.

[Verwijderd]

curry684 schreef op maandag 11 april 2005 @ 11:58:
[...]

Yukon kan het wel:

[...]

Goed dat Yukon het iniedergeval wel kan. Ik neem aan dat die dan ook arrays ondersteund. Arrays zijn overigens wel een min of meer officieel sql datatype (SQL99).

Waarom ik het niet clientside doe? Opzich kan ik dat doen, ik ben namelijk bezig met een universele query manager library, waarmee je (plug-in) operaties kan opgeven die op de table werken. Het is echter wel redelijk rommelig om de "group by" in code te gaan nabouwen. Vergeet niet dat deze universeel moet zijn, en dus bijvoorbeeld ook moet kunnen werken op een tabel waarin je een "group by" op 10 kolommen wilt zetten van een tabel die een vrij groot aantal rijen heeft.

Elk DBMS bevat een super geoptimaliseerde implementatie van "group by", ik ben er nog helemaal over uit of ik die echt wel wil gaan nabouwen. Mocht echter blijken dat er toch wel veel mensen tegen de Custom aggregate SP zijn, of dat het gewoon niet beschikbaar is onder Oracle of Db2, etc dan zal ik toch voor de client side moeten gaan (zoals eigenlijk al geplanned).

De oplossing met de custom aggregate is echter zo simpel en IMHO toch wel ellegant, dat ik door het zien daarvan toch ben gaan twijfelen.

Als ik zo wat google bestaat het ook onder Oracle:

http://www.stanford.edu/d...6595/dci11agg.htm#1004572

Oracle provides a number of pre-defined aggregate functions such as MAX, MIN, SUM for performing operations on a set of records. These pre-defined aggregate functions can be used only with scalar data. However, you can create your own custom implementations of these functions, or define entirely new aggregate functions, to use with complex data--for example, with multimedia data stored using object types, opaque types, and LOBs.

User-defined aggregate functions are used in SQL DML statements just like Oracle's own built-in aggregates.

Alleen voor Db2 kan ik het niet zo snel vinden.