Sortierfunktion mit COUNT für Dubletten gesucht - Seite 2

Dark Guardian · 29.01.2012, 03:15

Code:

SELECT alias, COUNT(*)
FROM sim_icd_cvt
GROUP BY alias

Gibt dir ja die Anzahl eines jeden Alias aus, aber nur einmalig den Alias.

Code:

SELECT points, alias
FROM sim_icd_cvt

Gibt dir die Alias, aber ohne Anzahl.

Kombiniere es mit einem JOIN:

Code:

SELECT 

t1.points, t1.alias, t2.count

FROM 
sim_icd_cvt AS t1

LEFT JOIN 
(SELECT alias, COUNT(*) AS count
FROM sim_icd_cvt
GROUP BY alias) AS t2

ON t1.alias = t2.alias

In MySQL lässt sich die Ergebnismenge eines Selects als Tabelle nutzen.

akretschmer · 29.01.2012, 10:06

Zitat:

Zitat von Dark Guardian

In MySQL lässt sich die Ergebnismenge eines Selects als Tabelle nutzen.

Ja, immerhin geht das mittlerweile bei MySQL. Du kannst es aber IIRC nicht in einem WHER in der Form where id in (select ...) nutzen.

Btw.: Deine Lösung funktioniert selbstverständlich, mit dem Nachteil, daß 2 Scans auf der Tabelle nötig sind. Mit Windowing-Functions (wie von mir gezeigt) reicht ein Durchlauf.

Andreas

norebo · 29.01.2012, 11:57

Zitat:

Zitat von Dark Guardian

Kombiniere es mit einem JOIN:

Code:

SELECT 

t1.points, t1.alias, t2.count

FROM 
sim_icd_cvt AS t1

LEFT JOIN 
(SELECT alias, COUNT(*) AS count
FROM sim_icd_cvt
GROUP BY alias) AS t2

ON t1.alias = t2.alias

In MySQL lässt sich die Ergebnismenge eines Selects als Tabelle nutzen.

Hi Dark Guardian,
es funktioniert tatsächlich!

Einen LEFT JOIN auf ein die dieselbe Tabelle laufen zu lassen, darauf bin ich nicht gekommen.
Allerdings habe ich den Query mal über 15.000 Datensätze laufen lassen und es hat 55 Sekunden gedauert. Ich habe leider Tabellen mit mehreren 100.000 Einträgen.

Ich werde mir wohl was anderes ausdenken müssen - aber trotzdem vielen Dank an diejenigen, die sich hier die Mühe gemacht haben!

Viele Grüße
Sascha

akretschmer · 29.01.2012, 12:37

Zitat:

Zitat von norebo

Hi Dark Guardian,
es funktioniert tatsächlich!

Einen LEFT JOIN auf ein die dieselbe Tabelle laufen zu lassen, darauf bin ich nicht gekommen.
Allerdings habe ich den Query mal über 15.000 Datensätze laufen lassen und es hat 55 Sekunden gedauert. Ich habe leider Tabellen mit mehreren 100.000 Einträgen.

Ich werde mir wohl was anderes ausdenken müssen - aber trotzdem vielen Dank an diejenigen, die sich hier die Mühe gemacht haben!

Viele Grüße
Sascha

55 Sekunden bei 15.000 Datensätzen?

Ich hab mal 500.000 erzeugt und beide Varianten getestet:

Code:

test=# insert into norebo select 'point'||s::text, 'alias_'||substring(regexp_replace(md5(s::text),'[0-9]','','g'),1,2) from generate_series(1,500000)s;
INSERT 0 500000

Daten sehen also so aus:

Code:

test=*# select * from norebo offset 12345 limit 10;
   point    |  alias
------------+----------
 point12346 | alias_ad
 point12347 | alias_ad
 point12348 | alias_ee
 point12349 | alias_dc
 point12350 | alias_ad
 point12351 | alias_ef
 point12352 | alias_bf
 point12353 | alias_ad
 point12354 | alias_ab
 point12355 | alias_bb
(10 rows)

Nun die Abfragen:

Code:

test=*# explain analyse  select t1.point, t1.alias, t2.count from norebo t1 left join (select alias, count(*) from norebo group by alias) t2 on t1.alias=t2.alias;
                                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=10638.17..25650.17 rows=500000 width=28) (actual time=470.217..1069.381 rows=500000 loops=1)
   Hash Cond: (t1.alias = t2.alias)
   ->  Seq Scan on norebo t1  (cost=0.00..8137.00 rows=500000 width=20) (actual time=0.036..160.543 rows=500000 loops=1)
   ->  Hash  (cost=10637.72..10637.72 rows=36 width=17) (actual time=470.164..470.164 rows=40 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 2kB
         ->  Subquery Scan on t2  (cost=10637.00..10637.72 rows=36 width=17) (actual time=470.092..470.136 rows=40 loops=1)
               ->  HashAggregate  (cost=10637.00..10637.36 rows=36 width=9) (actual time=470.091..470.109 rows=40 loops=1)
                     ->  Seq Scan on norebo  (cost=0.00..8137.00 rows=500000 width=9) (actual time=0.005..160.962 rows=500000 loops=1)
 Total runtime: 1181.446 ms
(9 rows)

Time: 1182,008 ms

test=*# explain analyse select point, alias, count(*) over (partition by alias) from norebo ;
                                                         QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
 WindowAgg  (cost=55465.92..64215.92 rows=500000 width=20) (actual time=1961.697..2716.047 rows=500000 loops=1)
   ->  Sort  (cost=55465.92..56715.92 rows=500000 width=20) (actual time=1961.676..2132.345 rows=500000 loops=1)
         Sort Key: alias
         Sort Method: quicksort  Memory: 40224kB
         ->  Seq Scan on norebo  (cost=0.00..8137.00 rows=500000 width=20) (actual time=0.012..162.409 rows=500000 loops=1)
 Total runtime: 2835.383 ms
(6 rows)

Time: 2835,762 ms

Also bei 500.000 Datensätzen 1,1 Sekunde mit dem JOIN und 2,8 Sekunden mit der Windowing-Function. Die Windowing-Funktionen sind z.Z. lahmer als 'traditionelle' Lösungen, sind ja auch noch recht neu.

Ich hatte vor wenigen Minuten ein falschen Explain hier reingepostet, falls das jemand gesehen hatte... sorry.

Du solltest prüfen, ob Deine Hardware sauber arbeitet, denn 55 Sekunden bei nur 15.000 Zeilen ist wirklich arg lahm...

Andreas

Dark Guardian · 29.01.2012, 15:42

Zitat:

Zitat von akretschmer

Ja, immerhin geht das mittlerweile bei MySQL. Du kannst es aber IIRC nicht in einem WHER in der Form where id in (select ...) nutzen.

Doch, kann man:

Code:

SELECT *
FROM test
WHERE id IN (
  SELECT id FROM test2
)

funktioniert problemlos.

Zitat:

Einen LEFT JOIN auf ein die dieselbe Tabelle laufen zu lassen, darauf bin ich nicht gekommen.

Hat nikosch dir zweimal vorgeschlagen.

Zitat:

Allerdings habe ich den Query mal über 15.000 Datensätze laufen lassen und es hat 55 Sekunden gedauert. Ich habe leider Tabellen mit mehreren 100.000 Einträgen.

Das kann nicht sein. Da liegt etwas anderes quer.

Zitat:

Zeige Datensätze 0 - 29 (~125,270 insgesamt, die Abfrage dauerte 0.1722 sek.)

Nichtmal eine Sekunde für über 100000 Einträge.

Edit: Weitere Messungen

Ohne Index

Zitat:

Zeige Datensätze 0 - 29 (~459,4201 insgesamt, die Abfrage dauerte 5.3965 sek.)

Mit Index auf "alias"

Zitat:

Zeige Datensätze 0 - 29 (~468,4061 insgesamt, die Abfrage dauerte 0.3268 sek.)

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

Ähnliche Themen
Thema	Autor	Forum	Antworten	Letzter Beitrag
[Erledigt] MySQL: SUM, COUNT, GROUP BY sortieren	comlar	Datenbanken	3	21.10.2010 00:25
[Erledigt] Dubletten	louis	Datenbanken	12	15.03.2010 10:58
Junior iPhone Entwickler/in gesucht - mit PHP-Kenntnissen (Essen)	osxpert	Beitragsarchiv	0	10.09.2009 16:34
BigSkull Studios-Verstärkung gesucht	Nuc	Beitragsarchiv	36	12.02.2009 17:05
Count über mehrere Tabellenspalten	plastic	Datenbanken	3	20.10.2006 17:52
"Column count doesn't match value count at row 1"	möchtegernchegga	Datenbanken	3	05.03.2006 16:07
UPDATE count nach Link Aufruf ?		PHP Tipps 2006	9	22.01.2006 14:53
Redakteure für sehr großes Projekt gesucht		Beitragsarchiv	1	19.08.2005 01:57
MySQL error: Column count doesn't match value count at row 1	atom-dragon	PHP Tipps 2005-2	4	21.07.2005 01:38
PEAR::DB LimitQuery was ist Count?		PHP Tipps 2005-2	3	06.06.2005 18:02
Dubletten herausfiltern mit verbinden 2er tabellen.	robo47	Datenbanken	2	12.05.2005 11:59
mehrere COUNT() auf selbe Tabelle und »can't reopen table«	tapferesschneiderlein	Datenbanken	3	04.04.2005 13:51
[Erledigt] COUNT() Problematik...		Datenbanken	2	20.08.2004 13:48

29.01.2012, 03:15
Dark Guardian Erfahrener Benutzer Registriert seit: 10.10.2009 Beiträge: 2.629 PHP-Kenntnisse: Fortgeschritten	Code: SELECT alias, COUNT() FROM sim_icd_cvt GROUP BY alias Gibt dir ja die Anzahl eines jeden Alias aus, aber nur einmalig den Alias. Code: SELECT points, alias FROM sim_icd_cvt Gibt dir die Alias, aber ohne Anzahl. Kombiniere es mit einem JOIN: Code: SELECT t1.points, t1.alias, t2.count FROM sim_icd_cvt AS t1 LEFT JOIN (SELECT alias, COUNT() AS count FROM sim_icd_cvt GROUP BY alias) AS t2 ON t1.alias = t2.alias In MySQL lässt sich die Ergebnismenge eines Selects als Tabelle nutzen. __________________ "Alles im Universum funktioniert, wenn du nur weißt wie du es anwenden musst".


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten