LoginSignup
19
19

More than 5 years have passed since last update.

@nogitsune413(中村 佑介)inQiitadonユーザー会

ROW_NUMBER()とPARTITION BYを使ってクーポン券を集計する

Last updated at Posted at 2018-06-17

問題

あるサービスの会員向けに商品券を配布したとします。
商品券は、商品購入時の特典として配布されるため、会員によっては複数枚の商品券を受け取ります。

会員 受け取った商品券 使った商品券
田中 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)

19
19
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
19
19