적용 대상:
Databricks SQL Databricks Runtime 10.4 LTS 이상
타임스탬프 표현을 기반으로 세션 창을 생성합니다.
구문
session_window(expr, gapDuration)
인수
-
expr: 창의 제목을 지정하는TIMESTAMP식입니다. -
gapDuration: 창의 너비를STRING리터럴로 나타내는INTERVAL DAY TO SECOND식입니다.
반품
집계 함수를 사용하여 작동할 수 있는 그룹 집합을 반환합니다.
GROUP BY 열 이름이 session_window.
STRUCT<start:TIMESTAMP, end:TIMESTAMP> 형식입니다.
예제
> SELECT a, session_window.start, session_window.end, count(*) as cnt
FROM VALUES ('A1', '2021-01-01 00:00:00'),
('A1', '2021-01-01 00:04:30'),
('A1', '2021-01-01 00:10:00'),
('A2', '2021-01-01 00:01:00') AS tab(a, b)
GROUP by a, session_window(b, '5 minutes')
ORDER BY a, start;
A1 2021-01-01 00:00:00 2021-01-01 00:09:30 2
A1 2021-01-01 00:10:00 2021-01-01 00:15:00 1
A2 2021-01-01 00:01:00 2021-01-01 00:06:00 1
> SELECT a, session_window.start, session_window.end, count(*) as cnt
FROM VALUES ('A1', '2021-01-01 00:00:00'),
('A1', '2021-01-01 00:04:30'),
('A1', '2021-01-01 00:10:00'),
('A2', '2021-01-01 00:01:00'),
('A2', '2021-01-01 00:04:30') AS tab(a, b)
GROUP by a, session_window(b, CASE WHEN a = 'A1' THEN '5 minutes'
WHEN a = 'A2' THEN '1 minute'
ELSE '10 minutes' END)
ORDER BY a, start;
A1 2021-01-01 00:00:00 2021-01-01 00:09:30 2
A1 2021-01-01 00:10:00 2021-01-01 00:15:00 1
A2 2021-01-01 00:01:00 2021-01-01 00:02:00 1
A2 2021-01-01 00:04:30 2021-01-01 00:05:30 1