SQL query táblatisztításra prefix alapon

Adatbázis: SQL, XML DB

Sziasztok!

Napi szinten használok SQL-t (esetemben MariaDB), de van egy query, ami feladta a leckét, lényegében elképzelésem sincs, hogy hogy nézne ez ki. Viszont számomra nagyon-nagyon hasznos lenne.

Van egy táblám amelyből jelen esetben 3 oszlop a lényeg: GROUP, PREFIX, VALUE.

Csoportonként (GROUP érték) szeretném törölni azokat a rekordokat, ahol a prefix hosszabb egy nála rövidebb de azonos értéűvel definiáltnál, viszont a VALUE értéke megegyezik.

Pl.:

group1 a123  1.2

group1 a1234 1.2    <-ez nem kell mert a rövidebben definiáltnál is ennyi a VALUE

group1 a1235 1.4

group2 a1234 1.6

group2 a12345 1.6   <-ez nem kell mert a rövidebben definiáltnál is ennyi a VALUE

És mindezt csoportonként.

 

Ez a DELETE query valahogy nem áll össze a fejemben.

Hogy kellene ennek nagyjából kinéznie?

A válaszaitokat előre is köszönöm!

( _Franko_ v | 2022. 05. 18., sze – 11:35 )

Szerkesztve: 2022. 05. 18., sze – 11:36

Hát, szerintem erre találták ki a PL/SQL-t, vélhetően extra programozás nélkül ezt csak SQL-ből nem tudod megoldani...

Alapvetően group és value szerint kellene group-ot képezni és a prefix-et sorba rendezni hossz szerint, majd az elsőt leszámítva törölni. Nem tűnik olyan komplexnek PL/SQL tárolt eljárásként.

https://iotguru.cloud

PHP is jó, ha van. Ilyesmi elvileg:

select `group`,`value` from `valamitábla` group by `group`,`value`;

Erre fogsz kapni egy listát, hogy milyen group-value párok vannak, azokon végig tudsz iterálni:

select `prefix` from `valamitábla` where `group`= ... and `value` = ...;

Aztán ha itt több sort kapsz vissza, akkor a logikád alapján már tudod törölni a felesleget.

https://iotguru.cloud

"ezt csak SQL-ből nem tudod megoldani"

```
DELETE FROM table t1
  WHERE EXISTS (
    SELECT 1 FROM table t2
    WHERE t1.group = t2.group and t1.value = t2.value and LEFT(t1.prefix, LENGTH(t2.prefix)) = t2.prefix) and LENGTH(t2.prefix) < LENGTH(t1.prefix)
  );

```

Kérdés, hogy milyen a prefix szabályrendszere, a fenti példára jó a length, de ha "értelmezni" is kell, akkor instr()-es a móka.

Gyakorlatilag group+value alapján újra kell csoportosítani és kiválasztani a min_prefix értéket, utána meg mehet egy sima WHERE ahol a prefix <> min_prefix

"The only valid measurement of code quality: WTFs/min"

kb erre gondoltam: https://www.db-fiddle.com/f/exwyNdjGcWhU9KNy1LxyBU/0

SELECT
  t2.*
FROM
  (
  SELECT 
    t1.*,
    LENGTH(t1.prefix) pfx_length
  FROM
    t1
  ) t2,
  (
  SELECT 
    t1.grp,
    t1.val,
    MIN(LENGTH(t1.prefix)) min_pfx_length
  FROM
    t1
  GROUP BY grp,val
  ) t3
WHERE 
  t2.grp = t3.grp
  AND t2.val = t3.val
  AND t2.pfx_length <> t3.min_pfx_length
;

Delete-re nem alakítottam át, meg lehet szépíteni ízlés szerint :)

"The only valid measurement of code quality: WTFs/min"

mit ir? valoszinuleg csak a group oszlop nevet kell idezojelbe rakni.

a postgres-ben gond nelkul lefut:

 

```

CREATE TABLE hup AS
select "group", "prefix","value" from (values
('group1', 'a123', '1.2'), ('group1', 'a1234', '1.2'), ('group1', 'a1235', '1.4'),('group2', 'a1234', '1.6'),('group2', 'a12345', '1.6')) as mytable("group", "prefix","value" );

DELETE FROM hup t1
WHERE EXISTS (SELECT 1 FROM hup t2 WHERE t1."group" = t2."group" and t1.value = t2.value and LEFT(t1.prefix, LENGTH(t2.prefix)) = t2.prefix and LENGTH(t2.prefix) < LENGTH(t1.prefix));

```

Köszönöm, a syntax kérdést megoldotta, ellenben úgy néz ki, ez a megoldás a MariaDB-nek nem tetszik:

1093 - Table 't1' is specified twice, both as a target for 'DELETE' and as a separate source for data, Time: 0.062000s

Viszont egy duplikált táblával kipróbálom, mert már ez nagyon nagy könnyítés a táblán. MÉg fut a query, ez el fog tartani egy darabig.

UPDATE:

Közben rájöttem, hogy hülye vagyok, mert még egy alcsoport szerint is külön kell szednem őket, de szerencsére a fenti query struktúrája egy sima "and t1.sub_group=t2.sub_group"-pal ezt lehetővé teszi.

Köszönöm.

( joco01 v | 2022. 05. 18., sze – 11:56 )

Joinold a táblát önmagához. Ha jól értem, value szerinti egyezést keresel, és minden sorhoz kapcsold hozzá a nála rövidebb prefixeit. Ezt lapátold be egy subquery-be és aszerint törölj.

( Oregon | 2022. 05. 18., sze – 15:25 )

Szerkesztve: 2022. 05. 18., sze – 15:26

Remélem értem a feladatot. Nem teszteltem. fejből írom. nem biztos, hogy megy (sőt!) a logika remélem átmegy:

 

SELECT t1.GROUP, t1.PREFIX, t1.VALUE FROM táblád AS t1

WHERE (SELECT t2.PREFIX FROM táblád AS t2 WHERE t2.prefix = substring(t1.prefix,0, length((t1.prefix)-1) LIMIT 1)

( fhuse | 2022. 05. 18., sze – 16:48 )

delete from table
where exist ( 
   select 1 
   from table t 
   join (select group, value, min(len(prefix)) lprfx 
              from table 
              group by group, value )  p
        on t.group = p.group
       and t.value = p.value
       and len(t.prefix)> p. lprfx 
)

ha tobb azonos min. hosszu prefix van meghagyja mindet. (nem tesztteltem, fejbol irtam)

 

delete from table
where concat(group,prefix,value) not in
      (select gpv
       from ( select concat(group,prefix,value) gpv
                     , row_number() over (partition by group,value  order by len(prefix )) rn
              from table
              ) x
       where rn = 1
       )

Nullokkal vigyazni! 

Ezek az értékek soha nem nullok. Viszont ezt a query-t nem értem. Nem tudom mi lenne ott az a gpv, meg az rn. Illetve bocsánat, az leesett hogy az a 3 oszlop rövidítése, csak nem látom át, hogy mit keres ott, ahol ott van. A len() alapján ez nekem MSSQL-nek tűnik.

fejbol irtam.
len -> length nyilvan :)

szerintem mukodnie kell MSSQLben is:

- nem sok RDBMS erti a:
          (x,y,z) in (select a,b,c from table)
    format.    
    ezert fuztem ossze az oszlopokat concat(group,prefix,value) sima left joinra is lehene cserelni...

he lefuttadod reszenkent:

- x: sorszamozza (rn) a sorokat prefix hossz szerint novekvobe a group ,value csoprtokra. 

-  where rn = 1 kiveszi az 1-es sorszamut, ha tobb van azonnos hosszal az egyiket! (ha mindet meg akarod hagyni :a row_numbert cserled RANK -ra)

- concat(group,prefix,value) not in : minden sort kivalszt torlesre ami nincs a gpv selectben.

 

Igy?

( powermate | 2022. 05. 24., k – 13:09 )

Nagyon köszönöm mindenkinek a hozzászólásokat. A tábla megtisztult! :)