-
Notifications
You must be signed in to change notification settings - Fork 3
/
Copy pathuseful-views
121 lines (97 loc) · 8.14 KB
/
useful-views
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
CREATE VIEW uitslag as select initials,prefix,lastname,firstname,woonplaats,gender,count(distinct(candentries.kieskringId)) as kkcount,name,affiliations.id as affid_,sum(votes) as svotes from candentries,candvotecounts,affiliations where candentries.id=candvotecounts.candid and candentries.kieskringId = candvotecounts.kieskringId and candentries.affid=affiliations.id and affiliations.id = candentries.affid and affiliations.kieskringId = candvotecounts.kieskringId and candvotecounts.affid = candentries.affid and formid='510c' group by 1,2,3,4,5,6 order by affid_ asc, candentries.id asc, affiliations.kieskringId asc
/* uitslag(initials,prefix,lastname,firstname,woonplaats,gender,kkcount,name,affid_,svotes) */;
CREATE VIEW uitplijst as select candentries.kieskringId, candid, initials,prefix,lastname,firstname,woonplaats,gender,name,affiliations.id as affid_,sum(votes) as svotes from candentries,candvotecounts,affiliations where candentries.id=candvotecounts.candid and candentries.kieskringId = candvotecounts.kieskringId and candentries.affid=affiliations.id and affiliations.id = candentries.affid and affiliations.kieskringId = candvotecounts.kieskringId and candvotecounts.affid = candentries.affid and formid='510c' group by 1,2,affid_ order by affid_, 1, 2 asc
/* uitplijst(kieskringId,candid,initials,prefix,lastname,firstname,woonplaats,gender,name,affid_,svotes) */;
CREATE VIEW uitslagp as select candentries.kieskringId, candid, initials,prefix,lastname,firstname,woonplaats,gender,name,affiliations.id as affid_,sum(votes) as svotes from candentries,candvotecounts,affiliations where candentries.id=candvotecounts.candid and candentries.kieskringId = candvotecounts.kieskringId and candentries.affid=affiliations.id and affiliations.id = candentries.affid and affiliations.kieskringId = candvotecounts.kieskringId and candvotecounts.affid = candentries.affid and formid='510c' group by 1,2,affid_ order by affid_, 1, 2 asc
/* uitslagp(kieskringId,candid,initials,prefix,lastname,firstname,woonplaats,gender,name,affid_,svotes) */;
CREATE VIEW scounts as select
rucandvotecounts.shortcode,rucandvotecounts.affid, affiliations.name, orderno,candentries.initials,candentries.prefix,candentries.lastname,candentries.firstname,candentries.woonplaats,candentries.gender,sum(rucandvotecounts.votes)
as svotes, election.id as electionId
from candvotecounts,rucandvotecounts,candentries,affiliations,election
where affiliations.id=rucandvotecounts.affid and affiliations.kieskringId =
rucandvotecounts.kieskringId and rucandvotecounts.formid='510d'
and candvotecounts.formid='510d'
and candvotecounts.shortcode = rucandvotecounts.shortcode and
candentries.affid=candvotecounts.affid and candentries.id =
rucandvotecounts.candid and
rucandvotecounts.kieskringId = candentries.kieskringId and
election.id = candvotecounts.electionId and
candvotecounts.electionId = rucandvotecounts.electionId and
rucandvotecounts.electionId=candentries.electionId and
candentries.electionId =affiliations.electionId
group by 1 order by rucandvotecounts.affid,orderno;
/* scounts(shortcode,affid,name,orderno,initials,prefix,lastname,firstname,woonplaats,gender,svotes) */;
CREATE VIEW escounts as select scounts.shortcode, scounts.affid,
scounts.name, scounts.electionId, scounts.orderno, scounts.initials,
scounts.prefix, scounts.lastname, scounts.firstname, scounts.svotes,
scounts.woonplaats, scounts.gender,elected, ranking
from scounts left join candresults on candresults.shortcode = scounts.shortcode
-- THIS NEEDS TO MATCH ON ELECTIONID FOR CANDRESULTS AS WELL!!
/* escounts(shortcode,affid,name,orderno,initials,prefix,lastname,firstname,svotes,woonplaats,gender,elected,ranking) */;
CREATE VIEW uniaffili as select id,name from affiliations group by 1,2
/* uniaffili(id,name) */;
CREATE VIEW sbcounts as select rucandvotecounts.stembureauId,affiliations.kieskringName,stembureaus.name as sbname,rucandvotecounts.gemeente,rucandvotecounts.gemeenteId as gemeenteId,rucandvotecounts.affid,affiliations.name as affname,rucandvotecounts.candid,candentries.initials,
candentries.prefix,candentries.lastname,
candentries.firstname,candentries.woonplaats,candentries.gender,rucandvotecounts.votes as svotes,
rucandvotecounts.electionId as electionId
from candentries, rucandvotecounts, affiliations,stembureaus where
candentries.electionId = rucandvotecounts.electionId and
rucandvotecounts.electionId = affiliations.electionId and
affiliations.electionId = stembureaus.electionId and
affiliations.id=rucandvotecounts.affid and
affiliations.kieskringId = rucandvotecounts.kieskringId and
rucandvotecounts.formid='510b' and
candentries.affid = rucandvotecounts.affid and
candentries.id = rucandvotecounts.candid and
rucandvotecounts.kieskringId = candentries.kieskringId and
stembureaus.id = rucandvotecounts.stembureauId and
stembureaus.gemeenteId = rucandvotecounts.gemeenteId
/* sbcounts(stembureauId,affid,name,candid,initials,prefix,lastname,firstname,woonplaats,gender,svotes) */;
CREATE VIEW scountsps1 as select candvotecounts.affid, name,
candentries.id,candentries.initials,candentries.prefix,candentries.lastname,
candentries.firstname,candentries.woonplaats,candentries.gender,
candvotecounts.votes as svotes
from candvotecounts,candentries,affiliations
where
affiliations.id=candvotecounts.affid and
candvotecounts.formid='510d' and candentries.id =candvotecounts.candid and
candentries.affid=candvotecounts.affid
order by candvotecounts.affid,candentries.id
/* scountsps1(affid,name,id,initials,prefix,lastname,firstname,woonplaats,gender,svotes) */;
CREATE VIEW escountsps1 as select scountsps1.id,scountsps1.affid, scountsps1.name, candresults.resultorder as orderno,
scountsps1.initials, scountsps1.prefix, scountsps1.lastname, scountsps1.firstname,
scountsps1.svotes, scountsps1.woonplaats, scountsps1.gender,elected, ranking
from scountsps1 left join candresults on candresults.resultorder = scountsps1.id and
candresults.affid = scountsps1.affid
/* escountsps1(id,affid,name,initials,prefix,lastname,firstname,svotes,woonplaats,gender,elected,ranking) */;
CREATE VIEW sbmeta as select stembureau,stembureauId,rumeta.gemeente,rumeta.gemeenteId,rumeta.electionId,
max(value) filter (where kind='ongeldig') as ongeldig,
max(value) filter (where kind='blanco') as blanco,
max(value) filter (where kind='totalballots') as kiesgerechtigden,
max(value) filter (where kind='totalcounted') as stemmen,
max(value) filter (where kind='geldige volmachtbewijzen') as volmachten,
max(value) filter (where kind='geldige kiezerspassen') as kiespassen,
max(value) filter (where kind='geldige stempassen') as stempassen,
max(value) filter (where kind='toegelaten kiezers') as toegelaten
from rumeta,stembureaus,election where
formid='510b' and
stembureaus.id = stembureauId and
election.id = stembureaus.electionId and
rumeta.electionId = stembureaus.electionId
group by rumeta.gemeenteId,stembureauId;
create index candidx1 on candentries(id, electionId, kieskringId, affId);
create index candvidx on candvotecounts(affid, candid, gemeenteId);
create index affidx on affiliations(id, kieskringId);
create index sbindex on stembureaus(gemeenteId, electionId);
create index ruaffvcindex on ruaffvotecounts(electionId, gemeenteId, stembureauId, formid);
create index rucandvcindex on rucandvotecounts(electionId, gemeenteId, stembureauId, formid);
CREATE INDEX rucandvtcidx on rucandvotecounts(shortcode);
CREATE INDEX candvtcidx on candvotecounts(shortcode);
CREATE INDEX candreshrt on candresults(shortcode);
CREATE INDEX candentridx3 on candentries(affid,id,kieskringId);
CREATE INDEX elindex on election(id);
CREATE INDEX ruelindx on rucandvotecounts(electionId, formid);
CREATE INDEX formidx on candvotecounts(electionId,formid,shortcode);
CREATE INDEX ruformidx on rucandvotecounts(electionId,formid,shortcode);
CREATE VIEW unicands as select affid,firstname,initials,prefix,lastname,gender,woonplaats,group_concat(kieskringId),group_concat(id) from candentries group by 1,5,4,3,2,5,6
/* unicands(affid,firstname,initials,prefix,lastname,gender,woonplaats,"group_concat(kieskringId)","group_concat(id)") */;