已經找了好幾天了。請幫忙。我有一個 SQL 查詢：
SELECT
WorkList, RecordStatus, COUNT(*) as QTY
FROM worklist_reports
WHERE WorkList IS NOT NULL
GROUP BY WorkList, RecordStatus
回傳：
| WorkList | RecordStatus | QTY |
| -------- | ------------ | ----|
|ADM |Closed |41 |
|ADM |Open |1 |
|BIL |Closed |16 |
|BIL |Hold |1 |
|BIL |Open |2 |
我需要為 RecordStatus 的可能值創建新列，以便我可以將結果表折疊為：
| WorkList | Open | Closed | Hold |
| -------- | ---- | ------ | ---- |
|ADM |1 |41 |0 |
|BIL |2 |16 |1 |
最終的資料格式將是一個 python 字典串列：
data = [
{'x': 'ADM', 'open': 1, 'closed': 41, 'hold': 0},
{'x': 'BIL', 'open': 2, 'closed': 16, 'hold': 1}
]
從查詢結果創建字典沒有問題。我只需要知道正確的 SQL 陳述句來格式化查詢結果。
非常感激任何的幫助！
uj5u.com熱心網友回復：
為此，您WorkList只需要分組并使用條件聚合：
SELECT WorkList,
SUM(RecordStatus = 'Open') AS Open,
SUM(RecordStatus = 'Closed') AS Closed,
SUM(RecordStatus = 'Hold') AS Hold
FROM worklist_reports
WHERE WorkList IS NOT NULL
GROUP BY WorkList;
uj5u.com熱心網友回復：
您還可以保持查詢原樣，并使用 pandas 將其轉換為您喜歡的記錄串列
import pandas as pd
df = pd.DataFrame({
"WorkList": ["ADM", "ADM", "BIL", "BIL", "BIL"],
"RecordStatus": ["Closed", "Open", "Closed", "Hold", "Open"],
"QTY": [41, 1, 16, 1, 2]
})
list_records = df.pivot(
index="WorkList", columns="RecordStatus", values="QTY"
).fillna(0).astype(int).reset_index().to_dict(orient='records')
list_records
[{'WorkList': 'ADM', 'Closed': 41, 'Hold': 0, 'Open': 1}, {'WorkList': 'BIL', 'Closed': 16, 'Hold': 1, 'Open': 2}]
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/451084.html
標籤：Python sql sqlite 字典 通过...分组
下一篇：PowershellForeach回圈遍歷字典并將結果發送到另一個字典。我可以使用for回圈，在python中使用foreach但不能使用powershell