如何基于日度营收成本数据计算各季度利润

核心逻辑 #

• 先将每条日度数据的Date字段转换为「年份+季度」的分组标识
• 计算单日日利润：Revenue - Cost
• 按分组标识求和，得到各季度总利润，无数据的季度自动不会出现在结果中，符合需求。

全数据库兼容实现方案 #

不依赖数据库内置季度函数，所有SQL环境均可通用：

SELECT
    -- 拼接季度标识，格式为「XXXX年QX」
    CONCAT(
        YEAR(`Date`),
        '年Q',
        CASE
            WHEN MONTH(`Date`) BETWEEN 1 AND 3 THEN 1
            WHEN MONTH(`Date`) BETWEEN 4 AND 6 THEN 2
            WHEN MONTH(`Date`) BETWEEN 7 AND 9 THEN 3
            WHEN MONTH(`Date`) BETWEEN 10 AND 12 THEN 4
        END
    ) AS 季度,
    SUM(Revenue - Cost) AS 总利润
FROM
    替换为你的实际表名
GROUP BY
    YEAR(`Date`),
    CASE
        WHEN MONTH(`Date`) BETWEEN 1 AND 3 THEN 1
        WHEN MONTH(`Date`) BETWEEN 4 AND 6 THEN 2
        WHEN MONTH(`Date`) BETWEEN 7 AND 9 THEN 3
        WHEN MONTH(`Date`) BETWEEN 10 AND 12 THEN 4
    END
ORDER BY
    YEAR(`Date`), 季度;

主流数据库简化写法 #

MySQL #

SELECT
    CONCAT(YEAR(`Date`), '年Q', QUARTER(`Date`)) AS 季度,
    SUM(Revenue - Cost) AS 总利润
FROM 替换为你的实际表名
GROUP BY YEAR(`Date`), QUARTER(`Date`)
ORDER BY YEAR(`Date`), QUARTER(`Date`);

PostgreSQL #

SELECT
    EXTRACT(YEAR FROM "Date") || '年Q' || EXTRACT(QUARTER FROM "Date") AS 季度,
    SUM(Revenue - Cost) AS 总利润
FROM 替换为你的实际表名
GROUP BY EXTRACT(YEAR FROM "Date"), EXTRACT(QUARTER FROM "Date")
ORDER BY EXTRACT(YEAR FROM "Date"), EXTRACT(QUARTER FROM "Date");

SQL Server #

SELECT
    CAST(YEAR([Date]) AS VARCHAR) + '年Q' + CAST(DATEPART(QUARTER, [Date]) AS VARCHAR) AS 季度,
    SUM(Revenue - Cost) AS 总利润
FROM 替换为你的实际表名
GROUP BY YEAR([Date]), DATEPART(QUARTER, [Date])
ORDER BY YEAR([Date]), DATEPART(QUARTER, [Date]);

示例验证 #

使用题目给出的测试数据执行上述代码，输出结果如下，完全符合预期：

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