【Oracle】LEFT JOINにおける結合条件

Question

実現したいこと

サイトのアクセスログからURLのみを抽出し、最終的にURLごとの合計値を出力したいです。
集計の元になるViewを作成し、SQLを実行して出力しようと考えています。
困っているのはVIEWの作成です。

アクセスログが格納されているテーブル(ACCESS_LOG)にはアクセス先のコンテンツやファイル名を含んだURLが保持されていますが、コンテンツやファイル名は不要なので"/"で区切り、サイト一覧(SITE_ALL)から一致する値をURL_sinとして持たせたいです。

---

ACCESS_LOG

ID	URL	TIME
A123	A123/sample/test.doc	2024/12/1 12:00
A123	A123/sample/test2.pdf	2024/12/1 12:05
A123	A123/sample/sub/test3.txt	2024/12/1 12:10
B456	B456/sample/test4.xlsx	2024/12/1 12:30
B456	B456/sample/sub/list/test5.png	2024/12/1 12:45
B456	B456/sample/sub/sub/doclib/test6.pdf	2024/12/1 12:50

SITE_ALL

NAME	URL
サイトA1	https://A123/sample
サイトA2	https://A123/sample/sub
サイトB1	https://B456/sample
サイトB2	https://B456/sample/sub
サイトB3	https://B456/sample/sub/sub

作成したいView

ID	URL	sub1	sub2	sub3	URL_sin	TIME
A123	https://A123/sample/test.doc	https://A123/sample	https://A123	https://A123	https://A123/sample	2024/12/1 12:00
A123	https://A123/sample/test2.pdf	https://A123/sample	https://A123/	https://A123	https://A123/sample	2024/12/1 12:05
A123	https://A123/sample/sub/test3.txt	https://A123/sample	https://A123/sample/sub	https://A123	https://A123/sample/sub	2024/12/1 12:10
B456	https://B456/sample/test4.xlsx	https://B456/sample	https://B456	https://B456	https://B456/sample	2024/12/1 12:30
B456	https://B456/sample/sub/list/test5.png	https://B456/sample	https://B456/sample/sub	https://B456/sample/sub/list	https://B456/sample/sub	2024/12/1 12:45
B456	https://B456/sample/sub/sub/doclib/test6.pdf	https://B456/sample	https://B456/sample/sub	https://B456/sample/sub/sub	https://B456/sample/sub/sub	2024/12/1 12:50

最終的にSQLで出力したいデータ

URL	アクセス数
https://A123/sample	2
https://A123/sample/sub	1
https://B456/sample	1
https://B456/sample/sub	1
https://B456/sample/sub/sub	1

発生している問題・分からないこと

ACCESS_LOGとSITE_ALLでキーになる項目がないため、ACCESS_LOGの項目に「https://」を結合し、SUBSTRを使って“/”で抜き出し、この文字列(sub1~3)をキーとして使っています。
また、CASEを使ってsub3→2→1の順に比較してSITE_ALLに存在する値をURL_sinに表示します。

現在はLEFT JOINのON句でORを使っているのですが、ORだと必要のないデータまで表示されてしまい、実際の件数よりも多く表示されてしまいます。
CASEのように、sub3と一致するURL→なければsub2と一致するURL→なければsub1と一致するURLというふうに結合したいです。

また、文字列を結合して使っているのでコードの見栄えが悪く、他の記載方法はありますでしょうか？

作成したいView(一例)

ID	URL	sub1	sub2	sub3	URL_sin	TIME
B456	https://B456/sample/sub/sub/doclib/test6.pdf	https://B456/sample	https://B456/sample/sub	https://B456/sample/sub/sub	https://B456/sample/sub/sub	2024/12/1 12:50

現在のView表示(一例)

ID	URL	sub1	sub2	sub3	URL_sin	TIME
B456	https://B456/sample/sub/sub/doclib/test6.pdf	https://B456/sample	https://B456/sample/sub	https://B456/sample/sub/sub	https://B456/sample	2024/12/1 12:50
B456	https://B456/sample/sub/sub/doclib/test6.pdf	https://B456/sample	https://B456/sample/sub	https://B456/sample/sub/sub	https://B456/sample/sub	2024/12/1 12:50
B456	https://B456/sample/sub/sub/doclib/test6.pdf	https://B456/sample	https://B456/sample/sub	https://B456/sample/sub/sub	https://B456/sample/sub/sub	2024/12/1 12:50

↑本当は1行だけでいいのに、3行表示されてしまう

該当のソースコード

CREATE OR REPLACE VIEW ACCESS_LOG_V
AS
SELECT AL.ID
      ,AL.URL
      ,'https://' || SUBSTR(AL.URL,1,INSTR(AL.URL,'/',1,1)-1) AS “sub1”
      ,'https://' || SUBSTR(AL.URL,1,INSTR(AL.URL,'/',1,2)-1) AS “sub2”
      ,'https://' || SUBSTR(AL.URL,1,INSTR(AL.URL,'/',1,3)-1) AS “sub3”
      ,CASE
         WHEN 'https://' || SUBSTR(AL.URL,1,INSTR(AL.URL,'/',1,3)-1) = SA.URL THEN SA.URL
         WHEN 'https://' || SUBSTR(AL.URL,1,INSTR(AL.URL,'/',1,2)-1) = SA.URL THEN SA.URL
         WHEN 'https://' || SUBSTR(AL.URL,1,INSTR(AL.URL,'/',1,1)-1) = SA.URL THEN SA.URL
         ELSE 'no'
       END AS “URL_sin“
      ,AL.TIME
FROM ACCESS_LOG AL
LEFT JOIN SITE_ALL SA
ON 'https://' || SUBSTR(AL.URL,1,INSTR(AL.URL,'/',1,3)-1) = SA.URL
OR 'https://' || SUBSTR(AL.URL,1,INSTR(AL.URL,'/',1,2)-1) = SA.URL
OR 'https://' || SUBSTR(AL.URL,1,INSTR(AL.URL,'/',1,1)-1) = SA.URL

試したこと・調べたこと

• teratailやGoogle等で検索した
• ソースコードを自分なりに変更した
• 知人に聞いた
• その他

上記の詳細・結果

結合条件について調べましたが、ORとAND以外の使い方が見つけられませんでした。
ANDではURL_sinが“no”になってしまうので、今はORを使っています。

補足

ACCESS_LOGのデータがかなり多く、結合した際の負荷が少なくなるように作成したいです。
また、SITE_ALLにACCESS_LOGと共通するIDを追加することは可能です。自分で作成した際はIDの紐付けはうまくいかず追加していない状況です。

Answer 1

「最終的にSQLで出力したいデータ」を実現したいのであれば、ビューをつくらずとも、以下のような再帰SQLを活用したクエリにて実現可能です。

WITH A (id, url, time, lvl, url_sin) AS (
        SELECT id, url, time, 0, NULL FROM access_log
    UNION ALL
        SELECT A.id, A.url, A.time, A.lvl + 1, SA.url
        FROM A
        LEFT OUTER JOIN site_all SA ON ('https://' || SUBSTR(A.url, 1, INSTR(A.url, '/', -1, A.lvl + 1) - 1)) = SA.url
        WHERE INSTR(A.url, '/', -1, A.lvl + 1) > 0 AND url_sin IS NULL
)
SELECT url_sin, COUNT(*)
FROM A
WHERE url_sin IS NOT NULL
GROUP BY url_sin

ただデータ量を考慮する必要がある、要件がやや複雑であるなどを考えると、SQLで実行するよりも、バッチ処理などで処理したほうが簡単かつ保守性も高そうな気はします。

なお稼働確認はOralce Database 19cにて行いました。データセットは以下の通りです。

CREATE TABLE access_log (id NVARCHAR2(4), url NVARCHAR2(100), time DATE);
INSERT INTO access_log VALUES ('A123','A123/sample/test.doc',TO_DATE('2024/12/1 12:00', 'YYYY/MM/DD HH:MI'));
INSERT INTO access_log VALUES ('A123','A123/sample/test2.pdf',TO_DATE('2024/12/1 12:05', 'YYYY/MM/DD HH:MI'));
INSERT INTO access_log VALUES ('A123','A123/sample/sub/test3.txt',TO_DATE('2024/12/1 12:10', 'YYYY/MM/DD HH:MI'));
INSERT INTO access_log VALUES ('B456','B456/sample/test4.xlsx',TO_DATE('2024/12/1 12:30', 'YYYY/MM/DD HH:MI'));
INSERT INTO access_log VALUES ('B456','B456/sample/sub/list/test5.png',TO_DATE('2024/12/1 12:45', 'YYYY/MM/DD HH:MI'));
INSERT INTO access_log VALUES ('B456','B456/sample/sub/sub/doclib/test6.pdf',TO_DATE('2024/12/1 12:50', 'YYYY/MM/DD HH:MI'));


CREATE TABLE site_all (name NVARCHAR2(100), url NVARCHAR2(100));
INSERT INTO site_all VALUES('サイトA1','https://A123/sample');
INSERT INTO site_all VALUES('サイトA2','https://A123/sample/sub');
INSERT INTO site_all VALUES('サイトB1','https://B456/sample');
INSERT INTO site_all VALUES('サイトB2','https://B456/sample/sub');
INSERT INTO site_all VALUES('サイトB3','https://B456/sample/sub/sub');

Comments

[sazi]

> SQLで実行するよりも、バッチ処理などで処理したほうが簡単かつ保守性も高そうな気はします。
利用頻度が高いなら、log書込み時にカウントもする方が、コスト的には最も効率的でしょうね

[ika_kani]

みなさま、ご回答いただきありがとうございます。また、回答いただいていたのになかなかお返事できず申し訳ありません。

バッチ処理で出力したことが無かったので、ぜひ経験としても試してみたいと思っております。
こちらの理解が追いついておらず申し訳ないのですが、バッチで処理する場合csvで出力されるのでしょうか？

[neko_the_shadow]

> SQLで実行するよりも、バッチ処理などで処理したほうが簡単かつ保守性も高そうな気はします

ここでの意図は「複雑なSQLを作るより、何らかのプログラミング言語で処理したり、csvにエクスポートした後にExcelで集計したりしたほうが、いろいろとメリットが大きい可能性がある」ということでした。わたしの書き方が誤解を招く書き方になっていました。申し訳ありません。

プログラミング言語からOracle Databaseに接続する方法やcsvにエクスポートする方法はWeb上にいくらでもサンプルがあると思いますので、調べていただければよいかと思います。

Answer 2

サイトのURLの階層関係を浅い順に「A，B，C」という集合で表すと
「A　＞　B　＞　C」となってAにはBもCも含まれています。
それをORで抽出すると結果的にA全体を求める事になります。
やりたいことは、
・Bを除いたAの件数
・Cを除いたBの件数
・Cのみの件数
ですので、それぞれで件数を求めてUNION等を用いて一つの表にすれば良いと思います。

Comments

[ika_kani]

お世話になっております。
Unionを使った方法も試しているところなのですが、「Bを除いたAの件数」という条件式がなかなかできません。実現方法あればご教示いただきたく、よろしくお願いします。

Answer 3

パフォーマンスはさておき、
DISTINCT句でどうでしょうか？

CREATE OR REPLACE VIEW ACCESS_LOG_V
AS
SELECT DISTINCT AL.ID --「DISTINCT」をSELECT句の次に入れる
      ,AL.URL
      ,'https://' || SUBSTR(AL.URL,1,INSTR(AL.URL,'/',1,1)-1) AS “sub1”
      ,'https://' || SUBSTR(AL.URL,1,INSTR(AL.URL,'/',1,2)-1) AS “sub2”
      ,'https://' || SUBSTR(AL.URL,1,INSTR(AL.URL,'/',1,3)-1) AS “sub3”
      ,CASE
         WHEN 'https://' || SUBSTR(AL.URL,1,INSTR(AL.URL,'/',1,3)-1) = SA.URL THEN SA.URL
         WHEN 'https://' || SUBSTR(AL.URL,1,INSTR(AL.URL,'/',1,2)-1) = SA.URL THEN SA.URL
         WHEN 'https://' || SUBSTR(AL.URL,1,INSTR(AL.URL,'/',1,1)-1) = SA.URL THEN SA.URL
         ELSE 'no'
       END AS “URL_sin“
      ,AL.TIME
FROM ACCESS_LOG AL
LEFT JOIN SITE_ALL SA
ON 'https://' || SUBSTR(AL.URL,1,INSTR(AL.URL,'/',1,3)-1) = SA.URL
OR 'https://' || SUBSTR(AL.URL,1,INSTR(AL.URL,'/',1,2)-1) = SA.URL
OR 'https://' || SUBSTR(AL.URL,1,INSTR(AL.URL,'/',1,1)-1) = SA.URL