PostgreSQLで前月分のデータを集計する

こんにちは、さるまりんです。

データ集計、プログラムが最も得意とする分野のひとつですね。

今回はPostgreSQLで前月のデータを集計してみます。

前月の範囲を取得

DATE_TRUNCとINTERVALを使って前月の開始日と終了日を求めます。

-- 前月の開始日
SELECT DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month';

-- 前月の終了日
SELECT DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 day';

DATE_TRUNC('month', CURRENT_DATE)は現在の月の最初の日(1日)を取得します。
その一月前が前月の1日、前日が前月の末日となります。

WHEREでフィルタリング

取得したいデータの範囲をBETWEENを使って指定します。

SELECT 
    SUM(total_amount) AS total_sales,
    COUNT(*) AS total_orders
FROM 
    orders
WHERE 
    order_date BETWEEN 
        DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month' AND
        DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 day';

注文データの入っているordersテーブルより、total_amountの合計をSUM(total_amount)でtotal_salesとして、注文数をCOUNT(*)でtotal_ordersとして集計しています。
この時order_dateはTIMESTAMPやDATE型のカラムである必要があります。

クエリ全体の例

usersテーブルをcreated_atで集計し、前月の新規ユーザー数を取得します。

SELECT 
    DATE_TRUNC('month', created_at) AS month,
    COUNT(*) AS new_users
FROM 
    users
WHERE 
    created_at BETWEEN 
        DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month' AND
        DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 day'
GROUP BY 
    month
ORDER BY 
    month;

月ごとの集計例

前月だけでなくて複数の月分のデータを集計するにはGROUP BYで月単位にします。

SELECT 
    TO_CHAR(DATE_TRUNC('month', order_date), 'YYYY-MM') AS month,
    SUM(total_amount) AS total_sales
FROM 
    orders
WHERE 
    order_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '6 months'
GROUP BY 
    DATE_TRUNC('month', order_date)
ORDER BY 
    month;

DATE_TRUNCに'month'を指定して月単位にしましたが、'day'を指定すると日単位、'hour'で時間単位の集計もできます。
用途によってどの精度にするかは検討しながら使ってみます。
データベースはデータの専門家です。任せられることは専門に任せて、いいプログラムを書きたいです。

読んでくださってありがとうございました。

それではまた！