在PostgreSQL中按月透视用户收入

CREATE TABLE user_income (
  user_id INT,
  income DECIMAL(10, 2),
  income_date DATE
);

INSERT INTO user_income (user_id, income, income_date)
VALUES
  (1, 1000.00, '2022-01-01'),
  (2, 1500.00, '2022-01-01'),
  (1, 800.00, '2022-02-01'),
  (2, 1200.00, '2022-02-01');

SELECT
  user_id,
  SUM(CASE WHEN EXTRACT(MONTH FROM income_date) = 1 THEN income ELSE 0 END) AS january_income,
  SUM(CASE WHEN EXTRACT(MONTH FROM income_date) = 2 THEN income ELSE 0 END) AS february_income,
  -- 继续添加其他月份的透视列
FROM
  user_income
GROUP BY
  user_id;