Oracle SQL按周末、工作日拆分统计月度营收的实现问题

方案1：使用条件聚合（推荐，性能最优） #

这是实现该需求最简洁高效的方式，仅需扫描一次表即可完成计算，不需要多次查询或关联：

SELECT
    enrollment_month,
    SUM(CASE WHEN lesson_day IN ('Saturday','Sunday') THEN total_revenue ELSE 0 END) AS weekend_revenue,
    SUM(CASE WHEN lesson_day IN ('Monday','Tuesday','Wednesday','Thursday','Friday') THEN total_revenue ELSE 0 END) AS weekday_revenue
FROM gracieMusicFact
GROUP BY enrollment_month;

逻辑说明：通过CASE WHEN在聚合时分别对符合周末、工作日条件的营收单独求和，直接得到两列的统计结果。

方案2：子查询关联（适配现有两段查询的写法） #

如果你希望基于已写好的两个独立查询实现合并，可以将两个查询作为子查询，通过月份字段关联得到横向拼接的结果：

SELECT
    COALESCE(wd.enrollment_month, we.enrollment_month) AS enrollment_month,
    COALESCE(we.weekend_revenue, 0) AS weekend_revenue,
    COALESCE(wd.weekday_revenue, 0) AS weekday_revenue
FROM (
    SELECT enrollment_month, sum(total_revenue) as weekday_revenue 
    FROM gracieMusicFact
    WHERE lesson_day IN ('Monday','Tuesday','Wednesday','Thursday','Friday')
    GROUP BY enrollment_month
) wd
FULL OUTER JOIN (
    SELECT enrollment_month, sum(total_revenue) as weekend_revenue 
    FROM gracieMusicFact
    WHERE lesson_day IN ('Saturday','Sunday')
    GROUP BY enrollment_month
) we ON wd.enrollment_month = we.enrollment_month;

逻辑说明：这里用FULL OUTER JOIN是为了避免某个月仅有工作日/仅有周末营收时数据丢失，COALESCE用于处理关联空值时的默认值。

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