I'm having trouble to get the average download in minutes from the sample record as follows in ANSI-92 SQL or Impala SQL standard.
The time which is in bold share the time space ( in minutes group ) for starttimestamp and stoptimestamp. How to get the average download in
and so on.
Any suggestion? Thank you very much in advance!
Regards,
Pozy
select
(unix_timestamp(stoptimestamp)-unix_timestamp(starttimestamp)) / 60.0 diff_minutes
from your_table
using unix_timestamp() calculates the difference in seconds then divide by 60 or 60.0 depending on the precision you want in the result.
To calculate an average download over several rows you need to use SUM() to aggregate the bytes and alo sume the time unit. You might want to use seconds for that inital calculation and then divide by 60.0
The following example is written for SQL Sever as I don't have Impala to use
declare @mytable table
([login_id] varchar(11), [starttimestamp_] datetime, [stoptimestamp_] datetime, [download_bytes] decimal(12,1))
;
INSERT INTO @mytable
([login_id], [starttimestamp_], [stoptimestamp_], [download_bytes])
VALUES
('[email protected]', '2015-12-31 23:59:50', '2016-01-01 00:00:20', 438.0),
('[email protected]', '2016-01-01 00:00:28', '2016-01-01 00:01:13', 2190.0),
('[email protected]', '2016-01-01 00:01:21', '2016-01-01 00:01:54', 876.0),
('[email protected]', '2016-01-01 00:01:59', '2016-01-01 00:02:34', 1168.0),
('[email protected]', '2016-01-01 00:02:43', '2016-01-01 00:03:34', 1179.0)
;
select
sum(download_bytes) sum_bytes
, sum(datediff(second,starttimestamp_,stoptimestamp_)) sum_time_unit
, sum(download_bytes)/sum(datediff(second,starttimestamp_,stoptimestamp_)) avg_bytes_sec
, (sum(download_bytes)/sum(datediff(second,starttimestamp_,stoptimestamp_)))/60.0 avg_bytes_min
from @mytable
-- WHERE ...
-- GROUP BY ...
+===========+===============+===============+===============+
| sum_bytes | sum_time_unit | avg_bytes_sec | avg_bytes_min |
+===========+===============+===============+===============+
| 5851 | 194 | 30.159793 | 0.502663 |
+-----------+---------------+---------------+---------------+