こんにちは、さるまりんです。
ランキングっていろんなところで見ますよね。今回はPostgreSQLのデータベースに格納されたデータよりトップ〇〇(上位いくつか)のデータを取得してみたいと思います。
以下の3つのテーブルがあります。
商品テーブル
create table products (
product_id varchar(10) primary key,
product_name varchar(30) not null,
price integer not null
);注文テーブル
create table orders (
order_id varchar(10) primary key,
created timestamp not null
);注文詳細テーブル
create order_details (
order_id varchar(10) not null,
order_detail_index integer not null,
quantity integer not null,
primary key (order_id, order_detail_index)
);商品テーブルは商品の名前とその価格を、注文テーブルと注文詳細テーブルは注文日時と注文に含まれる商品と数量を持ちます。
これらのテーブルにあるデータを集計し、トップ〇〇を取得してみたいと思います。
売上数トップ3
各商品の販売数(合計数量)を計算し、売上数の多い順に並べてトップ3を取得します。
SELECT
p.product_id,
p.product_name,
SUM(od.quantity) AS total_quantity
FROM order_details od
JOIN products p ON od.product_id = p.product_id
GROUP BY p.product_id, p.product_name
ORDER BY total_quantity DESC
LIMIT 3;売上額トップ3
今度は売上額です。
各商品の売上額(価格×数量)を計算し、売上額の多い順に並べてトップ3を取得します。
SELECT
p.product_id,
p.product_name,
SUM(p.price * od.quantity) AS total_sales
FROM order_details od
JOIN products p ON od.product_id = p.product_id
GROUP BY p.product_id, p.product_name
ORDER BY total_sales DESC
LIMIT 3;期間を指定する方法
先ほどの例ではテーブルに入っている全期間の注文に対しての集計でした。
ここからは期間で集計します。
売上期間を指定するにはordersテーブルのcreatedカラムを利用し、order_detailsとの結合時にWHEREで期間を絞ります。
先月の売上数トップ3
SELECT
p.product_id,
p.product_name,
SUM(od.quantity) AS total_quantity
FROM order_details od
JOIN orders o ON od.order_id = o.order_id
JOIN products p ON od.product_id = p.product_id
WHERE o.created >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
AND o.created < DATE_TRUNC('month', CURRENT_DATE)
GROUP BY p.product_id, p.product_name
ORDER BY total_quantity DESC
LIMIT 3;DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')は先月の1日の00:00:00、DATE_TRUNC('month', CURRENT_DATE)は今月の1日の00:00:00となり、それに挟まれた期間を指定することで「先月分」のみを抽出することができます。
過去7日間の売上額トップ3
SELECT
p.product_id,
p.product_name,
SUM(p.price * od.quantity) AS total_sales
FROM order_details od
JOIN orders o ON od.order_id = o.order_id
JOIN products p ON od.product_id = p.product_id
WHERE o.created >= CURRENT_DATE - INTERVAL '7 days'
GROUP BY p.product_id, p.product_name
ORDER BY total_sales DESC
LIMIT 3;このクエリではCURRENT_DATE - INTERVAL '7 days'を指定することによって過去7日間のデータを取得しています。
任意の期間を指定
例えば、2024年1月1日〜2024年1月31日のデータを取得するには、以下のように WHERE 句を変更します。
WHERE o.created >= '2024-01-01 00:00:00'
AND o.created < '2024-02-01 00:00:00'シンプルですね。
DATE_TRUNCはPostgreSQLで利用できる関数なので、他のMySQL等では期間を指定する箇所をそれぞれの環境に合わせる必要があります。
データベースシステムはパワフルなので、データの扱いはできるだけデータベースに任せたいと思っています。
適材適所でうまくプログラムを書きたいです。
読んでくださってありがとうございました。
それではまた！