Snowflake中按城市计算三日滚动平均值的实现问题

需求 #

按城市分组，计算指定日期区间内的销售平均值，并生成起始日期_结束日期格式的日期范围字段。

表结构与测试数据 #

Create table sales_info(sales_date date, daily_sales float, Salesman String, City String);

alter session set DATE_INPUT_FORMAT='DD-MM-YYYY';

insert into sales_info values
('12-12-2021',12000.30,'Max','KCR'),
('12-12-2021',32.30,'Max','Crux'),
('12-12-2021',13000.30,'Max','Xray'),
('13-12-2021',14000.30,'Kyle','KCR'),
('13-12-2021',14000.30,'Kyle','Crux'),
('13-12-2021',99000.30,'Kyle','XRay'),
('14-12-2021',2340.30,'Peter','XRay'),
('14-12-2021',1200.30,'Peter','Crux'),
('14-12-2021',22000.30,'Peter','KCR'),
('15-12-2021',132000.30,'Remo','Crux'),
('15-12-2021',124000.30,'Rexy','KCR'),
('15-12-2021',120500.30,'Tom','Xray'),
('16-12-2021',122000.30,'Felis','Crux'),
('16-12-2021',120300.30,'Felis','KCR'),
('16-12-2021',120040.30,'Max','Xray'),
('17-12-2021',120005.30,'Rubert','KCR'),
('17-12-2021',120.30,'Travis','Crux'),
('18-12-2021',200.30,'Peter','XRay'),
('18-12-2021',200.30,'Peter','Crux'),
('18-12-2021',200.30,'Peter','KCR'),
('19-12-2021',200.30,'Peter','XRay'),
('19-12-2021',500.30,'Peter','KCR'),
('19-12-2021',500.30,'Peter','CRUX'),
('20-12-2021',200.30,'Peter','XRay'),
('20-12-2021',500.30,'Peter','KCR'),
('20-12-2021',500.30,'Peter','CRUX');

预期输出 #

Days_range              Avg_sales  items
12-12-2021_15-12-2021   somevalue  KCR
12-12-2021_15-12-2021   somevalue  Crux
12-12-2021_15-12-2021   somevalue  Xray
16-12-2021_19-12-2021   somevalue  KCR
16-12-2021_19-12-2021   somevalue  Crux
16-12-2021_19-12-2021   somevalue  Xray
20-12-2021_23-12-2021   somevalue  KCR
20-12-2021_23-12-2021   somevalue  Crux
20-12-2021_23-12-2021   somevalue  Xray

当前查询问题 #

你提供的查询存在以下问题：

• 字段名错误：表中存储城市的字段是City，但查询中使用了item，导致无法正确分组。
• 逻辑不符：使用基于行的窗口函数（rows between 2 PRECEDING and current row），是计算连续3行的滚动平均，而非按指定日期区间分组的平均。
• 未处理大小写：数据中城市名称存在大小写差异（如Crux和CRUX），会被当作不同分组。

原查询：

Select sales_date, 
       salesman,
       item,
       avg(daily_sales) over (partition by item order by sales_date rows between 2 PRECEDING and current row) 
as avg
from sales_info 
where item = 'KCR' or item = 'Crux' or item = 'XRay';

解决方案 #

以下SQL可以生成符合预期的结果，解决了日期区间生成、城市大小写统一、空区间显示的问题：

WITH date_ranges AS (
    -- 生成需要的日期区间（每4天一个区间，起始+3天为结束）
    SELECT 
        start_date,
        DATEADD(day, 3, start_date) AS end_date
    FROM (
        SELECT 
            (SELECT MIN(sales_date) FROM sales_info) + (4 * (n-1)) AS start_date
        FROM TABLE(GENERATOR(ROWCOUNT => 3)) t(n) -- 生成3个区间，可根据实际数据调整数量
    )
),
unified_cities AS (
    -- 统一城市名称大小写，避免分组错误
    SELECT DISTINCT UPPER(City) AS city FROM sales_info
),
range_city_combinations AS (
    -- 生成所有区间与城市的组合，确保每个区间每个城市都有记录
    SELECT 
        dr.start_date,
        dr.end_date,
        uc.city
    FROM date_ranges dr
    CROSS JOIN unified_cities uc
),
sales_avg AS (
    -- 按区间和城市计算平均销售额
    SELECT 
        rcc.start_date,
        rcc.end_date,
        rcc.city,
        AVG(si.daily_sales) AS avg_sales
    FROM range_city_combinations rcc
    LEFT JOIN sales_info si 
        ON UPPER(si.City) = rcc.city
        AND si.sales_date BETWEEN rcc.start_date AND rcc.end_date
    GROUP BY rcc.start_date, rcc.end_date, rcc.city
)
-- 格式化输出日期范围和结果
SELECT 
    CONCAT(TO_CHAR(start_date, 'DD-MM-YYYY'), '_', TO_CHAR(end_date, 'DD-MM-YYYY')) AS Days_range,
    ROUND(avg_sales, 2) AS Avg_sales,
    city AS items
FROM sales_avg
ORDER BY start_date, city;

代码说明 #

1. date_ranges：从最小销售日期开始，生成连续的4天区间（可根据需要调整ROWCOUNT参数生成更多区间）。
2. unified_cities：提取所有不重复的城市名称并统一为大写，解决大小写不一致的分组问题。
3. range_city_combinations：交叉连接区间和城市，保证即使某个区间内城市没有销售数据，也能显示该区间-城市的记录。
4. sales_avg：左连接销售数据，按区间和城市分组计算平均销售额。
5. 最后一步格式化日期区间字符串，保留两位小数的平均值，按区间和城市排序输出。

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