SQL: 各ID毎に過去1年のイベント回数を、イベントの日付ごとに求めたい

Question

ご質問です。
「各IDごとに過去1年間に起きたイベント回数を、イベントの日付ごとに求める」という作業を考えています。
具体的には、以下のテーブルのfreq_past_1yearの列を求めるSQLクエリを、是非ご教示いただければ幸甚です。

なお上の例ですと、ID=Aは、
2015年1月1日…初回のイベントなので、過去のイベント0回
2015年2月3日…1年振り返ると、2015年1月1日の1回
2015年8月3日…1年振り返ると、2015年1月1日、2015年2月3日の2回
2016年2月1日…1年振り返ると、2015年2月3日、2015年8月3日の2回
となっています。
（うるう年の処理が困難な場合は、365日と定義しても差し支えないと考えています）

＜2020/11/29追記＞
サンプルデータは以下で作成いたしました。

CREATE TABLE TBL
    ("ID" varchar2(1), "EVENT_DATE" timestamp)
;

INSERT ALL 
    INTO TBL ("ID", "EVENT_DATE")
         VALUES ('A', '01-Jan-2015 12:00:00 AM')
    INTO TBL ("ID", "EVENT_DATE")
         VALUES ('A', '03-Feb-2015 12:00:00 AM')
    INTO TBL ("ID", "EVENT_DATE")
         VALUES ('A', '03-Aug-2015 12:00:00 AM')
    INTO TBL ("ID", "EVENT_DATE")
         VALUES ('A', '01-Feb-2016 12:00:00 AM')
    INTO TBL ("ID", "EVENT_DATE")
         VALUES ('B', '01-Apr-2013 12:00:00 AM')
    INTO TBL ("ID", "EVENT_DATE")
         VALUES ('B', '01-May-2014 12:00:00 AM')
    INTO TBL ("ID", "EVENT_DATE")
         VALUES ('B', '31-Mar-2015 12:00:00 AM')
SELECT * FROM dual
;

<11/29 追記2>
@gpsoft様のクエリを参考にさせていただきました。

SELECT A.ID
     , A.EVENT_DATE
     , COUNT(B.ID) AS FREQ_PAST_1YEAR
FROM Tbl A
LEFT JOIN Tbl B
ON    A.ID = B.ID
  AND B.EVENT_DATE < A.EVENT_DATE
  AND B.EVENT_DATE > ADD_MONTHS(A.EVENT_DATE, -12)
GROUP BY A.ID, A.EVENT_DATE
ORDER BY A.ID, A.EVENT_DATE
;

@gpsoft様、@sazi様、@Orlofsky様
初心者にもかかわらずご丁寧に教えていただき、大変勉強になりました。
今後質問させていただく時は、Markdownやタグ付けの徹底など留意させていただきます。
どうぞよろしくお願い申し上げます。

Comments

[sazi]

freq_past_1yearの定義はどうなっていますか？
過去1年に限定しているなら、年と年度の何れでしょうか？
※サンプルが合っていない
それから、event_dateの型は何ですか？

[mochiwo]

ご指摘ありがとうございます。
「freq_past_1yearの定義はどうなっていますか？
過去1年に限定しているなら、年と年度の何れでしょうか？」
こちら、365日前以内に含まれるならOKとしており、年度ではなく年で考えております。
event_dateの型はSQL Fiddle上でintで作成いたしました。

[Orlofsky]

日付関係は日付型に変更しましょう。日付型を使わずに日付ではないデータが入って来て破綻して大改造を強いられたシステムが絶えません。
https://docs.microsoft.com/ja-jp/sql/t-sql/functions/date-and-time-data-types-and-functions-transact-sql?view=sql-server-ver15

あと、この掲示板ではMarkdownを使ってください。
CREATE TABLE, INSERTは https://teratail.com/help/question-tips#questionTips3-7 の [コード] に修正してください。

[mochiwo]

@Orlofsky様
日付に変更するようにいたします。
Markdown形式に修正いたしました。

[sazi]

@Orlofskyさん
> 日付関係は日付型に変更しましょう。
あくまで要件次第だと思います。有効な日付で縛らない方が良い時もありますので。
この質問の用途だけなら、日付型の方が良いと思いますけど。

[mochiwo]

@Orlofsky様
@sazi様
申し訳ございません。
SQL serverとOracleを間違えてタグ付けしておりましたので、タグを修正いたしました。
お手数をおかけしてしまい誠に申し訳ございません。

Answer 1

回答は既に出ているので、DATE型データに使える関数の紹介。

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS' ;

セッションが変更されました。

SQL> SELECT
  2      A.TODAYS_DATE
  3    , A.TRUNCED_DATE
  4    , ADD_MONTHS(A.TRUNCED_DATE, -12) AS ONE_YEAR_AGO
  5    , ADD_MONTHS(A.TRUNCED_DATE, 12) AS ONE_YEAR_LATER
  6  FROM(
  7      SELECT
  8          SYSDATE AS TODAYS_DATE
  9        , TRUNC(SYSDATE) AS TRUNCED_DATE
 10      FROM DUAL
 11      ) A ;

TODAYS_DATE         TRUNCED_DATE        ONE_YEAR_AGO        ONE_YEAR_LATER
------------------- ------------------- ------------------- -------------------
2020/11/30 08:50:07 2020/11/30 00:00:00 2019/11/30 00:00:00 2021/11/30 00:00:00

頑張ってください。

Comments

[mochiwo]

@Orlofsky様
日付型データの処理方法をご教示いただき誠にありがとうございます。
ご指導いただいた方法を実解析へ応用していきたいと存じます。

Answer 2

質問の意図を読み違えていたので、訂正しました。
相関サブクエリーを用いています。

select  *
      , (
           select count(*) from tbl 
           where id = t1.id 
             and event_date >= t1.event_date -10000
             and event_date < t1.event_date  
        ) as freq_past_1year
from tbl t1
order by ID, event_date

※1年前の算出は単に単に年から-1しているだけですので、閏年などは考慮していません。
（1日分の誤差が生じる）
閏年の考慮が必要なら、日付型に変更して演算して下さい。

追記

質問に追記された内容を相関サブクエリーに変更してみました。

SELECT A.ID
     , A.EVENT_DATE
     , (select COUNT(ID) from tbl  
        where ID = A.ID
          AND EVENT_DATE < A.EVENT_DATE
          AND EVENT_DATE > ADD_MONTHS(A.EVENT_DATE, -12)
       ) AS FREQ_PAST_1YEAR
FROM Tbl A
;

Comments

[mochiwo]

大変詳細にご教示いただき誠にありがとうございます。
後日、実際の解析環境で試してみます。

[sazi]

質問のサンプル画像は間違っているという事でいいですか。
あ、event_dateの年の一年前って事ですね。
修正します。

[mochiwo]

修正いただき誠にありがとうございます。
後日、確認いたします。

[mochiwo]

@sazi様
誠に申し訳ございません。
SQL serverとOracleを間違えてタグ付けしておりました。
Oracleでも相関サブクエリを使うことは可能でしょうか？
ご面倒をおかけしてしまい大変恐縮です。

[mochiwo]

自分でもOracleへの変換を考えてみます。
ご迷惑をおかけしてしまい誠に申し訳ございませんでした。

[sazi]

相関サブクエリーは使えます。

[mochiwo]

@sazi様
相関サブクエリの使い方まで教えていただき誠にありがとうございます。
無事sazi様の方法でも、結果を得ることができました。
大変勉強になりました。

Answer 3

すみません、動かしてないので自信ないですが、ダメ元で…。

SELECT EV.ID, EV.event_date, COUNT(EV2.ID) AS freq_past_1year
FROM event EV
  LEFT JOIN event EV2
    ON EV2.ID=EV.ID
      AND EV2.event_date < EV.event_date
      AND EV2.event_date > DATEADD(year, -1, EV.event_date)
GROUP BY EV.ID, EV.event_date
ORDER BY EV.ID, EV.event_date

※テーブル名はeventと仮定してます。

※「過去1年間」の意味次第では、>を>=に変えるべきかも。

※event_dateの型次第では、DATEADD()がエラーになるかなぁ。

[追記]
event_dateの型がintとのことなので、クエリーの手直しが必要ですね。

CREATE TABLE event(ID varchar(1), event_date int);

INSERT INTO event(ID, event_date) VALUES
('A', 20150101)
,('A', 20150203)
,('A', 20150803)
,('A', 20160201)
,('B', 20130401)
,('B', 20140501)
,('B', 20150331);

SELECT EV.ID, EV.event_date, COUNT(EV2.ID) AS freq_past_1year
FROM event EV
  LEFT JOIN event EV2
    ON EV2.ID=EV.ID
      AND EV2.event_date < EV.event_date
      AND EV2.event_date > FORMAT(DATEADD(year, -1, DATEFROMPARTS(EV.event_date/10000, EV.event_date%10000/100, EV.event_date%100)), 'yyyyMMdd')
GROUP BY EV.ID, EV.event_date
ORDER BY EV.ID, EV.event_date;

以下、SQL Server 2012での実行結果です。

Comments

[mochiwo]

早速のご連絡誠にありがとうございます。
SQL Fiddleというサイトで、ご指導いただいたクエリを試してみたところ、
"EV"."EVENT_DATE": invalid identifier
というエラーが生じてしまいました。
原因おわかりでしょうか…？

[Orlofsky]

質問にCREATE TABLE文を載せないから適切なコメントが付きにくいことに気が付けるとちょっとした進歩なんだが。

[sazi]

@mochiwoさん
gpsoftさんは
> event_dateの型次第では、DATEADD()がエラーになる
と言われていますよ。

[mochiwo]

@Orlofsky様
@sazi様
ご指摘誠にありがとうございます。
わかりづらく申し訳ございません。
サンプルデータは、下記のように作成いたしました。

CREATE TABLE event
("ID" varchar2(1), "event_date" int)
;

INSERT ALL
INTO event ("ID", "event_date")
VALUES ('A', 20150101)
INTO event ("ID", "event_date")
VALUES ('A', 20150203)
INTO event ("ID", "event_date")
VALUES ('A', 20150803)
INTO event ("ID", "event_date")
VALUES ('A', 20160201)
INTO event ("ID", "event_date")
VALUES ('B', 20130401)
INTO event ("ID", "event_date")
VALUES ('B', 20140501)
INTO event ("ID", "event_date")
VALUES ('B', 20150331)
SELECT * FROM dual
;

ただ、私のサンプルデータの作り方や、ブラウザ上（SQL fiddle）で動かしているのが悪いためか、うまくいかないため、実際の解析環境で後日試してみます。
結果分かり次第、ご連絡いたします。

[Orlofsky]

CREATE TABLEとINSERTは質問に追加してください。

[mochiwo]

@ Orlofsky様
質問に追加いたしました。
どうぞご確認くだされば幸甚に存じます。

[gpsoft]

遅くなりましたが、最新情報を踏まえて、回答を編集しました。
コメントして頂いた皆さま、ありがとうございます。

[mochiwo]

@gpsoft様
大変丁寧にご教示いただき誠にありがとうございます。
実は私のミスで、SQL serverとOracleのタグ付を間違えておりました。
教えていただいた方法で、Oracleの書き方へ変更して試してみます。

[mochiwo]

@gpsoft様
望みの結果を得ることができました！
誠にありがとうございました。