postgresql同比环比计算
时间: 2024-08-26 13:03:11 浏览: 41
在PostgreSQL中,计算同比(同比增长)和环比(月度或季度环比增长)通常涉及到日期范围分析和比较。这里是一个简单的例子:
1. **同比**(Year-over-Year Growth): 这通常是通过将当前时期的值与上一年同期的值进行比较来计算的。假设你有一个销售表(sales_table),包含`date`和`sales_amount`字段,你可以这样做:
```sql
SELECT
(current_period.sales_amount - previous_year_same_period.sales_amount) / previous_year_same_period.sales_amount AS yoy_growth
FROM
(SELECT date, sales_amount FROM sales_table WHERE date BETWEEN 'now()' AND date_trunc('year', now())) current_period,
(SELECT date, sales_amount FROM sales_table WHERE date BETWEEN date_trunc('year', now()) - INTERVAL '1 year' AND date_trunc('year', now() - INTERVAL '1 year') + INTERVAL '1 month' - '1 day') previous_year_same_period
GROUP BY current_period.date;
```
2. **环比**(Month-to-Month Growth 或 Quarter-to-Quarter Growth): 可以按月或季度来计算,例如,如果是按月环比,可以这样:
```sql
WITH monthly_sales AS (
SELECT
date,
sales_amount,
LAG(sales_amount, 1) OVER (ORDER BY date) AS last_month_sales
FROM
sales_table
WHERE
date >= now() - INTERVAL '1 month'
)
SELECT
sales_amount - last_month_sales AS mth_to_mth_growth
FROM
monthly_sales
ORDER BY
date;
```
对于季度环比,需要先确定每个季度的开始和结束日期,然后计算。