Geek asks and answers

Hive QL time slot sampling and hop calculation

I have time series data in a table. Basically, each line has a timestamp and value. The frequency of the data is completely random.

I would like to try it with a given frequency and for each frequency to extract the relevant information about it: min, max, last, change (relative previous), return (change / previous) and, possibly, more (count ...)

So here is my input:

08:00:10, 1
08:01:20, 2
08:01:21, 3
08:01:24, 5
08:02:24, 2

And I would like to get the following result for a 1-minute sample (ts, min, max, last, change, return):

ts        m  M  L  Chg   Return   
08:01:00, 1, 1, 1, NULL, NULL
08:02:00, 2, 5, 5, 4,    4
08:03:00, 2, 2, 2, -3,   -0.25
+3
source share
1 answer

You can do this with something like this (comments in a line):

SELECT
    min
  , mn
  , mx
  , l
  , l - LAG(l, 1) OVER (ORDER BY min) c
    -- This might not be the right calculation. Unsure how -0.25 was derived in question.
  , (l - LAG(l, 1) OVER (ORDER BY min)) / (LAG(l, 1) OVER (ORDER BY min)) r
FROM
(
  SELECT
      min
    , MIN(val) mn
    , MAX(val) mx
    -- We can take MAX here because all l (last values) for the minute are the same.
    , MAX(l) l
  FROM
  (
    SELECT
        min
      , val
      -- The last value of the minute, ordered by the timestamp, using all rows.
      , LAST_VALUE(val) OVER (PARTITION BY min ORDER BY ts ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) l
    FROM
    (
      SELECT
          ts
        -- Drop the seconds and go back one minute by converting to seconds,
        -- subtracting 60, and then going back to a shorter string format.
        -- 2000-01-01 is a dummy date just to enable the conversion.
        , CONCAT(FROM_UNIXTIME(UNIX_TIMESTAMP(CONCAT("2000-01-01 ", ts), "yyyy-MM-dd HH:mm:ss") + 60, "HH:mm"), ":00") min       
        , val
      FROM
        -- As from the question.
        21908430_input a
    ) val_by_min
  ) val_by_min_with_l
  GROUP BY min
) min_with_l_m_M
ORDER BY min
;

Result:

+----------+----+----+---+------+------+
| min      | mn | mx | l | c    | r    |
+----------+----+----+---+------+------+
| 08:01:00 | 1  | 1  | 1 | NULL | NULL |
| 08:02:00 | 2  | 5  | 5 | 4    | 4    |
| 08:03:00 | 2  | 2  | 2 | -3   | -0.6 |
+----------+----+----+---+------+------+
+1
source

More articles:


All Articles