《围观》第80期：拍成人电影的两年

TUMBLE(time_attr, interval)

CREATE TABLE user_income (
      username VARCHAR,   
      Income INT,   
      times TIMESTAMP(3),                               
      WATERMARK FOR times AS times - INTERVAL '3' SECOND
) WITH (
      ...
  );
?
CREATE TABLE output (
            win_start TIMESTAMP,
            win_end TIMESTAMP,
            username VARCHAR,   
            hour_income BIGINT
)WITH(
      ...
);
?
INSERT INTO output
SELECT
TUMBLE_START(times,INTERVAL '1' HOUR),
TUMBLE_END(times,INTERVAL '1' HOUR),
username,
SUM(Income)
FROM user_income
GROUP BY TUMBLE(times,INTERVAL '1' HOUR),username;

HOP(time_attr, sliding_interval, window_size_interval)

CREATE TABLE user_income (
      username VARCHAR,   
      Income INT,   
      times TIMESTAMP(3),                               
      WATERMARK FOR times AS times - INTERVAL '3' MINUTE
)WITH(
      ...
  );
?
CREATE TABLE output (
            win_start TIMESTAMP,
            win_end TIMESTAMP,
            username VARCHAR,   
            hour_income BIGINT
)WITH(
      ...
  );
?
INSERT INTO output
SELECT
HOP_START(times,INTERVAL '30' MINUTE,INTERVAL '1' HOUR),
HOP_END(times,INTERVAL '30' MINUTE,INTERVAL '1' HOUR),
username,
SUM(income)
FROM user_income
GROUP BY HOP(times,INTERVAL '30' MINUTE,INTERVAL '1' HOUR),username;

SESSION(time_attr, interval)

CREATE TABLE user_income (
      username VARCHAR,   
      Income INT,   
      times TIMESTAMP(3),                               
      WATERMARK FOR times AS times - INTERVAL '3' MINUTE
)WITH(
      ...
  );
?
CREATE TABLE output (
            win_start TIMESTAMP,
            win_end TIMESTAMP,
            username VARCHAR,   
            hour_income BIGINT
)WITH(
      ...
  );
?
INSERT INTO output
SELECT
SESSION_START(times,INTERVAL '30' MINUTE),
SESSION_END(times,INTERVAL '30' MINUTE),
username,
SUM(Income)
FORM user_income
GROUP BY SESSION(times,INTERVAL '30' MINUTE),username;

SELECT user,
TUMBLE_START(rowtime, INTERVAL '12' HOUR) AS sStart,
TUMBLE_ROWTIME(rowtime, INTERVAL '12' HOUR) AS snd,
SUM(amount)
FROM Orders
GROUP BY TUMBLE(rowtime, INTERVAL '12' HOUR), user

SELECT user,
TUMBLE_START(PROCTIME, INTERVAL '12' HOUR) AS sStart,
TUMBLE_PROCTIME(PROCTIME, INTERVAL '12' HOUR) AS snd,
SUM(amount)
FROM Orders
GROUP BY TUMBLE(PROCTIME, INTERVAL '12' HOUR), user