postgresql语句实现同比环比

以下是postgresql语句实现同比环比的方法：

1. 同比

SELECT
  date_part('year', current_date) AS year,
  sum(sales) AS current_year_sales,
  sum(sales) OVER (ORDER BY date_trunc('month', sale_date)) AS monthly_sales
FROM
  sales
WHERE
  date_part('year', sale_date) = date_part('year', current_date)
GROUP BY
  date_part('year', current_date)
ORDER BY
  date_part('year', current_date)

2. 环比

SELECT
  date_trunc('month', sale_date) AS month,
  sum(sales) AS current_month_sales,
  sum(sales) OVER (ORDER BY date_trunc('month', sale_date) ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS last_month_sales
FROM
  sales
WHERE
 date_trunc('year', sale_date) = date_trunc('year', current_date)
GROUP BY
  date_trunc('month', sale_date)
ORDER BY
  date_trunc('month', sale_date)