first_value() over (order by Something asc) 是否应该与 last_value() over (order by so

根据 first_value() over (order by Something asc) 和 last_value() over (order by Something desc) 的定义，它们并不一定相同。这是因为这两个窗口函数是基于不同的排序方式进行计算的。

SELECT date, sales, 
       first_value(sales) over (PARTITION BY EXTRACT(MONTH FROM date) 
                                ORDER BY date ASC) as first_day_sales
FROM sales;

SELECT date, sales, 
       last_value(sales) over (PARTITION BY EXTRACT(MONTH FROM date) 
                               ORDER BY date DESC) as last_day_sales
FROM sales;

sql
-- 创建销售表
CREATE TABLE sales (
    date DATE,
    sales INT
);
-- 插入示例数据
INSERT INTO sales (date, sales)
VALUES 
    ('2022-01-01', 100),
    ('2022-01-15', 200),
    ('2022-01-31', 150),
    ('2022-02-01', 300),
    ('2022-02-15', 250),
    ('2022-02-28', 180);
-- 使用first_value()函数计算每个月的第一天的销售额
SELECT date, sales, 
       first_value(sales) over (PARTITION BY EXTRACT(MONTH FROM date) 
                                ORDER BY date ASC) as first_day_sales
FROM sales;
-- 使用last_value()函数计算每个月的最后一天的销售额
SELECT date, sales, 
       last_value(sales) over (PARTITION BY EXTRACT(MONTH FROM date) 
                               ORDER BY date DESC) as last_day_sales
FROM sales;