yes, I want output in the form I noted; the averages I typed were numbers pulled from thin air. There will eventually be many records for many machines, and what I'm looking to output is one line for each domain name with the average speed for all machines on that domain. The data I listed is just what's floating around from a couple runs I did myself (and the aol result from my mom, who refuses to change just on principle) and is representative only in structure; there will (I hope!) eventually be thousands of results from probably a hundred domains or so, each result potentially with a different machine name.
The fun part to which I refer is (what I perceive to be) the crux of the problem: figuring out all (for example) adelphia.com records, finding their average speed, and spitting out that one line. What I was thinking of doing is having the php run thru and return all domain names alphabetically, then remove the duplicates, then go back and do a query for each line matching the domain name and fetching the speeds, then averaging them together, then spitting it out and repeating for each domain name in the list. That just sounds like a royal pain in the butt and I was hoping there'd be a slick way to do it all in one (albeit fat and bloated) query that didn't take five hours of CPU time to execute every time the page is loaded. The project itself is at http://www.dibcomputers.com/bandwidthmeter/index.php if you care to have a look at it. Pretty simple really. As to the database layout, it's a canned script I'm tweaking to suit my fancy; eventually I'll get into its internals and optimize them but for now I'll be happy if the dumb thing just works. Also it's a write once read multi sort of setup; the records are only written on completion of a test, after that I'm just looking at them to see what they were. I suppose to avoid bloat I might have a maintenance script cut off everything older than, say, a year...but that's a ways off yet. thanks for all your help, Dan ----- Original Message ----- From: "Roger Baklund" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Saturday, January 24, 2004 5:09 PM Subject: Re: A fun one > * [EMAIL PROTECTED] > > > * Don Read > > > See SUBSTRING_INDEX() > > > Almost. I'm sure that'll be needed in there someplace..... > > > > here's what I've got: > > CREATE TABLE `readings` ( > > `date` timestamp(14) NOT NULL, > > `speed` float NOT NULL default '0', > > `ip` text NOT NULL, > > `name` text NOT NULL > > ) TYPE=MyISAM; > [...] > > You shouldn't use text columns for ip/name... ip addresses fits in an > unsigned int, a varchar would be good for a host name. You should also > consider making a separate domain column, if you need to do these queries on > a regular basis and the amount of data is significant. > > You should also be carefull with the timestamp column type. If you for > instance would try to add a domain column with ALTER TABLE ADD domain > VARCHAR(255) NOT NULL or similar, and the do an UPDATE readings SET domain = > SUBSTRING_INDEX(name,'.',-2), you will experience that the date column is > changed as well, to the current time. This is a special feature of the > timestamp column. You can avoid it by instead doing "UPDATE readings SET > domain = SUBSTRING_INDEX(name,'.',-2),date=date", i.e. setting the date > column to be equal to what it allready is... This is described in the > manual: > > <URL: http://www.mysql.com/doc/en/DATETIME.html > > > If you want a DATETIME column instead of the TIMESTAMP: > > ALTER TABLE readings MODIFY date DATETIME NOT NULL; > > > Here's what I want: > > adelphia.net: 3000.0 kbps average > > aol.com: 5.0 kbps average > > dsl-verizon.net: 1500.0 kbps average > > > > Told you it'd be a fun one! ;) > > I am trying to understand what the fun part is... :) > > There is only one "aol.com" record, the speed value for this record is 30.1, > and you want the output to be "5.0 kbps average"? Was that just an example, > or is that the fun part? You could divide by 6, but it does not match your > other example values... I got 2184.5 as an average for adelphia.net > _without_ dividing by 6. > > You want the output in the _form_ you specify above? Not columns, but one > line for each domain, followed by a colon and then the average value? The > inconsistent number of spaces between the colon and the number? Is that part > of what you want? Is this the fun part? I guess not, but please be more > specific. :) > > Try something like this: > > SELECT > CONCAT( > SUBSTRING_INDEX(name,'.',-2),': ', > TRUNCATE(AVG(speed),1), > ' kbps average') "Here's what I want:" > FROM readings > GROUP BY SUBSTRING_INDEX(name,'.',-2); > > or simply: > > SELECT SUBSTRING_INDEX(name,'.',-2) domain, > TRUNCATE(AVG(speed),1) avgspeed > FROM readings > GROUP BY domain; > > -- > Roger > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
