問題
あるサービスの会員向けに商品券を配布したとします。
商品券は、商品購入時の特典として配布されるため、会員によっては複数枚の商品券を受け取ります。
| 会員 | 受け取った商品券 | 使った商品券 |
|---|---|---|
| 田中 | 2 | 0 |
| 西本 | 3 | 2 |
| 佐藤 | 1 | 1 |
| 商品券の配布履歴と使用履歴はクーポン履歴テーブルに残っています。 | ||
| しかし、商品券にIDが振られていないため、どの商品券をいつ使ったかは分かりません。 |
| 会員 | 日付 | イベント |
|---|---|---|
| 田中 | 2017-11-05 | 配布 |
| 西本 | 2017-11-23 | 配布 |
| 佐藤 | 2017-12-01 | 配布 |
| 西本 | 2017-12-24 | 使用 |
| 佐藤 | 2018-01-07 | 使用 |
| 西本 | 2018-02-10 | 配布 |
| 田中 | 2018-03-04 | 配布 |
| 西本 | 2018-04-15 | 配布 |
| 西本 | 2018-04-16 | 使用 |
| このテーブルを作るSQLは以下の通りです。 |
クーポン履歴テーブルを作成
CREATE TABLE CouponHistory (
member nchar(8) NOT NULL,
date date NOT NULL,
event nvarchar(10) NOT NULL
)
INSERT INTO CouponHistory (member,date,event) VALUES ('田中','2017-11-05','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('西本','2017-11-23','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('佐藤','2017-12-01','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('西本','2017-12-24','使用')
INSERT INTO CouponHistory (member,date,event) VALUES ('佐藤','2018-01-07','使用')
INSERT INTO CouponHistory (member,date,event) VALUES ('西本','2018-02-10','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('田中','2018-03-04','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('西本','2018-04-15','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('西本','2018-04-16','使用')
クーポン履歴テーブルを元に、いつ配った商品券がいつ使われたのか、集計したいと思います。
ただし、先ほど書いた通り、どの商品券がいつ使われたのか厳密には分からないため、先に受け取った商品券から先に使ったと見做して集計する事とします。
■ 集計のルール
- 会員は先に受け取った商品券から先に使う
■ 作成する予定の集計表
| 配布年月 | 配布枚数 | 201712使用 | 201801使用 | 201802使用 | 201803使用 | 201804使用 |
|---|---|---|---|---|---|---|
| 2017-11 | 2 | 1 | 0 | 0 | 0 | 0 |
| 2017-12 | 1 | 0 | 1 | 0 | 0 | 0 |
| 2018-02 | 1 | 0 | 0 | 0 | 0 | 1 |
| 2018-03 | 1 | 0 | 0 | 0 | 0 | 0 |
| 2018-04 | 1 | 0 | 0 | 0 | 0 | 0 |
集計表の作成
１．配布した商品券と使用した商品券を紐づける。
まず、クーポン履歴テーブルから、配布履歴だけ抜き出してみます。
配布履歴を取り出す
SELECT
member,
date,
event
FROM
CouponHistory
■ 取得結果
| 会員 | 日付 | イベント |
|---|---|---|
| 田中 | 2017-11-05 | 配布 |
| 西本 | 2017-11-23 | 配布 |
| 佐藤 | 2017-12-01 | 配布 |
| 西本 | 2018-02-10 | 配布 |
| 田中 | 2018-03-04 | 配布 |
| 西本 | 2018-04-15 | 配布 |
| 分かりやすいように会員ごとに並べ直し、更に配布した順に会員単位で番号を振ります。 |
会員単位で配布順に番号を振る
SELECT
ROW_NUMBER() OVER(
PARTITION BY
member
ORDER BY
date ASC
) AS '配布順',
member,
date,
event
FROM
CouponHistory
WHERE
event = '配布'
ORDER BY
member ASC,
date ASC
■ 取得結果
| 配布順 | 会員 | 日付 | イベント |
|---|---|---|---|
| 1 | 佐藤 | 2017-12-01 | 配布 |
| 1 | 西本 | 2017-11-23 | 配布 |
| 2 | 西本 | 2018-02-10 | 配布 |
| 3 | 西本 | 2018-04-15 | 配布 |
| 1 | 田中 | 2017-11-05 | 配布 |
| 2 | 田中 | 2018-03-04 | 配布 |
| 同様のやり方で使用履歴も取得します。番号は商品券を使用した順に振ります。 |
使用履歴も取得する
SELECT
ROW_NUMBER() OVER(
PARTITION BY
member
ORDER BY
date ASC
) AS '使用順',
member,
date,
event
FROM
CouponHistory
WHERE
event = '使用'
ORDER BY
member ASC,
date ASC
■ 取得結果
| 使用順 | 会員 | 日付 | イベント |
|---|---|---|---|
| 1 | 佐藤 | 2018-01-07 | 使用 |
| 1 | 西本 | 2017-12-24 | 使用 |
| 2 | 西本 | 2018-04-16 | 使用 |
| ここで、配布履歴の（配布順,会員）と使用履歴の（使用順,会員）を紐付けてみます。 |
配布履歴と使用履歴を紐付ける
SELECT
*
FROM
(
SELECT
ROW_NUMBER() OVER(
PARTITION BY
member
ORDER BY
date ASC
) AS '配布順',
member,
date,
event
FROM
CouponHistory
WHERE
event = '配布'
) AS ISSUE
LEFT OUTER JOIN
(
SELECT
ROW_NUMBER() OVER(
PARTITION BY
member
ORDER BY
date ASC
) AS '使用順',
member,
date,
event
FROM
CouponHistory
WHERE
event = '使用'
) AS USE_ ON USE_.使用順 = ISSUE.配布順 AND USE_.member = ISSUE.member
ORDER BY
ISSUE.member ASC,
ISSUE.date ASC
■ 取得結果
| 配布順 | 会員 | 日付 | イベント | 使用順 | 会員 | 日付 | イベント |
|---|---|---|---|---|---|---|---|
| 1 | 佐藤 | 2017-12-01 | 配布 | 1 | 佐藤 | 2018-01-07 | 使用 |
| 1 | 西本 | 2017-11-23 | 配布 | 1 | 西本 | 2017-12-24 | 使用 |
| 2 | 西本 | 2018-02-10 | 配布 | 2 | 西本 | 2018-04-16 | 使用 |
| 3 | 西本 | 2018-04-15 | 配布 | NULL | NULL | NULL | NULL |
| 1 | 田中 | 2017-11-05 | 配布 | NULL | NULL | NULL | NULL |
| 2 | 田中 | 2018-03-04 | 配布 | NULL | NULL | NULL | NULL |
最初に配布した商品券と最初に使った商品券が紐付いているのが分かります。
貰っても使っていない商品券は、使用履歴側がNULLになっています。
２．商品券を配布した月ごとに、いつ使用されたか集計する
さきほど作成した表を、商品券を配布した年月でグルーピングしてみると、配布した枚数が分かります。
配布枚数を数える
SELECT
FORMAT(YEAR(ISSUE.date),'D4') + '-' + FORMAT(MONTH(ISSUE.date),'D2') AS '配布年月',
COUNT(*) AS '配布枚数'
FROM
(
SELECT
ROW_NUMBER() OVER(
PARTITION BY
member
ORDER BY
date ASC
) AS '配布順',
member,
date,
event
FROM
CouponHistory
WHERE
event = '配布'
) AS ISSUE
LEFT OUTER JOIN
(
SELECT
ROW_NUMBER() OVER(
PARTITION BY
member
ORDER BY
date ASC
) AS '使用順',
member,
date,
event
FROM
CouponHistory
WHERE
event = '使用'
) AS USE_ ON USE_.使用順 = ISSUE.配布順 AND USE_.member = ISSUE.member
GROUP BY
YEAR(ISSUE.date),
MONTH(ISSUE.date)
ORDER BY
YEAR(ISSUE.date) ASC,
MONTH(ISSUE.date) ASC
■ 取得結果
| 配布年月 | 配布枚数 |
|---|---|
| 2017-11 | 2 |
| 2017-12 | 1 |
| 2018-02 | 1 |
| 2018-03 | 1 |
| 2018-04 | 1 |
| ここに商品券を使った年月の列を加えてみます。 | |
| 年月を値からカラムに変えるため、CASE式を使っています。 |
使用年月を出力する
SELECT
FORMAT(YEAR(ISSUE.date),'D4') + '-' + FORMAT(MONTH(ISSUE.date),'D2') AS '配布年月',
COUNT(*) AS '配布枚数',
SUM(CASE WHEN YEAR(USE_.date) = 2017 AND MONTH(USE_.date) = 12 THEN 1 ELSE 0 END) AS '201712使用',
SUM(CASE WHEN YEAR(USE_.date) = 2018 AND MONTH(USE_.date) = 1 THEN 1 ELSE 0 END) AS '201801使用',
SUM(CASE WHEN YEAR(USE_.date) = 2018 AND MONTH(USE_.date) = 2 THEN 1 ELSE 0 END) AS '201802使用',
SUM(CASE WHEN YEAR(USE_.date) = 2018 AND MONTH(USE_.date) = 3 THEN 1 ELSE 0 END) AS '201803使用',
SUM(CASE WHEN YEAR(USE_.date) = 2018 AND MONTH(USE_.date) = 4 THEN 1 ELSE 0 END) AS '201804使用'
FROM
(
SELECT
ROW_NUMBER() OVER(
PARTITION BY
member
ORDER BY
date ASC
) AS '配布順',
member,
date
FROM
CouponHistory
WHERE
event = '配布'
) AS ISSUE
LEFT OUTER JOIN
(
SELECT
ROW_NUMBER() OVER(
PARTITION BY
member
ORDER BY
date ASC
) AS '使用順',
member,
date
FROM
CouponHistory
WHERE
event = '使用'
) AS USE_ ON USE_.使用順 = ISSUE.配布順 AND USE_.member = ISSUE.member
GROUP BY
YEAR(ISSUE.date),
MONTH(ISSUE.date)
ORDER BY
YEAR(ISSUE.date) ASC,
MONTH(ISSUE.date) ASC
■ 取得結果
| 配布年月 | 配布枚数 | 201712使用 | 201801使用 | 201802使用 | 201803使用 | 201804使用 |
|---|---|---|---|---|---|---|
| 2017-11 | 2 | 1 | 0 | 0 | 0 | 0 |
| 2017-12 | 1 | 0 | 1 | 0 | 0 | 0 |
| 2018-02 | 1 | 0 | 0 | 0 | 0 | 1 |
| 2018-03 | 1 | 0 | 0 | 0 | 0 | 0 |
| 2018-04 | 1 | 0 | 0 | 0 | 0 | 0 |
集計表を作成できました。
テスト
念のため、もうちょっと大きなデータで調べてみます。
データ設定
INSERT INTO CouponHistory (member,date,event) VALUES ('田中','2017-11-05','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('西本','2017-11-23','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('佐藤','2017-12-01','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('西本','2017-12-24','使用')
INSERT INTO CouponHistory (member,date,event) VALUES ('佐藤','2018-01-07','使用')
INSERT INTO CouponHistory (member,date,event) VALUES ('西本','2018-02-10','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('田中','2018-03-04','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('西本','2018-04-15','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('西本','2018-04-16','使用')
INSERT INTO CouponHistory (member,date,event) VALUES ('豊島','2017-12-07','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('豊島','2018-03-20','使用')
INSERT INTO CouponHistory (member,date,event) VALUES ('寺本','2018-04-21','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('寺本','2018-01-16','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('寺本','2018-02-04','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('寺本','2018-03-17','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('寺本','2018-02-02','使用')
INSERT INTO CouponHistory (member,date,event) VALUES ('寺本','2018-03-28','使用')
INSERT INTO CouponHistory (member,date,event) VALUES ('赤羽','2018-03-13','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('赤羽','2017-11-04','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('赤羽','2018-02-15','使用')
INSERT INTO CouponHistory (member,date,event) VALUES ('赤羽','2018-04-18','使用')
INSERT INTO CouponHistory (member,date,event) VALUES ('石橋','2018-02-16','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('石橋','2018-03-08','使用')
INSERT INTO CouponHistory (member,date,event) VALUES ('西原','2017-11-08','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('西原','2018-03-03','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('西原','2018-04-20','使用')
INSERT INTO CouponHistory (member,date,event) VALUES ('加藤','2018-02-08','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('加藤','2018-02-28','使用')
INSERT INTO CouponHistory (member,date,event) VALUES ('根本','2017-11-19','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('根本','2018-12-26','使用')
INSERT INTO CouponHistory (member,date,event) VALUES ('山本','2018-03-21','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('山本','2018-03-28','配布')
INSERT INTO CouponHistory (member,date,event) VALUES ('山本','2018-04-28','使用')
■ 取得結果
| 配布年月 | 配布枚数 | 201712使用 | 201801使用 | 201802使用 | 201803使用 | 201804使用 |
|---|---|---|---|---|---|---|
| 2017-11 | 5 | 1 | 0 | 1 | 0 | 1 |
| 2017-12 | 2 | 0 | 1 | 0 | 1 | 0 |
| 2018-01 | 1 | 0 | 0 | 1 | 0 | 0 |
| 2018-02 | 5 | 0 | 0 | 2 | 2 | 1 |
| 2018-03 | 6 | 0 | 0 | 0 | 0 | 2 |
| 2018-04 | 2 | 0 | 0 | 0 | 0 | 0 |
ちゃんと集計できているようです。
まとめ
RDBで順序を扱うには、ROW_NUMBER()とPARTITION BYを使う
ROW_NUMBER()とPARTITION BYを使うと、RDB上で順序を扱う事ができます。
ソートされたデータを突合して処理を行う事ができるので便利です。
今回は、配布した商品券と使用した商品券を紐付けるために使用しました。
CASE式とSUMを使って、条件に合致する数を合計する。
CASE式で値を絞り込んでからSUMで値を足しこむと、CASE式で示した条件に合致する数値を合計する事ができます。
今回は、使用した商品券の枚数を使用年月で絞って合計しています。
環境
Microsoft SQL Server 2017 Express Edition (64-bit)