Mostra una relazione uno a molti come 2 colonne – 1 riga unica (ID e elenco separato da virgola)

Ho bisogno di qualcosa di simile a queste 2 domande SO, ma usando syntax SQL Informix.

I miei dati in arrivo sono di questo tipo:

id codes 63592 PELL 58640 SUBL 58640 USBL 73571 PELL 73571 USBL 73571 SUBL 

Voglio vederlo tornare così:

 id codes 63592 PELL 58640 SUBL, USBL 73571 PELL, USBL, SUBL 

Vedi anche group_concat () in Informix .

Credo che la risposta di cui hai bisogno sia un aggregato definito dall’utente, simile a questo:

 CREATE FUNCTION gc_init(dummy VARCHAR(255)) RETURNING LVARCHAR; RETURN ''; END FUNCTION; CREATE FUNCTION gc_iter(result LVARCHAR, value VARCHAR(255)) RETURNING LVARCHAR; IF result = '' THEN RETURN TRIM(value); ELSE RETURN result || ',' || TRIM(value); END IF; END FUNCTION; CREATE FUNCTION gc_comb(partial1 LVARCHAR, partial2 LVARCHAR) RETURNING LVARCHAR; IF partial1 IS NULL OR partial1 = '' THEN RETURN partial2; ELIF partial2 IS NULL OR partial2 = '' THEN RETURN partial1; ELSE RETURN partial1 || ',' || partial2; END IF; END FUNCTION; CREATE FUNCTION gc_fini(final LVARCHAR) RETURNING LVARCHAR; RETURN final; END FUNCTION; CREATE AGGREGATE group_concat WITH (INIT = gc_init, ITER = gc_iter, COMBINE = gc_comb, FINAL = gc_fini); 

Data una tabella di elementi (chiamati elementi) con una colonna denominata nome contenente (in modo abbastanza divertente) il nome dell’elemento e un’altra colonna denominata numero atomico, questa query produce questo risultato:

 SELECT group_concat(name) FROM elements WHERE atomic_number < 10; Hydrogen,Helium,Lithium,Beryllium,Boron,Carbon,Nitrogen,Oxygen,Fluorine 

Applicato alla domanda, dovresti ottenere la risposta di cui hai bisogno da:

 SELECT id, group_concat(codes) FROM anonymous_table GROUP BY id; 

 CREATE TEMP TABLE anonymous_table ( id INTEGER NOT NULL, codes CHAR(4) NOT NULL, PRIMARY KEY (id, codes) ); INSERT INTO anonymous_table VALUES(63592, 'PELL'); INSERT INTO anonymous_table VALUES(58640, 'SUBL'); INSERT INTO anonymous_table VALUES(58640, 'USBL'); INSERT INTO anonymous_table VALUES(73571, 'PELL'); INSERT INTO anonymous_table VALUES(73571, 'USBL'); INSERT INTO anonymous_table VALUES(73571, 'SUBL'); INSERT INTO anonymous_table VALUES(73572, 'USBL'); INSERT INTO anonymous_table VALUES(73572, 'PELL'); INSERT INTO anonymous_table VALUES(73572, 'SUBL'); SELECT id, group_concat(codes) FROM anonymous_table GROUP BY id ORDER BY id; 

L'output da quello è:

 58640 SUBL,USBL 63592 PELL 73571 PELL,SUBL,USBL 73572 PELL,SUBL,USBL 

La serie aggiuntiva di dati è stata aggiunta per verificare se la sequenza di inserimento influiva sul risultato; sembra non farlo (i codici sono in ordine, non sono sicuro se c'è un modo per alterare - invertire - quell'ordine).


Gli appunti:

  1. Questo aggregato dovrebbe essere utilizzabile per qualsiasi tipo che può essere convertito in VARCHAR (255), il che significa qualsiasi tipo numerico o temporale. Non vengono gestite lunghe colonne CHAR e tipi di BLOB (BYTE, TEXT, BLOB, CLOB).
  2. Il semplice LVARCHAR limita la dimensione aggregata a 2048 byte. Se pensi di aver bisogno di lunghezze maggiori, specifica LVARCHAR(10240) (per 10 KiB), ad esempio.
  3. A partire da Informix 12.10.FC5, la lunghezza massima che funziona sembra essere 16380; sembra che qualcosa di più inneschi SQL -528: Maximum output rowsize (32767) exceeded , il che mi sorprende.
  4. Se è necessario rimuovere l'aggregato, è ansible utilizzare:

     DROP AGGREGATE IF EXISTS group_concat; DROP FUNCTION IF EXISTS gc_fini; DROP FUNCTION IF EXISTS gc_init; DROP FUNCTION IF EXISTS gc_iter; DROP FUNCTION IF EXISTS gc_comb; 

Non sono sicuro di informix sql, ma in MSSQL o Oracle, puoi farlo con il

Parole chiave DECODE o CASE, concatenandole insieme. Tuttavia, ciò richiede che conosciate tutti i valori potenziali in anticipo, il che è fragile.

Suppongo che il motivo per cui non ti piace la parola chiave STUFF è perché informix non lo supporta?

Oracle supporta anche le parole chiave CONNECT BY, che funzionerebbero, ma potrebbe non essere supportato da informix.

Probabilmente la migliore risposta sarebbe quella di build questo output nel tuo livello client / dati, dopo la query. C’è una ragione particolare per cui questo deve essere fatto nella query?

Inoltre, se informix consente di creare funzioni utente, è ansible creare una funzione che restituisca una stringa con il valore concatenato.

Basandosi sull’esempio di Jonathan Leffler e sui commenti RET sull’ordinamento dei valori concatenati, utilizzando Informix 12.10FC8DE, ho trovato il seguente aggregato utente:

 CREATE FUNCTION mgc_init ( dummy VARCHAR(255) ) RETURNING SET(LVARCHAR(2048) NOT NULL); RETURN SET{}::SET(LVARCHAR(2048) NOT NULL); END FUNCTION; CREATE FUNCTION mgc_iter ( p_result SET(LVARCHAR(2048) NOT NULL) , p_value VARCHAR(255) ) RETURNING SET(LVARCHAR(2048) NOT NULL); IF p_value IS NOT NULL THEN INSERT INTO TABLE(p_result) VALUES (TRIM(p_value)); END IF; RETURN p_result; END FUNCTION; CREATE FUNCTION mgc_comb ( p_partial1 SET(LVARCHAR(2048) NOT NULL) , p_partial2 SET(LVARCHAR(2048) NOT NULL) ) RETURNING SET(LVARCHAR(2048) NOT NULL); INSERT INTO TABLE(p_partial1) SELECT vc1 FROM TABLE(p_partial2)(vc1); RETURN p_partial1; END FUNCTION; CREATE FUNCTION mgc_fini ( p_final SET(LVARCHAR(2048) NOT NULL) ) RETURNING LVARCHAR; DEFINE l_str LVARCHAR(2048); DEFINE l_value LVARCHAR(2048); LET l_str = NULL; FOREACH SELECT vvalue1 INTO l_value FROM TABLE(p_final) AS vt1(vvalue1) ORDER BY vvalue1 IF l_str IS NULL THEN LET l_str = l_value; ELSE LET l_str = l_str || ',' || l_value; END IF; END FOREACH; RETURN l_str; END FUNCTION; GRANT EXECUTE ON mgc_fini TO PUBLIC; CREATE AGGREGATE m_group_concat WITH ( INIT = mgc_init , ITER = mgc_iter , COMBINE = mgc_comb , FINAL = mgc_fini ); 

I valori concatenati non avranno duplicati e saranno ordinati.

Ho usato le collections Informix, ovvero SET che non consente valori duplicati, per cercare di mantenere il codice in qualche modo semplice.

Il metodo è usare SET per mantenere i risultati intermedi (ed eliminare i duplicati) e alla fine build la stringa concatenata dai valori ordinati del SET finale.

L’uso di LVARCHAR per gli elementi SET è dovuto al fatto che inizialmente stavo usando VARCHAR ma il consumo di memoria era molto, molto alto. La documentazione suggerisce che internamente Informix potrebbe lanciare il VARCHAR su CHAR . Ho apportato la modifica e in effetti ha ridotto il consumo di memoria (ma è ancora alto).

Tuttavia, questo consumo di memoria aggregato è di circa 2 ordini di grandezza superiore a quello di Jonathan e circa 2 volte più lento nei test che ho condotto (utilizzando una tabella con circa 300.000 righe).

Quindi usa con cura. Consuma molta memoria e non è ampiamente testato (potrebbe esserci una perdita di memoria da qualche parte).

MODIFICA 1:

Il mio codice precedente deve perdere una struttura di memoria da qualche parte (o internamente Informix mantiene le tabelle derivate dalla collezione e ne può generare molte).

Quindi, cercando ancora di evitare di dover codificare la funzione di aggregazione in C , ecco un’altra alternativa, utilizzando le funzioni integrate di Informix BSON , che useranno molta meno memoria e saranno un po ‘più veloci.

 CREATE FUNCTION m2gc_init ( dummy VARCHAR(255) ) RETURNING BSON; RETURN '{"terms":[]}'::JSON::BSON; END FUNCTION; CREATE FUNCTION m2gc_iter ( p_result BSON , p_value VARCHAR(255) ) RETURNING BSON; DEFINE l_add_array_element LVARCHAR(2048); IF p_value IS NOT NULL THEN LET l_add_array_element = '{ $addToSet: { terms: "' || TRIM(p_value) || '" } }'; LET p_result = BSON_UPDATE(p_result, l_add_array_element); END IF; RETURN p_result; END FUNCTION; CREATE FUNCTION m2gc_comb ( p_partial1 BSON , p_partial2 BSON ) RETURNING BSON; DEFINE l_array_elements LVARCHAR(2048); DEFINE l_an_element LVARCHAR(2048); DEFINE l_guard INTEGER; LET l_array_elements = NULL; LET l_guard = BSON_SIZE(p_partial2, 'terms.0'); IF l_guard > 0 THEN WHILE l_guard > 0 LET l_an_element = BSON_VALUE_LVARCHAR(p_partial2, 'terms.0'); IF l_array_elements IS NULL THEN LET l_array_elements = '"' || l_an_element || '"'; ELSE LET l_array_elements = l_array_elements || ', "' || l_an_element || '"'; END IF; LET p_partial2 = BSON_UPDATE(p_partial2, '{ $pop: { terms: -1 } }'); LET l_guard = BSON_SIZE(p_partial2, 'terms.0'); END WHILE; LET l_array_elements = '{ $addToSet: { terms: { $each: [ ' || l_array_elements || ' ] } } }'; LET p_partial1 = BSON_UPDATE(p_partial1, l_array_elements); END IF; RETURN p_partial1; END FUNCTION; CREATE FUNCTION m2gc_fini ( p_final BSON ) RETURNING LVARCHAR; DEFINE l_str_agg LVARCHAR(2048); DEFINE l_an_element LVARCHAR(2048); DEFINE l_iter_int INTEGER; DEFINE l_guard INTEGER; LET l_str_agg = NULL; LET l_guard = BSON_SIZE(p_final, 'terms.0'); IF l_guard > 0 THEN LET p_final = BSON_UPDATE(p_final, '{ $push: { terms: { $each: [], $sort: 1 } } }'); LET l_iter_int = 0; WHILE l_guard > 0 LET l_an_element = BSON_VALUE_LVARCHAR(p_final, 'terms.' || l_iter_int); IF l_str_agg IS NULL THEN LET l_str_agg = TRIM(l_an_element); ELSE LET l_str_agg = l_str_agg || ',' || TRIM(l_an_element); END IF; LET l_iter_int = l_iter_int + 1; LET l_guard = BSON_SIZE(p_final, 'terms.' || l_iter_int); END WHILE; END IF; RETURN l_str_agg; END FUNCTION; CREATE AGGREGATE m2_group_concat WITH ( INIT = m2gc_init , ITER = m2gc_iter , COMBINE = m2gc_comb , FINAL = m2gc_fini ) ; 

Il valore di ritorno aggregato sarà ordinato e senza duplicati.

Ancora una volta, questo non è stato testato correttamente. È solo un POC.

Uno dei problemi è che non sta disinfettando i valori di input. Alcune delle funzioni di manipolazione BSON ricevono i parametri che vengono creati concatenando le stringhe e i caratteri non di escape possono rompere quei parametri. Ad esempio, un valore stringa con citazioni su di esso: 'I"BrokeIt' ) può provocare un assortimento di errori (errori di asserzione inclusi).

E sono certo che ci sono altri problemi.

Tuttavia, il consumo di memoria di questa implementazione è nello stesso ordine di grandezza dell’esempio di Jonathan e di circa il 60% più lento (anche in questo caso sono stati eseguiti solo test molto rudimentali).

Vorrei indicarti questa risposta su un’altra domanda simile su Stack Overflow. Stai cercando qualcosa come la funzione group_concat() di MySQL.