Improve reduce functions of SQLite3 request

Steffen Mutter steffen at webanimations.de
Mon Feb 4 23:05:08 CET 2013


Dennis Lee Bieber wrote:

> 	I suspect you have a poorly normalized database (what does that
> trailing number identify? Heck, are the leading initials unique to the
> subsequent name?). The trailing number should probably be something
> stored as a separate field. If the initials are unique, they should be a
> separate field used as a foreign reference to retrieve the longer name.

There's much more stuff in the table of the database, but these ones
does not matter, the table Runde20122013 stores all the data needed for
gameplay during this season and is made to handle ALL leagues in germany
and if you enter another federation name as 'dhb' it could handle even
more...
 Jutst to explain: the numbers define the team(s) of the club in a
special category (male/female, youth E,D,C,B,A adults, senior), playing
in a league. 
Those leagues matter in the where clause, so the area
'karlsruhe' represents one of the  lowest areas a team can play in -
called 'Kreisklasse'. 
So, if a club has only team 2 playing here, and
the team (1) is playing higher, the number 2 needs to remain here.
Just to give you a peek on what I am doing:
https://handball.ws/generator.html has sometimes over 50.000 clicks in
a weekend - and manages it very nicely with a very small hetzner
shared server running lighttpd and python cgi.

At home I am working on a new bugfixed version which I hopefully get
online before planning for season 20132014 starts (April)

-- SNIP --
> sqlite> select nr, min(club) from test group by nr;
> nr          min(club)
> ----------  -------------------------
> 359         TV Calmbach
> 21101       SG Heidel/Helm
> 21236       JSG Neuth/Buch
> 23108       TG Eggenstein
> 23109       TV Ettlingenw
> 23112       TSV Johlingen
> sqlite>
>
> 	Don't even need the DISTINCT with the GROUP BY.

Woha!
Why didn't I get this out by myself?
So, let's see what my machine spits out:

SQL:
SELECT nr, min(club) FROM (
  SELECT HeimNr as nr, Heim as club 
    FROM Runde20122013 WHERE kreis = "karlsruhe" 
  UNION SELECT GastNr as nr, gast as club 
     FROM Runde20122013 WHERE kreis= "karlsruhe") 
  GROUP BY nr;

REPLY:
359|TV Calmbach
21101|SG Heidel/Helm
21236|JSG Neuth/Büch
23108|TG Eggenstein
23109|TV Ettlingenw
23112|TSV Jöhlingen
23113|HC Karlsbad
23114|MTV Karlsruhe
23115|Post Südst KA
23117|TSV Bulach
23118|TS Durlach
23119|TV Knielingen
23120|TS Mühlburg
23121|TG Neureut
23122|TSV Rintheim
23123|TUS Rüppurr
23124|SV Langenstb.
23125|FV Leopoldshfn
23126|TV Malsch
23128|TV Wössingen
23130|HSG Ettl/Bruch
23132|HSG Li-Ho-Li
23133|HSG PSV/SSC KA
23136|HSG Ri/Wei/Grö 2 <--- PERFECT!!!
23138|HSG Wei/Grö
23231|SG Stutensee
23234|KIT SC 2010
23251|SG MTV/Bulach
23503|SG RüBu
23516|SG Malsch/Ettl
25149|HC Neuenbürg
25201|SG PF/Eutingen
25224|HSG Pforzheim
25232|JSG Goldst. P

I need to glue home/guest aka heim/gast together, because there are K.O.
leagues, where a team may play only as guest, before kicked out of the
game (Amateur Deutschlandpokal)

HSG Rintheim-Weingarten-Grötzingen 2nd team plays in Karlsruhe, the 1st
one plays Badenliga.

If you come to Karlsruhe one day, send me an email, I take you out for a
beer or so...

Kind regars,
Steffen (very happy)

Steffen 



More information about the Python-list mailing list