如何编写SQL查询，计算满足时间规则的各玩家按姓名统计的每日销售额

请使用以下两张表，按玩家姓名统计每位玩家的每日销售额，其中当日凌晨4点前发生的每笔交易需计入前一日的销售额。例如：9月10日凌晨3点发生的销售需计入9月9日的销售额数据中。

实现方案 #

核心逻辑是通过时间偏移修正统计日期，再分组聚合即可。以常见的表结构（玩家表player含player_id、player_name字段，交易表transaction含player_id、trans_time交易时间、amount交易金额字段）为例，不同数据库的实现代码如下：

MySQL 版本

SELECT
    p.player_name,
    DATE(t.trans_time - INTERVAL 4 HOUR) AS statistics_date,
    SUM(t.amount) AS daily_sales_amount
FROM `transaction` t
INNER JOIN player p ON t.player_id = p.player_id
GROUP BY p.player_name, statistics_date
ORDER BY statistics_date, p.player_name;

PostgreSQL 版本

SELECT
    p.player_name,
    DATE(t.trans_time - INTERVAL '4 hours') AS statistics_date,
    SUM(t.amount) AS daily_sales_amount
FROM "transaction" t
INNER JOIN player p ON t.player_id = p.player_id
GROUP BY p.player_name, statistics_date
ORDER BY statistics_date, p.player_name;

SQL Server 版本

SELECT
    p.player_name,
    CAST(DATEADD(HOUR, -4, t.trans_time) AS DATE) AS statistics_date,
    SUM(t.amount) AS daily_sales_amount
FROM [transaction] t
INNER JOIN player p ON t.player_id = p.player_id
GROUP BY p.player_name, CAST(DATEADD(HOUR, -4, t.trans_time) AS DATE)
ORDER BY statistics_date, p.player_name;

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