Improve reduce functions of SQLite3 request
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
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 -
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
> Don't even need the DISTINCT with the GROUP BY.
Why didn't I get this out by myself?
So, let's see what my machine spits out:
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;
23115|Post Südst KA
23133|HSG PSV/SSC KA
23136|HSG Ri/Wei/Grö 2 <--- PERFECT!!!
23234|KIT SC 2010
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...
Steffen (very happy)
More information about the Python-list