cny.sql

WITH t1(dt, year) AS (
    select dt, date_part('year', dt)::INTEGER
    from (
        select date '1980-02-16' UNION
        select date '1980-02-05'
    ) AS d(dt)
)
select dt as "日期",
       before_cny_in_jan_count as "节前（一月）",
       before_cny_in_feb_count as "节前（二月）",
       after_cny_in_jan_count  as "节后（一月）",
       after_cny_in_feb_count  as "节后（二月）"
from (
    select
        dt,
        CASE
            WHEN isempty(before_cny_in_jan)
            THEN 0
            ELSE upper(before_cny_in_jan) - lower(before_cny_in_jan)
        END,
        CASE
            WHEN isempty(before_cny_in_feb)
            THEN 0
            ELSE upper(before_cny_in_feb) - lower(before_cny_in_feb)
        END,
        CASE
            WHEN isempty(after_cny_in_jan)
            THEN 0
            ELSE upper(after_cny_in_jan) - lower(after_cny_in_jan)
        END,
        CASE
            WHEN isempty(after_cny_in_feb)
            THEN 0
            ELSE upper(after_cny_in_feb) - lower(after_cny_in_feb)
        END
    from (
        select dt,
               before_cny * jan,
               before_cny * feb,
               after_cny * jan,
               after_cny * feb
        from (
            select dt,
                   daterange(make_date(year, 1, 1), make_date(year, 2, 1)),
                   daterange(make_date(year, 2, 1), make_date(year, 3, 1)),
                   daterange(dt - 7, dt),
                   daterange(dt + 1, dt + 8)
            from t1
        ) AS t3(dt, jan, feb, before_cny, after_cny)
    ) AS t4(dt, before_cny_in_jan, before_cny_in_feb, after_cny_in_jan, after_cny_in_feb)
) AS t5(dt, before_cny_in_jan_count, before_cny_in_feb_count, after_cny_in_jan_count, after_cny_in_feb_count);