以立委投票指南 為例,例如單一表決頁面中將記名表決名單依照投票決定、政黨歸類是怎麼做的?
其中主要用到了 postgres 9.4 的 json function
- json_build_object
- json_agg
- json_array_length
如想深入了解 SQL,以下是有關的 Database table:
- legislator_legislatordetail:立委
{'第一選區': ["10018010005", "10018010006", "10018010007", "10018010011", "10018010012", "10018010013", "10018010017", "10018010018", "10018010019", "10018010020", "10018010021", "10018010022", "10018010023", "10018010024", "10018010025", "10018010026", "10018010027", "10018010032", "10018010033", "10018010035", "10018010036", "10018010037", "10018010038", "10018010039", "10018010040", "10018010041", "10018010042", "10018010043", "10018010044", "10018010045", "10018010046", "10018010047", "10018010048", "10018010050", "10018010051", "10018010054"], '第二選區': ["10018010001", "10018010002", "10018010003", "10018010004", "10018010008", "10018010009", "10018010010", "10018010014", "10018010015", "10018010016", "10018010028", "10018010029", "10018010030", "10018010031", "10018010049", "10018010052", "10018010053"], '第三選區': ["10018020001", "10018020002", "10018020003", "10018020004", "10018020005", "10018020006", "10018020007", "10018020030", "10018020031", "10018020032", "10018020033", "10018020034", "10018020035", "100 |
TOWNID | TOWNCODE | COUNTYNAME | TOWNNAME | TOWNENG | COUNTYID | COUNTYCODE | |
---|---|---|---|---|---|---|---|
V02 | 10014020 | 臺東縣 | 成功鎮 | Chenggong Township | V | 10014 | |
T21 | 10013210 | 屏東縣 | 佳冬鄉 | Jiadong Township | T | 10013 | |
P13 | 10009130 | 雲林縣 | 麥寮鄉 | Mailiao Township | P | 10009 | |
V11 | 10014110 | 臺東縣 | 綠島鄉 | Lvdao Township | V | 10014 | |
V16 | 10014160 | 臺東縣 | 蘭嶼鄉 | Lanyu Township | V | 10014 | |
N07 | 10007120 | 彰化縣 | 田中鎮 | Tianzhong Township | N | 10007 | |
N19 | 10007170 | 彰化縣 | 社頭鄉 | Shetou Township | N | 10007 | |
T14 | 10013140 | 屏東縣 | 竹田鄉 | Zhutian Township | T | 10013 | |
T05 | 10013050 | 屏東縣 | 萬丹鄉 | Wandan Township | T | 10013 |
# Configuration file for runtime kernel parameters. | |
# See sysctl.conf(5) for more information. | |
# See also http://www.nateware.com/linux-network-tuning-for-2013.html for | |
# an explanation about some of these parameters, and instructions for | |
# a few other tweaks outside this file. | |
# Protection from SYN flood attack. | |
net.ipv4.tcp_syncookies = 1 |
"experience": { | |
"0": "現任大台南市議會第1屆議長。", | |
"2": "台南縣議會第13、14、15、16屆議員。", | |
"3": "台南縣議會教育、社會、勞工、文化小組召集人。", | |
"4": "2000年、2004年陳水扁總統大選後壁鄉後援會會長。", | |
"5": "台南縣賴羅傅氏宗親會常務理事。", | |
"6": "後壁鄉義消分隊顧問團團長。", | |
"7": "國立中山大學政治學", | |
"8": "研究所碩士班。" | |
}, |
{ | |
"縣市": "county", | |
"選舉年": "election_year", | |
"審查會別": "category", | |
"案號": "id", | |
"提案類別": "type", | |
"提案單位/人": "proposed_by", | |
"連署人": "petitioned_by", | |
"主旨": "abstract", | |
"說明": "description", |
第11屆臺北市議員,陳玉梅,,0,人民團體捐贈收入,0,0,0,,,"支 租用競選辦事處支出",0,交通旅運支出,0,,"出 繳庫支出",0,0 | |
第1屆新北市議員,劉美芳,,0,,"入 匿名捐贈收入",0,0,宣傳支出,0,"支 租用競選辦事處支出",0,交通旅運支出,0,,"出 繳庫支出",0,0 | |
第1屆臺中市議員,曾朝榮,,0,人民團體捐贈收入,0,0,0,,,"支 租用競選辦事處支出",0,交通旅運支出,0,,"出 繳庫支出",0,0 | |
第1屆高雄市議員,鄭光峰,,0,,,0,0,,,,0,,,,"出 繳庫支出",0,0 |