我有一個玩家階梯，我想比較排名并添加自上次獲得積分以來他們上升或下降的資訊。我從 sqlite3 中選擇當前和上一個梯形串列。現在我想比較每個玩家的排名并將結果寫入新的 moverList 中。
#Get Ranks and Names from current Ranking table as a list
def getCurrentRankNew():
conn = get_db_connection()
currentList = conn.execute("SELECT ROW_NUMBER () OVER (ORDER BY Points DESC) Rank, Name FROM table").fetchall()
conn.close()
return currentList
#Get Ranks and Names from last Ranking table as a list
def getLastRankNew():
conn = get_db_previous()
lastList = conn.execute("SELECT ROW_NUMBER () OVER (ORDER BY Points DESC) Rank, Name FROM table").fetchall()
conn.close()
return lastList
以下是無效的偽代碼，因為我不知道該怎么做。目標是制作一個綜合串列，每個值都是一個階梯移動指標，是每個名稱當前和最后一個表排名的比較結果。
請注意，我嘗試了： except: if 條件，但是當它失敗時，這意味著在當前串列中存在一個新的玩家條目，而該條目不在最后一個串列中。在這種情況下，sqlite3 會拋出一個錯誤，所以除了 mov = 'new'
def compareRanksNew():
currentRank = getCurrentRankNew()
lastRank = getLastRankNew()
try:
if CurrentRank < LastRank:
mov = 'up'
elif CurrentRank > LastRank:
mov = 'down'
else:
mov = '-'
except:
mov = 'new'
movList = [mov for i in currentRank if i in lastRank]
return movList
我在使用多個串列和 for 回圈之前解決了這個問題，但是對于 ~350 個表行來說這很慢，比如 ~3 秒。我讀過使用綜合串列要快得多，但我好幾天都沒有讓它作業。
這是我的舊解決方案：
#Make a mov list for all players
def getMoverList():
conn = get_db_connection()
cur = conn.cursor()
cur.execute('SELECT Name FROM table ORDER BY Points DESC')
moverList = []
for Name in cur.fetchall():
playerName = str(Name[0])
moverList.append(compareRanks(playerName))
return moverList
#Get Rank from a playName from current Ranking table
def getCurrentRank(playerName):
conn = get_db_connection()
player = conn.execute("SELECT ROW_NUMBER () OVER (ORDER BY Points DESC) Rank, Name FROM table")
for item in player:
if playerName in item:
return item[0]
#Get Rank from a playName from previous Ranking table
def getLastRank(playerName):
conn = get_db_previous()
player = conn.execute("SELECT ROW_NUMBER () OVER (ORDER BY Points DESC) Rank, Name FROM table").fetchall()
conn.close()
for item in player:
if playerName in item:
return item[0]
#compare ranks and return moving value (mov)
def compareRanks(playerName):
CurrentRank = getCurrentRank(playerName)
LastRank = getLastRank(playerName)
try:
if CurrentRank < LastRank:
mov = 'up'
elif CurrentRank > LastRank:
mov = 'down'
else:
mov = '-'
except:
mov = 'new'
return mov
getMoverList()
conn.close()
uj5u.com熱心網友回復：
一種可能的解決方案是在一個 SQLite 連接中加載兩個資料庫，您可以以不同的方式連接兩個資料庫，讓資料庫引擎本身執行作業。
首先，創建一個小的測驗資料庫，這大致類似于您的資料庫的布局，只是為了完整性：
# Create a couple of databases with some test data
for fn in ["db_cur.db", "db_prev.db"]:
db = sqlite3.connect(fn)
db.execute("CREATE TABLE players(Name, Points);")
for i in range(350):
if random.random() <= 0.01:
# 1 percent chance of a random score, near the "real" score
db.execute("INSERT INTO players(Name, Points) VALUES(?,?);", (f"Player {i}", i random.randint(1, 10)))
else:
# Otherwise, just use the score based off the player name
db.execute("INSERT INTO players(Name, Points) VALUES(?,?);", (f"Player {i}", i))
db.commit()
db.close()
然后，創建這兩個資料庫后，您可以通過attaching 來在一個連接中讀取它們：
# Now load both databases so we can join them:
db = sqlite3.connect(":memory:")
db.execute("ATTACH 'db_cur.db' as cur;")
db.execute("ATTACH 'db_prev.db' as prev;")
sql = """
SELECT
prev_ranks.Rank,
prev_ranks.Points,
cur_ranks.Rank,
cur_ranks.Points,
prev_ranks.Name
FROM
(SELECT
ROW_NUMBER () OVER (ORDER BY Points DESC) AS Rank,
Name,
Points
FROM
prev.players) AS prev_ranks
INNER JOIN
(SELECT
ROW_NUMBER () OVER (ORDER BY Points DESC) AS Rank,
Name,
Points
FROM
cur.players) AS cur_ranks
ON
prev_ranks.Name = cur_ranks.Name;
"""
for prev_rank, prev_points, cur_rank, cur_points, name in db.execute(sql):
if prev_rank < cur_rank:
print(f"{name} moved down from {prev_rank} to {cur_rank}")
elif prev_rank > cur_rank:
print(f"{name} moved up from {prev_rank} to {cur_rank}")
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/345392.html