<p>ORMではなく、直接SQLを書く必要が出てきたので復習してみた。<br>要件としては以下のような感じです。<br></p><h2 id="he7a3e9e48e">売上テーブル（sales_amounts）</h2><p>以下のフィールドを持っている</p><ul><li>顧客ID(client_id): number</li><li>金額(amount): number</li><li>対象日時[何日分の売上か] (target_date): date</li></ul><p><br></p><h2 id="ha31bd80de1">要件</h2><ul><li>前月分の売上と過去三ヶ月分の売上平均の比率を出したい</li><li>売上の推移を確認する目的</li></ul><p><br></p><h2 id="h00b8af9eb7">仕様</h2><ul><li>金額は月ごとに積み上げ形式</li><li>シンプルに月で集約して最大値を取ってくれば月の売上は取得できる</li></ul><p><br></p><h2 id="h3dfbe92010">SQL</h2><h3 id="h6494a94420">前月の売上</h3><ul><li>target_dateで絞り込む</li><li>顧客IDとtarget_dateを年月で切ったもので集約</li><li>金額の最大値を取得</li></ul><pre><code>    SELECT
        client_id,
        to_char(target_date, 'YYYY-MM') as ym,
        MAX(amount)
    FROM sales_amounts
    WHERE target_date &gt;= date_trunc('month', now()) - interval '1 month'
    AND target_date &lt; date_trunc('month', now())
    GROUP BY client_id, ym</code></pre><p><br></p><h3 id="h42c28cc452">（現在月を含まない）過去３ヶ月分の売上の平均</h3><p>まず過去３ヶ月分の売上を取得する。前月売上同様年月でグルーピングした状態にする</p><pre><code>SELECT
    client_id,
    to_char(target_date, 'YYYY-MM') as ym,
    MAX(amount)
FROM sales_amounts
WHERE target_date &gt;= date_trunc('month', now()) - interval '3 month'
AND target_date &lt; date_trunc('month', now())
GROUP BY client_id, ym</code></pre><p><br>上記をサブクエリとして、顧客IDで集約する<br>さらに平均を出す</p><pre><code>    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 &gt;= date_trunc('month', now()) - interval '3 month'
          AND target_date &lt; date_trunc('month', now())
          GROUP BY client_id, ym
       )
     GROUP BY client_id</code></pre><p><br>上記２つのSQLを更にサブクエリにしてjoinする<br>売上の上昇率と下降率両方を取得するため　<code>直近/過去３ヶ月</code>  、 <code>過去３ヶ月/直近</code>  の値を出す<br><br>最終形はこんな感じ</p><pre><code>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 &gt;= date_trunc('month', now()) - interval '3 month'
        AND target_date &lt; 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 &gt;= date_trunc('month', now()) - interval '1 month'
  AND target_date &lt; date_trunc('month', now())
  GROUP BY client_id, ym
  ORDER BY client_id, ym
) as B


ON A.client_id = B.client_id</code></pre><p><br></p><h2 id="h86c1eb6dfa">注意点</h2><ul><li>今回のクエリはアプリケーション経由で叩かれるものではないのでパフォーマンスの考慮はしていない（あとでやる）</li></ul><p><br></p><h2 id="h3bcda3e6b0">最後に</h2><ul><li>自分で振り返ったときに何を取得しようとしていたかがわからなくなりそうなので、途中経過を記載しておくのは良いと思った</li><li>とはいえあんまりSQLを直接書くのは極力さけたい（今回は特殊な事情による）</li></ul>

Tag: postgresql

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