Database Query for Getting Max, Min of a column and corresponding values from other columns and Total Record Count from a Single Table in Hive

Question

I have below dataset in a Hive table name - PUBLISH

N.B. There can be duplicate records in PUBLISH

DATE    |HOUR|SOURCE|COL_TIMESTAMP              |ID
20200101|14  |A     |2020-01-01 14:18:53.016 GMT|ID_111
20200101|14  |A     |2020-01-01 14:18:53.012 GMT|ID_222
20200101|14  |A     |2020-01-01 14:18:53.016 GMT|ID_111
20200101|14  |A     |2020-01-01 14:18:53.019 GMT|ID_333
20200101|15  |C     |2020-01-01 15:18:53.016 GMT|ID_444
20200102|00  |A     |2020-01-01 15:18:53.016 GMT|ID_444

I want to generate below output based on a particular date, hour and source E.g. For (DATE=20200101 & HOUR=14 & SOURCE=A), the output should be:

DATE    |HOUR|SOURCE|MIN_TIMESTAMP              |START_ID|MAX_TIMESTAMP              |END_ID|RECORD_CNT
20200101|14  |A     |2020-01-01 14:18:53.012 GMT|ID_222  |2020-01-01 14:18:53.019 GMT|ID_333|3

N.B. The timestamp has 'GMT' at the end. Also I am trying to run the query using spark java code. Please suggest a hive query which will show good performance, when the data size is huge.

Sounds like a groupwise-max problem. http://mysql.rjweb.org/doc.php/groupwise_max — Rick James, Feb 13 '20 at 23:00

Nick · Answer 1 · 2020-02-10T21:54:15.347

You should be able to use a subquery to determine MIN & MAX timestamps for a given hour as as well as the count of distinct rows and then join that back to the main table to get the id values for those times:

SELECT DISTINCT P.DATE, P.HOUR, P.SOURCE,
       P.MIN_TIMESTAMP, p1.ID AS START_ID,
       P.MAX_TIMESTAMP, p2.ID AS END_ID
       P.COUNT
FROM (
    SELECT DATE, HOUR, SOURCE, 
           MIN(COL_TIMESTAMP) AS MIN_TIMESTAMP,
           MAX(COL_TIMESTAMP) AS MAX_TIMESTAMP,
           COUNT(DISTINCT DATE, HOUR, SOURCE, COL_TIMESTAMP, ID) AS COUNT
    FROM PUBLISH
    WHERE DATE = '20200101'
      AND HOUR = 14
      AND SOURCE = 'A'
    GROUP BY DATE, HOUR, SOURCE
) P
JOIN PUBLISH P1 ON P1.DATE = P.DATE AND P1.HOUR = P.HOUR AND P1.SOURCE = P.SOURCE AND P1.COL_TIMESTAMP = P.MIN_TIMESTAMP
JOIN PUBLISH P2 ON P2.DATE = P.DATE AND P2.HOUR = P.HOUR AND P2.SOURCE = P.SOURCE AND P2.COL_TIMESTAMP = P.MAX_TIMESTAMP

As long as you have an index on (DATE, HOUR, SOURCE) this should perform well.

Thanks for your answer. As mentioned, my data has duplicate records, so the query was resulting in duplicates. I added distinct on the outer most query and it worked. — user1326784, Feb 10 '20 at 21:19

leftjoin · Answer 2 · 2020-02-09T14:37:36.653

Use analytic functions to get START_ID and LAST_ID, then aggregate:

with PUBLISH as ( --Use your_table instead of this CTE
select stack(6,
'20200101','14','A','2020-01-01 14:18:53.016 GMT','ID_111',
'20200101','14','A','2020-01-01 14:18:53.012 GMT','ID_222',
'20200101','14','A','2020-01-01 14:18:53.016 GMT','ID_111',
'20200101','14','A','2020-01-01 14:18:53.019 GMT','ID_333',
'20200101','15','C','2020-01-01 15:18:53.016 GMT','ID_444',
'20200102','00','A','2020-01-01 15:18:53.016 GMT','ID_444'
) as (DT, HOUR, SOURCE, COL_TIMESTAMP, ID)
)

select DT, HOUR, SOURCE,
       min(COL_TIMESTAMP) as MIN_TIMESTAMP,
       START_ID,
       max(COL_TIMESTAMP) as MAX_TIMESTAMP,
       END_ID,
       sum(case when rn=1 then 1 else 0 end) as RECORD_CNT --unique records have rn=1
 from
     (
      select DT, HOUR, SOURCE, COL_TIMESTAMP, ID,
             first_value(ID) over(partition by DT, HOUR, SOURCE order by COL_TIMESTAMP)      as START_ID, 
             first_value(ID) over(partition by DT, HOUR, SOURCE order by COL_TIMESTAMP desc) as END_ID,
             row_number() over(partition by DT, HOUR, SOURCE, COL_TIMESTAMP, ID)             as rn
        from PUBLISH p
     ) s
 group by DT, HOUR, SOURCE, START_ID, END_ID;

Result:

dt  hour    source  min_timestamp   start_id    max_timestamp   end_id  record_cnt
20200101    14  A   2020-01-01 14:18:53.012 GMT ID_222  2020-01-01 14:18:53.019 GMT ID_333  3
20200101    15  C   2020-01-01 15:18:53.016 GMT ID_444  2020-01-01 15:18:53.016 GMT ID_444  1
20200102    00  A   2020-01-01 15:18:53.016 GMT ID_444  2020-01-01 15:18:53.016 GMT ID_444  1

Database Query for Getting Max, Min of a column and corresponding values from other columns and Total Record Count from a Single Table in Hive

2 Answers2