sql - 连续n周计算

Question

我有一个由 3 列组成的表：产品、周、售出的单位。我正在尝试制作一个表格来显示连续 4 周的最佳销售额。

我尝试了几种不同的窗口分析功能，但似乎无法得到我需要的东西。

这是输入。

PRODUCT WEEK SALES 
ICE     1   17
ICE     2   20
ICE     3   17
ICE     4   10
ICE     5   12
ICE     6   13
ICE     7    2
ICE     8   25
WATER   1   25
WATER   2   20
WATER   3   9
WATER   4   7
WATER   5   24
WATER   6   16
WATER   7   10
WATER   8   16
SODA    1   22
SODA    2   2
SODA    3   10
SODA    4   24
SODA    5   9
SODA    6   20
SODA    7   9
SODA    8   21

这是输出。

PRODUCT BEST_4_WK   BEST_4_WK_SALE
ICE     1-4            64
WATER   5-8            66
SODA    3-6            63

我想我需要使用 LAG()（对销售额求和）、MIN()、MAX() + 转换为字符串 + 连接（数周）。我尝试了几个小时，但无法得到它。感谢您的帮助！

标签： sqlgoogle-bigqueryranking-functionsanalytical

Answer 1

试试这个

with cte as
(
select 'ICE' as product,     1 as week,   17 as sales union all
select 'ICE' as product,     2 as week,   20 as sales union all
select 'ICE' as product,     3 as week,   17 as sales union all
select 'ICE' as product,     4 as week,   10 as sales union all
select 'ICE' as product,     5 as week,   12 as sales union all
select 'ICE' as product,     6 as week,   13 as sales union all
select 'ICE' as product,     7 as week,    2 as sales union all
select 'ICE' as product,     8 as week,   25 as sales union all
select 'WATER' as product,   1 as week,   25 as sales union all
select 'WATER' as product,   2 as week,   20 as sales union all
select 'WATER' as product,   3 as week,   9  as sales union all
select 'WATER' as product,   4 as week,   7  as sales union all
select 'WATER' as product,   5 as week,   24 as sales union all
select 'WATER' as product,   6 as week,   16 as sales union all
select 'WATER' as product,   7 as week,   10 as sales union all
select 'WATER' as product,   8 as week,   16 as sales union all
select 'SODA' as product,    1 as week,   22 as sales union all
select 'SODA' as product,    2 as week,   2  as sales union all
select 'SODA' as product,    3 as week,   10 as sales union all
select 'SODA' as product,    4 as week,   24 as sales union all
select 'SODA' as product,    5 as week,   9  as sales union all
select 'SODA' as product,    6 as week,   20 as sales union all
select 'SODA' as product,    7 as week,   9  as sales union all
select 'SODA' as product,    8 as week,   21 as sales
),cte2 as
(
select  *, 
        sum(sales) over ( partition by product order by week rows between current row and 3 following ) as summed
from cte
)
select  product,
        cast(week as string)||'-'||cast(week + 3 as string) as week,
        summed
from cte2
where 1 = 1
qualify row_number() over (partition by product order by summed desc)  = 1

OP