在写SQL练习的时候，发现有神秘的力量在阻止窗口函数的分区： sum() over(partiton by） 谁在阻止partition?

项目场景：

提示：这里简述项目相关背景：

例如：项目场景：在写SQL练习的时候，发现有神秘的力量在阻止窗口函数的分区:
建表代码及数据如下：
CREATE TABLE test_window_orders (
order_id INT COMMENT ‘订单id’,
customer_id INT COMMENT ‘用户id’,
order_date DATE COMMENT ‘订单日期’,
order_total DECIMAL(10, 2) COMMENT ‘订单金额’,
product_id INT COMMENT ‘商品id’,
product_name VARCHAR(50) COMMENT ‘商品名称’,
product_price DECIMAL(10, 2) COMMENT ‘商品单价’
);

INSERT INTO test_window_orders VALUES
(1, 1, ‘2022-01-01’, 100.00, 1, ‘Product A’, 20.00),
(2, 1, ‘2022-01-02’, 50.00, 2, ‘Product B’, 25.00),
(3, 2, ‘2022-01-03’, 75.00, 3, ‘Product C’, 15.00),
(4, 2, ‘2022-01-04’, 200.00, 1, ‘Product A’, 20.00),
(5, 2, ‘2022-01-05’, 30.00, 2, ‘Product B’, 25.00),
(6, 3, ‘2022-01-06’, 90.00, 3, ‘Product C’, 15.00),
(7, 3, ‘2022-01-07’, 40.00, 1, ‘Product A’, 20.00),
(8, 4, ‘2022-01-08’, 150.00, 2, ‘Product B’, 25.00),
(9, 4, ‘2022-01-09’, 60.00, 3, ‘Product C’, 15.00),
(10, 5, ‘2022-01-10’, 50.00, 1, ‘Product A’, 20.00),
(11, 5, ‘2022-01-11’, 100.00, 2, ‘Product B’, 25.00),
(12, 5, ‘2022-01-12’, 45.00, 3, ‘Product C’, 15.00);
INSERT INTO test_window_orders VALUES(13, 5, ‘2022-01-01’, 45.00, 3, ‘Product C’, 15.00);

---

问题描述

提示：这里描述项目中遇到的问题：
此时一条需求出现：
– 在数据表中增加1列，计算各个商品在每一天的销量占该商品总销量的比例

with a as (select *,
       order_total/product_price as day_sold #计算每天的销量
       from test_window_orders)
select  *,
        day_sold /sum(day_sold)  over (
        		partition by product_id  #按照product_id进行分区
        		order by order_date 
       			rows  between  unbounded  preceding and  unbounded  following
        ) bb # 每天的销售量/产品总销售量
from a;

发现sum () over() 中的partition by 并未对id进行分区
为了更方便的查看写了以下代码

with a as (
                        select *,
                                round(order_total/product_price ,2) day_sold
                                 from test_window_orders
)
select  *,
        sum(day_sold)  over (
        						partition by product_id  
        						order by order_date 
        						rows  between  unbounded  preceding and  unbounded  following
        						) bb,
        sum(day_sold)  over (
        						partition by product_id  
        						order by order_date 
        						rows  between  unbounded  preceding and  unbounded  following
        						) aa
from a;

运行结果如下：

---

神奇的一幕发生了！！！！，bb无法实现分区，但aa居然能准确分区，是什么样的力量在阻止第一条sum()的分区操作！！！！！！！

原因分析：

经过排查，语法并无问题，猜想是with 引导的中间表可能无法进行分区，于是写成子查询：
select *, sum(day_sold) over (partition by product_id order by order_date rows between unbounded preceding and unbounded following ) aa from (select *, order_total/product_price day_sold from test_window_orders ) as a;
结果是一样的，无法分区

---

解决方案：

法一：创建新表

将中间表创建出来后能正常分区

create table t_test as select *, order_total/product_price day_sold from test_window_orders;
select *, sum(day_sold) over (partition by product_id order by order_date rows between unbounded preceding and unbounded following) aa from t_test;

法二：将sum放在子查询里即可

with temp as (
            select *,
                   order_total/product_price a,
                   sum(order_total/product_price)  over (partition by product_id  order by order_date rows  between  unbounded  preceding and  unbounded  following) b
            from test_window_orders
)
select order_id,
       customer_id,
       order_date,
       order_total,
       product_id,
       product_name,
       product_price,
       round(a/b,2)
from temp;

求问一下大佬，此处的临时表为什么不能进行对指定列的分区！！！！！！！！！！！！！！！