0

Here is a Postgres code I created, it works. Is there a way to code it in a more efficient way? My goal is to get how much periods a given date falls from 2014-03-01. One period is a half-year starting from March or September.

I updated this code below on 2022-05-18 at 10:19 UTC+2

select  date,
        dense_rank() over (order by half_year_mar_sep) as period_index

from
(
        select  date as date,
                case    when extract(month from date) = 12 then (extract(year from date) || '-09-01')
                        when extract(month from date) in (1, 2) then (extract(year from date) - 1 || '-09-01')
                        when extract(month from date) in (3, 4, 5) then (extract(year from date) || '-03-01')
                        when extract(month from date) in (6, 7, 8) then (extract(year from date) || '-03-01')
                        else extract(year from date) || '-09-01'
                end::date as half_year_mar_sep
        from
        (
                select  generate_series(date '2014-03-01', CURRENT_DATE, interval '1 day')::date as date
        ) s1
) s2

If I encapsulate the code above into select min(date), period_index from (<code above>) s3 group by 2 order by 1 then here is the result what I need:

result

andexte
  • 181
  • 6
  • 1
    It would help to see some example data, for instance dates and corresponding periods. A quick dirty method would be: `select round((current_date - '2014-03-01')/182.25, 2); 16.46` – Adrian Klaver May 17 '22 at 15:23
  • I amended my question with the result what I expect. Your code is much more simpler than mine, and it's almost perfect, but not 100% correct. E.g.: The period_index for today (2022-05-18) would be 17. – andexte May 18 '22 at 07:14
  • is the pic is what you want to get? if so that would be easy. – jian May 18 '22 at 07:17
  • I need all dates between 2014-03-01 and today where the period indexes are added to them. E.g.: All dates between 2014-03-01 and 2014-08-31 belong to 1 period index. – andexte May 18 '22 at 07:58

2 Answers2

1
WITH cte AS (
    SELECT
        date1::date,
        rank() OVER (ORDER BY date1)
    FROM generate_series(date '2014-03-01', CURRENT_DATE + interval '1' month, interval '6 month') g (date1)
),
cteall AS (
    SELECT
        all_date::date
    FROM
        generate_series(date '2014-03-01', CURRENT_DATE + interval '1' month, interval ' 1 day') s (all_date)
),
cte3 AS (
    SELECT
        *
    FROM
        cteall c1
        LEFT JOIN cte c2 ON date1 = all_date
),
cte4 AS (
    SELECT
        *,
        count(rank) OVER w AS ct_str
        FROM
            cte3
WINDOW w AS (ORDER BY all_date))
SELECT
    *,
    rank() OVER (PARTITION BY ct_str ORDER BY all_date) AS rank1,
        dense_rank() OVER (ORDER BY all_date) AS dense_rank1
        FROM
            cte4;

Hope it's not intimidating. personally I found cte is a good tool, since it make logic more clearly.
demo
useful link: How to do forward fill as a PL/PGSQL function

If some column don't need, you can simple replace * with the columns you want.

jian
  • 4,119
  • 1
  • 17
  • 32
  • Your solution does exactly what I wanted. I appreciate your time, I upvoted your answer, but it doesn't seem simpler than my solution but that was my original question. :) Based on your idea, I made another code (added as an answer) which is also not a simpler one. Maybe I was hoping for a tricky function or something. – andexte May 18 '22 at 09:31
1

Based on @Mark's answer I wrote this code below, but it's not simpler than the original code.

select  s.date,
        m.period_index
from
(
        select  date::date as half_year_start,
                rank() over (order by date) as period_index,
                coalesce(lead(date::date, 1) over (), CURRENT_DATE) as following_half_year_start
        from    generate_series(date '2014-03-01', CURRENT_DATE + interval '1' month, interval '6 month') as date
) m

left join
(
        select  generate_series(date '2014-03-01', CURRENT_DATE, interval '1 day')::date as date
) s
on      s.date between m.half_year_start and m.following_half_year_start
;
andexte
  • 181
  • 6