一つのテーブルに対してWhere句のパラメータを変えたもの同士で計算したい

Question

前提・実現したいこと

顧客ID,購入日,購入回数,商品コード,枝番,商品名という項目で構成された購入回数テーブルがあります。
この購入回数というのは、同じ顧客IDで複数回購入した場合のN回目という値が入っています。

|行数|顧客ID|購入日|購入回数|商品名|
|:--|:--:|--:|
|1|001|2020/01/01|1|AAA
|2|001|2020/01/10|2|CCC
|3|002|2020/01/15|1|BBB
|4|002|2020/01/18|2|CCC
|5|003|2020/01/20|1|DDD
|6|003|2020/01/25|2|EEE

このテーブルから同じ顧客IDで購入回数2回目と1回目の差の平均を求めたいのですが、
自己結合してサブクエリを作るなど模索してみたものの
どのようにSQLを書いてよいのかがわかりません。
テーブルのレコード数としては118万行ほどです。

上記のデータを元にした場合、得たい結果としては下記のようになります。

顧客ID001の２レコード目と１レコード目の差
select 購入日 from 購入回数テーブル where 顧客ID='001' and 購入回数=2
の購入日から
select 購入日 from 購入回数テーブル where 顧客ID='001' and 購入回数=1
の購入日の日数の差を出す。上記の例では９日

２回商品を購入している顧客すべてに対して上記の差を求めて平均を出す
ID002の１回目と２回目の差→３日
ID003の１回目と２回目の差→５日

3人の顧客の１回目と２回目の差の日数の平均→(9+3+5)/3→5.667

こういった場合、どのような書き方をすればよいのでしょうか。
よろしくお願いします。

###環境
Macローカルpostgresql（pgadmin）

Comments

[m.ts10806]

PostgreSQLのバージョンと
テーブル定義（CREATE TABLE文）をご提示ください。

[shintaro1001]

ポスグレのバージョンはPostgreSQL 13.4

テーブル情報は必要なカラムに限定して、カラム名は企業情報などを特定できないように一般名称化しています。

CREATE TABLE IF NOT EXISTS public.購入回数テーブル
(
顧客ID character varying(10) COLLATE pg_catalog."default",
"購入日" date,
"購入回数" integer,
"商品コード" character varying(4) COLLATE pg_catalog."default",
"枝番" character varying(2) COLLATE pg_catalog."default",
"商品名" character varying(20) COLLATE pg_catalog."default",
)

よろしくお願いします。

[mayu-]

> 顧客ID,購入日,購入回数,商品コード,枝番,商品名

というフィールド構成の表において
構成に合致しないデータ例を掲載された理由は何でしょう。

SQLの記述に重要な要素である「 列 」を
"ご自身の判断で" 省略して掲載なさったりすると
ミスリードを誘発する原因になりかねないと思います。

また、"購入回数テーブル" という名前からすると
読み手は、[ 顧客ID + 購入回数 ]で行がユニークになる
と考えるのが自然ではないでしょうか。少なくとも私はそう感じます。

しかし、それならなぜ[ 枝番 ]という列がテーブルに存在するのか、という疑問が出てきます。
[ 顧客ID + 購入回数 ]で行がユニークになるのでしたら、どう考えても不要なカラムですよね。

[ 顧客ID + 購入回数 ]　　　　では行がユニークにならず
[ 顧客ID + 購入回数 + 枝番 ] だと行の特定( 顧客ごとの比較 )が可能
ということなのでしたら
回答してくださったお二方のSQLに少し記述を加えることでご希望の結果になるでしょう。

■ gentaroさんのSQL

[現在]：
WHERE 購入回数=2
WHERE 購入回数=1

[変更後]：
WHERE 購入回数=2 and 枝番='1'
WHERE 購入回数=1 and 枝番='1'

■ takanaweb5さんのSQL

[現在]：
HAVING COUNT(*) = 2

[変更後]：
HAVING COUNT( distinct 購入日 ) = 2

[shintaro1001]

> SQLの記述に重要な要素である「 列 」を
> "ご自身の判断で" 省略して掲載なさったりすると
> ミスリードを誘発する原因になりかねないと思います。
ご指摘ありがとうございます。まさに自分の判断で省略してしまっておりました。
結論を申し上げると[ 顧客ID + 購入回数 ]でユニークになります。
枝番は商品をグループ分けするために付与されたものとなります。
回答いただいた方々、それぞれの方法で、求めたい値が得られることを確認しました。

[mayu-]

そうでしたか。杞憂に過ぎなかったようなので本当に良かったです。
回答がついた後、日を跨いでも未解決だったため
データ構造についてはけっこう疑念を抱いていました。ご容赦下さい。

[m.ts10806]

質問本文に記載してください。

Answer 1

サンプルデータ

INSERT INTO 購入回数テーブル
    (顧客ID, 購入日, 購入回数)
VALUES
    (001, '2020-01-01', 1),
    (001, '2020-01-10', 2),
    (001, '2020-01-11', 3), -- 購入回数>2のため計算の対象外となるレコード
    (002, '2020-01-15', 1),
    (002, '2020-01-18', 2),
    (003, '2020-01-20', 1),
    (003, '2020-01-25', 2),
    (004, '2020-01-20', 1),
    (004, '2020-01-20', 2), -- 同一日に2回購入したケース
    (005, '2020-01-20', 1)  -- 2回目の購入がないため対象外となるレコード
;

以下のSQLを実行することで、顧客IDごとの１回目と２回目の差の日数を一覧化出来ます。

SELECT
  顧客ID
, MAX(購入日) - MIN(購入日) AS 経過日
--, MIN(購入日) AS 一回目購入日 -- (購入回数が1と2のレコードのうち小さいほう)
--, MAX(購入日) AS 二回目購入日 -- (購入回数が1と2のレコードのうち大きいほう)
FROM 購入回数テーブル
WHERE 購入回数 IN(1,2) -- 対象のレコードを絞り込む
GROUP BY 顧客ID
HAVING 
-- HAVING句に条件を指定することで、1回しか購入していない顧客を対象外にする
 COUNT(*) = 2 --  あるいは MAX(購入回数) = 2 でも可
ORDER BY 顧客ID

結果

顧客id	経過日
1	9
2	3
3	5
4	0

求めたいのは上記結果の平均であるため、全体のSQLは以下となります。

SELECT AVG(経過日)
FROM
 (SELECT MAX(購入日) - MIN(購入日) AS 経過日
  FROM 購入回数テーブル
  WHERE 購入回数 IN(1,2)
  GROUP BY 顧客ID
  HAVING COUNT(*) = 2
  ) AS T

Comments

[shintaro1001]

ありがとうございます。
一旦ユーザーごとの平均を出した上で全体の平均も求められるので、応用が効きました。

Answer 2

SELECT AVG(A.購入日 - B.購入日)
FROM (
    select 顧客ID, 購入日 from 購入回数テーブル WHERE 購入回数=2
) AS A
INNER JOIN (
    select 顧客ID, 購入日 from 購入回数テーブル WHERE 購入回数=1
) AS B
ON A.顧客ID = B.顧客ID;

Comments

[gentaro]

「購入日」がdate型を使っているという前提の回答。

[shintaro1001]

ありがとうございました。