一つ後ろのデータの値を取得する方法を知りたい

Question

SPでの購入率が高いため、施策によってSPでの購入が増えたか確認したいです。
下記のようなデータがあった場合、日別でPCを使っていたユーザーがどれくらいSPへ転換したか確認したいです。
一つ後以降のデータを取得する方法、SPという特定の値が入った場合どのようにフラグをつけるのか
わかっていないので教えていただきたいです。

もし不明な点があればコメントを頂けると嬉しいです。
Bigquery を使用しておりますが、SQLも近しい言語なのでタグを付けさせていただいております。
よろしくお願い致します。

データイメージ

userID	購入日	購入端末
aaaa	2021-12-01	PC
aaaa	2021-12-02	SP
aaaa	2021-12-03	SP
aaaa	2021-12-03	PC
bbbb	2021-12-02	PC
bbbb	2021-12-03	PC
bbbb	2021-12-0４	SP

ほしいアウトプット

|購入日|PC購入数|2021-12-01|2021-12-02|2021-12-03|2021-12-0４|
|:--|:--:|:--:|：--:|--:|
|2021-12-01|1|1|1|1|０|
|2021-12-0２|1|0|０|０|1|

Comments

[neko_the_shadow]

①「ほしいアウトプット」の縦軸はユーザIDですか?
② userID=aaaa、購入日=2021-12-03のように同じ日に購入端末がPCとSPになっているものはどのように処理しますか?

[YT0014]

タグ「データベーススペシャリスト」は該当の資格の為のタグなので、削除されることをお勧めします。
同じくタグ「データマイニング」も関連が薄いと思われるので、削除が妥当と思われます。
併せてタグ「データ構造」は、DBから取得したデータの管理方法の為のタグなので、こちらも削除を。

[aiueo122]

タグを削除いたしました。

[aiueo122]

>neko_the_shadow様
ご確認ありがとうございます！
① 「ほしいアウトプット」の縦軸はユーザIDですか?
セル内の数字はユーザーIDです
② userID=aaaa、購入日=2021-12-03のように同じ日に購入端末がPCとSPになっているものはどのように処理しますか?
　→SPを使っているのでSPにカウントしたいです。

Answer 1

1. useridごとにPCで最初に購入した日を求める
2. PCで最初に購入した日以降にSPで購入した日をuseridごとに求める
3. ピボットテーブル形式で表示する

やりたいことは↑であっていますか? あっているなら、たとえば以下のようなSQLで実現できると思います。

SELECT 
  T1.PC購入日,
  COUNT(CASE WHEN T2.購入日 = '2021-12-01' THEN T1.userid END) AS "2021-12-01",
  COUNT(CASE WHEN T2.購入日 = '2021-12-02' THEN T1.userid END) AS "2021-12-02",
  COUNT(CASE WHEN T2.購入日 = '2021-12-03' THEN T1.userid END) AS "2021-12-03",
  COUNT(CASE WHEN T2.購入日 = '2021-12-04' THEN T1.userid END) AS "2021-12-04"
FROM (
  SELECT userid, MIN(購入日) AS PC購入日
  FROM tbl
  WHERE 購入端末 = 'PC'
  GROUP BY userid
) AS T1
JOIN tbl AS T2 ON T1.userid = T2.userid AND T1.PC購入日 < T2.購入日
WHERE T2.購入端末 = 'SP'
GROUP BY T1.PC購入日

---

2021/12/23 10:20 追記

「その日に使われたPC経由の注文数」
「列の12月1日〜12月7日」
をいれる方法はありますでしょうか？

やり方はいろいろあると思いますが、たとえば以下のようなSQLはどうでしょうか?

SELECT 
  T1.PC購入日,
  (SELECT COUNT(*) FROM tbl T3 WHERE T1.PC購入日 = T3.購入日 AND 購入端末 = 'PC') AS PC購入数,
  COUNT(CASE WHEN T2.購入日 = '2021-12-01' THEN T1.userid END) AS "2021-12-01",
  COUNT(CASE WHEN T2.購入日 = '2021-12-02' THEN T1.userid END) AS "2021-12-02",
  COUNT(CASE WHEN T2.購入日 = '2021-12-03' THEN T1.userid END) AS "2021-12-03",
  COUNT(CASE WHEN T2.購入日 = '2021-12-04' THEN T1.userid END) AS "2021-12-04",
  COUNT(CASE WHEN T2.購入日 = '2021-12-05' THEN T1.userid END) AS "2021-12-05",
  COUNT(CASE WHEN T2.購入日 = '2021-12-06' THEN T1.userid END) AS "2021-12-06",
  COUNT(CASE WHEN T2.購入日 = '2021-12-07' THEN T1.userid END) AS "2021-12-07"
FROM (
  SELECT userid, MIN(購入日) AS PC購入日
  FROM tbl
  WHERE 購入端末 = 'PC'
  GROUP BY userid
) AS T1
JOIN tbl AS T2 ON T1.userid = T2.userid AND T1.PC購入日 < T2.購入日
WHERE T2.購入端末 = 'SP'
GROUP BY T1.PC購入日

Comments

[aiueo122]

ありがとうございます。
できました。
ちなみにこの結果の左側のカラムに
「その日に使われたPC経由の注文数」
「列の12月1日〜12月7日」

をいれる方法はありますでしょうか？
質問も修正致します。

[neko_the_shadow]

「その日に使われたPC経由の注文数」というのは、PCで購入するのが初めてではない場合もカウントしますか? たとえばユーザccccがいて、2021-12-01と2021-12-02にPCで購入していたとします。このとき2021-12-02のPCでの購入は「その日に使われたPC経由の注文数」にカウントしますか?

[aiueo122]

カウントします。