CODEWITHSUNDEEP
sqldatetimemaxhiveqlmin
Cybernetic
Cybernetic

Reputation: 13334

Show date ranges for each unique value of a column (HIVE-QL Query)

I have a HIVE table like this (only showing first 5 lines out of thousands):

date          metric          timestamp          value
2020-06-18    cpu_mem     2020-08-15 00:05:00      10
2020-10-18    cpu_mem     2020-08-15 00:10:00      15
2020-22-18    gpu_mem     2020-08-15 00:15:00      12
2020-26-18    cpu_mem     2020-08-15 00:20:00      10
2020-29-18    threads     2020-08-15 00:25:00      05

I want to show date ranges for each unique metric of a column. The result would look like:

unique_metrics      date_range(min/max)
cpu_mem             2019-08-10 00:05:00 - 2020-02-15 00:05:00
gpu_mem             2020-08-15 00:05:00 - 2020-09-10 00:15:00
threads             2018-06-09 00:05:00 - 2020-08-15 00:06:00

I am just showing example mins and maxes for the dates. So the query would return only the unique names of the metrics and the min and max of the dates for those unique metrics.

Something like:

SELECT metric, timestamp FROM table WHERE timestamp >= MIN(timestamp) AND timestamp <= MAX(timestamp) GROUP BY metric;

But obviously that's not right, since I don't want to select between a given range I just want to know what the range is for each unique metric.

Upvotes: 0

Views: 297

Answers (1)

GMB
GMB

Reputation: 222432

So the query would return only the unique names of the metrics and the min and max of the dates for those unique metrics.

Do you just want aggregation?

select metric, min(timestamp) min_timestamp, max(timestamp) max_timestamp
from mytable
group by metric

Upvotes: 2

Related Questions