【SQL】AthenaでユーザーごとのN回目にログインした日時を集計する

SQL

2023.12.16

この記事は約2分で読めます。

Athenaでユーザーごとの7回目にtログインした日時を集計するには、ウィンドウ関数を利用して各ユーザーのログイン回数を数え、7回目の検索に該当する行を抽出します。以下はそのためのSQLクエリの例です。

WITH NumberedSearches AS ( 
  SELECT 
    user_id, 
    login_date, 
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS login_count 
  FROM search_logs 
) 

SELECT 
  user_id, 
  login_date AS seventh_login_date 
FROM 
  NumberedSearches 
WHERE 
  login_count = 7;

このクエリは次のように機能します：

NumberedSearchesという共通テーブル式（CTE）を使用して、search_logsテーブルの各行に、同じユーザー (user_id) の中での検索の順位（search_rank）を割り当てます。これはROW_NUMBER()ウィンドウ関数によって行われます。
次に、search_rank が7である行を選択し、それに該当するuser_idとsearch_dateを出力します。

このクエリの結果として、各ユーザーが7回目にログインした日時が得られます。ただし、7回未満の検索しか行っていないユーザーについては、結果に含まれません。