postgresで直SQLを書くことになったので復習してみた - 集約周り

ORMではなく、直接SQLを書く必要が出てきたので復習してみた。
要件としては以下のような感じです。

売上テーブル（sales_amounts）

以下のフィールドを持っている

顧客ID(client_id): number
金額(amount): number
対象日時[何日分の売上か] (target_date): date

要件

前月分の売上と過去三ヶ月分の売上平均の比率を出したい
売上の推移を確認する目的

仕様

金額は月ごとに積み上げ形式
シンプルに月で集約して最大値を取ってくれば月の売上は取得できる

SQL

前月の売上

target_dateで絞り込む
顧客IDとtarget_dateを年月で切ったもので集約
金額の最大値を取得

    SELECT
        client_id,
        to_char(target_date, 'YYYY-MM') as ym,
        MAX(amount)
    FROM sales_amounts
    WHERE target_date >= date_trunc('month', now()) - interval '1 month'
    AND target_date < date_trunc('month', now())
    GROUP BY client_id, ym

（現在月を含まない）過去３ヶ月分の売上の平均

まず過去３ヶ月分の売上を取得する。前月売上同様年月でグルーピングした状態にする

SELECT
    client_id,
    to_char(target_date, 'YYYY-MM') as ym,
    MAX(amount)
FROM sales_amounts
WHERE target_date >= date_trunc('month', now()) - interval '3 month'
AND target_date < date_trunc('month', now())
GROUP BY client_id, ym

上記をサブクエリとして、顧客IDで集約する
さらに平均を出す

    SELECT
        client_id,
        round(avg(amount)) as total_amount_avg_3month
    FROM
        (
          SELECT
              client_id,
              to_char(target_date, 'YYYY-MM') as ym,
              MAX(amount) as amount
          FROM sales_amounts
          WHERE target_date >= date_trunc('month', now()) - interval '3 month'
          AND target_date < date_trunc('month', now())
          GROUP BY client_id, ym
       )
     GROUP BY client_id

上記２つのSQLを更にサブクエリにしてjoinする
売上の上昇率と下降率両方を取得するため　直近/過去３ヶ月 、 過去３ヶ月/直近 の値を出す

最終形はこんな感じ

SELECT
  A.client_id,
  round(((NULLIF(B.last_amount, 0) / NULLIF(A.amount_avg_3month, 0) - 1) * 100)) as ratio_last_to_3month,
  round(((NULLIF(A.amount_avg_3month, 0) / NULLIF(B.last_amount, 0) - 1) * 100)) as ratio_3month_to_last,
FROM


(
  SELECT
    client_id,
    round(avg(amount)) as amount_avg_3month
  FROM
      (
        SELECT
          client_id,
          to_char(target_date, 'YYYY-MM') as ym,
          MAX(amount) as amount
        FROM sales_amounts
        WHERE target_date >= date_trunc('month', now()) - interval '3 month'
        AND target_date < date_trunc('month', now())
        GROUP BY client_id, ym
      ) as three_month_sales_amounts
  GROUP BY client_id
) as A



INNER JOIN
(
  SELECT
    client_id,
    to_char(target_date, 'YYYY-MM') as ym,
    MAX(amount) as last_amount
  FROM sales_amounts
  WHERE target_date >= date_trunc('month', now()) - interval '1 month'
  AND target_date < date_trunc('month', now())
  GROUP BY client_id, ym
  ORDER BY client_id, ym
) as B


ON A.client_id = B.client_id

注意点

今回のクエリはアプリケーション経由で叩かれるものではないのでパフォーマンスの考慮はしていない（あとでやる）

最後に

自分で振り返ったときに何を取得しようとしていたかがわからなくなりそうなので、途中経過を記載しておくのは良いと思った
とはいえあんまりSQLを直接書くのは極力さけたい（今回は特殊な事情による）