MySQL如何将月内所有日期替换为当月首日并汇总播放量？

解决方案 #

不同数据库的当月首日计算语法略有区别，以下是主流数据库的实现方案，同时修正了你原SQL的语法问题：原SQL中SELECT了未参与分组的plays_字段，不符合分组聚合的SQL规范，已移除冗余字段。

MySQL 实现

SELECT 
  film_title,
  DATE_FORMAT(period_day, '%Y-%m-01') AS first_day_of_month,
  SUM(plays_) AS accum_plays
FROM yaya_csv
GROUP BY film_title, first_day_of_month
ORDER BY film_title, first_day_of_month;

直接通过DATE_FORMAT固定日期的日部分为01，即可得到对应月份的首日，返回值为标准日期格式字符串，也可通过STR_TO_DATE转换为DATE类型。

PostgreSQL 实现

SELECT 
  film_title,
  DATE_TRUNC('month', period_day)::DATE AS first_day_of_month,
  SUM(plays_) AS accum_plays
FROM yaya_csv
GROUP BY film_title, first_day_of_month
ORDER BY film_title, first_day_of_month;

用DATE_TRUNC函数将日期截断到月份维度，再通过::DATE强转为纯日期类型，即可得到当月首日。

SQL Server 实现

SELECT 
  film_title,
  DATEFROMPARTS(YEAR(period_day), MONTH(period_day), 1) AS first_day_of_month,
  SUM(plays_) AS accum_plays
FROM yaya_csv
GROUP BY film_title, DATEFROMPARTS(YEAR(period_day), MONTH(period_day), 1)
ORDER BY film_title, first_day_of_month;

通过DATEFROMPARTS拼接年、月、固定值1，直接生成当月首日的日期类型值。

Hive/Spark SQL 大数据场景实现

SELECT 
  film_title,
  TRUNC(period_day, 'MM') AS first_day_of_month,
  SUM(plays_) AS accum_plays
FROM yaya_csv
GROUP BY film_title, TRUNC(period_day, 'MM')
ORDER BY film_title, first_day_of_month;

用TRUNC函数指定截断维度为月份，直接返回当月首日的日期值。

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