开启ONLY_FULL_GROUP_BY时MySQL分组求和查询1055报错解决

原SQL存在两个问题触发1055错误：

1. SELECT里的portfolio_id字段没有加入GROUP BY子句，不符合ONLY_FULL_GROUP_BY的校验规则：分组查询中，SELECT返回的非聚合字段必须全在GROUP BY列表中。
2. 窗口函数直接写在聚合层时，计算的数据源是原始明细表的行，不是按日聚合后的结果，不仅过不了校验，算出来的滚动1年分红值也会和预期不符。

SELECT
    portfolio_id,
    sum_shares_owned_value_usd,
    total_dividends_received_usd,
    SUM(total_dividends_received_usd) OVER (
        ORDER BY date_utc
        RANGE BETWEEN INTERVAL 1 YEAR PRECEDING AND CURRENT ROW
    ) AS dividends_usd_ttm,
    date_utc
FROM (
    -- 第一层先按日期聚合，得到每日的聚合指标
    SELECT
        portfolio_id,
        SUM(shares_owned_value_usd) AS sum_shares_owned_value_usd,
        date_utc,
        SUM(dividends_received_value_usd) AS total_dividends_received_usd
    FROM stock_portfolio_historical_values
    WHERE portfolio_id = 5
    GROUP BY portfolio_id, date_utc
) AS daily_agg
ORDER BY date_utc ASC;

• 内层子查询先完成按日聚合：把同日期下不同ticker的持仓市值、分红加总，同时把portfolio_id加入GROUP BY，完全满足严格模式的校验要求，因为WHERE条件已经固定查询portfolio_id=5的数据，加这个字段不会改变聚合粒度。
• 外层查询基于已经聚合好的日度数据计算窗口函数，滚动统计过去1年的累计分红，和给出的预期结果完全匹配：比如2022-06-08的TTM分红为2022-03-05的45加当日45，合计90。
• 该语句无需调整sql_mode，在ONLY_FULL_GROUP_BY开启状态下可正常执行。

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