过去n周的sql销售额总和,包括销售额为0的一周

d6kp6zgx  于 2021-08-09  发布在  Java
关注(0)|答案(4)|浏览(479)

我想显示每件商品每周(本周加上前3周)4周销售额的总和。
原始数据

+--------+--------+----------+
| Item   | Week   | sales    |
+--------+--------+----------+
|   a    |      1 |     10   |
|   a    |      2 |     10   |
|   a    |      4 |     10   |
|   a    |      7 |     10   |
|   a    |      8 |     10   |
|   a    |     10 |     10   |
|   b    |      1 |     10   |
|   b    |      2 |     10   |
|   b    |      4 |     10   |
|   b    |      7 |     10   |
|   b    |      8 |     10   |
|   b    |     10 |     10   |
+--------+--------+----------+

预期结果(以a项为例)

+------+------+------------------------------------------------------+
| Item | Week |                        sales                         |
+------+------+------------------------------------------------------+
| a    |    1 | 10                                                   |
| a    |    2 | 20                                                   |
| a    |    3 | 30                                                   |
| a    |    4 | 30                                                   |
| a    |    5 | 20(Note: sales of Week 5+Week 4+Week 3+Week 2)       |
| a    |    6 | 10                                                   |
| a    |    7 | 20[Note: 10(Week 7)+0(Week 6)+0(Week 5)+10 (Week 4)] |
| a    |    8 | 20                                                   |
| a    |    9 | 20                                                   |
| a    |   10 | 30(Note: sales of Week 10+Week 9+Week 8+Week 7)      |
+------+------+------------------------------------------------------+

我试着用

sum (sales) over (partition by item order by week row 3 preceding)

然而,它跳过了没有销售记录的一周,把所有的非零值加起来。例如:对于第7周,sum over将第7、4、2、1周的销售额相加,计算结果为40。有没有办法达到预期的效果?

34gzjxbg

34gzjxbg1#

SELECT week,Item ,sum(sales) WHERE (curweek - week < 4) group by week,Item
cedebl8k

cedebl8k2#

每周,如果你的table是table

SELECT sum(B.sales), A.week as curweek As totalsales
FROM table A, table B
WHERE curweek - B.week <4 
AND curweek - B.week >=0
GROUP BY curweek
egmofgnx

egmofgnx3#

由于问题没有提到rdbms,我在sql server中测试了下面的代码。下面的解决方案是针对sql server的
我正在做以下活动:
生成周、项的所有组合
对于组合,生成前4周的总和(销售额)
删除原始列表中不存在的周

DECLARE @table TABLE (Item CHAR(1), Week TINYINT, sales INT)

INSERT INTO @table
VALUES ('a', 1, 10), ('a', 2, 10), ('a', 4, 10), ('a', 7, 10), ('a', 8, 10), ('a', 10, 10), ('b', 1, 10), ('b', 2, 10), ('b', 4, 10), ('b', 7, 10), ('b', 8, 10), ('b', 10, 10);;

WITH CTE_ItemWeek
AS (
    SELECT Item, Week
    FROM (
        VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)
        ) AS t(week)
    CROSS JOIN (
        SELECT DISTINCT Item
        FROM @table
        ) A
    )
SELECT *
FROM (
    SELECT t1.Item, CASE 
            WHEN t2.Week IS NULL
                THEN LAG(t2.week) OVER (
                        PARTITION BY t1.Item ORDER BY t2.week
                        )
            ELSE t2.week
            END AS week, SUM(t2.sales) OVER (
            PARTITION BY t1.Item ORDER BY t1.week ROWS BETWEEN 3 preceding
                    AND CURRENT ROW
            ) AS total_sales
    FROM CTE_ItemWeek AS t1
    LEFT OUTER JOIN @table AS t2 ON t1.Item = t2.Item
        AND t1.week = t2.week
    ) AS t
WHERE week IS NOT NULL

结果集

+------+------+-------------+
| Item | week | total_sales |
+------+------+-------------+
| a    |    1 |          10 |
| a    |    2 |          20 |
| a    |    4 |          30 |
| a    |    7 |          20 |
| a    |    8 |          20 |
| a    |   10 |          30 |
| b    |    1 |          10 |
| b    |    2 |          20 |
| b    |    4 |          30 |
| b    |    7 |          20 |
| b    |    8 |          20 |
| b    |   10 |          30 |
+------+------+-------------+
7uzetpgm

7uzetpgm4#

如果您只需要查看现有周数的总和(因此不必填充缺少的周数),可以在标准sql中使用范围而不是行来实现这一点:

select item, 
       week,
       sum (sales) over (partition by item order by week range between 3 preceding and current row)
from the_table
order by item, week;

如果您需要以行的形式填写缺少的周,那么答案在很大程度上取决于所使用的dbms。在postgres中,您可以执行以下操作:

with items as (
  select distinct item
  from data
), all_weeks as (
  select i.item, g.week
  from items i
    cross join generate_series(1,10) as g(week)
)    
select t.item,
       t.week,
       sum (d.sales) over (partition by t.item order by t.week range between 3 preceding and current row)
from all_weeks t
  left join data d on d.item = t.item and d.week = t.week

对于其他dbms系统 generate_series() 呼叫可以替换为固定的周数:

select i.item, g.week
from items i
  cross join ( values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10) ) as g(week)

在线示例

相关问题