显示PostgreSQL中多个列的每月总计 [英] Showing Monthly Totals from Multiple Columns in PostgreSQL

问题描述

我希望查看今年与去年相比在给定日期范围内每月所见的首次检查患者的数量，并将其与相同日期范围内每月所见的患者总数进行比较。

I am looking to see the number of firstexam patients seen per month for a given date range this year vs last year, and compare that to the total number of patients seen per month for the same date range.

我能够按以下步骤设置初诊患者：

I am able to set up the firstexam patients as follows:

select case EXTRACT(month FROM patient_info.firstexam)
    when 1 then '01 - January'
    when 2 then '02 - February'
    when 3 then '03 - March'
    when 4 then '04 - April'
    when 5 then '05 - May'
    when 6 then '06 - June'
    when 7 then '07 - July'
    when 8 then '08 - August'
    when 9 then '09 - September' 
    when 10 then '10 - October'
    when 11 then '11 - November'
    when 12 then '12 - December'
end as month,
sum(case when patient_info.firstexam >= '2013-01-01' AND patient_info.firstexam <= '2013-12-31' then 1 else 0 end) thisyear,
sum(case when patient_info.firstexam >= '2012-01-01' AND patient_info.firstexam <= '2012-12-31' then 1 else 0 end) lastyear

from patient_info WHERE (patient_info.firstexam >= '2013-01-01' AND patient_info.firstexam <= '2013-12-31' OR patient_info.firstexam >= '2012-01-01' AND patient_info.firstexam <= '2012-12-31') 

GROUP BY month
ORDER BY month

这给了我三列：月，今年，去年。

This gives me three columns: month, thisyear, lastyear.

请注意：我在月份名称前输入了数字，因为否则我无法使月份按时间顺序显示。

Please note: I entered numerical month before month name because I could not get the months to appear in chronological order otherwise. Any hints to not show the number before the month would be appreciated.

我想为总患者增加两列-例如：

I would like to add two more columns for total patients - something like:

select case EXTRACT(month FROM patient_info.lastexam)
    when 1 then '01 - January'
    when 2 then '02 - February'
    when 3 then '03 - March'
    when 4 then '04 - April'
    when 5 then '05 - May'
    when 6 then '06 - June'
    when 7 then '07 - July'
    when 8 then '08 - August'
    when 9 then '09 - September' 
    when 10 then '10 - October'
    when 11 then '11 - November'
    when 12 then '12 - December'
end as month,
sum(case when patient_info.lastexam >= '2013-01-01' AND patient_info.lastexam <= '2013-12-31' then 1 else 0 end) totalthisyear,
sum(case when patient_info.lastexam >= '2012-01-01' AND patient_info.lastexam <= '2012-12-31' then 1 else 0 end) totallastyear

from patient_info WHERE (patient_info.lastexam >= '2013-01-01' AND patient_info.lastexam <= '2013-12-31' OR patient_info.lastexam >= '2012-01-01' AND patient_info.lastexam <= '2012-12-31') 

GROUP BY month
ORDER BY month

，结果分为5列：月，今年，totalthisyear，去年，totallastyear

with the results in 5 columns: month, thisyear, totalthisyear, lastyear, totallastyear

，但是似乎无法弄清楚该怎么做。列的顺序并不重要。
可能是：月份，今年，去年，totalthisyear，totallastyear

but can't seem to figure out exactly how this could be done. The order of the columns is not important. It could be: month, thisyear, lastyear, totalthisyear, totallastyear

推荐答案

SQL小提琴

select
    to_char(('2012-' || m || '-01')::date, 'Month'),
    thisyear, lastyear, totalthisyear, totallastyear
from (
    select
        extract(month from m) as m,
        sum(case
            when firstexam between '2013-01-01' and '2013-12-31' then firstexam_count
            else 0 end
        ) as thisyear,
        sum(case
            when firstexam between '2012-01-01' and '2012-12-31' then firstexam_count
            else 0 end
        ) as lastyear,
        sum(case
            when lastexam between '2013-01-01' and '2013-12-31' then lastexam_count
            else 0 end
        ) as totalthisyear,
        sum(case
            when lastexam between '2012-01-01' and '2012-12-31' then lastexam_count
            else 0 end
        ) as totallastyear
    from
        generate_series (
            '2012-01-01'::date, '2013-12-31', '1 month'
        ) g(m)
        left join (
            select count(*) as firstexam_count, date_trunc('month', firstexam) as firstexam
            from patient_info
            where firstexam between '2012-01-01' and '2013-12-31'
            group by 2
        ) pif on firstexam = m
        left join (
            select count(*) as lastexam_count, date_trunc('month', lastexam) as lastexam
            from patient_info
            where lastexam between '2012-01-01' and '2013-12-31'
            group by 2
        ) pil on lastexam = m
    group by 1
) s
order by m

这篇关于显示PostgreSQL中多个列的每月总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！