PostgreSQL从一个特殊的日期开始计数

Question

我有以下代码，

SELECT      
years_month_count.day_date,
years_month_count.year_date,
years_month_count.month_date,
years_month_count.no_of_customers_day,
sum(years_month_count.no_of_customers_day) OVER (PARTITION BY year_date ORDER BY day_date) AS no_of_customers_ytd
FROM (
SELECT 
DATE(date) as day_date,
DATE_PART('year',date) as year_date,
DATE_PART('month',date) as month_date,
count(prepare_first_buyer.person_id) as no_of_customers_day
FROM (
SELECT 
DATE(bestelldatum),
person_id,
ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY person_id)
FROM ani.bestellung
) prepare_first_buyer
WHERE row_number=1
GROUP BY DATE(date), DATE_PART('year',date),DATE_PART('month',date)
ORDER BY DATE(date), DATE_PART('year',date),DATE_PART('month',date)
) years_month_count

输出如下所示:

no_of_customers_dayno_of_Customers_ytd2017-04-0462017-04-0510…………………………

分步演示:db<>fiddle

SELECT
*,
SUM(value) OVER (PARTITION BY                             -- 4
date_part('year',                                     -- 3
the_date - interval '5 months'                    -- 2
)     
)
FROM t
WHERE date_part('month', the_date)::int NOT BETWEEN 4 AND 5   -- 1

1. 过滤您不需要的所有日期。在您的示例中，所有月份为4和5的日期
2. 将日期范围开始移到年初。在你的例子中，你必须把year-06-01移到year-01-01，所以你需要减去5 months。因为你的日期范围从来没有超过一年，所以你所有的相关数据现在都是同一年，这是一个很好的组标准
3. 提取year部分作为组/分区标准
4. 按此标准计算