INSERT语句使用COUNT函数触发1140错误的解决方案咨询

错误原因 #

你遇到的报错触发原因符合MySQL的only_full_group_by模式约束：

Error Code: 1140. In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'sakila.s_rental.rental_id'; this is incompatible with sql_mode=only_full_group_by.

简单来说就是SQL中使用了COUNT()聚合函数，但SELECT列表中包含rental_id这类非聚合字段，同时没有指定GROUP BY分组维度，MySQL无法确认聚合统计的维度，因此抛出错误。
除此之外你原SQL的表关联逻辑存在严重缺失：sakila库中rental表和film表没有直接关联字段，需要通过inventory表中转关联，否则会产生笛卡尔积，返回大量错误的重复数据。

可行方案 #

根据雪花模型事实表的常见设计逻辑，fact_rental为租赁明细事实表，每条记录对应一次独立的租赁行为，不需要在插入时做聚合计算：

• count_rentals：每条租赁记录对应1次租赁，直接赋值为1即可，后续统计总租赁次数时聚合该字段求和即可
• count_returns：如果本条租赁已经归还（return_date不为空）则赋值为1，未归还则赋值为0，后续聚合求和即可得到总归还次数

修正后SQL代码 #

INSERT INTO sakila_snowflake.fact_rental (
    rental_id,
    rental_last_update,
    customer_key,
    staff_key,
    film_key,
    store_key,
    rental_date_key,
    return_date_key,
    count_returns,
    count_rentals,
    rental_duration,
    dollar_amount)
SELECT
    s_rental.rental_id,
    s_rental.last_update,
    s_customer.customer_id,
    s_staff.staff_id,
    s_film.film_id,
    s_store.store_id,
    s_rental.rental_date,
    s_rental.return_date,
    -- 归还标记：已归还为1，未归还为0
    IF(s_rental.return_date IS NOT NULL, 1, 0),
    -- 租赁标记：每条记录对应1次租赁
    1,
    s_rental.return_date - s_rental.rental_date,
    (s_rental.return_date - s_rental.rental_date)*s_film.rental_rate
FROM
    sakila.rental as s_rental
    -- 补全inventory关联，打通rental和film的关联关系
    JOIN sakila.inventory as s_inventory ON s_rental.inventory_id = s_inventory.inventory_id
    JOIN sakila.customer as s_customer ON s_rental.customer_id = s_customer.customer_id
    JOIN sakila.staff as s_staff ON s_rental.staff_id = s_staff.staff_id
    JOIN sakila.film as s_film ON s_inventory.film_id = s_film.film_id
    JOIN sakila.store as s_store ON s_staff.store_id = s_store.store_id;

若需要按影片维度生成汇总事实表 #

如果你的fact_rental是按影片维度聚合的汇总表，不需要单条租赁明细，可调整为按film_id分组聚合，SQL示例如下：

INSERT INTO sakila_snowflake.fact_rental (
    film_key,
    store_key,
    count_returns,
    count_rentals
    -- 其他你需要的汇总字段
)
SELECT
    s_film.film_id,
    s_store.store_id,
    Count(s_rental.return_date),
    Count(s_rental.rental_date)
    -- 其他汇总计算逻辑
FROM
    sakila.rental as s_rental
    JOIN sakila.inventory as s_inventory ON s_rental.inventory_id = s_inventory.inventory_id
    JOIN sakila.staff as s_staff ON s_rental.staff_id = s_staff.staff_id
    JOIN sakila.film as s_film ON s_inventory.film_id = s_film.film_id
    JOIN sakila.store as s_store ON s_staff.store_id = s_store.store_id
-- 按影片、门店维度分组聚合
GROUP BY s_film.film_id, s_store.store_id;

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