条件に合うSQL文を知りたい

前提・実現したいこと

pythonからpostgresqlのDBにpsycopg2でアクセスし、以下のテーブルから以下の情報を取得したい。その際に必要なSQL文が知りたい。またSQL文の実行は1回で行いたい。

指定した区間内の指定選手no(player_no)の順位(rank)が2位以上（1位または2位）の割合(rate)を取得したい。ただし、指定した区間に該当する選手の情報がない場合はNULL（pycopg2で取得した場合はNone）にしたい。

例）

game	player_no	date	rank
1	1	2020-01-01 00:00:00.000000	3
1	2	2020-01-01 00:00:00.000000	1
1	3	2020-01-01 00:00:00.000000	2
2	1	2020-01-02 00:00:00.000000	1
2	4	2020-01-02 00:00:00.000000	2
2	6	2020-01-02 00:00:00.000000	4
2	10	2020-01-02 00:00:00.000000	3
3	1	2020-01-03 00:00:00.000000	3
3	7	2020-01-03 00:00:00.000000	2
3	8	2020-01-03 00:00:00.000000	1
3	9	2020-01-03 00:00:00.000000	4
3	11	2020-01-03 00:00:00.000000	5

指定した区間：2020-01-01 00:00:00から2020-01-02 23:59:59
指定した選手no：1,2,3,5,6,9,10,11

取得したいデータ

player_no	rate
1	50%
2	100%
3	0%
5	NULL
6	0%
9	NULL
10	0%
11	NULL

該当のソースコード

python3
1import psycopg2
2
3# 以下は適宜設定
4user_name = ""
5password = ""
6host_name = ""
7port = ""
8database = ""
9
10def execute_sql(sql):
11    dsn = "postgresql://{}:{}@{}:{}/{}".format(user_name, password, host_name, port, database)
12    with psycopg2.connect(dsn) as conn:
13        with conn.cursor() as cur:
14            cur.execute(sql)
15            value = []
16            for row in cur:
17                value.append(row)
18    return value
19
20
21# CREATE,INSERT用
22def execute_sql_no_result(sql):
23    dsn = "postgresql://{}:{}@{}:{}/{}".format(user_name, password, host_name, port, database)
24    with psycopg2.connect(dsn) as conn:
25        with conn.cursor() as cur:
26            cur.execute(sql)
27
28# テストデータ作成
29execute_sql_no_result("DROP TABLE IF EXISTS test")
30execute_sql_no_result("CREATE TABLE test (game INTEGER,player_no INTEGER,date timestamp,rank INTEGER);")
31execute_sql_no_result("INSERT INTO test VALUES ('1','1','2020/1/1  0:00:00','3');")
32execute_sql_no_result("INSERT INTO test VALUES ('1','2','2020/1/1  0:00:00','1');")
33execute_sql_no_result("INSERT INTO test VALUES ('1','3','2020/1/1  0:00:00','2');")
34execute_sql_no_result("INSERT INTO test VALUES ('2','1','2020/1/2  0:00:00','1');")
35execute_sql_no_result("INSERT INTO test VALUES ('2','4','2020/1/2  0:00:00','2');")
36execute_sql_no_result("INSERT INTO test VALUES ('2','6','2020/1/2  0:00:00','4');")
37execute_sql_no_result("INSERT INTO test VALUES ('2','10','2020/1/2  0:00:00','3');")
38execute_sql_no_result("INSERT INTO test VALUES ('3','1','2020/1/3  0:00:00','3');")
39execute_sql_no_result("INSERT INTO test VALUES ('3','7','2020/1/3  0:00:00','2');")
40execute_sql_no_result("INSERT INTO test VALUES ('3','8','2020/1/3  0:00:00','1');")
41execute_sql_no_result("INSERT INTO test VALUES ('3','9','2020/1/3  0:00:00','4');")
42execute_sql_no_result("INSERT INTO test VALUES ('3','11','2020/1/3  0:00:00','5');")
43
44execute_sql("ここのSQL文が知りたいです")
45

試したこと

検索したが解決策をうまく見つけることができなかった。

補足情報（FW/ツールのバージョンなど）

Python 3.6.2
psycopg2 2.8.5
PostgreSQL 12.2

その他

簡単な説明も頂けると嬉しいです。

退会済みユーザー

2020/05/01 09:34

第三者に再現してもらいたい場合は、面倒でもCREATE TABLE文やサンプルデータ用のINSERT INTO文を用意していただけると好印象です。

koa

2020/05/01 09:47

ありがとうございます。帰宅次第修正します。他のソフトウェアで自動でテーブル作成されたものだったので気づきませんでした。

Orlofsky

2020/05/01 11:15

> 指定した選手no：1,2,3,5,6,9,10,11 実は、選手no を取得するテーブルを忘れていた、とか？

Orlofsky

2020/05/01 11:16

質問のデータ量を必要最小限に削ってシンプルにできることも大切なことです。

koa

2020/05/01 12:24 編集

以下の意味がわからないのですが、どういうことでしょうか？こちらをテーブルにして欲しいということでしょうか？ > 指定した選手no：1,2,3,5,6,9,10,11 実は、選手no を取得するテーブルを忘れていた、とか？

Orlofsky

2020/05/01 13:49

SELECT文は基本的に既にどこかのテーブルに存在しているデータを持ってくる機能です。会社の先輩にでも確認した方が良いのでは？

koa

2020/05/01 14:24 編集

SELECT文自身は詳しくはないですが知っていますし、会社で作ってるものでもなく個人で作成しているものです。以下の内容は今回の質問に関係があるのでしょうか？ここでSELECT文でplayer_noをテーブルからとってくるということでしょうか？それは今回の質問にどのような意味があるのでしょうか？それがわかりません。 > 指定した選手no：1,2,3,5,6,9,10,11 実は、選手no を取得するテーブルを忘れていた、とか？今回は他のシステムがテーブルからとってきたデータを処理し、その結果について情報を取得したいので直接関係がないためこのような記載にしています。今回の問題を解決するのには不要と考え今回の記載にしています。もしそこも含めるとなるとかなり膨大になるので今回シンプルな状態にしたつもりです。

Orlofsky

2020/05/01 15:19

では、教育的指導として、質問に選手noテーブルの CREATE TABLEとINSERTを https://teratail.com/help/question-tips#questionTips3-7 の [コード] で追記してください。

koa

2020/05/01 15:35 編集

意味が分からないのですがどういうことでしょうか？player_noのテーブルを作成する意味があるのでしょうか？私がした質問に答えて欲しいです。

Orlofsky

2020/05/01 20:40

理由は既に書きました。技術者は基本的に必要なことしか書きません。 m6uさんの指導も無視して質問を直さないことが目的に変わったので、指導は終わります。

koa

2020/05/02 04:02

何回も答えてるのですが、理由がわかりません。 m6uさんから頂いた指摘は既に修正したつもりですが、修正内容にどのような問題があるのですか？具体的にコードのテストデータ作成に何が足りないのかわかりません。足りない理由もわかりません。