以立委投票指南 為例，例如單一表決頁面中將記名表決名單依照投票決定、政黨歸類是怎麼做的？
其中主要用到了 postgres 9.4 的 json function
- json_build_object
- json_agg
- json_array_length
如想深入了解 SQL，以下是有關的 Database table：
- legislator_legislatordetail：立委
- vote_vote：表決
- vote_legislator_vote：各立委的表決紀錄
將如下的 raw data：
| decision |
party |
name |
legislator_id |
| 贊成 |
臺灣團結聯盟 |
賴振昌 |
1806 |
| 贊成 |
民主進步黨 |
蔡煌瑯 |
752 |
| 反對 |
中國國民黨 |
孫大千 |
919 |
| 下略 |
|
|
|
轉換成歸類過的 json：
[
{
"decision": "反對",
"party_list": [
{
"count": 56,
"party": "中國國民黨",
"legislators": [
{
"legislator_id": 919,
"name": "孫大千"
},
(...)
]
}
],
"sum": 56
},
{
"decision": "贊成",
"party_list": [
{
"count": 38,
"party": "民主進步黨",
"legislators": [
{
"legislator_id": 752,
"name": "蔡煌瑯"
},
(...)
]
},
{
"count": 3,
"party": "臺灣團結聯盟",
"legislators": [
{
"legislator_id": 1806,
"name": "賴振昌"
},
(...)
]
}
],
"sum": 41
},
{
"decision": "沒投票",
"party_list": [
{
"count": 9,
"party": "中國國民黨",
"legislators": [
{
"legislator_id": 1751,
"name": "陳雪生"
},
(...)
]
},
{
"count": 2,
"party": "民主進步黨",
"legislators": [
{
"legislator_id": 1733,
"name": "何欣純"
},
(...)
]
},
{
"count": 1,
"party": "民國黨",
"legislators": [
{
"legislator_id": 1747,
"name": "徐欣瑩"
}
]
},
{
"count": 1,
"party": "無黨團結聯盟",
"legislators": [
{
"legislator_id": 926,
"name": "高金素梅"
}
]
},
{
"count": 2,
"party": "親民黨",
"legislators": [
{
"legislator_id": 896,
"name": "李桐豪"
},
(...)
]
}
],
"sum": 15
}
]
- 取出投票決定、立委政黨、立委姓名、立委 id
SQL generate result：
| decision |
party |
name |
legislator_id |
| 贊成 |
臺灣團結聯盟 |
賴振昌 |
1806 |
| 贊成 |
民主進步黨 |
蔡煌瑯 |
752 |
| 反對 |
中國國民黨 |
孫大千 |
919 |
| 下略 |
|
|
|
- 將立委姓名、立委 id合併成一欄 key/value json
SQL generate result：
| decision |
party |
detail |
| 贊成 |
臺灣團結聯盟 |
{"name" : "賴振昌", "legislator_id" : 1806} |
| 贊成 |
民主進步黨 |
{"name" : "蔡煌瑯", "legislator_id" : 752} |
| 反對 |
中國國民黨 |
{"name" : "孫大千", "legislator_id" : 919} |
| 下略 |
|
|
- 將上個步驟中，投票決定和政黨一樣的 detail 欄位 合併成 json array
SQL generate result：
| decision |
party |
legislators |
| 反對 |
中國國民黨 |
[{"name" : "孫大千", "legislator_id" : 919}, {"name" : "林鴻池", "legislator_id" : 1340}, {"name" : "李貴敏", "legislator_id" : 1739}, {"name" : "林德福", "legislator_id" : 908}, {"name" : "賴士葆", "legislator_id" : 866}, {"name" : "曾巨威", "legislator_id" : 1755}, {"name (...) |
| 沒投票 |
中國國民黨 |
[{"name" : "陳雪生", "legislator_id" : 1751}, {"name" : "林國正", "legislator_id" : 1742}, {"name" : "洪秀柱", "legislator_id" : 546}, {"name" : "羅明才", "legislator_id" : 879}, {"name" : "謝國樑", "legislator_id" : 1387}, {"name" : "吳育仁", "legislator_id" : 1734}, {"nam (...) |
| 沒投票 |
民主進步黨 |
[{"name" : "何欣純", "legislator_id" : 1733}, {"name" : "邱志偉", "legislator_id" : 1744}] |
| 沒投票 |
民國黨 |
[{"name" : "徐欣瑩", "legislator_id" : 1747}] |
| 沒投票 |
無黨團結聯盟 |
[{"name" : "高金素梅", "legislator_id" : 926}] |
| 沒投票 |
親民黨 |
[{"name" : "李桐豪", "legislator_id" : 896}, {"name" : "陳怡潔", "legislator_id" : 1804}] |
| 贊成 |
民主進步黨 |
[{"name" : "蔡煌瑯", "legislator_id" : 752}, {"name" : "薛凌", "legislator_id" : 1384}, {"name" : "吳宜臻", "legislator_id" : 1735}, {"name" : "陳節如", "legislator_id" : 1709}, {"name" : "尤美女", "legislator_id" : 1730}, {"name" : "陳亭妃", "legislator_id" : 1708}, {"nam (...) |
| 贊成 |
臺灣團結聯盟 |
[{"name" : "賴振昌", "legislator_id" : 1806}, {"name" : "周倪安", "legislator_id" : 1807}, {"name" : "葉津鈴", "legislator_id" : 1805}] |
- 將上個步驟中的 party, legislators 兩欄位合併成一個 json，並順便計算該類總數
SQL generate result：
| decision |
party_list |
count |
| 反對 |
{"party" : "中國國民黨", "legislators" : [{"name" : "孫大千", "legislator_id" : 919}, {"name" : "林鴻池", "legislator_id" : 1340}, {"name" : "李貴敏", "legislator_id" : 1739}, {"name" : "林德福", "legislator_id" : 908}, {"name" : "賴士葆", "legislator_id" : 866}, {"name" : "曾 (...) |
56 |
| 沒投票 |
{"party" : "中國國民黨", "legislators" : [{"name" : "陳雪生", "legislator_id" : 1751}, {"name" : "林國正", "legislator_id" : 1742}, {"name" : "洪秀柱", "legislator_id" : 546}, {"name" : "羅明才", "legislator_id" : 879}, {"name" : "謝國樑", "legislator_id" : 1387}, {"name" : " (...) |
9 |
| 沒投票 |
{"party" : "民主進步黨", "legislators" : [{"name" : "何欣純", "legislator_id" : 1733}, {"name" : "邱志偉", "legislator_id" : 1744}], "count" : 2} |
2 |
| 沒投票 |
{"party" : "民國黨", "legislators" : [{"name" : "徐欣瑩", "legislator_id" : 1747}], "count" : 1} |
1 |
| 沒投票 |
{"party" : "無黨團結聯盟", "legislators" : [{"name" : "高金素梅", "legislator_id" : 926}], "count" : 1} |
1 |
| 沒投票 |
{"party" : "親民黨", "legislators" : [{"name" : "李桐豪", "legislator_id" : 896}, {"name" : "陳怡潔", "legislator_id" : 1804}], "count" : 2} |
2 |
| 贊成 |
{"party" : "民主進步黨", "legislators" : [{"name" : "蔡煌瑯", "legislator_id" : 752}, {"name" : "薛凌", "legislator_id" : 1384}, {"name" : "吳宜臻", "legislator_id" : 1735}, {"name" : "陳節如", "legislator_id" : 1709}, {"name" : "尤美女", "legislator_id" : 1730}, {"name" : " (...) |
38 |
| 贊成 |
{"party" : "臺灣團結聯盟", "legislators" : [{"name" : "賴振昌", "legislator_id" : 1806}, {"name" : "周倪安", "legislator_id" : 1807}, {"name" : "葉津鈴", "legislator_id" : 1805}], "count" : 3} |
3 |
- 將同樣的投票決定合併成一個 json array，並順便計算該類總數
SQL generate result：
| decision |
party_list |
sum |
| 反對 |
[{"party" : "中國國民黨", "legislators" : [{"name" : "孫大千", "legislator_id" : 919}, {"name" : "林鴻池", "legislator_id" : 1340}, {"name" : "李貴敏", "legislator_id" : 1739}, {"name" : "林德福", "legislator_id" : 908}, {"name" : "賴士葆", "legislator_id" : 866}, {"name" : " (...)" |
56 |
| 贊成 |
[{"party" : "民主進步黨", "legislators" : [{"name" : "蔡煌瑯", "legislator_id" : 752}, {"name" : "薛凌", "legislator_id" : 1384}, {"name" : "吳宜臻", "legislator_id" : 1735}, {"name" : "陳節如", "legislator_id" : 1709}, {"name" : "尤美女", "legislator_id" : 1730}, {"name" : (...)" |
41 |
| 沒投票 |
[{"party" : "中國國民黨", "legislators" : [{"name" : "陳雪生", "legislator_id" : 1751}, {"name" : "林國正", "legislator_id" : 1742}, {"name" : "洪秀柱", "legislator_id" : 546}, {"name" : "羅明才", "legislator_id" : 879}, {"name" : "謝國樑", "legislator_id" : 1387}, {"name" : (...)" |
15 |
- 上步驟已達成我們最終想要的結構，此步驟只是再將上步驟結果壓成一個 json
SQL generate result：
| json_agg |
| [{"decision":"反對","party_list":[{"party" : "中國國民黨", "legislators" : [{"name" : "孫大千", "legislator_id" : 919}, {"name" : "林鴻池", "legislator_id" : 1340}, {"name" : "李貴敏", "legislator_id" : 1739}, {"name" : "林德福", "legislator_id" : 908}, {"name" : "賴士葆", "leg (...) |
終於有空寫點分享，上述六步驟感覺有點冗長，歡迎指導改進！
