まずはテストデータテーブルを作成
DROP TABLE IF EXISTS score_table;
CREATE TABLE score_table (
amazon_user_id text,
product_id text,
score numeric
);
INSERT INTO score_table (amazon_user_id, product_id, score)
VALUES ('U0001', 'P001', '3.0'), ('U0001', 'P002', '4.0'), ('U0001', 'P003', '5.0'), ('U0002', 'P001', '4.0'), ('U0002', 'P002', '3.0'), ('U0002', 'P003', '4.0'), ('U0003', 'P001', '2.0'), ('U0003', 'P002', '5.0'), ('U0003', 'P003', '1.0');
Amazonユーザーが製品ごとに星による評価（⭐️１〜５個）をしたデータがあったとします。
amazon_user_id | product_id | score ----------------+------------+------- U0001 | P001 | 3.0 U0001 | P002 | 4.0 U0001 | P003 | 5.0 U0002 | P001 | 4.0 U0002 | P002 | 3.0 U0002 | P003 | 4.0 U0003 | P001 | 2.0 U0003 | P002 | 5.0 U0003 | P003 | 1.0 (9 rows)
こんな感じですね。
product_id がP001, P002, P003があってそれぞれについてスコアを付けています。
テーブル全体の行数，ユーザーの人数，製品の種類，スコアの平均値，最高スコア，最低スコアを計算する
SELECT
COUNT(*) AS total_count,
COUNT(DISTINCT amazon_user_id) AS user_count,
COUNT(DISTINCT product_id) AS product_count,
SUM(score) AS sum,
AVG(score) AS avg,
MAX(score) AS max,
MIN(score) AS min
FROM
score_table;
total_count | user_count | product_count | sum | avg | max | min
-------------+------------+---------------+------+--------------------+-----+-----
9 | 3 | 3 | 31.0 | 3.4444444444444444 | 5.0 | 1.0
(1 row)
DISTINCTを使うとユニークな文字列をカウントします。
この例では同じamazon_user_idを持つ場合は一つとしてカウントするわけです。
全行数は９行ありますが、ユニークなamazon_user_idはU001, U002, U003しかないわけですから、DISTINCT句を使うことによってuser_countは３つとして計算されます。
グルーピングしたデータごとに行数，ユーザーの人数，製品の種類，スコアの平均値，最高スコア，最低スコアを計算する
GROUP BY を使ってグループ化すると便利ですよね。
コメント