Pythonの同じpsqlテーブルに2つの列を追加して結果を取得する方法

debugcn 投稿 Dev

ilvcs

私は最近データベースについて学び始めました。現在、Pythonのデータベースに裏打ちされたアプリケーションで自分のスキルをテストしています。私はこのようなデータベースに2つのテーブルを作成しました

CREATE TABLE players
(
    id     SERIAL PRIMARY KEY,
    name   TEXT
);

CREATE TABLE matches 
(
    game  SERIAL,
    winner SERIAL REFERENCES players(id),
    loser SERIAL REFERENCES players(id)
);

（id、name、wins、matches）のような順序で、playerid、name wins、matchesとして結果を取得するためにそれらにクエリを作成することにしました。

私はそれを行うためにこのコードを使用しています

SELECT id, name, count(matches.winner) as wins, 
 (count(matches.winner) + count(matches.loser)) as match 
  FROM players LEFT JOIN matches ON players.id = matches.winner 
  GROUP BY players.id ORDER BY wins

動作していないようです。これを解決するのを手伝ってください。

ありがとう。

私は以下のこのコードを試しました

    DB = connect()
    c = DB.cursor()
    c.execute("""WITH
                   results AS (
                            select P.id,
                                   P.name,
                                   1     win
                            from   players P inner join matches M
                            on M.winner = P.id
                            union all
                            select P.id,
                                   P.name,
                                   0    win
                            from   players P inner join matches M
                            on M.loser = P.id)
                select id,
                       name,
                       sum(win)   wins,
                       count(*)   matches
                from   results
                group by id, name """)
    return c.fetchall()
    DB.close()

このコードはデータベースからデータを取得しておらず、代わりに[]だけを返しています。また、クエリ文字列を次のように置き換えようとしました

query = oldstring.replace(" ","_")
c.execute(query)

しかし、それも機能しません。これを解決するのを手伝ってください。

ポール

まず、SERIALFK列にデータ型を使用したくない場合があります。これは、PKには適しているが、FKには適していない自動インクリメント整数型であるため、に固執することをお勧めしますINTEGER。

次に、ビジネスルールと呼ばれるものを適用するためのいくつかの制約を検討します。常に2人のプレイヤーがいますか？試合に勝者も敗者もいないのは現実的ですか？ネクタイで終わったらどうしますか？

とにかく、とりあえず簡単にしましょう。2人のプレーヤーが向かい合うときはいつでも、一方が勝者になり、もう一方が負けると仮定しましょう。したがって、試合の総数は勝ち+負けです。

WITH
  results AS (
    select P.id,
           P.name,
           1     win
    from   players P inner join matches M
    on M.winner = P.id
    union all
    select P.id,
           P.name,
           0    win
    from   players P inner join matches M
    on M.loser = P.id)
select id,
       name,
       sum(win)   wins,
       count(*)   matches
from   results
group by id, name

だから、ここで何が起こっているのですか？

WITHは共通テーブル式です。これは基本的に名前付きサブクエリです。ここでは読みやすくするために使用しました。FROM句の中にサブクエリを入れることもできますが、この方法の方がはるかに見栄えがします。
あなたは参加する必要があるplayersとmatches何とかが、各列matchesの参照2人の選手。では、2回参加します。
参加するmatches.winnerと、試合ごとに1つの行が表示され、この行に勝者がいます。これはすでに勝利を数えるのに十分ですが、損失に関する情報はまったくありません。
2番目のクエリでは、ゲームごとに1行が追加されますが、今回はこの行に敗者がいます。
UNION ALL 2つのクエリの結果を1つの結果セットに結合します。
ただし、結果が異なる行を区別できる必要があります。最も簡単な方法は数字を合計することなので、私は勝ち負けを設定1し0ました。
最後に、現在使用している行をグループ化しGROUP BY、集計します。