PythonとSQLite3でグループごとの最大値(最新日)のレコードを取得

概要

NameごとのDateの最新日のレコードを取得。
下部に記載のLinkを参考にしコードを作成。

基盤データ

ID	Ver	Name	Date
220	220.0	nico	2022/02/20
221	221.1	nuke	2022/02/21
222	222.2	neko	2022/02/22
320	320.0	nico	2022/03/20
321	321.1	nuke	2022/03/21
322	322.2	neko	2022/03/22
420	420.0	nico	2022/04/20
421	421.1	nuke	2022/04/21
422	422.2	neko	2022/04/22

取得データ

ID	Ver	Name	Date
422	422.2	neko	2022/04/22
420	420.0	nico	2022/04/20
421	421.1	nuke	2022/04/21

Pythonコード

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 Sample (
                ID   INTEGER
               ,Ver  REAL
               ,Name TEXT
               ,Date TEXT DEFAULT (datetime('now', 'localtime'))
            )
        """
        cur.execute(create_sql)
        cur.execute("INSERT INTO Sample VALUES(220, 220.0, 'nico', '2022/02/20')")
        cur.execute("INSERT INTO Sample VALUES(221, 221.1, 'nuke', '2022/02/21')")
        cur.execute("INSERT INTO Sample VALUES(222, 222.2, 'neko', '2022/02/22')")
        cur.execute("INSERT INTO Sample VALUES(320, 320.0, 'nico', '2022/03/20')")
        cur.execute("INSERT INTO Sample VALUES(321, 321.1, 'nuke', '2022/03/21')")
        cur.execute("INSERT INTO Sample VALUES(322, 322.2, 'neko', '2022/03/22')")
        cur.execute("INSERT INTO Sample VALUES(420, 420.0, 'nico', '2022/04/20')")
        cur.execute("INSERT INTO Sample VALUES(421, 421.1, 'nuke', '2022/04/21')")
        cur.execute("INSERT INTO Sample VALUES(422, 422.2, 'neko', '2022/04/22')")

        # create_sql = """CREATE TABLE Result AS """
        # select_sql = """
        #     SELECT   Sample.ID
        #             ,Sample.Ver
        #             ,Sample.Name
        #             ,Sample.Date
        #     FROM     Sample
        #     INNER JOIN (
        #         SELECT   Name
        #                 ,max(Date) AS DateMax
        #         FROM     Sample
        #         GROUP BY Name
        #     )AS Temp
        #     ON       Sample.Date = Temp.DateMax
        #     AND      Sample.Name = Temp.Name
        #     ORDER BY Sample.Name
        # """
        # cur.execute(create_sql + select_sql)

        create_sql = """CREATE TABLE Result AS """
        select_sql = """
            SELECT   ID
                    ,Ver
                    ,Name
                    ,Date
            FROM (
                SELECT   *
                        ,max(Date) over(partition by Name) as DateMax
                FROM     Sample
            )AS Temp
            WHERE    Date = DateMax
            ORDER BY Name
        """
        cur.execute(create_sql + select_sql)

        # pprint.pprint(tuple(cur.execute("SELECT * FROM sqlite_master")))
        # pprint.pprint(tuple(cur.execute("PRAGMA table_info('Sample')")))
        pprint.pprint(tuple([row[0] for row in cur.execute("SELECT name FROM sqlite_master WHERE type='table'")]))
        pprint.pprint(tuple([row[1] for row in cur.execute("PRAGMA table_info('Sample')")]))
        pprint.pprint(tuple(cur.execute("SELECT * FROM 'Sample'")))
        pprint.pprint(tuple([row[1] for row in cur.execute("PRAGMA table_info('Result')")]))
        pprint.pprint(tuple(cur.execute("SELECT * FROM 'Result'")))

    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()

Link

SQLでグループごとにある最大値の行を取得する
https://qiita.com/nogitsune413/items/f413268d01b4ea2394b1

そろそろ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/

あとがき

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