Complicated mySQL Query Order By

10
votes

Example Data:

╔════╦════════════╦════════════╦═══════╦═══════════╦════════╗
║ ID ║   START    ║    STOP    ║ USER  ║   FILE    ║  SIZE  ║
╠════╬════════════╬════════════╬═══════╬═══════════╬════════╣
║  1 ║ 1330133409 ║ 1330133410 ║ user1 ║ file1.zip ║ 300000 ║
║  2 ║ 1330133409 ║ 1330133410 ║ user1 ║ file2.zip ║ 300500 ║
║  3 ║ 1330133409 ║ 1330133410 ║ user2 ║ file1.zip ║ 300000 ║
║  4 ║ 1330133409 ║ 1330133410 ║ user2 ║ file2.zip ║ 300500 ║
║  5 ║ 1330133409 ║ 1330133410 ║ user1 ║ file3.zip ║ 500000 ║
║  6 ║ 1330133409 ║ 1330133310 ║ user6 ║ file3.zip ║ 500000 ║
╚════╩════════════╩════════════╩═══════╩═══════════╩════════╝

I need to create a MySQL query that computes PER_USER_AVERAGE_BANDWIDTH where PER_USER_AVERAGE_BANDWIDTH = SUM(SIZE) / (STOP - START), and then order by PER_USER_AVERAGE_BANDWIDTH to produce results like this:

╔═══════╦════════════════════════════╗
║ USER  ║ PER_USER_AVERAGE_BANDWIDTH ║
╠═══════╬════════════════════════════╣
║ user3 ║ 110.37                     ║
║ user1 ║ 100.25                     ║
║ user2 ║ 75.70                      ║
╚═══════╩════════════════════════════╝

Clear as mud ;) Anyone?

3
mysql
+1 for the lovely layout. Have'n't seen that sort of thing since Paradox for Dos 2.0 - Tony Hopkinson
You show data for user1, user2, and user6 - where does user3 in the result set come from? Also, how do you SUM(SIZE) (multiple rows) but only use one row in (STOP - START)? There are three rows for user1 - which would you expect to be used for that part of the calculation? (Yes, I realize in this case STOP and START are the same values for all three rows, but the database doesn't know that when it's parsing the SQL.) - Ken White
@TonyHopkinson: That should be my +1! Sadface ;) FYI sensefulsolutions.com/2010/10/format-text-as-table.html - eggyal
@eggyal, + 1 for trying to make sure you got the credit. :) - Tony Hopkinson

3 Answers

2
votes

I think your average should be total size over total duration, grouped by user:

SELECT   USER,
         SUM(SIZE) / SUM(STOP - START) AS PER_USER_AVERAGE_BANDWIDTH
FROM     my_table
GROUP BY USER
ORDER BY PER_USER_AVERAGE_BANDWIDTH DESC

See it on sqlfiddle.

2
votes

straight forward for average

SELECT 
  `user`,
  AVG( size / ( stop - start ) ) per_user_average_bandwidth
FROM
  tab_dl
GROUP BY `user`
ORDER BY per_user_average_bandwidth DESC

SQL Fiddle DEMO

0
votes

This query should do it:

SELECT USER, (SUM(SIZE) / (STOP - START)) AS PER_USER_AVERAGE_BANDWIDTH
FROM table
GROUP BY USER, stop, start
ORDER BY PER_USER_AVERAGE_BANDWIDTH DESC

This will give you the average bandwidth per user per unique time frame (i.e. you will get 2 rows for a user if they download file 1 and file 2 between time 1 and time 5 and file 3 between time 1 and time 10).