如何在SQL查询中添加日期列获取月度数据及计算指定客户月度收款

问题1：如何在SQL查询中添加包含日期的列，以获取月度数据？ #

不同SQL数据库的日期格式化函数略有差异，核心是将日期转换为年份-月份格式的列，方便按月份筛选或聚合，以下是主流数据库的实现方式：

• MySQL/MariaDB：使用DATE_FORMAT函数

SELECT
  custID,
  received_amount,
  transaction_date,
  DATE_FORMAT(transaction_date, '%Y-%m') AS month_year -- 生成类似"2024-05"的月度列
FROM customer_payments;

• PostgreSQL：使用TO_CHAR函数

SELECT
  custID,
  received_amount,
  transaction_date,
  TO_CHAR(transaction_date, 'YYYY-MM') AS month_year
FROM customer_payments;

• SQL Server：可使用FORMAT或DATEFROMPARTS（后者生成当月第一天，更适合聚合）

SELECT
  custID,
  received_amount,
  transaction_date,
  FORMAT(transaction_date, 'yyyy-MM') AS month_year,
  DATEFROMPARTS(YEAR(transaction_date), MONTH(transaction_date), 1) AS month_start
FROM customer_payments;

如果需要按月份聚合数据，直接在GROUP BY子句中使用上述格式化后的列或日期截断函数即可。

问题2：计算指定5k个客户每月的已收款项金额 #

假设两个表定义如下：

• customer_payments：包含custID、transaction_date、received_amount（50k客户的收款记录）
• target_customers：仅包含需统计的custID（5k个目标客户）

通过关联两张表，按客户和月份分组求和即可得到结果：

基础版（仅统计有收款记录的月份） #

SELECT
  tc.custID,
  DATE_FORMAT(cp.transaction_date, '%Y-%m') AS month_year, -- 替换为对应数据库的格式化函数
  SUM(cp.received_amount) AS monthly_total_received
FROM target_customers tc
JOIN customer_payments cp ON tc.custID = cp.custID
GROUP BY tc.custID, DATE_FORMAT(cp.transaction_date, '%Y-%m')
ORDER BY tc.custID, month_year;

进阶版（包含无收款记录的月份，显示0） #

如果需要保留所有目标客户的所有月份（即使当月没有收款），需生成连续月份维度表，再用LEFT JOIN关联：

-- 以MySQL为例，生成2023-01至2024-06的连续月份
WITH months AS (
  SELECT DATE_FORMAT('2023-01-01' + INTERVAL (m-1) MONTH, '%Y-%m') AS month_year
  FROM (SELECT 1 m UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
        UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
        UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16 UNION SELECT 17 UNION SELECT 18) AS months
)
SELECT
  tc.custID,
  m.month_year,
  COALESCE(SUM(cp.received_amount), 0) AS monthly_total_received
FROM target_customers tc
CROSS JOIN months m
LEFT JOIN customer_payments cp 
  ON tc.custID = cp.custID 
  AND DATE_FORMAT(cp.transaction_date, '%Y-%m') = m.month_year
GROUP BY tc.custID, m.month_year
ORDER BY tc.custID, m.month_year;

内容的提问来源于stack exchange，提问作者Amar