service.data.impl/src/main/resources/db-changelog-1.3.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-251">
<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" /> <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>
<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>
<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,
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</createView>
<createView
viewName="view_riksdagen_vote_data_ballot_summary_weekly">select 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</createView>
<createView
viewName="view_riksdagen_vote_data_ballot_summary_monthly">select 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</createView>
<createView
viewName="view_riksdagen_vote_data_ballot_summary_annual">select 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</createView>
<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>
<createView
viewName="view_riksdagen_vote_data_ballot_party_summary_weekly">select date_trunc('week', vote_date) as 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) as 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) as 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_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) as 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) as 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) as 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>