PostgreSQLで最新のアクティビティをユーザーごとに追跡する方法

2024-07-03

SQLで最新行をユーザーごとに列に転換するプログラミング

このチュートリアルでは、SQLを使用して最新の行をユーザーごとに列に転換する方法を説明します。これは、最新のユーザーアクティビティを追跡したり、ユーザーごとの最新のデータを表示したりする必要がある場合に役立ちます。

前提条件

このチュートリアルを完了するには、以下のものが必要です。

PostgreSQLデータベースサーバー
PostgreSQLクライアント
SQLの基本的な知識

手順

データの準備

まず、サンプルデータを作成します。この例では、次のテーブルを作成します。

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_activities (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  activity VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

次に、いくつかのデータを追加します。

INSERT INTO users (username) VALUES
  ('alice'),
  ('bob'),
  ('charlie');

INSERT INTO user_activities (user_id, activity) VALUES
  (1, 'logged in'),
  (1, 'viewed profile'),
  (1, 'logged out'),
  (2, 'logged in'),
  (2, 'posted message'),
  (3, 'logged in');

最新行をユーザーごとに列に転換する

以下のクエリを使用して、最新の行をユーザーごとに列に転換できます。

WITH latest_activities AS (
  SELECT
    user_id,
    activity,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS row_num
  FROM user_activities
)

SELECT
  u.username,
  la.activity AS latest_activity
FROM users AS u
LEFT JOIN latest_activities AS la ON u.id = la.user_id AND la.row_num = 1
ORDER BY u.username;

このクエリは、以下の手順を実行します。

latest_activities というCTE (Common Table Expression) を作成します。このCTEは、各ユーザーの最新のアクティビティを activity 列に格納します。
users テーブルと latest_activities CTE を結合します。この結合により、各ユーザーとその最新のアクティビティが取得されます。
結果を username 列で昇順にソートします。

結果

username | latest_activity
------- | --------------
alice    | logged out
bob     | posted message
charlie | logged in

説明

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) 関数は、各ユーザーの行に固有の行番号を割り当てます。行番号は、最新の行が 1 になるように、created_at 列で降順にソートされます。
LEFT JOIN は、すべてのユーザーを含め、一致する最新のアクティビティがないユーザーでも NULL 値を返します。

応用例

このテクニックは、以下の場合に役立ちます。

最新のユーザーアクティビティを追跡するダッシュボードを作成する
ユーザーごとに最新のデータをリストするWebページを作成する

補足

この例では、created_at 列を使用して最新の行を特定しています。他の列に基づいて最新の行を特定する場合は、その列を ORDER BY 句で使用できます。

また、この例では、latest_activities CTE を使用して最新の行を抽出しています。サブクエリを使用して同様の結果を得ることもできますが、CTEの方が効率的で読みやすい場合があります。

PostgreSQLにおけるサンプルコード

-- サンプルデータを作成する
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_activities (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  activity VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO users (username) VALUES
  ('alice'),
  ('bob'),
  ('charlie');

INSERT INTO user_activities (user_id, activity) VALUES
  (1, 'logged in'),
  (1, 'viewed profile'),
  (1, 'logged out'),
  (2, 'logged in'),
  (2, 'posted message'),
  (3, 'logged in');

-- 最新行をユーザーごとに列に転換する
WITH latest_activities AS (
  SELECT
    user_id,
    activity,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS row_num
  FROM user_activities
)

SELECT
  u.username,
  la.activity AS latest_activity
FROM users AS u
LEFT JOIN latest_activities AS la ON u.id = la.user_id AND la.row_num = 1
ORDER BY u.username;

このコードを実行すると、以下の結果が出力されます。

username | latest_activity
------- | --------------
alice    | logged out
bob     | posted message
charlie | logged in

このコードは、チュートリアルで説明した手順をそのまま実装しています。各行の意味については、チュートリアルを参照してください。

このコードは、PostgreSQL 9.6以降で動作します。古いバージョンのPostgreSQLを使用している場合は、コードを修正する必要がある場合があります。

また、このコードは基本的な例であり、ニーズに合わせてカスタマイズする必要があります。たとえば、latest_activities CTE に追加の列を含めたり、SELECT 句で返す列を変更したりできます。

PostgreSQLで最新行をユーザーごとに列に転換するその他の方法

サブクエリを使用する

以下のクエリは、サブクエリを使用して最新の行を抽出します。

SELECT
  u.username,
  (
    SELECT activity
    FROM user_activities AS ua
    WHERE ua.user_id = u.id
    ORDER BY ua.created_at DESC
    LIMIT 1
  ) AS latest_activity
FROM users AS u;

users テーブルの各行に対して、user_activities テーブルをサブクエリでクエリします。
サブクエリは、そのユーザーの最新のアクティビティを取得します。
最新のアクティビティは、created_at 列で降順にソートされ、LIMIT 1 句を使用して最初の行のみが返されます。
サブクエリの結果は、latest_activity 列としてメインクエリに返されます。

WINDOW 関数を使用する

SELECT
  u.username,
  FIRST_VALUE(activity) OVER (PARTITION BY u.id ORDER BY created_at DESC) AS latest_activity
FROM users AS u
CROSS JOIN user_activities AS ua ON u.id = ua.user_id;

FIRST_VALUE 関数は、各ユーザーのパーティション内の最初の行の activity 列を返します。
パーティションは user_id 列で定義されます。
行は created_at 列で降順にソートされます。

MATERIALIZED VIEW を使用する

CREATE MATERIALIZED VIEW latest_user_activities AS (
  SELECT
    user_id,
    activity,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS row_num
  FROM user_activities
);

SELECT
  u.username,
  la.activity AS latest_activity
FROM users AS u
LEFT JOIN latest_user_activities AS la ON u.id = la.user_id AND la.row_num = 1
ORDER BY u.username;

latest_user_activities というマテリアライズドビューを作成します。このビューは、各ユーザーの最新のアクティビティを格納します。
ROW_NUMBER 関数は、各ユーザーの行に固有の行番号を割り当てます。行番号は、最新の行が 1 になるように、created_at 列で降順にソートされます。