pgsql计算同环比
时间: 2023-07-27 19:20:45 浏览: 62
计算同比和环比需要使用到SQL语句中的窗口函数,具体的计算方式如下:
同比计算:
```sql
SELECT
DATE_TRUNC('month', date) AS month,
COUNT(*) AS cnt,
COUNT(*) / LAG(COUNT(*)) OVER (ORDER BY DATE_TRUNC('month', date)) - 1 AS mom_growth_rate
FROM
your_table
WHERE
date BETWEEN '2021-01-01' AND '2021-12-31'
GROUP BY
DATE_TRUNC('month', date)
ORDER BY
DATE_TRUNC('month', date);
```
其中,`DATE_TRUNC` 函数用于将日期截断到月份,`LAG` 函数用于获取上一月份的数据,计算同比增长率。
环比计算:
```sql
SELECT
DATE_TRUNC('month', date) AS month,
COUNT(*) AS cnt,
COUNT(*) / LAG(COUNT(*)) OVER (PARTITION BY DATE_TRUNC('month', date) - INTERVAL '1 month') - 1 AS mom_growth_rate
FROM
your_table
WHERE
date BETWEEN '2021-01-01' AND '2021-12-31'
GROUP BY
DATE_TRUNC('month', date)
ORDER BY
DATE_TRUNC('month', date);
```
其中,`PARTITION BY` 子句用于按月份进行分区,`- INTERVAL '1 month'` 用于获取上一月份的数据,计算环比增长率。