Redshift でユーザー別の接続数を調べるクエリ

Redshift でユーザー別の接続数を調べるクエリ。分単位のユーザー別の接続数を集計する。

-- psql で実行する場合
\o user_connection_count.csv
\pset format unaligned
\pset fieldsep '|'
\t off

WITH RECURSIVE time_series(time_value) AS (
/* Recursive CTE で過去 7 日間の 1 分ごとの時間を生成 */
  -- 開始時点（7日前）
  SELECT date_trunc('minute', dateadd(day, -7, GETDATE()))       
  UNION ALL           
  -- 現在までの1分刻み
  SELECT dateadd(minute, 1, time_value)                  
  FROM time_series                      
  WHERE time_value < date_trunc('minute', GETDATE())
),session_timeline AS (
/* セッションごとの開始、終了を整理 */
    SELECT 
    user_name
    ,session_id
    ,MIN(CASE WHEN event = 'authenticated' THEN record_time END) as connection_start
    ,NVL(MIN(CASE WHEN event = 'disconnecting session' THEN record_time END), '9999-12-31 00:00:00'::timestamp) as connection_end -- まだ接続が切れていない場合は 9999/12/31 を返す
    FROM SYS_CONNECTION_LOG
    WHERE user_name <> 'rdsdb'
    GROUP BY 
    user_name,
    session_id
)
SELECT 
 t.time_value
,c.user_name
,COUNT(c.user_name) as session_count
FROM time_series t
LEFT OUTER JOIN session_timeline c
ON t.time_value >= c.connection_start AND t.time_value <= c.connection_end --time_value 時点で接続している
GROUP BY 
 t.time_value
,c.user_name
ORDER BY time_value DESC;