service.data.impl/src/main/resources/db-changelog-1.5.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-257">
<dropView viewName="view_riksdagen_committee_decision_type_summary" />
<dropView viewName="view_riksdagen_committee_decision_type_org_summary" />
<dropView viewName="view_riksdagen_committee_decisions" />
<createView
viewName="view_riksdagen_committee_decisions">select id as embedded_id_id,title,header,hangar_id as embedded_id_hangar_id,label as committee_report,committee_document_data.rm,end_number,issue_number as embedded_id_issue_nummer,org,created_date,public_date,committee_proposal_url_xml,decision_type,ballot_id,against_proposal_parties,against_proposal_number,winner from committee_proposal_data LEFT JOIN committee_document_data ON committee_document_data.label=committee_report and committee_proposal_data.rm=committee_document_data.rm</createView>
<createView
viewName="view_riksdagen_committee_decision_type_summary">select upper(decision_type) as embedded_id_decision_type,public_date as embedded_id_decision_date,count(*) as total,sum( case when winner='utskottet' then 1 else 0 end) as committee_winner,sum( case when winner like'reservation%' then 1 else 0 end) as reservation_winner from view_riksdagen_committee_decisions group by upper(decision_type),public_date order by public_date</createView>
<createView
viewName="view_riksdagen_committee_decision_type_org_summary">select upper(decision_type) as embedded_id_decision_type,org as embedded_id_org,public_date as embedded_id_decision_date,count(*) as total,sum( case when winner='utskottet' then 1 else 0 end) as committee_winner,sum( case when winner like'reservation%' then 1 else 0 end) as reservation_winner from view_riksdagen_committee_decisions group by upper(decision_type),public_date,org</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-258">
<createView
viewName="view_riksdagen_committee_ballot_decision_summary">select embedded_id_id,embedded_id_issue,embedded_id_hangar_id,committee_report,view_riksdagen_committee_decisions.rm,title,header as sub_title,end_number,org,created_date,public_date,ballot_id,decision_type,against_proposal_parties,against_proposal_number,winner,embedded_id_concern,ballot_type,label,vote_date,avg_born_year,total_votes,yes_votes,no_votes,abstain_votes,absent_votes,approved,no_winner,percentage_yes,percentage_no,percentage_absent,percentage_abstain,percentage_male from view_riksdagen_committee_decisions left join view_riksdagen_vote_data_ballot_summary on ballot_id = embedded_id_ballot_id and view_riksdagen_committee_decisions.rm=view_riksdagen_vote_data_ballot_summary.rm and view_riksdagen_committee_decisions.embedded_id_issue_nummer ::text=view_riksdagen_vote_data_ballot_summary.embedded_id_issue where decision_type='röstning' and ballot_id is not null and embedded_id_ballot_id is not null</createView>
<createView
viewName="view_riksdagen_committee_ballot_decision_party_summary">select embedded_id_party,embedded_id_id,embedded_id_issue,embedded_id_hangar_id,committee_report,view_riksdagen_committee_decisions.rm,title,header as sub_title,end_number,org,created_date,public_date,ballot_id,decision_type,against_proposal_parties,against_proposal_number,winner,embedded_id_concern,ballot_type,label,vote_date,avg_born_year,total_votes,yes_votes,no_votes,abstain_votes,absent_votes,approved,no_winner,percentage_yes,percentage_no,percentage_absent,percentage_abstain,percentage_male,party_avg_born_year,party_total_votes,party_yes_votes,party_no_votes,party_abstain_votes,party_absent_votes,party_approved,party_no_winner,party_percentage_yes,party_percentage_no,party_percentage_absent,party_percentage_abstain,party_percentage_male from view_riksdagen_committee_decisions left join view_riksdagen_vote_data_ballot_party_summary on ballot_id = embedded_id_ballot_id and view_riksdagen_committee_decisions.rm=view_riksdagen_vote_data_ballot_party_summary.rm and view_riksdagen_committee_decisions.embedded_id_issue_nummer::text=view_riksdagen_vote_data_ballot_party_summary.embedded_id_issue where decision_type='röstning' and ballot_id is not null and embedded_id_ballot_id is not null</createView>
<createView
viewName="view_riksdagen_committee_ballot_decision_politician_summary">select embedded_id_intressent_id,party,embedded_id_id,embedded_id_issue,embedded_id_hangar_id,first_name,last_name,born_year,vote,won,rebel,committee_report,view_riksdagen_committee_decisions.rm,title,header as sub_title,end_number,org,created_date,public_date,ballot_id,decision_type,against_proposal_parties,against_proposal_number,winner,embedded_id_concern,ballot_type,label,vote_date,avg_born_year,total_votes,yes_votes,no_votes,abstain_votes,absent_votes,approved,no_winner,percentage_yes,percentage_no,percentage_absent,percentage_abstain,percentage_male,party_avg_born_year,party_total_votes,party_yes_votes,party_no_votes,party_abstain_votes,party_absent_votes,party_approved,party_no_winner,party_percentage_yes,party_percentage_no,party_percentage_absent,party_percentage_abstain,party_percentage_male from view_riksdagen_committee_decisions left join view_riksdagen_vote_data_ballot_politician_summary on ballot_id = embedded_id_ballot_id and view_riksdagen_committee_decisions.rm=view_riksdagen_vote_data_ballot_politician_summary.rm and view_riksdagen_committee_decisions.embedded_id_issue_nummer::text=view_riksdagen_vote_data_ballot_politician_summary.embedded_id_issue where decision_type='röstning' and ballot_id is not null and embedded_id_ballot_id is not null</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-259">
<dropView viewName="view_riksdagen_committee_decision_type_summary" /> <createView
viewName="view_riksdagen_committee_decision_type_summary">select upper(decision_type) as embedded_id_decision_type,created_date as embedded_id_decision_date,count(*) as total,sum( case when winner='utskottet' then 1 else 0 end) as committee_winner,sum( case when winner like'reservation%' then 1 else 0 end) as reservation_winner from view_riksdagen_committee_decisions group by upper(decision_type),created_date order by created_date</createView>
<dropView viewName="view_riksdagen_committee_decision_type_org_summary" /> <createView
viewName="view_riksdagen_committee_decision_type_org_summary">select upper(decision_type) as embedded_id_decision_type,org as embedded_id_org,created_date as embedded_id_decision_date,count(*) as total,sum( case when winner='utskottet' then 1 else 0 end) as committee_winner,sum( case when winner like'reservation%' then 1 else 0 end) as reservation_winner from view_riksdagen_committee_decisions group by upper(decision_type),created_date,org</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-260">
<dropView viewName="view_riksdagen_committee_decision_type_summary" />
<dropView viewName="view_riksdagen_committee_decision_type_org_summary" />
<dropView viewName="view_riksdagen_committee_ballot_decision_summary" />
<dropView viewName="view_riksdagen_committee_ballot_decision_party_summary" />
<dropView
viewName="view_riksdagen_committee_ballot_decision_politician_summary" />
<dropView viewName="view_riksdagen_committee_decisions" /> <createView
viewName="view_riksdagen_committee_decisions">select id as embedded_id_id,title,header,hangar_id as embedded_id_hangar_id,label as committee_report,committee_document_data.rm,end_number,issue_number as embedded_id_issue_nummer,upper(org) as org,created_date,public_date,committee_proposal_url_xml,decision_type,ballot_id,against_proposal_parties,against_proposal_number,winner from committee_proposal_data LEFT JOIN committee_document_data ON committee_document_data.label=committee_report and committee_proposal_data.rm=committee_document_data.rm</createView>
<createView
viewName="view_riksdagen_committee_ballot_decision_summary">select embedded_id_id,embedded_id_issue,embedded_id_hangar_id,committee_report,view_riksdagen_committee_decisions.rm,title,header as sub_title,end_number,upper(org) as org,created_date,public_date,ballot_id,decision_type,against_proposal_parties,against_proposal_number,winner,embedded_id_concern,ballot_type,label,vote_date,avg_born_year,total_votes,yes_votes,no_votes,abstain_votes,absent_votes,approved,no_winner,percentage_yes,percentage_no,percentage_absent,percentage_abstain,percentage_male from view_riksdagen_committee_decisions left join view_riksdagen_vote_data_ballot_summary on ballot_id = embedded_id_ballot_id and view_riksdagen_committee_decisions.rm=view_riksdagen_vote_data_ballot_summary.rm and view_riksdagen_committee_decisions.embedded_id_issue_nummer ::text=view_riksdagen_vote_data_ballot_summary.embedded_id_issue where decision_type='röstning' and ballot_id is not null and embedded_id_ballot_id is not null</createView>
<createView
viewName="view_riksdagen_committee_ballot_decision_party_summary">select embedded_id_party,embedded_id_id,embedded_id_issue,embedded_id_hangar_id,committee_report,view_riksdagen_committee_decisions.rm,title,header as sub_title,end_number,upper(org) as org,created_date,public_date,ballot_id,decision_type,against_proposal_parties,against_proposal_number,winner,embedded_id_concern,ballot_type,label,vote_date,avg_born_year,total_votes,yes_votes,no_votes,abstain_votes,absent_votes,approved,no_winner,percentage_yes,percentage_no,percentage_absent,percentage_abstain,percentage_male,party_avg_born_year,party_total_votes,party_yes_votes,party_no_votes,party_abstain_votes,party_absent_votes,party_approved,party_no_winner,party_percentage_yes,party_percentage_no,party_percentage_absent,party_percentage_abstain,party_percentage_male from view_riksdagen_committee_decisions left join view_riksdagen_vote_data_ballot_party_summary on ballot_id = embedded_id_ballot_id and view_riksdagen_committee_decisions.rm=view_riksdagen_vote_data_ballot_party_summary.rm and view_riksdagen_committee_decisions.embedded_id_issue_nummer::text=view_riksdagen_vote_data_ballot_party_summary.embedded_id_issue where decision_type='röstning' and ballot_id is not null and embedded_id_ballot_id is not null</createView>
<createView
viewName="view_riksdagen_committee_ballot_decision_politician_summary">select embedded_id_intressent_id,party,embedded_id_id,embedded_id_issue,embedded_id_hangar_id,first_name,last_name,born_year,vote,won,rebel,committee_report,view_riksdagen_committee_decisions.rm,title,header as sub_title,end_number,upper(org) as org,created_date,public_date,ballot_id,decision_type,against_proposal_parties,against_proposal_number,winner,embedded_id_concern,ballot_type,label,vote_date,avg_born_year,total_votes,yes_votes,no_votes,abstain_votes,absent_votes,approved,no_winner,percentage_yes,percentage_no,percentage_absent,percentage_abstain,percentage_male,party_avg_born_year,party_total_votes,party_yes_votes,party_no_votes,party_abstain_votes,party_absent_votes,party_approved,party_no_winner,party_percentage_yes,party_percentage_no,party_percentage_absent,party_percentage_abstain,party_percentage_male from view_riksdagen_committee_decisions left join view_riksdagen_vote_data_ballot_politician_summary on ballot_id = embedded_id_ballot_id and view_riksdagen_committee_decisions.rm=view_riksdagen_vote_data_ballot_politician_summary.rm and view_riksdagen_committee_decisions.embedded_id_issue_nummer::text=view_riksdagen_vote_data_ballot_politician_summary.embedded_id_issue where decision_type='röstning' and ballot_id is not null and embedded_id_ballot_id is not null</createView>
<createView
viewName="view_riksdagen_committee_decision_type_summary">select upper(decision_type) as embedded_id_decision_type,created_date as embedded_id_decision_date,count(*) as total,sum( case when winner='utskottet' then 1 else 0 end) as committee_winner,sum( case when winner like'reservation%' then 1 else 0 end) as reservation_winner from view_riksdagen_committee_decisions group by upper(decision_type),created_date order by created_date</createView>
<createView
viewName="view_riksdagen_committee_decision_type_org_summary">select upper(decision_type) as embedded_id_decision_type,upper(org) as embedded_id_org,created_date as embedded_id_decision_date,count(*) as total,sum( case when winner='utskottet' then 1 else 0 end) as committee_winner,sum( case when winner like'reservation%' then 1 else 0 end) as reservation_winner from view_riksdagen_committee_decisions group by upper(decision_type),created_date,upper(org)</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-261">
<createView
viewName="view_worldbank_indicator_data_country_summary">select indicator_id as embedded_id_indicator_id,country_id as embedded_id_country_id,max(indicator_name) as indicator_name,max(source_id) as source_id,max(source_value) as source_value,max(source_note) as source_note,max(source_organization) as source_organization,min(year_date) as start_year, max(year_date) as end_year,count(*) as data_point from world_bank_data left join indicator_element on indicator_id = indicator_element.id where data_value!='' group by indicator_id,country_id</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-262">
<dropView viewName="view_worldbank_indicator_data_country_summary" /> <createView
viewName="view_worldbank_indicator_data_country_summary">select indicator_id as embedded_id_indicator_id,country_id as embedded_id_country_id,max(indicator_name) as indicator_name,max(source_id) as source_id,max(source_value) as source_value,max(source_note) as source_note,max(source_organization) as source_organization,min(year_date) as start_year, max(year_date) as end_year,count(*) as data_point from world_bank_data left join indicator_element on indicator_id = indicator_element.id where data_value!='' and data_value!='0' and year_date is not null and source_organization is not null and source_organization !='' group by indicator_id,country_id</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-263">
<dropView viewName="view_worldbank_indicator_data_country_summary" /> <createView
viewName="view_worldbank_indicator_data_country_summary">select indicator_id as embedded_id_indicator_id,country_id as embedded_id_country_id,max(indicator_name) as indicator_name,max(source_id) as source_id,max(source_value) as source_value,max(source_note) as source_note,max(source_organization) as source_organization,min(year_date) as start_year, max(year_date) as end_year,count(*) as data_point,max(topics::text) as topics from world_bank_data left join (select * from indicator_element inner join (select topic_topics_hjid,string_agg(distinct value_,';') as topics,count(*) as number_of_topics from topic group by topic_topics_hjid) AS derivedTable on indicator_element.topics_indicator_element_hjid=derivedTable.topic_topics_hjid) as indicators on indicator_id = indicators.id where data_value!='' and data_value!='0' and year_date is not null and source_organization is not null and source_organization !='' group by indicator_id,country_id</createView>
</changeSet>
<changeSet author="pether" id="1414872417007-264" >
<dropView viewName="view_riksdagen_committee_decision_type_summary" />
<dropView viewName="view_riksdagen_committee_decision_type_org_summary" />
<dropView viewName="view_riksdagen_committee_ballot_decision_summary" />
<dropView viewName="view_riksdagen_committee_ballot_decision_party_summary" />
<dropView viewName="view_riksdagen_committee_ballot_decision_politician_summary" />
<dropView viewName="view_riksdagen_committee_decisions"/>
<dropView viewName="view_riksdagen_vote_data_ballot_politician_summary_weekly" />
<dropView viewName="view_riksdagen_vote_data_ballot_politician_summary_monthly" />
<dropView viewName="view_riksdagen_vote_data_ballot_politician_summary_annual" />
<dropView viewName="view_riksdagen_vote_data_ballot_politician_summary_daily" />
<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" />
<dropView viewName="view_riksdagen_vote_data_ballot_party_summary_weekly" />
<dropView viewName="view_riksdagen_vote_data_ballot_party_summary_monthly" />
<dropView viewName="view_riksdagen_vote_data_ballot_party_summary_annual" />
<dropView viewName="view_riksdagen_vote_data_ballot_party_summary_daily" />
<dropView viewName="view_riksdagen_vote_data_ballot_politician_summary" />
<dropView viewName="view_riksdagen_vote_data_ballot_party_summary" />
<dropView viewName="view_riksdagen_vote_data_ballot_summary"/>
<sql>create materialized view view_riksdagen_vote_data_ballot_summary as 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</sql>
<sql>create materialized view view_riksdagen_vote_data_ballot_party_summary as 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</sql>
<sql>create materialized view view_riksdagen_vote_data_ballot_politician_summary as select vote_data.embedded_id_ballot_id,embedded_id_intressent_id,vote_data.embedded_id_concern,vote_data.embedded_id_issue,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</sql>
<sql>create materialized view view_riksdagen_vote_data_ballot_summary_daily as 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,
sum( case when approved then 1 else 0 end) as approved_ballots,
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</sql>
<sql>create materialized view view_riksdagen_vote_data_ballot_summary_weekly as select date(date_trunc('week', vote_date)) as 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,
sum(approved_ballots) as approved_ballots,
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((100.0 * sum(approved_ballots)) / sum(number_ballots),2) as percentage_approved,
round(avg(percentage_approved),2) as avg_percentage_approved
from view_riksdagen_vote_data_ballot_summary_daily group by 1</sql>
<sql>create materialized view view_riksdagen_vote_data_ballot_summary_monthly as select date(date_trunc('month', vote_date)) as 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,
sum(approved_ballots) as approved_ballots,
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((100.0 * sum(approved_ballots)) / sum(number_ballots),2) as percentage_approved,
round(avg(percentage_approved),2) as avg_percentage_approved
from view_riksdagen_vote_data_ballot_summary_daily group by 1</sql>
<sql>create materialized view view_riksdagen_vote_data_ballot_summary_annual as select date(date_trunc('year', vote_date)) as 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,
sum(approved_ballots) as approved_ballots,
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((100.0 * sum(approved_ballots)) / sum(number_ballots),2) as percentage_approved,
round(avg(percentage_approved),2) as avg_percentage_approved
from view_riksdagen_vote_data_ballot_summary_daily group by 1</sql>
<sql>create materialized view view_riksdagen_vote_data_ballot_party_summary_daily as select vote_date as embedded_id_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</sql>
<sql>create materialized view view_riksdagen_vote_data_ballot_party_summary_weekly as select date(date_trunc('week', embedded_id_vote_date)) as embedded_id_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</sql>
<sql>create materialized view view_riksdagen_vote_data_ballot_party_summary_monthly as select date(date_trunc('month', embedded_id_vote_date)) as embedded_id_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</sql>
<sql>create materialized view view_riksdagen_vote_data_ballot_party_summary_annual as select date(date_trunc('year', embedded_id_vote_date)) as embedded_id_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</sql>
<sql>create materialized view view_riksdagen_vote_data_ballot_politician_summary_daily as select vote_date as embedded_id_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(party) as 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</sql>
<sql>create materialized view view_riksdagen_vote_data_ballot_politician_summary_weekly as select date(date_trunc('week', embedded_id_vote_date)) as embedded_id_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(party) as 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</sql>
<sql>create materialized view view_riksdagen_vote_data_ballot_politician_summary_monthly as select date(date_trunc('month', embedded_id_vote_date)) as embedded_id_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(party) as 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</sql>
<sql>create materialized view view_riksdagen_vote_data_ballot_politician_summary_annual as select date(date_trunc('year', embedded_id_vote_date)) as embedded_id_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(party) as 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</sql>
<sql>create materialized view view_riksdagen_committee_decisions as select id as embedded_id_id,title,header,hangar_id as embedded_id_hangar_id,label as committee_report,committee_document_data.rm,end_number,issue_number as embedded_id_issue_nummer,upper(org) as org,created_date,public_date,committee_proposal_url_xml,decision_type,ballot_id,against_proposal_parties,against_proposal_number,winner from committee_proposal_data LEFT JOIN committee_document_data ON committee_document_data.label=committee_report and committee_proposal_data.rm=committee_document_data.rm</sql>
<sql>create materialized view view_riksdagen_committee_ballot_decision_summary as select embedded_id_id,embedded_id_issue,embedded_id_hangar_id,committee_report,view_riksdagen_committee_decisions.rm,title,header as sub_title,end_number,upper(org) as org,created_date,public_date,ballot_id,decision_type,against_proposal_parties,against_proposal_number,winner,embedded_id_concern,ballot_type,label,vote_date,avg_born_year,total_votes,yes_votes,no_votes,abstain_votes,absent_votes,approved,no_winner,percentage_yes,percentage_no,percentage_absent,percentage_abstain,percentage_male from view_riksdagen_committee_decisions left join view_riksdagen_vote_data_ballot_summary on ballot_id = embedded_id_ballot_id and view_riksdagen_committee_decisions.rm=view_riksdagen_vote_data_ballot_summary.rm and view_riksdagen_committee_decisions.embedded_id_issue_nummer ::text=view_riksdagen_vote_data_ballot_summary.embedded_id_issue where decision_type='röstning' and ballot_id is not null and embedded_id_ballot_id is not null</sql>
<sql>create materialized view view_riksdagen_committee_ballot_decision_party_summary as select embedded_id_party,embedded_id_id,embedded_id_issue,embedded_id_hangar_id,committee_report,view_riksdagen_committee_decisions.rm,title,header as sub_title,end_number,upper(org) as org,created_date,public_date,ballot_id,decision_type,against_proposal_parties,against_proposal_number,winner,embedded_id_concern,ballot_type,label,vote_date,avg_born_year,total_votes,yes_votes,no_votes,abstain_votes,absent_votes,approved,no_winner,percentage_yes,percentage_no,percentage_absent,percentage_abstain,percentage_male,party_avg_born_year,party_total_votes,party_yes_votes,party_no_votes,party_abstain_votes,party_absent_votes,party_approved,party_no_winner,party_percentage_yes,party_percentage_no,party_percentage_absent,party_percentage_abstain,party_percentage_male from view_riksdagen_committee_decisions left join view_riksdagen_vote_data_ballot_party_summary on ballot_id = embedded_id_ballot_id and view_riksdagen_committee_decisions.rm=view_riksdagen_vote_data_ballot_party_summary.rm and view_riksdagen_committee_decisions.embedded_id_issue_nummer::text=view_riksdagen_vote_data_ballot_party_summary.embedded_id_issue where decision_type='röstning' and ballot_id is not null and embedded_id_ballot_id is not null</sql>
<sql>create materialized view view_riksdagen_committee_ballot_decision_politician_summary as select embedded_id_intressent_id,party,embedded_id_id,embedded_id_issue,embedded_id_hangar_id,first_name,last_name,born_year,vote,won,rebel,committee_report,view_riksdagen_committee_decisions.rm,title,header as sub_title,end_number,upper(org) as org,created_date,public_date,ballot_id,decision_type,against_proposal_parties,against_proposal_number,winner,embedded_id_concern,ballot_type,label,vote_date,avg_born_year,total_votes,yes_votes,no_votes,abstain_votes,absent_votes,approved,no_winner,percentage_yes,percentage_no,percentage_absent,percentage_abstain,percentage_male,party_avg_born_year,party_total_votes,party_yes_votes,party_no_votes,party_abstain_votes,party_absent_votes,party_approved,party_no_winner,party_percentage_yes,party_percentage_no,party_percentage_absent,party_percentage_abstain,party_percentage_male from view_riksdagen_committee_decisions left join view_riksdagen_vote_data_ballot_politician_summary on ballot_id = embedded_id_ballot_id and view_riksdagen_committee_decisions.rm=view_riksdagen_vote_data_ballot_politician_summary.rm and view_riksdagen_committee_decisions.embedded_id_issue_nummer::text=view_riksdagen_vote_data_ballot_politician_summary.embedded_id_issue where decision_type='röstning' and ballot_id is not null and embedded_id_ballot_id is not null</sql>
<sql>create materialized view view_riksdagen_committee_decision_type_summary as select upper(decision_type) as embedded_id_decision_type,created_date as embedded_id_decision_date,count(*) as total,sum( case when winner='utskottet' then 1 else 0 end) as committee_winner,sum( case when winner like'reservation%' then 1 else 0 end) as reservation_winner from view_riksdagen_committee_decisions group by upper(decision_type),created_date order by created_date</sql>
<sql>create materialized view view_riksdagen_committee_decision_type_org_summary as select upper(decision_type) as embedded_id_decision_type,upper(org) as embedded_id_org,created_date as embedded_id_decision_date,count(*) as total,sum( case when winner='utskottet' then 1 else 0 end) as committee_winner,sum( case when winner like'reservation%' then 1 else 0 end) as reservation_winner from view_riksdagen_committee_decisions group by upper(decision_type),created_date,upper(org)</sql>
</changeSet>
</databaseChangeLog>