我需要顯示我們有多少活躍客戶以及年底。因此，我需要總是從上一年得到 last year_month。使用 PostgreSQL。
這是我的 SQL 以獲取每月 (year_month) 視圖的客戶群。
select *
from (
with data as (
select
a.brand,
a.d,
a.activations,
t.terminations,
a.activations-t.terminations count
from (select c.brand, dd.year_month d,
COALESCE(case when dd.year_month is not null then count(c.customer_number) else 0 end, 0) as activations
from generate_series(current_date - interval '8 years', current_date, '1 day') d
left join dim_date dd on dd."date" = d.d
left join r_contracts_report c on to_date(c.service_start_date, 'dd.mm.yyy') = d
where c.contract_status in ('aktiv', 'Kündigung vorgemerkt', 'gekündigt')
and c.contract in ('3048', '3049', '3050', '3055', '3056')
group by dd.year_month,
brand) a,
(select c.brand, dd.year_month d,
COALESCE(case when dd.year_month is not null then count(c.customer_number) else 0 end, 0) as terminations
from generate_series(current_date - interval '8 years', current_date, '1 day') d
left join dim_date dd on dd."date" = d.d
left join r_contracts_report c on to_date(c.termination_date, 'dd.mm.yyy') = d
where c.contract_status in ('aktiv', 'Kündigung vorgemerkt', 'gekündigt')
and c.contract in ('3048', '3049', '3050', '3055', '3056')
group by dd.year_month,
brand) t
where a.d = t.d
and a.brand = t.brand)
select
d.d year_month,
d.brand,
sum(count) over (order by d.d asc rows between unbounded preceding and current row) eop
from data d
where d.brand = '3'
) as foo
在“as foo”之后使用以下 where 子句，我得到了過去 12 個月的客戶群： WHERE year_month >= to_char ((current_date - INTERVAL '12 months'), 'YYYY-MM')
結果如下所示：
但我總是希望只有上一年的十二月。在這種情況下，它將是“2021-12”。
uj5u.com熱心網友回復：
...
where year_month = '2021-12'
或自動為上一年：
...
where year_month = (extract(year from current_date) - 1)::text || '-12'
但這是獲取這些資料的一種非常低效的方法。
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/526534.html
標籤：PostgreSQL