PythonとSQLite3と再帰SQLで最新の会員番号の取得

概要

［Next版］
カードの紛失や再発行などでカードが再発行された場合は新規に番号が追加され、
切替前カード番号のNextのカラムに新規のカード番号が入力されるものとします。
上記TABLEを再帰的に(再帰SQLで)処理して最新の会員番号を取得。

［Prev版］
カードの紛失や再発行などでカードが再発行された場合は新規に番号が追加され、
切替後カード番号のPrevのカラムに前回のカード番号が入力されるものとします。
上記TABLEを再帰的に(再帰SQLで)処理して最新の会員番号を取得。

下部に記載のLinkを参考にして作成。

---

基盤データ［Next版］

CardNo	NextCardNo
1001	1002
1002	1003
1003	1004
1004	1005
1005	None
2001	2002
2002	None
3001	None

取得データ［Next版］

CardNo	NextCardNo
1001	1005
1002	1005
1003	1005
1004	1005
1005	1005
2001	2002
2002	2002
3001	3001

Pythonコード［Next版］

import pprint
import sqlite3


def main():

    print("PRESS ENTER KEY TO BOOT")
    input()

    con = None
    cur = None

    try:
        con = sqlite3.connect(":memory:")
        cur = con.cursor()

        create_sql = """
            CREATE TABLE CardMember (
                CardNo     INTEGER
               ,NextCardNo INTEGER
            )
        """
        cur.execute(create_sql)

        cur.execute("INSERT INTO CardMember VALUES(1001, 1002)")
        cur.execute("INSERT INTO CardMember VALUES(1002, 1003)")
        cur.execute("INSERT INTO CardMember VALUES(1003, 1004)")
        cur.execute("INSERT INTO CardMember VALUES(1004, 1005)")
        cur.execute("INSERT INTO CardMember VALUES(1005, null)")

        cur.execute("INSERT INTO CardMember VALUES(2001, 2002)")
        cur.execute("INSERT INTO CardMember VALUES(2002, null)")

        cur.execute("INSERT INTO CardMember VALUES(3001, null)")

        # create_sql = """CREATE TABLE CardMemberInterimResult01 AS """
        # select_sql = """
        #     WITH RECURSIVE Recursions(CardNo, NextCardNo) AS (
        #         SELECT   CardMember.CardNo
        #                 ,CardMember.CardNo
        #         FROM     CardMember
        #         UNION
        #         SELECT   CardMember.CardNo
        #                 ,Recursions.NextCardNo
        #         FROM     CardMember
        #                 ,Recursions
        #         WHERE    CardMember.NextCardNo = Recursions.CardNo
        #     )
        #     SELECT   CardNo
        #             ,NextCardNo
        #     FROM     Recursions
        # """
        # cur.execute(create_sql + select_sql)

        # create_sql = """CREATE TABLE CardMemberInterimResult02 AS """
        # select_sql = """
        #     WITH RECURSIVE Recursions(Depth, CardNo, NextCardNo) AS (  --change
        #         SELECT   0                                             --change
        #                 ,CardMember.CardNo                             --insert comma
        #                 ,CardMember.CardNo
        #         FROM     CardMember
        #         UNION
        #         SELECT   Recursions.Depth + 1                          --change
        #                 ,CardMember.CardNo                             --insert comma
        #                 ,Recursions.NextCardNo
        #         FROM     CardMember
        #                 ,Recursions
        #         WHERE    CardMember.NextCardNo = Recursions.CardNo
        #     )
        #     SELECT   Depth                                             --change
        #             ,CardNo                                            --insert comma
        #             ,NextCardNo
        #     FROM     Recursions
        # """
        # cur.execute(create_sql + select_sql)

        # create_sql = """CREATE TABLE CardMemberInterimResult03 AS """
        # select_sql = """
        #     WITH RECURSIVE Recursions(Depth, CardNo, NextCardNo) AS (
        #         SELECT   0
        #                 ,CardMember.CardNo
        #                 ,CardMember.CardNo
        #         FROM     CardMember
        #         UNION
        #         SELECT   Recursions.Depth + 1
        #                 ,CardMember.CardNo
        #                 ,Recursions.NextCardNo
        #         FROM     CardMember
        #                 ,Recursions
        #         WHERE    CardMember.NextCardNo = Recursions.CardNo
        #     )
        #     SELECT   Depth
        #             ,CardNo
        #             ,NextCardNo                                                 --insert
        #     FROM (                                                              --insert
        #         SELECT   *                                                      --insert
        #                 ,max(Depth) over(partition by CardNo) as DepthMax       --insert
        #         FROM     Recursions                                             --insert space
        #     )                                                                   --insert
        #     WHERE    Depth = DepthMax                                           --insert
        #     ORDER BY Depth                                                      --insert
        #             ,CardNo                                                     --insert
        #             ,NextCardNo                                                 --insert
        # """
        # cur.execute(create_sql + select_sql)

        create_sql = """CREATE TABLE CardMemberFinallyResult AS """
        select_sql = """
            WITH RECURSIVE Recursions(Depth, CardNo, NextCardNo) AS (
                SELECT   0
                        ,CardMember.CardNo
                        ,CardMember.CardNo
                FROM     CardMember
                UNION
                SELECT   Recursions.Depth + 1
                        ,CardMember.CardNo
                        ,Recursions.NextCardNo
                FROM     CardMember
                        ,Recursions
                WHERE    CardMember.NextCardNo = Recursions.CardNo
            )
            SELECT --Depth                                             --delete
                     CardNo                                            --delete comma
                    ,NextCardNo
            FROM (
                SELECT   *
                        ,max(Depth) over(partition by CardNo) as DepthMax
                FROM     Recursions
            )
            WHERE    Depth = DepthMax
            ORDER BY CardNo                                            --change
                    ,NextCardNo                                        --change
                    ,Depth                                             --change
        """
        cur.execute(create_sql + select_sql)

        print("sqlite_master")
        pprint.pprint(tuple([row[0] for row in cur.execute("SELECT name FROM sqlite_master WHERE type='table'")]))
        print()

        def pprint_pprint_table(cur, table):
            print(f"{table}")
            pprint.pprint(tuple([row[1] for row in cur.execute(f"PRAGMA table_info({table})")]))
            pprint.pprint(tuple(cur.execute(f"SELECT * FROM {table}")))
            print()

        pprint_pprint_table(cur, "CardMember")
        # pprint_pprint_table(cur, "CardMemberInterimResult01")
        # pprint_pprint_table(cur, "CardMemberInterimResult02")
        # pprint_pprint_table(cur, "CardMemberInterimResult03")
        pprint_pprint_table(cur, "CardMemberFinallyResult")

    except Exception as e:
        print(e)
    finally:
        cur = cur.close() if cur is not None else None
        con = con.close() if con is not None else None

    print()
    print("PRESS ENTER KEY TO EXIT")
    input()


if __name__ == "__main__":
    main()

中間結果

行頭でコメントアウトしている部分を、
行頭のコメントアウトをアンコメントしてコードを有効化すると、
以下の途中経過の情報を出力可能です。

CardMemberInterimResult01
再帰SQLで総当たり(?)した情報を出力。

CardMemberInterimResult02
再帰深度(再帰回数)を保持するカラムを追加。

CardMemberInterimResult03
Nameごとの最大深度のレコードのみを取得。

最終結果

CardMemberFinallyResult
再帰深度のカラムを出力対象外に変更。

---

基盤データ［Prev版］

CardNo	PrevCardNo
1001	None
1002	1001
1003	1002
1004	1003
1005	1004
2001	None
2002	2001
3001	None

取得データ［Prev版］

CardNo	PrevCardNo
1005	1001
1005	1002
1005	1003
1005	1004
1005	1005
2002	2001
2002	2002
3001	3001

Pythonコード［Prev版］

import pprint
import sqlite3


def main():

    print("PRESS ENTER KEY TO BOOT")
    input()

    con = None
    cur = None

    try:
        con = sqlite3.connect(":memory:")
        cur = con.cursor()

        create_sql = """
            CREATE TABLE CardMember (
                CardNo     INTEGER
               ,PrevCardNo INTEGER
            )
        """
        cur.execute(create_sql)

        cur.execute("INSERT INTO CardMember VALUES(1001, null)")
        cur.execute("INSERT INTO CardMember VALUES(1002, 1001)")
        cur.execute("INSERT INTO CardMember VALUES(1003, 1002)")
        cur.execute("INSERT INTO CardMember VALUES(1004, 1003)")
        cur.execute("INSERT INTO CardMember VALUES(1005, 1004)")

        cur.execute("INSERT INTO CardMember VALUES(2001, null)")
        cur.execute("INSERT INTO CardMember VALUES(2002, 2001)")

        cur.execute("INSERT INTO CardMember VALUES(3001, null)")

        # create_sql = """CREATE TABLE CardMemberInterimResult01 AS """
        # select_sql = """
        #     WITH RECURSIVE Recursions(CardNo, PrevCardNo) AS (
        #         SELECT   CardMember.CardNo
        #                 ,CardMember.CardNo
        #         FROM     CardMember
        #         UNION
        #         SELECT   CardMember.CardNo
        #                 ,Recursions.PrevCardNo
        #         FROM     CardMember
        #                 ,Recursions
        #         WHERE    CardMember.PrevCardNo = Recursions.CardNo
        #     )
        #     SELECT   CardNo
        #             ,PrevCardNo
        #     FROM     Recursions
        # """
        # cur.execute(create_sql + select_sql)

        # create_sql = """CREATE TABLE CardMemberInterimResult02 AS """
        # select_sql = """
        #     WITH RECURSIVE Recursions(Depth, CardNo, PrevCardNo) AS (  --change
        #         SELECT   0                                             --change
        #                 ,CardMember.CardNo                             --insert comma
        #                 ,CardMember.CardNo
        #         FROM     CardMember
        #         UNION
        #         SELECT   Recursions.Depth + 1                          --change
        #                 ,CardMember.CardNo                             --insert comma
        #                 ,Recursions.PrevCardNo
        #         FROM     CardMember
        #                 ,Recursions
        #         WHERE    CardMember.PrevCardNo = Recursions.CardNo
        #     )
        #     SELECT   Depth                                             --change
        #             ,CardNo                                            --insert comma
        #             ,PrevCardNo
        #     FROM     Recursions
        # """
        # cur.execute(create_sql + select_sql)

        # create_sql = """CREATE TABLE CardMemberInterimResult03 AS """
        # select_sql = """
        #     WITH RECURSIVE Recursions(Depth, CardNo, PrevCardNo) AS (
        #         SELECT   0
        #                 ,CardMember.CardNo
        #                 ,CardMember.CardNo
        #         FROM     CardMember
        #         UNION
        #         SELECT   Recursions.Depth + 1
        #                 ,CardMember.CardNo
        #                 ,Recursions.PrevCardNo
        #         FROM     CardMember
        #                 ,Recursions
        #         WHERE    CardMember.PrevCardNo = Recursions.CardNo
        #     )
        #     SELECT   Depth
        #             ,CardNo
        #             ,PrevCardNo                                                 --insert
        #     FROM (                                                              --insert
        #         SELECT   *                                                      --insert
        #                 ,max(Depth) over(partition by PrevCardNo) as DepthMax   --insert
        #         FROM     Recursions                                             --insert space
        #     )                                                                   --insert
        #     WHERE    Depth = DepthMax                                           --insert
        #     ORDER BY Depth                                                      --insert
        #             ,CardNo                                                     --insert
        #             ,PrevCardNo                                                 --insert
        # """
        # cur.execute(create_sql + select_sql)

        create_sql = """CREATE TABLE CardMemberFinallyResult AS """
        select_sql = """
            WITH RECURSIVE Recursions(Depth, CardNo, PrevCardNo) AS (
                SELECT   0
                        ,CardMember.CardNo
                        ,CardMember.CardNo
                FROM     CardMember
                UNION
                SELECT   Recursions.Depth + 1
                        ,CardMember.CardNo
                        ,Recursions.PrevCardNo
                FROM     CardMember
                        ,Recursions
                WHERE    CardMember.PrevCardNo = Recursions.CardNo
            )
            SELECT --Depth                                             --delete
                     CardNo                                            --delete comma
                    ,PrevCardNo
            FROM (
                SELECT   *
                        ,max(Depth) over(partition by PrevCardNo) as DepthMax
                FROM     Recursions
            )
            WHERE    Depth = DepthMax
            ORDER BY CardNo                                            --change
                    ,PrevCardNo                                        --change
                    ,Depth                                             --change
        """
        cur.execute(create_sql + select_sql)

        print("sqlite_master")
        pprint.pprint(tuple([row[0] for row in cur.execute("SELECT name FROM sqlite_master WHERE type='table'")]))
        print()

        def pprint_pprint_table(cur, table):
            print(f"{table}")
            pprint.pprint(tuple([row[1] for row in cur.execute(f"PRAGMA table_info({table})")]))
            pprint.pprint(tuple(cur.execute(f"SELECT * FROM {table}")))
            print()

        pprint_pprint_table(cur, "CardMember")
        # pprint_pprint_table(cur, "CardMemberInterimResult01")
        # pprint_pprint_table(cur, "CardMemberInterimResult02")
        # pprint_pprint_table(cur, "CardMemberInterimResult03")
        pprint_pprint_table(cur, "CardMemberFinallyResult")

    except Exception as e:
        print(e)
    finally:
        cur = cur.close() if cur is not None else None
        con = con.close() if con is not None else None

    print()
    print("PRESS ENTER KEY TO EXIT")
    input()


if __name__ == "__main__":
    main()

中間結果

コメントアウトしている部分、
アンコメントでコードを有効化すると、
以下の途中経過の情報を出力可能です。

CardMemberInterimResult01
再帰SQLで総当たり(?)した情報を出力。

CardMemberInterimResult02
再帰深度(再帰回数)を保持するカラムを追加。

CardMemberInterimResult03
Nameごとの最大深度のレコードのみを取得。

最終結果

CardMemberFinallyResult
再帰深度のカラムを出力対象外に変更。

---

Link

SQLで再帰的に最新の会員番号をもとめて一つの会員として売上を集計したい。
https://teratail.com/questions/cae1gknhvu5uiq

再帰SQL -図解-
https://qiita.com/Shoyu_N/items/f1786f99545fa5053b75

そろそろSQLのウィンドウ関数を理解したい
https://qiita.com/w-sato-ist/items/63600a3ab84aad38e879
https://qiita.com/w-sato-ist/items/75bf6bb60ccafda82840
https://qiita.com/w-sato-ist/items/86aab7e7722ff9454ec0

SQL便利な関数
https://avinton.com/academy/sql-intermediate/

あとがき

ノンプログラマーの素人が記述をしたコードです。
狭い利用範囲と少ない利用頻度での確認ですので、
記載内容に間違いや勘違いがあるかもしれません。
上記内容を参照の際は自己責任でお願い致します。