SQLのクエリを教えてください

■やりたいこと
あるコンテンツ（例えば占い）の初回閲覧者が次回以降の更新される際に何人見ているのか調べたいです。
■やってみたこと
二つのテーブルがあります

（１）閲覧テーブルは
閲覧日毎のパーティションで区切られ、
ユーザーID（文字）,コンテンツID（文字）,partition（文字）で
コンテンツIDはジャンル（占いuranai、お出かけodekakeなど）の後ろに数字4桁がついています）

（２）コンテンツの更新テーブル
更新日（文字）　コンテンツ（文字）　コンテンツID（文字）のようなもので
20180416　お出かけ5月　odekake01234
20180516　お出かけ6月　odekake00342
20180618　お出かけ7月　odekake00358
20180420　占い5月　uranai00213
20180518　占い6月　uranai00433
20180620　占い7月　uranai00243

それに対して私は
------------------
select
count( a.ユーザーID）
from
(
--初回閲覧者
select
distinct ユーザーID
FROM
コンテンツの閲覧履歴のテーブル
where
partition between '2018-04-16'and'2018-05-15'and
content_id like '占い％’
）a
join
(
--次回閲覧者
select
distinct ユーザーID
FROM
コンテンツの閲覧履歴のテーブル
where
partition between '2018-05-16'and'2018-06-17'and
content_id like '占い％’
）b
on a.ユーザーID＝b.ユーザーID

--------------
このようなクエリを回しています。

次回の掲載期間は2018-05-16から2018-06-17ですが
その次は2018-06-18から2018-07-16のように
少しずれます。

次回のところの日付を変更してその都度クエリを回しています。
これを2年分、コンテンツ毎（占いのほかにお出かけ、グルメなど…）に回すのですが
もっと効率の良いやり方があれば教えてほしいです。

（例えば1個のクエリが終わったら次のクエリを回せるとか、コンテンツ毎にまとめて出力するなど）

よろしくお願いいたします。

No.1 ベストアンサー 回答者： kmee 回答日時： 2020/01/08 23:53

まず確認です。

○ 閲覧テーブル の 「コンテンツID」と 更新テーブルの「コンテンツID」って違うものなのですか?
普通は同じものにするのですが。


「がんばってこれをなんとかしよう」
とするのではなく、
「ここが違ってたら楽なのになぁ」
を考えるとよいのでは。

見ると
・コンテンツIDからコンテンツグループに分けるところ
・注目するコンテンツと、「その次」のコンテンツを、partition(日付) から betweenを使って分けているところ

が面倒になっている原因で、これがなんとかなれば楽になりそうだ、ということがわかります。
ならば
・グループが最初からわかるようにする。
・日付ではなく「順番」の整数を使えば「次」は「元+1」で求められる
とすれば、かなり楽になりますし、
・全コンテンツの集計が1度でできる
という利点もあります。

例えば。

・コンテンツの更新テーブルを変えます。
あるいは、追加列を別テーブルにしてJOINします。
更新日（文字）　コンテンツ（文字）　コンテンツID（文字） コンテンツグループ コンテンツ順番 閲覧テーブル_コンテンツID
20180416　お出かけ5月　odekake01234 odekake 1 お出かけodekake01234
20180516　お出かけ6月　odekake00342 odekake 2 お出かけodekake00342
20180618　お出かけ7月　odekake00358 odekake 3 お出かけodekake00358
20180420　占い5月　uranai00213 uranai 1 占いuranai00213
20180518　占い6月　uranai00433 uranai 2 占いuranai00433
20180620　占い7月　uranai00243 uranai 3 占いuranai00243

・これを閲覧テーブルを適切のJOINすれば、次のようなテーブルになります
ユーザーID コンテンツグループ コンテンツ順番
A001 uranai 1
A001 uranai 2

・これを 2 つJOINして
ON A.ユーザーID=B.ユーザーID AND A.コンテンツグループ=B.コンテンツグループ
AND A.コンテンツ順番 = B.コンテンツ順番-1
とでもやれば、全てのコンテンツのうち、「次も閲覧している」リストができます。

※ ざっとした流れだけを書いています。
正確なのは、学習もかねて御自身でやってみてください。

この回答へのお礼

ありがとうございます

お礼日時：2020/11/17 05:56