Amazon Redshift-按类别获取每周明智的销售计数

问题描述

我的每日销售数据如下.我正在尝试按销售周分组.

I have daily sales data as below. I am trying to group by sales week wise.

我尝试使用group by给出该期间的总计数，如何修改查询以获取如下所示的输出:

I tried using group by that gives total count for the period, how could I modify the query to obtain an output as shown below:

预期输出:

Last N days,Count,Category

Last 7 days,225,Category_1
Last 14 days,136,Category_2
Last 7 days,172,Category_1
Last 14 days,321,Category_2

输入数据:

Date,*Sales*,Category

01-06-2018,10,Category_1
01-06-2018,19,Category_1
03-06-2018,3,Category_1
04-06-2018,13,Category_1
05-06-2018,10,Category_1
06-06-2018,14,Category_1
07-06-2018,20,Category_1
08-06-2018,49,Category_1
09-06-2018,5,Category_1
10-06-2018,4,Category_1 
11-06-2018,20,Category_1
12-06-2018,49,Category_1
13-06-2018,5,Category_1
14-06-2018,4,Category_1
01-06-2018,10,Category_1
01-06-2018,34,Category_2
03-06-2018,22,Category_2
04-06-2018,13,Category_2
05-06-2018,1,Category_2
06-06-2018,9,Category_2
07-06-2018,60,Category_2
08-06-2018,9,Category_2
09-06-2018,35,Category_2
10-06-2018,41,Category_2 
11-06-2018,2,Category_2
12-06-2018,9,Category_2
13-06-2018,35,Category_2
14-06-2018,41,Category_2

尝试查询:

select count(*),Category from sales
group by Category;

我正在使用Amazon redshift DB.

I am using Amazon redshift DB.

任何人都可以帮助，谢谢..

Could anyone help,thanks..

自06/19起更新:

select Category,
sum(case when date >= dateadd(day, -7, CURRENT_DATE) then count(sales) else 0 end) as count_07,
sum(case when date >= dateadd(day, -14, CURRENT_DATE) then count(sales) else 0 end) as count_14
from sales
group by Category

推荐答案

我将这些值放在单独的列中.像这样:

I would put the values in separate columns. Something like this:

select category, 
       sum(case when date >= dateadd(day, -7, CURRENT_DATE) then sales else 0 end) as count_07,
       sum(case when date >= dateadd(day, -14, CURRENT_DATE) then sales else 0 end) as count_14
from sales s
group by category;

这篇关于Amazon Redshift-按类别获取每周明智的销售计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！