service.data.impl/src/main/resources/db-changelog-1.2.xml
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.5.xsd">
<changeSet author="pether" id="1414872417007-236">
<dropView viewName="view_riksdagen_party_summary" />
<dropView viewName="view_riksdagen_politician" /> <createView viewName="view_riksdagen_politician">select
view_riksdagen_party_member.id as person_id,
max(first_name) as first_name,
max(last_name) as last_name,
max(party) as party,
max(born_year) as born_year,
max(gender) as gender,
min(assignment_data.from_date) as first_assignment_date,
max(assignment_data.to_date) as last_assignment_date,
sum((CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - (CASE WHEN from_date > CURRENT_DATE THEN CURRENT_DATE ELSE from_date END)) as total_days_served,
sum( CASE WHEN assignment_type='kammaruppdrag' and assignment_data.status !='LEDIG' THEN ((CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - (CASE WHEN from_date > CURRENT_DATE THEN CURRENT_DATE ELSE from_date END)) ELSE 0 END) as total_days_served_parliament,
sum( CASE WHEN (org_code is not null and assignment_type='uppdrag') THEN ( (CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - (CASE WHEN from_date > CURRENT_DATE THEN CURRENT_DATE ELSE from_date END)) ELSE 0 END) as total_days_served_committee,
sum( CASE WHEN (role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen') THEN ( (CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - (CASE WHEN from_date > CURRENT_DATE THEN CURRENT_DATE ELSE from_date END)) ELSE 0 END) as total_days_served_government,
sum( CASE WHEN (detail='Europaparlamentet') THEN ( (CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - (CASE WHEN from_date > CURRENT_DATE THEN CURRENT_DATE ELSE from_date END)) ELSE 0 END) as total_days_served_eu,
(CASE WHEN max(to_date) > CURRENT_DATE THEN true ELSE false END) as active,
count(*) total_assignments,
sum((CASE WHEN to_date > CURRENT_DATE THEN 1 ELSE 0 END)) as current_assignments,
sum( CASE WHEN (assignment_type='talmansuppdrag') THEN ( (CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - (CASE WHEN from_date > CURRENT_DATE THEN CURRENT_DATE ELSE from_date END)) ELSE 0 END) as total_days_served_speaker,
CASE WHEN sum(CASE WHEN to_date > CURRENT_DATE AND (assignment_type='talmansuppdrag') THEN 1 ELSE 0 END) > 0 THEN true ELSE false END as active_speaker,
sum( CASE WHEN (assignment_type='partiuppdrag') THEN ( (CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - (CASE WHEN from_date > CURRENT_DATE THEN CURRENT_DATE ELSE from_date END)) ELSE 0 END) as total_days_served_party,
CASE WHEN sum(CASE WHEN to_date > CURRENT_DATE AND (assignment_type='partiuppdrag') THEN 1 ELSE 0 END) > 0 THEN true ELSE false END as active_party,
sum((CASE WHEN to_date > CURRENT_DATE and org_code is not null and assignment_type='uppdrag' THEN 1 ELSE 0 END)) as current_committee_assignments,
sum((CASE WHEN to_date > CURRENT_DATE and (role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen') THEN 1 ELSE 0 END)) as current_ministry_assignments,
sum((CASE WHEN to_date > CURRENT_DATE and assignment_type='partiuppdrag' THEN 1 ELSE 0 END)) as current_party_assignments,
sum((CASE WHEN to_date > CURRENT_DATE and assignment_type='talmansuppdrag' THEN 1 ELSE 0 END)) as current_speaker_assignments,
sum((CASE WHEN org_code is not null and assignment_type='uppdrag' THEN 1 ELSE 0 END)) as total_committee_assignments,
sum((CASE WHEN assignment_type='partiuppdrag' THEN 1 ELSE 0 END)) as total_party_assignments,
sum((CASE WHEN assignment_type='talmansuppdrag' THEN 1 ELSE 0 END)) as total_speaker_assignments,
sum((CASE WHEN (role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen') THEN 1 ELSE 0 END)) as total_ministry_assignments,
(CASE WHEN sum(CASE WHEN to_date > CURRENT_DATE AND detail='Europaparlamentet' THEN 1 ELSE 0 END) > 0 THEN true ELSE false END) as active_eu,
(CASE WHEN sum(CASE WHEN to_date > CURRENT_DATE AND (role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen') THEN 1 ELSE 0 END) > 0 THEN true ELSE false END) as active_government,(
(CASE WHEN sum(CASE WHEN to_date > CURRENT_DATE AND (org_code is not null and assignment_type='uppdrag') THEN 1 ELSE 0 END) > 0 THEN true ELSE false END)) as active_committee,
(CASE WHEN sum(CASE WHEN to_date > CURRENT_DATE AND assignment_type='kammaruppdrag' and assignment_data.status !='LEDIG' THEN 1 ELSE (CASE WHEN to_date > CURRENT_DATE AND assignment_type='kammaruppdrag' and assignment_data.status ='LEDIG' THEN -1 ELSE 0 END) END) > 0 THEN true ELSE false END) as active_parliament
from assignment_data left join view_riksdagen_party_member on assignment_data.intressent_id = view_riksdagen_party_member.id group by view_riksdagen_party_member.id</createView>
<createView viewName="view_riksdagen_party_summary">select
party,
min(first_assignment_date) as first_assignment_date,
max(last_assignment_date) as last_assignment_date,
sum(total_days_served::int8)::int8 as total_days_served,
sum(total_days_served_parliament::int8)::int8 as total_days_served_parliament,
sum(total_days_served_committee::int8)::int8 as total_days_served_committee,
sum(total_days_served_government::int8)::int8 as total_days_served_government,
sum(total_days_served_eu::int8)::int8 as total_days_served_eu,
sum(total_days_served_speaker::int8)::int8 as total_days_served_speaker,
sum(total_days_served_party::int8)::int8 as total_days_served_party,
bool_or(active) as active,
bool_or(active_eu) as active_eu,
bool_or(active_government) as active_government ,
bool_or(active_committee) as active_committee,
bool_or(active_parliament) as active_parliament,
bool_or(active_speaker) as active_speaker,
bool_or(active_party) as active_party,
sum(CASE WHEN active THEN 1 ELSE 0 END) as total_active,
sum(CASE WHEN active_eu THEN 1 ELSE 0 END) as total_active_eu,
sum(CASE WHEN active_government THEN 1 ELSE 0 END) as total_active_government,
sum(CASE WHEN active_committee THEN 1 ELSE 0 END) as total_active_committee,
sum(CASE WHEN active_parliament THEN 1 ELSE 0 END) as total_active_parliament,
sum(total_assignments::int8)::int8 as total_assignments,
sum(total_party_assignments::int8)::int8 as total_party_assignments,
sum(total_committee_assignments::int8)::int8 as total_committee_assignments,
sum(total_ministry_assignments::int8)::int8 as total_ministry_assignments,
sum(total_speaker_assignments::int8)::int8 as total_speaker_assignments,
sum(current_assignments::int8)::int8 as current_assignments,
sum(current_party_assignments::int8)::int8 as current_party_assignments,
sum(current_committee_assignments::int8)::int8 as current_committee_assignments,
sum(current_ministry_assignments::int8)::int8 as current_ministry_assignments,
sum(current_speaker_assignments::int8)::int8 as current_speaker_assignments
from view_riksdagen_politician group by party</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-237">
<dropView viewName="view_riksdagen_goverment_role_member" /> <createView
viewName="view_riksdagen_goverment_role_member">select hjid as roleId,detail, role_code,first_name,last_name,from_date,to_date,person_data.id as person_id,person_data.party as party,(CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date as total_days_served,(CASE WHEN to_date > CURRENT_DATE THEN true ELSE false END) as active from assignment_data left join person_data on assignment_data.intressent_id = person_data.id where role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen'</createView>
<dropView viewName="view_riksdagen_committee_role_member" /> <createView
viewName="view_riksdagen_committee_role_member">select hjid as roleId,detail, role_code,first_name,last_name,from_date,to_date,person_data.id as person_id,person_data.party as party,(CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date as total_days_served,(CASE WHEN to_date > CURRENT_DATE THEN true ELSE false END) as active from assignment_data left join person_data on assignment_data.intressent_id = person_data.id where org_code is not null and assignment_type='uppdrag'</createView>
<createView
viewName="view_riksdagen_party_role_member">select hjid as roleId,detail, role_code,first_name,last_name,from_date,to_date,person_data.id as person_id,person_data.party as party,(CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date as total_days_served,(CASE WHEN to_date > CURRENT_DATE THEN true ELSE false END) as active from assignment_data left join person_data on assignment_data.intressent_id = person_data.id where assignment_type='partiuppdrag'</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-238">
<dropForeignKeyConstraint baseTableName="user_account"
constraintName="fk_8mmnmcgjut9nc7dfhrgxi598f" />
<dropTable cascadeConstraints="true" tableName="aggregated_bug_data" />
<dropTable cascadeConstraints="true" tableName="aggregated_country_data" />
<dropColumn columnName="country_user_account_hjid"
tableName="user_account" />
<addColumn tableName="user_account">
<column name="country" type="varchar(255)" />
</addColumn>
</changeSet>
<changeSet author="pether" id="1414872417007-239">
<dropView viewName="view_riksdagen_goverment_role_member" /> <createView
viewName="view_riksdagen_goverment_role_member">select hjid as role_id,detail, role_code,first_name,last_name,from_date,to_date,person_data.id as person_id,person_data.party as party,(CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date as total_days_served,(CASE WHEN to_date > CURRENT_DATE THEN true ELSE false END) as active from assignment_data left join person_data on assignment_data.intressent_id = person_data.id where role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen'</createView>
<dropView viewName="view_riksdagen_committee_role_member" /> <createView
viewName="view_riksdagen_committee_role_member">select hjid as role_id,detail, role_code,first_name,last_name,from_date,to_date,person_data.id as person_id,person_data.party as party,(CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date as total_days_served,(CASE WHEN to_date > CURRENT_DATE THEN true ELSE false END) as active from assignment_data left join person_data on assignment_data.intressent_id = person_data.id where org_code is not null and assignment_type='uppdrag'</createView>
<dropView viewName="view_riksdagen_party_role_member" /> <createView
viewName="view_riksdagen_party_role_member">select hjid as role_id,detail, role_code,first_name,last_name,from_date,to_date,person_data.id as person_id,person_data.party as party,(CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - from_date as total_days_served,(CASE WHEN to_date > CURRENT_DATE THEN true ELSE false END) as active from assignment_data left join person_data on assignment_data.intressent_id = person_data.id where assignment_type='partiuppdrag'</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-240">
<dropView viewName="view_riksdagen_goverment_role_member" /> <createView
viewName="view_riksdagen_goverment_role_member">select hjid as role_id,detail, role_code,first_name,last_name,from_date,to_date,person_data.id as person_id,person_data.party as party,(CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - (CASE WHEN from_date > CURRENT_DATE THEN CURRENT_DATE ELSE from_date END) as total_days_served,(CASE WHEN to_date > CURRENT_DATE and from_date < CURRENT_DATE THEN true ELSE false END) as active from assignment_data left join person_data on assignment_data.intressent_id = person_data.id where role_code like'%MINISTER' or detail like '%departementet' or detail='Statsrådsberedningen'</createView>
<dropView viewName="view_riksdagen_committee_role_member" /> <createView
viewName="view_riksdagen_committee_role_member">select hjid as role_id,detail, role_code,first_name,last_name,from_date,to_date,person_data.id as person_id,person_data.party as party,(CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - (CASE WHEN from_date > CURRENT_DATE THEN CURRENT_DATE ELSE from_date END) as total_days_served,(CASE WHEN to_date > CURRENT_DATE and from_date < CURRENT_DATE THEN true ELSE false END) as active from assignment_data left join person_data on assignment_data.intressent_id = person_data.id where org_code is not null and assignment_type='uppdrag'</createView>
<dropView viewName="view_riksdagen_party_role_member" /> <createView
viewName="view_riksdagen_party_role_member">select hjid as role_id,detail, role_code,first_name,last_name,from_date,to_date,person_data.id as person_id,person_data.party as party,(CASE WHEN to_date > CURRENT_DATE THEN CURRENT_DATE ELSE to_date END) - (CASE WHEN from_date > CURRENT_DATE THEN CURRENT_DATE ELSE from_date END) as total_days_served,(CASE WHEN to_date > CURRENT_DATE and from_date < CURRENT_DATE THEN true ELSE false END) as active from assignment_data left join person_data on assignment_data.intressent_id = person_data.id where assignment_type='partiuppdrag'</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-241">
<createView
viewName="view_riksdagen_vote_data_ballot_summary">select embedded_id_ballot_id as ballot_id,max(embedded_id_concern) as concern,max(embedded_id_issue) as issue,max(ballot_type),max(rm) as rm, max(label) as label,max(vote_date) as vote_date,round(avg(born_year)) as avg_born_year,count(*) as total_votes,sum( case when vote='JA' then 1 else 0 end) as yes_votes,sum( case when vote='NEJ' then 1 else 0 end) as no_votes,sum( case when vote='AVSTÅR' then 1 else 0 end) as abstain_votes,sum( case when vote='FRÅNVARANDE' then 1 else 0 end) as absent_votes , sum( case when vote='JA' then 1 else 0 end) > sum( case when vote='Nej' then 1 else 0 end) as approved ,sum( case when vote='JA' then 1 else 0 end) = sum( case when vote='Nej' then 1 else 0 end) as no_winner, round((100.0 *sum( case when vote='JA' then 1 else 0 end)) / count(*),2) as percentage_yes,round((100.0 *sum( case when vote='NEJ' then 1 else 0 end)) / count(*),2) as percentage_no,round((100.0 *sum( case when vote='FRÅNVARANDE' then 1 else 0 end)) / count(*),2) as percentage_absent,round((100.0 *sum( case when vote='AVSTÅR' then 1 else 0 end)) / count(*),2) as percentage_abstain,round((100.0 *sum( case when gender='MAN' then 1 else 0 end)) / count(*),2) as percentage_male from vote_data group by embedded_id_ballot_id</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-242">
<createView
viewName="view_riksdagen_vote_data_ballot_party_summary">select embedded_id_ballot_id as ballot_id,party,
max(avg_born_year) as avg_born_year,
max(total_votes) as total_votes,
max(yes_votes) as yes_votes,
max(no_votes) as no_votes,
max(abstain_votes) as abstain_votes,
max(absent_votes) as absent_votes,
bool_or(approved) as approved,
((sum( case when vote='JA' then 1 else 0 end) > sum( case when vote='Nej' then 1 else 0 end)) and bool_or(approved)) or ((sum( case when vote='NEJ' then 1 else 0 end) > sum( case when vote='JA' then 1 else 0 end)) and not bool_or(approved)) as party_won,
bool_or(no_winner) as no_winner,
max(percentage_yes) as percentage_yes,
max(percentage_no) as percentage_no,
max(percentage_absent) as percentage_absent,
max(percentage_abstain) as percentage_abstain,
max(percentage_male) as percentage_male,
max(embedded_id_concern) as concern,max(embedded_id_issue) as issue,max(ballot_type),max(vote_data.rm) as rm, max(vote_data.label) as label,max(vote_data.vote_date) as vote_date,round(avg(born_year)) as party_avg_born_year,count(*) as party_total_votes,sum( case when vote='JA' then 1 else 0 end) as party_yes_votes,sum( case when vote='NEJ' then 1 else 0 end) as party_no_votes,sum( case when vote='AVSTÅR' then 1 else 0 end) as party_abstain_votes,sum( case when vote='FRÅNVARANDE' then 1 else 0 end) as party_absent_votes , sum( case when vote='JA' then 1 else 0 end) > sum( case when vote='Nej' then 1 else 0 end) as party_approved ,sum( case when vote='JA' then 1 else 0 end) = sum( case when vote='Nej' then 1 else 0 end) as party_no_winner, round((100.0 *sum( case when vote='JA' then 1 else 0 end)) / count(*),2) as party_percentage_yes,round((100.0 *sum( case when vote='NEJ' then 1 else 0 end)) / count(*),2) as party_percentage_no,round((100.0 *sum( case when vote='FRÅNVARANDE' then 1 else 0 end)) / count(*),2) as party_percentage_absent,round((100.0 *sum( case when vote='AVSTÅR' then 1 else 0 end)) / count(*),2) as party_percentage_abstain,round((100.0 *sum( case when gender='MAN' then 1 else 0 end)) / count(*),2) as party_percentage_male from vote_data left join view_riksdagen_vote_data_ballot_summary on vote_data.embedded_id_ballot_id = view_riksdagen_vote_data_ballot_summary.ballot_id group by embedded_id_ballot_id,party</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-243">
<dropView viewName="view_riksdagen_vote_data_ballot_party_summary" />
<dropView viewName="view_riksdagen_vote_data_ballot_summary" />
<createView
viewName="view_riksdagen_vote_data_ballot_summary">select embedded_id_ballot_id,embedded_id_concern,embedded_id_issue,max(ballot_type) as ballot_type,max(rm) as rm, max(label) as label,max(vote_date) as vote_date,round(avg(born_year)) as avg_born_year,count(*) as total_votes,sum( case when vote='JA' then 1 else 0 end) as yes_votes,sum( case when vote='NEJ' then 1 else 0 end) as no_votes,sum( case when vote='AVSTÅR' then 1 else 0 end) as abstain_votes,sum( case when vote='FRÅNVARANDE' then 1 else 0 end) as absent_votes , sum( case when vote='JA' then 1 else 0 end) > sum( case when vote='Nej' then 1 else 0 end) as approved ,sum( case when vote='JA' then 1 else 0 end) = sum( case when vote='Nej' then 1 else 0 end) as no_winner, round((100.0 *sum( case when vote='JA' then 1 else 0 end)) / count(*),2) as percentage_yes,round((100.0 *sum( case when vote='NEJ' then 1 else 0 end)) / count(*),2) as percentage_no,round((100.0 *sum( case when vote='FRÅNVARANDE' then 1 else 0 end)) / count(*),2) as percentage_absent,round((100.0 *sum( case when vote='AVSTÅR' then 1 else 0 end)) / count(*),2) as percentage_abstain,round((100.0 *sum( case when gender='MAN' then 1 else 0 end)) / count(*),2) as percentage_male from vote_data group by embedded_id_ballot_id,embedded_id_issue,embedded_id_concern order by vote_date</createView>
<createView
viewName="view_riksdagen_vote_data_ballot_party_summary">select vote_data.embedded_id_ballot_id,vote_data.embedded_id_concern,vote_data.embedded_id_issue,party as embedded_id_party,
max(avg_born_year) as avg_born_year,
max(total_votes) as total_votes,
max(yes_votes) as yes_votes,
max(no_votes) as no_votes,
max(abstain_votes) as abstain_votes,
max(absent_votes) as absent_votes,
bool_or(approved) as approved,
((sum( case when vote='JA' then 1 else 0 end) > sum( case when vote='Nej' then 1 else 0 end)) and bool_or(approved)) or ((sum( case when vote='NEJ' then 1 else 0 end) > sum( case when vote='JA' then 1 else 0 end)) and not bool_or(approved)) as party_won,
bool_or(no_winner) as no_winner,
max(percentage_yes) as percentage_yes,
max(percentage_no) as percentage_no,
max(percentage_absent) as percentage_absent,
max(percentage_abstain) as percentage_abstain,
max(percentage_male) as percentage_male,
max(vote_data.ballot_type) as ballot_type ,max(vote_data.rm) as rm, max(vote_data.label) as label,max(vote_data.vote_date) as vote_date,round(avg(born_year)) as party_avg_born_year,count(*) as party_total_votes,sum( case when vote='JA' then 1 else 0 end) as party_yes_votes,sum( case when vote='NEJ' then 1 else 0 end) as party_no_votes,sum( case when vote='AVSTÅR' then 1 else 0 end) as party_abstain_votes,sum( case when vote='FRÅNVARANDE' then 1 else 0 end) as party_absent_votes , sum( case when vote='JA' then 1 else 0 end) > sum( case when vote='Nej' then 1 else 0 end) as party_approved ,sum( case when vote='JA' then 1 else 0 end) = sum( case when vote='Nej' then 1 else 0 end) as party_no_winner, round((100.0 *sum( case when vote='JA' then 1 else 0 end)) / count(*),2) as party_percentage_yes,round((100.0 *sum( case when vote='NEJ' then 1 else 0 end)) / count(*),2) as party_percentage_no,round((100.0 *sum( case when vote='FRÅNVARANDE' then 1 else 0 end)) / count(*),2) as party_percentage_absent,round((100.0 *sum( case when vote='AVSTÅR' then 1 else 0 end)) / count(*),2) as party_percentage_abstain,round((100.0 *sum( case when gender='MAN' then 1 else 0 end)) / count(*),2) as party_percentage_male from vote_data left join view_riksdagen_vote_data_ballot_summary on vote_data.embedded_id_ballot_id = view_riksdagen_vote_data_ballot_summary.embedded_id_ballot_id and vote_data.embedded_id_issue = view_riksdagen_vote_data_ballot_summary.embedded_id_issue and vote_data.embedded_id_concern = view_riksdagen_vote_data_ballot_summary.embedded_id_concern group by vote_data.embedded_id_ballot_id,vote_data.embedded_id_issue,vote_data.embedded_id_concern,party order by vote_date</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-244">
<createView
viewName="view_riksdagen_vote_data_ballot_summary_daily">select vote_date,count(*) as number_ballots,round(avg(avg_born_year),0) as avg_born_year,max(total_votes) avg_total_votes,round(avg(yes_votes),2) as avg_yes_votes,round(avg(no_votes),2) as avg_no_votes,round(avg(abstain_votes),2) as avg_abstain_votes,round(avg(absent_votes),2) as avg_absent_votes, round((100.0 * sum( case when approved then 1 else 0 end)) / count(*),2) as percentage_approved, round(avg(percentage_yes),2) as avg_percentage_yes,
round(avg(percentage_no),2) as avg_percentage_no,
round(avg(percentage_absent),2) as avg_percentage_absent,
round(avg(percentage_abstain),2) as avg_percentage_abstain,
round(avg(percentage_male),2) as avg_percentage_male
from view_riksdagen_vote_data_ballot_summary group by 1 order by vote_date</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-245">
<createView
viewName="view_riksdagen_vote_data_ballot_summary_weekly">select date_trunc('week', vote_date), sum(number_ballots) as number_ballots,round(avg(avg_born_year),0) as avg_born_year,
round(avg(avg_percentage_yes),2) as avg_percentage_yes,
round(avg(avg_percentage_no),2) as avg_percentage_no,
round(avg(avg_percentage_absent),2) as avg_percentage_absent,
round(avg(avg_percentage_abstain),2) as avg_percentage_abstain,
round(avg(avg_percentage_male),2) as avg_percentage_male,
round(avg(percentage_approved),2) as avg_percentage_approved
from view_riksdagen_vote_data_ballot_summary_daily group by 1</createView>
<createView
viewName="view_riksdagen_vote_data_ballot_summary_monthly">select date_trunc('month', vote_date), sum(number_ballots) as number_ballots,round(avg(avg_born_year),0) as avg_born_year,
round(avg(avg_percentage_yes),2) as avg_percentage_yes,
round(avg(avg_percentage_no),2) as avg_percentage_no,
round(avg(avg_percentage_absent),2) as avg_percentage_absent,
round(avg(avg_percentage_abstain),2) as avg_percentage_abstain,
round(avg(avg_percentage_male),2) as avg_percentage_male,
round(avg(percentage_approved),2) as avg_percentage_approved
from view_riksdagen_vote_data_ballot_summary_daily group by 1</createView>
<createView
viewName="view_riksdagen_vote_data_ballot_summary_annual">select date_trunc('year', vote_date), sum(number_ballots) as number_ballots,round(avg(avg_born_year),0) as avg_born_year,
round(avg(avg_percentage_yes),2) as avg_percentage_yes,
round(avg(avg_percentage_no),2) as avg_percentage_no,
round(avg(avg_percentage_absent),2) as avg_percentage_absent,
round(avg(avg_percentage_abstain),2) as avg_percentage_abstain,
round(avg(avg_percentage_male),2) as avg_percentage_male,
round(avg(percentage_approved),2) as avg_percentage_approved
from view_riksdagen_vote_data_ballot_summary_daily group by 1</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-246">
<dropView viewName="view_riksdagen_vote_data_ballot_summary_weekly" />
<dropView viewName="view_riksdagen_vote_data_ballot_summary_monthly" />
<dropView viewName="view_riksdagen_vote_data_ballot_summary_annual" />
<dropView viewName="view_riksdagen_vote_data_ballot_summary_daily" /> <createView
viewName="view_riksdagen_vote_data_ballot_summary_daily">select vote_date,count(*) as number_ballots,round(avg(avg_born_year),0) as avg_born_year,max(total_votes) avg_total_votes,round(avg(yes_votes),2) as avg_yes_votes,round(avg(no_votes),2) as avg_no_votes,round(avg(abstain_votes),2) as avg_abstain_votes,round(avg(absent_votes),2) as avg_absent_votes, round((100.0 * sum( case when approved then 1 else 0 end)) / count(*),2) as percentage_approved, round(avg(percentage_yes),2) as avg_percentage_yes,
round(avg(percentage_no),2) as avg_percentage_no,
round(avg(percentage_absent),2) as avg_percentage_absent,
round(avg(percentage_abstain),2) as avg_percentage_abstain,
round(avg(percentage_male),2) as avg_percentage_male,
sum(total_votes) as total_votes,
sum(yes_votes) as yes_votes,
sum(no_votes) as no_votes,
sum(abstain_votes) as abstain_votes,
sum(absent_votes) as absent_votes,
round((100.0 * sum(yes_votes)) / sum(total_votes),2) as percentage_yes,
round((100.0 * sum(no_votes)) / sum(total_votes),2) as percentage_no,
round((100.0 * sum(abstain_votes)) / sum(total_votes),2) as percentage_abstain,
round((100.0 * sum(absent_votes)) / sum(total_votes),2) as percentage_absent
from view_riksdagen_vote_data_ballot_summary group by 1 order by vote_date</createView>
<createView
viewName="view_riksdagen_vote_data_ballot_summary_weekly">select date_trunc('week', vote_date), sum(number_ballots) as number_ballots,round(avg(avg_born_year),0) as avg_born_year,
round(avg(avg_percentage_yes),2) as avg_percentage_yes,
round(avg(avg_percentage_no),2) as avg_percentage_no,
round(avg(avg_percentage_absent),2) as avg_percentage_absent,
round(avg(avg_percentage_abstain),2) as avg_percentage_abstain,
round(avg(avg_percentage_male),2) as avg_percentage_male,
sum(total_votes) as total_votes,
sum(yes_votes) as yes_votes,
sum(no_votes) as no_votes,
sum(abstain_votes) as abstain_votes,
sum(absent_votes) as absent_votes,
round((100.0 * sum(yes_votes)) / sum(total_votes),2) as percentage_yes,
round((100.0 * sum(no_votes)) / sum(total_votes),2) as percentage_no,
round((100.0 * sum(abstain_votes)) / sum(total_votes),2) as percentage_abstain,
round((100.0 * sum(absent_votes)) / sum(total_votes),2) as percentage_absent,
round(avg(percentage_approved),2) as avg_percentage_approved
from view_riksdagen_vote_data_ballot_summary_daily group by 1</createView>
<createView
viewName="view_riksdagen_vote_data_ballot_summary_monthly">select date_trunc('month', vote_date), sum(number_ballots) as number_ballots,round(avg(avg_born_year),0) as avg_born_year,
round(avg(avg_percentage_yes),2) as avg_percentage_yes,
round(avg(avg_percentage_no),2) as avg_percentage_no,
round(avg(avg_percentage_absent),2) as avg_percentage_absent,
round(avg(avg_percentage_abstain),2) as avg_percentage_abstain,
round(avg(avg_percentage_male),2) as avg_percentage_male,
sum(total_votes) as total_votes,
sum(yes_votes) as yes_votes,
sum(no_votes) as no_votes,
sum(abstain_votes) as abstain_votes,
sum(absent_votes) as absent_votes,
round((100.0 * sum(yes_votes)) / sum(total_votes),2) as percentage_yes,
round((100.0 * sum(no_votes)) / sum(total_votes),2) as percentage_no,
round((100.0 * sum(abstain_votes)) / sum(total_votes),2) as percentage_abstain,
round((100.0 * sum(absent_votes)) / sum(total_votes),2) as percentage_absent,
round(avg(percentage_approved),2) as avg_percentage_approved
from view_riksdagen_vote_data_ballot_summary_daily group by 1</createView>
<createView
viewName="view_riksdagen_vote_data_ballot_summary_annual">select date_trunc('year', vote_date), sum(number_ballots) as number_ballots,round(avg(avg_born_year),0) as avg_born_year,
round(avg(avg_percentage_yes),2) as avg_percentage_yes,
round(avg(avg_percentage_no),2) as avg_percentage_no,
round(avg(avg_percentage_absent),2) as avg_percentage_absent,
round(avg(avg_percentage_abstain),2) as avg_percentage_abstain,
round(avg(avg_percentage_male),2) as avg_percentage_male,
sum(total_votes) as total_votes,
sum(yes_votes) as yes_votes,
sum(no_votes) as no_votes,
sum(abstain_votes) as abstain_votes,
sum(absent_votes) as absent_votes,
round((100.0 * sum(yes_votes)) / sum(total_votes),2) as percentage_yes,
round((100.0 * sum(no_votes)) / sum(total_votes),2) as percentage_no,
round((100.0 * sum(abstain_votes)) / sum(total_votes),2) as percentage_abstain,
round((100.0 * sum(absent_votes)) / sum(total_votes),2) as percentage_absent,
round(avg(percentage_approved),2) as avg_percentage_approved
from view_riksdagen_vote_data_ballot_summary_daily group by 1</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-247">
<createView
viewName="view_riksdagen_vote_data_ballot_party_summary_daily">select vote_date,embedded_id_party,count(*) as number_ballots,round(avg(avg_born_year),0) as avg_born_year,max(total_votes) avg_total_votes,round(avg(yes_votes),2) as avg_yes_votes,round(avg(no_votes),2) as avg_no_votes,round(avg(abstain_votes),2) as avg_abstain_votes,round(avg(absent_votes),2) as avg_absent_votes, round((100.0 * sum( case when approved then 1 else 0 end)) / count(*),2) as percentage_approved,
round(avg(percentage_yes),2) as avg_percentage_yes,
round(avg(percentage_no),2) as avg_percentage_no,
round(avg(percentage_absent),2) as avg_percentage_absent,
round(avg(percentage_abstain),2) as avg_percentage_abstain,
round(avg(percentage_male),2) as avg_percentage_male,
sum(total_votes) as total_votes,
sum(yes_votes) as yes_votes,
sum(no_votes) as no_votes,
sum(abstain_votes) as abstain_votes,
sum(absent_votes) as absent_votes,
sum(party_total_votes) as party_total_votes,
sum(party_yes_votes) as party_yes_votes,
sum(party_no_votes) as party_no_votes,
sum(party_abstain_votes) as party_abstain_votes,
sum(party_absent_votes) as party_absent_votes,
round(avg(party_avg_born_year),0) as party_avg_born_year,
round(avg(party_percentage_male),2) as party_avg_percentage_male,
round((100.0 * sum(party_yes_votes)) / sum(party_total_votes),2) as party_percentage_yes,
round((100.0 * sum(party_no_votes)) / sum(party_total_votes),2) as party_percentage_no,
round((100.0 * sum(party_abstain_votes)) / sum(party_total_votes),2) as party_percentage_abstain,
round((100.0 * sum(party_absent_votes)) / sum(party_total_votes),2) as party_percentage_absent,
round((100.0 * sum(yes_votes)) / sum(total_votes),2) as percentage_yes,
round((100.0 * sum(no_votes)) / sum(total_votes),2) as percentage_no,
round((100.0 * sum(abstain_votes)) / sum(total_votes),2) as percentage_abstain,
round((100.0 * sum(absent_votes)) / sum(total_votes),2) as percentage_absent,
sum(case when party_won then 1 else 0 end) as party_won_total,
round( (100 * sum(case when party_won then 1 else 0 end)) / count(*),2) as party_won_percentage,
sum(case when approved then 1 else 0 end) as approved_total,
round( (100 * sum(case when approved then 1 else 0 end)) / count(*),2) as approved_percentage
from view_riksdagen_vote_data_ballot_party_summary group by embedded_id_party,1 order by vote_date</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-248">
<createView
viewName="view_riksdagen_vote_data_ballot_party_summary_weekly">select date_trunc('week', vote_date),embedded_id_party, sum(number_ballots) as number_ballots,round(avg(avg_born_year),0) as avg_born_year,
round(avg(avg_percentage_yes),2) as avg_percentage_yes,
round(avg(avg_percentage_no),2) as avg_percentage_no,
round(avg(avg_percentage_absent),2) as avg_percentage_absent,
round(avg(avg_percentage_abstain),2) as avg_percentage_abstain,
round(avg(avg_percentage_male),2) as avg_percentage_male,
sum(total_votes) as total_votes,
sum(yes_votes) as yes_votes,
sum(no_votes) as no_votes,
sum(abstain_votes) as abstain_votes,
sum(absent_votes) as absent_votes,
sum(party_total_votes) as party_total_votes,
sum(party_yes_votes) as party_yes_votes,
sum(party_no_votes) as party_no_votes,
sum(party_abstain_votes) as party_abstain_votes,
sum(party_absent_votes) as party_absent_votes,
round(avg(party_avg_born_year),0) as party_avg_born_year,
round(avg(party_avg_percentage_male),2) as party_avg_percentage_male,
round((100.0 * sum(party_yes_votes)) / sum(party_total_votes),2) as party_percentage_yes,
round((100.0 * sum(party_no_votes)) / sum(party_total_votes),2) as party_percentage_no,
round((100.0 * sum(party_abstain_votes)) / sum(party_total_votes),2) as party_percentage_abstain,
round((100.0 * sum(party_absent_votes)) / sum(party_total_votes),2) as party_percentage_absent,
sum(party_won_total) as party_won_total,
round( (100 * sum(party_won_total)) / sum(number_ballots),2) as party_won_percentage,
sum(approved_total) as approved_total,
round( (100 * sum(approved_total)) / sum(number_ballots),2) as approved_percentage,
round((100.0 * sum(yes_votes)) / sum(total_votes),2) as percentage_yes,
round((100.0 * sum(no_votes)) / sum(total_votes),2) as percentage_no,
round((100.0 * sum(abstain_votes)) / sum(total_votes),2) as percentage_abstain,
round((100.0 * sum(absent_votes)) / sum(total_votes),2) as percentage_absent,
round(avg(percentage_approved),2) as avg_percentage_approved
from view_riksdagen_vote_data_ballot_party_summary_daily group by 1,embedded_id_party</createView>
<createView
viewName="view_riksdagen_vote_data_ballot_party_summary_monthly">select date_trunc('month', vote_date),embedded_id_party, sum(number_ballots) as number_ballots,round(avg(avg_born_year),0) as avg_born_year,
round(avg(avg_percentage_yes),2) as avg_percentage_yes,
round(avg(avg_percentage_no),2) as avg_percentage_no,
round(avg(avg_percentage_absent),2) as avg_percentage_absent,
round(avg(avg_percentage_abstain),2) as avg_percentage_abstain,
round(avg(avg_percentage_male),2) as avg_percentage_male,
sum(total_votes) as total_votes,
sum(yes_votes) as yes_votes,
sum(no_votes) as no_votes,
sum(abstain_votes) as abstain_votes,
sum(absent_votes) as absent_votes,
sum(party_total_votes) as party_total_votes,
sum(party_yes_votes) as party_yes_votes,
sum(party_no_votes) as party_no_votes,
sum(party_abstain_votes) as party_abstain_votes,
sum(party_absent_votes) as party_absent_votes,
round(avg(party_avg_born_year),0) as party_avg_born_year,
round(avg(party_avg_percentage_male),2) as party_avg_percentage_male,
round((100.0 * sum(party_yes_votes)) / sum(party_total_votes),2) as party_percentage_yes,
round((100.0 * sum(party_no_votes)) / sum(party_total_votes),2) as party_percentage_no,
round((100.0 * sum(party_abstain_votes)) / sum(party_total_votes),2) as party_percentage_abstain,
round((100.0 * sum(party_absent_votes)) / sum(party_total_votes),2) as party_percentage_absent,
sum(party_won_total) as party_won_total,
round( (100 * sum(party_won_total)) / sum(number_ballots),2) as party_won_percentage,
sum(approved_total) as approved_total,
round( (100 * sum(approved_total)) / sum(number_ballots),2) as approved_percentage,
round((100.0 * sum(yes_votes)) / sum(total_votes),2) as percentage_yes,
round((100.0 * sum(no_votes)) / sum(total_votes),2) as percentage_no,
round((100.0 * sum(abstain_votes)) / sum(total_votes),2) as percentage_abstain,
round((100.0 * sum(absent_votes)) / sum(total_votes),2) as percentage_absent,
round(avg(percentage_approved),2) as avg_percentage_approved
from view_riksdagen_vote_data_ballot_party_summary_daily group by 1,embedded_id_party</createView>
<createView
viewName="view_riksdagen_vote_data_ballot_party_summary_annual">select date_trunc('year', vote_date),embedded_id_party, sum(number_ballots) as number_ballots,round(avg(avg_born_year),0) as avg_born_year,
round(avg(avg_percentage_yes),2) as avg_percentage_yes,
round(avg(avg_percentage_no),2) as avg_percentage_no,
round(avg(avg_percentage_absent),2) as avg_percentage_absent,
round(avg(avg_percentage_abstain),2) as avg_percentage_abstain,
round(avg(avg_percentage_male),2) as avg_percentage_male,
sum(total_votes) as total_votes,
sum(yes_votes) as yes_votes,
sum(no_votes) as no_votes,
sum(abstain_votes) as abstain_votes,
sum(absent_votes) as absent_votes,
sum(party_total_votes) as party_total_votes,
sum(party_yes_votes) as party_yes_votes,
sum(party_no_votes) as party_no_votes,
sum(party_abstain_votes) as party_abstain_votes,
sum(party_absent_votes) as party_absent_votes,
round(avg(party_avg_born_year),0) as party_avg_born_year,
round(avg(party_avg_percentage_male),2) as party_avg_percentage_male,
round((100.0 * sum(party_yes_votes)) / sum(party_total_votes),2) as party_percentage_yes,
round((100.0 * sum(party_no_votes)) / sum(party_total_votes),2) as party_percentage_no,
round((100.0 * sum(party_abstain_votes)) / sum(party_total_votes),2) as party_percentage_abstain,
round((100.0 * sum(party_absent_votes)) / sum(party_total_votes),2) as party_percentage_absent,
sum(party_won_total) as party_won_total,
round( (100 * sum(party_won_total)) / sum(number_ballots),2) as party_won_percentage,
sum(approved_total) as approved_total,
round( (100 * sum(approved_total)) / sum(number_ballots),2) as approved_percentage,
round((100.0 * sum(yes_votes)) / sum(total_votes),2) as percentage_yes,
round((100.0 * sum(no_votes)) / sum(total_votes),2) as percentage_no,
round((100.0 * sum(abstain_votes)) / sum(total_votes),2) as percentage_abstain,
round((100.0 * sum(absent_votes)) / sum(total_votes),2) as percentage_absent,
round(avg(percentage_approved),2) as avg_percentage_approved
from view_riksdagen_vote_data_ballot_party_summary_daily group by 1,embedded_id_party</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-249">
<createView
viewName="view_riksdagen_vote_data_ballot_politician_summary">select vote_data.embedded_id_ballot_id,embedded_id_intressent_id,vote_data.embedded_id_concern,vote_data.embedded_id_issue,party as embedded_id_party,max(first_name) as first_name, max(last_name) as last_name,max(gender) as gender, max(born_year) as born_year,
max(avg_born_year) as avg_born_year,
max(total_votes) as total_votes,
max(yes_votes) as yes_votes,
max(no_votes) as no_votes,
max(abstain_votes) as abstain_votes,
max(absent_votes) as absent_votes,
bool_or(approved) as approved,
max(vote) as vote,
case when (vote='JA' and bool_or(approved)) or (vote='NEJ' and not bool_or(approved)) then true else false end as won,
case when (vote='NEJ' and bool_or(party_approved)) or (vote='JA' and not bool_or(party_approved)) then true else false end as rebel,
bool_or(party_won) as party_won,
bool_or(no_winner) as no_winner,
max(percentage_yes) as percentage_yes,
max(percentage_no) as percentage_no,
max(percentage_absent) as percentage_absent,
max(percentage_abstain) as percentage_abstain,
max(percentage_male) as percentage_male,
max(vote_data.ballot_type) as ballot_type ,max(vote_data.rm) as rm, max(vote_data.label) as label,max(vote_data.vote_date) as vote_date,max(party_avg_born_year) as party_avg_born_year,max(party_total_votes) as party_total_votes,max(party_yes_votes) as party_yes_votes,max(party_no_votes) as party_no_votes,max(party_abstain_votes) as party_abstain_votes,max(party_absent_votes) as party_absent_votes ,bool_or(party_approved) as party_approved,bool_or(party_no_winner) as party_no_winner, max(party_percentage_yes) as party_percentage_yes,max(party_percentage_no) as party_percentage_no,max(party_percentage_absent) as party_percentage_absent,max(party_percentage_abstain) as party_percentage_abstain,max(party_percentage_male) as party_percentage_male from vote_data left join view_riksdagen_vote_data_ballot_party_summary on vote_data.embedded_id_ballot_id = view_riksdagen_vote_data_ballot_party_summary.embedded_id_ballot_id and vote_data.embedded_id_issue = view_riksdagen_vote_data_ballot_party_summary.embedded_id_issue and vote_data.embedded_id_concern = view_riksdagen_vote_data_ballot_party_summary.embedded_id_concern and vote_data.party = view_riksdagen_vote_data_ballot_party_summary.embedded_id_party group by vote_data.embedded_id_ballot_id,vote_data.embedded_id_issue,vote_data.embedded_id_concern,embedded_id_intressent_id order by vote_date</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-250">
<createView
viewName="view_riksdagen_vote_data_ballot_politician_summary_daily">select vote_date,embedded_id_intressent_id,max(first_name) as first_name,max(last_name) as last_name,max(gender) as gender,max(born_year) as born_year,max(embedded_id_party) as embedded_id_party,count(*) as number_ballots,round(avg(avg_born_year),0) as avg_born_year,max(total_votes) avg_total_votes,round(avg(yes_votes),2) as avg_yes_votes,round(avg(no_votes),2) as avg_no_votes,round(avg(abstain_votes),2) as avg_abstain_votes,round(avg(absent_votes),2) as avg_absent_votes, round((100.0 * sum( case when approved then 1 else 0 end)) / count(*),2) as percentage_approved,
round(avg(percentage_yes),2) as avg_percentage_yes,
round(avg(percentage_no),2) as avg_percentage_no,
round(avg(percentage_absent),2) as avg_percentage_absent,
round(avg(percentage_abstain),2) as avg_percentage_abstain,
round(avg(percentage_male),2) as avg_percentage_male,
sum(total_votes) as total_votes,
sum(yes_votes) as yes_votes,
sum(no_votes) as no_votes,
sum(abstain_votes) as abstain_votes,
sum(absent_votes) as absent_votes,
sum(party_total_votes) as party_total_votes,
sum(party_yes_votes) as party_yes_votes,
sum(party_no_votes) as party_no_votes,
sum(party_abstain_votes) as party_abstain_votes,
sum(party_absent_votes) as party_absent_votes,
sum( case when vote='JA' then 1 else 0 end) as politician_yes_votes,
sum( case when vote='NEJ' then 1 else 0 end) as politician_no_votes,
sum( case when vote='AVSTÅR' then 1 else 0 end) as politician_abstain_votes,
sum( case when vote='FRÅNVARANDE' then 1 else 0 end) as politician_absent_votes,
round((100.0 *sum( case when vote='JA' then 1 else 0 end)) / count(*),2) as politician_percentage_yes,
round((100.0 *sum( case when vote='NEJ' then 1 else 0 end)) / count(*),2) as politician_percentage_no,
round((100.0 *sum( case when vote='FRÅNVARANDE' then 1 else 0 end)) / count(*),2) as politician_percentage_absent,
round((100.0 *sum( case when vote='AVSTÅR' then 1 else 0 end)) / count(*),2) as politician_percentage_abstain,
round(avg(party_avg_born_year),0) as party_avg_born_year,
round(avg(party_percentage_male),2) as party_avg_percentage_male,
round((100.0 * sum(party_yes_votes)) / sum(party_total_votes),2) as party_percentage_yes,
round((100.0 * sum(party_no_votes)) / sum(party_total_votes),2) as party_percentage_no,
round((100.0 * sum(party_abstain_votes)) / sum(party_total_votes),2) as party_percentage_abstain,
round((100.0 * sum(party_absent_votes)) / sum(party_total_votes),2) as party_percentage_absent,
round((100.0 * sum(yes_votes)) / sum(total_votes),2) as percentage_yes,
round((100.0 * sum(no_votes)) / sum(total_votes),2) as percentage_no,
round((100.0 * sum(abstain_votes)) / sum(total_votes),2) as percentage_abstain,
round((100.0 * sum(absent_votes)) / sum(total_votes),2) as percentage_absent,
sum(case when party_won then 1 else 0 end) as party_won_total,
round( (100 * sum(case when party_won then 1 else 0 end)) / count(*),2) as party_won_percentage,
sum(case when approved then 1 else 0 end) as approved_total,
round( (100 * sum(case when approved then 1 else 0 end)) / count(*),2) as approved_percentage,
round( (100 * sum(case when won then 1 else 0 end)) / count(*),2) as won_percentage,
sum(case when won then 1 else 0 end) as won_total,
round( (100 * sum(case when rebel then 1 else 0 end)) / count(*),2) as rebel_percentage,
sum(case when rebel then 1 else 0 end) as rebel_total
from view_riksdagen_vote_data_ballot_politician_summary group by embedded_id_intressent_id,1</createView>
<createView
viewName="view_riksdagen_vote_data_ballot_politician_summary_weekly">select date_trunc('week', vote_date),embedded_id_intressent_id,max(first_name) as first_name,max(last_name) as last_name,max(gender) as gender,max(born_year) as born_year,max(embedded_id_party) as embedded_id_party, sum(number_ballots) as number_ballots,round(avg(avg_born_year),0) as avg_born_year,
round(avg(avg_percentage_yes),2) as avg_percentage_yes,
round(avg(avg_percentage_no),2) as avg_percentage_no,
round(avg(avg_percentage_absent),2) as avg_percentage_absent,
round(avg(avg_percentage_abstain),2) as avg_percentage_abstain,
round(avg(avg_percentage_male),2) as avg_percentage_male,
sum(total_votes) as total_votes,
sum(yes_votes) as yes_votes,
sum(no_votes) as no_votes,
sum(abstain_votes) as abstain_votes,
sum(absent_votes) as absent_votes,
sum(party_total_votes) as party_total_votes,
sum(party_yes_votes) as party_yes_votes,
sum(party_no_votes) as party_no_votes,
sum(party_abstain_votes) as party_abstain_votes,
sum(party_absent_votes) as party_absent_votes,
round(avg(party_avg_born_year),0) as party_avg_born_year,
round(avg(party_avg_percentage_male),2) as party_avg_percentage_male,
round((100.0 * sum(party_yes_votes)) / sum(party_total_votes),2) as party_percentage_yes,
round((100.0 * sum(party_no_votes)) / sum(party_total_votes),2) as party_percentage_no,
round((100.0 * sum(party_abstain_votes)) / sum(party_total_votes),2) as party_percentage_abstain,
round((100.0 * sum(party_absent_votes)) / sum(party_total_votes),2) as party_percentage_absent,
sum(party_won_total) as party_won_total,
sum(politician_yes_votes) as politician_yes_votes,
sum(politician_no_votes) as politician_no_votes,
sum(politician_abstain_votes) as politician_abstain_votes,
sum(politician_absent_votes) as politician_absent_votes,
round((100.0 *sum(politician_yes_votes)) / sum(number_ballots),2) as politician_percentage_yes,
round((100.0 *sum(politician_no_votes)) / sum(number_ballots),2) as politician_percentage_no,
round((100.0 *sum(politician_abstain_votes)) / sum(number_ballots),2) as politician_percentage_absent,
round((100.0 *sum( politician_absent_votes)) / sum(number_ballots),2) as politician_percentage_abstain,
round( (100 * sum(won_total)) / sum(number_ballots),2) as won_percentage,
sum(won_total) as won_total,
round( (100 * sum(rebel_total)) / sum(number_ballots),2) as rebel_percentage,
sum(rebel_total) as rebel_total,
round( (100 * sum(party_won_total)) / sum(number_ballots),2) as party_won_percentage,
sum(approved_total) as approved_total,
round( (100 * sum(approved_total)) / sum(number_ballots),2) as approved_percentage,
round((100.0 * sum(yes_votes)) / sum(total_votes),2) as percentage_yes,
round((100.0 * sum(no_votes)) / sum(total_votes),2) as percentage_no,
round((100.0 * sum(abstain_votes)) / sum(total_votes),2) as percentage_abstain,
round((100.0 * sum(absent_votes)) / sum(total_votes),2) as percentage_absent,
round(avg(percentage_approved),2) as avg_percentage_approved
from view_riksdagen_vote_data_ballot_politician_summary_daily group by 1,embedded_id_intressent_id</createView>
<createView
viewName="view_riksdagen_vote_data_ballot_politician_summary_monthly">select date_trunc('month', vote_date),embedded_id_intressent_id,max(first_name) as first_name,max(last_name) as last_name,max(gender) as gender,max(born_year) as born_year,max(embedded_id_party) as embedded_id_party, sum(number_ballots) as number_ballots,round(avg(avg_born_year),0) as avg_born_year,
round(avg(avg_percentage_yes),2) as avg_percentage_yes,
round(avg(avg_percentage_no),2) as avg_percentage_no,
round(avg(avg_percentage_absent),2) as avg_percentage_absent,
round(avg(avg_percentage_abstain),2) as avg_percentage_abstain,
round(avg(avg_percentage_male),2) as avg_percentage_male,
sum(total_votes) as total_votes,
sum(yes_votes) as yes_votes,
sum(no_votes) as no_votes,
sum(abstain_votes) as abstain_votes,
sum(absent_votes) as absent_votes,
sum(party_total_votes) as party_total_votes,
sum(party_yes_votes) as party_yes_votes,
sum(party_no_votes) as party_no_votes,
sum(party_abstain_votes) as party_abstain_votes,
sum(party_absent_votes) as party_absent_votes,
round(avg(party_avg_born_year),0) as party_avg_born_year,
round(avg(party_avg_percentage_male),2) as party_avg_percentage_male,
round((100.0 * sum(party_yes_votes)) / sum(party_total_votes),2) as party_percentage_yes,
round((100.0 * sum(party_no_votes)) / sum(party_total_votes),2) as party_percentage_no,
round((100.0 * sum(party_abstain_votes)) / sum(party_total_votes),2) as party_percentage_abstain,
round((100.0 * sum(party_absent_votes)) / sum(party_total_votes),2) as party_percentage_absent,
sum(party_won_total) as party_won_total,
sum(politician_yes_votes) as politician_yes_votes,
sum(politician_no_votes) as politician_no_votes,
sum(politician_abstain_votes) as politician_abstain_votes,
sum(politician_absent_votes) as politician_absent_votes,
round((100.0 *sum(politician_yes_votes)) / sum(number_ballots),2) as politician_percentage_yes,
round((100.0 *sum(politician_no_votes)) / sum(number_ballots),2) as politician_percentage_no,
round((100.0 *sum(politician_abstain_votes)) / sum(number_ballots),2) as politician_percentage_absent,
round((100.0 *sum( politician_absent_votes)) / sum(number_ballots),2) as politician_percentage_abstain,
round( (100 * sum(won_total)) / sum(number_ballots),2) as won_percentage,
sum(won_total) as won_total,
round( (100 * sum(rebel_total)) / sum(number_ballots),2) as rebel_percentage,
sum(rebel_total) as rebel_total,
round( (100 * sum(party_won_total)) / sum(number_ballots),2) as party_won_percentage,
sum(approved_total) as approved_total,
round( (100 * sum(approved_total)) / sum(number_ballots),2) as approved_percentage,
round((100.0 * sum(yes_votes)) / sum(total_votes),2) as percentage_yes,
round((100.0 * sum(no_votes)) / sum(total_votes),2) as percentage_no,
round((100.0 * sum(abstain_votes)) / sum(total_votes),2) as percentage_abstain,
round((100.0 * sum(absent_votes)) / sum(total_votes),2) as percentage_absent,
round(avg(percentage_approved),2) as avg_percentage_approved
from view_riksdagen_vote_data_ballot_politician_summary_daily group by 1,embedded_id_intressent_id</createView>
<createView
viewName="view_riksdagen_vote_data_ballot_politician_summary_annual">select date_trunc('year', vote_date),embedded_id_intressent_id,max(first_name) as first_name,max(last_name) as last_name,max(gender) as gender,max(born_year) as born_year,max(embedded_id_party) as embedded_id_party, sum(number_ballots) as number_ballots,round(avg(avg_born_year),0) as avg_born_year,
round(avg(avg_percentage_yes),2) as avg_percentage_yes,
round(avg(avg_percentage_no),2) as avg_percentage_no,
round(avg(avg_percentage_absent),2) as avg_percentage_absent,
round(avg(avg_percentage_abstain),2) as avg_percentage_abstain,
round(avg(avg_percentage_male),2) as avg_percentage_male,
sum(total_votes) as total_votes,
sum(yes_votes) as yes_votes,
sum(no_votes) as no_votes,
sum(abstain_votes) as abstain_votes,
sum(absent_votes) as absent_votes,
sum(party_total_votes) as party_total_votes,
sum(party_yes_votes) as party_yes_votes,
sum(party_no_votes) as party_no_votes,
sum(party_abstain_votes) as party_abstain_votes,
sum(party_absent_votes) as party_absent_votes,
round(avg(party_avg_born_year),0) as party_avg_born_year,
round(avg(party_avg_percentage_male),2) as party_avg_percentage_male,
round((100.0 * sum(party_yes_votes)) / sum(party_total_votes),2) as party_percentage_yes,
round((100.0 * sum(party_no_votes)) / sum(party_total_votes),2) as party_percentage_no,
round((100.0 * sum(party_abstain_votes)) / sum(party_total_votes),2) as party_percentage_abstain,
round((100.0 * sum(party_absent_votes)) / sum(party_total_votes),2) as party_percentage_absent,
sum(party_won_total) as party_won_total,
sum(politician_yes_votes) as politician_yes_votes,
sum(politician_no_votes) as politician_no_votes,
sum(politician_abstain_votes) as politician_abstain_votes,
sum(politician_absent_votes) as politician_absent_votes,
round((100.0 *sum(politician_yes_votes)) / sum(number_ballots),2) as politician_percentage_yes,
round((100.0 *sum(politician_no_votes)) / sum(number_ballots),2) as politician_percentage_no,
round((100.0 *sum(politician_abstain_votes)) / sum(number_ballots),2) as politician_percentage_absent,
round((100.0 *sum( politician_absent_votes)) / sum(number_ballots),2) as politician_percentage_abstain,
round( (100 * sum(won_total)) / sum(number_ballots),2) as won_percentage,
sum(won_total) as won_total,
round( (100 * sum(rebel_total)) / sum(number_ballots),2) as rebel_percentage,
sum(rebel_total) as rebel_total,
round( (100 * sum(party_won_total)) / sum(number_ballots),2) as party_won_percentage,
sum(approved_total) as approved_total,
round( (100 * sum(approved_total)) / sum(number_ballots),2) as approved_percentage,
round((100.0 * sum(yes_votes)) / sum(total_votes),2) as percentage_yes,
round((100.0 * sum(no_votes)) / sum(total_votes),2) as percentage_no,
round((100.0 * sum(abstain_votes)) / sum(total_votes),2) as percentage_abstain,
round((100.0 * sum(absent_votes)) / sum(total_votes),2) as percentage_absent,
round(avg(percentage_approved),2) as avg_percentage_approved
from view_riksdagen_vote_data_ballot_politician_summary_daily group by 1,embedded_id_intressent_id</createView>
</changeSet>
</databaseChangeLog>