Use the result of CASE expression in window function

Question

The following SQL script generates 2022 year calendar with months, quarters, half-years and days count for each calendar interval:

with clndr as
(
select
    gs_day::date
from
    generate_series('2022-01-01'::timestamp, current_timestamp, '1 day') as gs_day
),
draft as
(
select 
     gs_day::date as gs_day
    ,count(*) over() as full_year_days_cnt
    ,date_trunc( 'month', gs_day)::date as mnth
    ,count(*) over(partition by to_char(gs_day::date , 'MM')) as full_month_days_cnt
    ,case
        when to_char(gs_day::date , 'MM') in ('01', '02', '03') then 1
        when to_char(gs_day::date , 'MM') in ('04', '05', '06') then 2
        when to_char(gs_day::date , 'MM') in ('07', '08', '09') then 3
        when to_char(gs_day::date , 'MM') in ('10', '11', '12') then 4
     end as year_quarter
    ,case 
        when to_char(gs_day::date, 'MM') in ('01', '02', '03', '04', '05', '06') then 1
        when to_char(gs_day::date, 'MM') in ('07', '08', '09', '10', '11', '12') then 2
        else null
     end as year_half
from
    generate_series('2022-01-01'::timestamp, current_timestamp, '1 day') as gs_day
)

select
     gs_day
    ,full_year_days_cnt
    ,mnth
    ,full_month_days_cnt
    ,year_quarter
    ,count(*) over(partition by year_quarter) as year_quarter_days_cnt
    ,year_half
    ,count(*) over(partition by year_half) as year_half_days_cnt
from
    draft

Unfortunately, I have to use cte "draft" to count "year_quarter_days_cnt" and "year_half_days_cnt", because I didn't find any way to refer to "year_quarter" and "year_half" aliases in select statement of draft cte.

Is there any possibilities to avoid cte or joins to get the same result using count window function?

well... try it and find out? – user14063792468 Nov 26 '22 at 21:22 — user14063792468, Nov 26 '22 at 21:22

Erwin Brandstetter · Accepted Answer · 2022-11-27T09:08:43.520

You cannot refer to a column alias ("output" column name) in the same SELECT list, only to input column names. So you would repeat (spell out again) a CASE statement in a window function to avoid a subquery or CTE.

Luckily, you don't seem to need these convoluted CASE expressions to begin with ...

SELECT d::date AS the_day
     , count(*) OVER (PARTITION BY extract('year' FROM d)) AS full_year_days_cnt
     , date_trunc('month', d)::date AS month
     , count(*) OVER (PARTITION BY date_trunc('month', d)) AS full_month_days_cnt
     , extract('quarter' FROM d)::int AS year_quarter
     , count(*) OVER (PARTITION BY extract('year' FROM d), extract('quarter' FROM d)) AS year_quarter_days_cnt
     ,(extract('quarter' FROM d)::int + 1) / 2 AS year_half
     , count(*) OVER (PARTITION BY extract('year' FROM d), (extract('quarter' FROM d)::int + 1) / 2) AS year_half_days_cnt
FROM   generate_series('2022-01-01'::timestamp, LOCALTIMESTAMP, '1 day') d

This also works for any given start date, optionally spanning multiple years - as opposed to your original.

Your leading CTE clndr was dead freight.
The CTE draft can be optimized away, as requested.
I also simplified and/or improved the rest.

Instead of (PARTITION BY extract('year' FROM d), extract('quarter' FROM d) you might also use (PARTITION BY to_char(d, 'YYYY-Q')). But there is no equivalent simplification for year_half_days_cnt.

I also replaced CURRENT_TIMESTAMP with LOCALTIMESTAMP to avoid involving timestamptz a priori. Cleaner, faster. See:

Generating time series between two dates in PostgreSQL

Use the result of CASE expression in window function

1 Answers1