logan-keede commented on issue #15949:
URL: https://github.com/apache/datafusion/issues/15949#issuecomment-2891084886
```sql
SELECT a.*, b.avg_response_time from (SELECT
"OS",
AVG(to_timestamp("ResponseEndTiming")-to_timestamp("ConnectTiming")) as
avg_latency
FROM
'data/hits_partitioned'
GROUP BY
"OS"
ORDER BY
avg_latency DESC limit 10) as a INNER JOIN (SELECT
"OS",
AVG(to_timestamp("ResponseEndTiming")-to_timestamp("ResponseStartTiming"))
as avg_response_time
FROM
'data/hits_partitioned'
GROUP BY
"OS"
ORDER BY
avg_response_time DESC limit 10) as b ON a."OS"=b."OS";
```
With business question: What OS are in top 10 worst performers in terms of
both average response time and avg latency?
Results on my PC:
```
+-----+------------------------------------------+------------------------------------------+
| OS | avg_latency | avg_response_time
|
+-----+------------------------------------------+------------------------------------------+
| 103 | 0 days 0 hours 13 mins 47.509466437 secs | 0 days 0 hours 13 mins
47.509466437 secs |
| 49 | 0 days 0 hours 13 mins 42.811185779 secs | 0 days 0 hours 13 mins
9.107811258 secs |
| 74 | 0 days 0 hours 13 mins 47.184892702 secs | 0 days 0 hours 12 mins
51.287431013 secs |
| 77 | 0 days 0 hours 11 mins 26.796840642 secs | 0 days 0 hours 9 mins
47.668864464 secs |
| 112 | 0 days 0 hours 8 mins 32.958042116 secs | 0 days 0 hours 3 mins
19.018355160 secs |
| 39 | 0 days 0 hours 3 mins 1.060289083 secs | 0 days 0 hours 2 mins
11.248827183 secs |
| 70 | 0 days 0 hours 8 mins 8.089117199 secs | 0 days 0 hours 1 mins
31.113089802 secs |
+-----+------------------------------------------+------------------------------------------+
7 row(s) fetched.
Elapsed 19.330 seconds.
```
or if we want to avoid subqueries or joins:-
```sql
SELECT
"OS",
AVG(to_timestamp("ResponseEndTiming")-to_timestamp("ResponseStartTiming"))
as
avg_response_time,AVG(to_timestamp("ResponseEndTiming")-to_timestamp("ConnectTiming"))
as avg_latency
FROM
'data/hits_partitioned'
GROUP BY
"OS"
ORDER BY
avg_latency DESC limit 10
```
@alamb what do you think about this?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org
For additional commands, e-mail: github-h...@datafusion.apache.org
